Difference between reorg-rebuild and index re-create in Sybase ASE Could someone please explain what the main differences are between reorg-rebuilds and index re-creation in Sybase ASE?

Reorg rebuild vs drop/create index

reorg does not perform sort.
reorg uses the index to retrieve rows while rebuilding the table.
reorg rebuils is minimally logged(should be faster than creating clustered index).

Reorg usage

All reorgs for indexes can only run on DOL tables.
Reorg for tables can run on APL and DOL.
Cannot run on corrupt or suspect index.
Cannot run on text or image chain.
Cannot run within a transaction.

Rebuilding indexes using reorg (reorg rebuild tablename indexname)

Not necessary to set 'select into'.
Less free space required.
Dump tran can be done after rebuilding index using reorg.
Only available for DOL tables.
Works in small transaction; de-allocates pages once copied.
Only the index pages are rebuilt, data pages untouched, not locked.
Only a small number of index pages are locked at any given time.
Estimate time to complete : approx 2-3 times of 'select count(*) from tablename(indexname)'.
If commannd interupted, index is consistent.
If command reissued, starts afresh but does not rebuild those parts of index that already has the desired space usage and page clustering.


Which NoSQL database(s) should be used for advertising platform? What NoSQL database or combination of databases best meets these criteria?

Very fast writes and reads
Persistent data that won't get lost in a crash
Plays nice with PHP
Works well in the cloud (EC2)
High availability
Preferably open-source
The data needs to be able to be manipulated in a way to show statistics reports (sums, ranges, groupings, etc)

EDIT
Would a document database such as couch give me the following features?

Very fast writes and reads
Persistent data that won't get lost in a crash
Plays nice with PHP
Works well in the cloud (EC2)
High availability
The ability to manipulate data to show statistics reports (sums, ranges, groupings, etc)

EDIT
If you were me, would you choose couch, mongo or riak as the NoSQL database for me to use on my PHP system knowing that these are my primary goals:

Very fast writes and reads
Persistent data that won't get lost in a crash
Works well in the cloud (EC2)
High availability

I'm examining non-traditional datastores for my environment and am having some trouble deciding which way I should go here.

Either one of the two edits provided above would've been a better match for the question at hand, allowing for better answers and probing questions. It's a shame that this wasn't caught in time.

To be very fast, the database should only use memory and not disc since disc operations usually takes much longer time. But then your data aren't persistent in case of a crash. What you could do is assynchronous disk operations, by that most of your writes will be persistent but it's not guaranteed that the last few writes are persistent. If this is okey for you, you can use Redis. See also Redis - The Hacker's Database.

If you really care for persistent data, you should use a database that have been tested and used for years. Then PostgreSQL is a good choice.

To achive high availability, you need a distributed system with multiple nodes, so they can tolerate that at least one node goes down. Then I would use Riak.

The question is why do you need NoSQL? That word means almost nothing. I would recommend you to go with a traditional RDBMS like PostgreSQL until that can't handle your problem. It will be much easier to handle for you and it's much more flexible.

What you can do to improve the speed is to use caching. Then you can use Varnish Cash infront of your server, that will improve your speed a lot.

See also What are the differences between NoSQL and a traditional RDBMS?


How Much Resources Does the BLACKHOLE Storage Engine Consume？ I'm going to add a MySQL Relay Slave which use BLACKHOLE Storage Engine.

The relay slave will just filter some databases binlog to downstream slave.

Our MySQL Master generates about 15G a day in binlogs.

How much CPU and Memory will be enough for this use case?

If a relay slave (a.k.a. Distribution Master) is using nothing but the BLACKHOLE storage engine for application tables, you really should not have to tune anything for MyISAM or InnoDB since there are no data or index pages to cache.

You will have a nominal amount of disk I/O (mostly reads) since the .frm file for every BLACKHOLE is still accessed for metadata purposes. This same behavior also happens to MEMORY tables. I wrote a post about this. I helped the person asking the question realize that dropping and creating MEMORY tables created disk I/O.

I presume you are not going to drop and add tables all that often since the relay slave needs to remain all BLACKHOLE. Nevertheless, CPU and Memory should not be a big factor on a relay slave. You should be concerned with the following on the Relay Slave:

Sufficient Disk Space for Binary and Relay Logs
Fast Enough Disk for Writing
Setting sync_binlog=1 in my.cnf for flushing binlogs thoroughly
Rotation of Binary Logs (setting a proper expire_logs_days)
Avoid replicating the following:

storage-engine specific ALTER TABLE statement that BLACKHOLE can't work with
ALTER TABLE statements not accepted by BLACKHOLE, InnoDB, and MyISAM
creating tables with ENGINE=InnoDB or ENGINE=MyISAM because you want all tables in the relay slave to remain BLACKHOLE.

I have addressed this scenario before

Aug 20, 2012 :  How to use MySQL Server 5.6 for GeoIP replication?
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?

UPDATE 2014-08-11 11:47 EDT

Additional Clarification : A pure Relay Slave (Distribution Master) should never have MyISAM and InnoDB tables present. Suppose you run CREATE TABLE (...) ENGINE=InnoDB; on the Master. The Relay Slave will attempt to create the InnoDB table on the Relay Slave. If the Relay Slave has skip-innodb configured, it could possibly end up as a MyISAM table. You want to avoid that because it will increase disk I/O and InnoDB or MyISAM activity.

If the Relay Slave has both skip-innodb and default-storage-engine=BLACKHOLE configured, you would have to run a create table with identifying the storage engine (CREATE TABLE (...);).


Auto-Inc gaps - Id value explodes We have a big issue in production environment, many tables were created with Int Auto-Inc Primary Key.

Adding to the fact that our insert mechanizes includes using Insert Ignore/Insert.. On Duplicate Update we have reached the Int.MaxValue In a few tables.

I am considering my options and would like to consult:

Switching to innodb_autoinc_lock_mode = 0
Alerting to BigInt in all tables auto-inc column
Switching all our application inserts to  Select + Insert/Update

I am considering option 1 as this is an On-Premise DB and the migration work here is very costly.

As far as I understand from my researching of using innodb_autoinc_lock_mode=0 is that it as somewhat of a performance downgrade when handling concurrent inserts. I am planning a benchmark to understand the performance difference, what do you guys think about this option?

I call this "burning ids".  I think it happens with all flavors of INSERT, including INSERT IGNORE, IODKU, REPLACE, etc.

Here is a workaround:  http://mysql.rjweb.org/doc.php/staging_table#normalization

That describes how to use 2 SQLs to "normalize" a batch of names and get their ids in an efficient way, and without burning ids.  If you have only a single name to insert/lookup, it is still 2 sqls.

INSERT allocates as many ids as it might need, then burns the ones it did not need.

I think the autoinc_mode is global, and hurts performance.
Sure, BIGINT will work, but I hate to use 8 bytes.
And, yeah, my recommendation is similar to your #3.  But look closely; there may be some useful differences, especially if you can batch things.


Stop SQL Server 2012 From Highlighting Implicitly Passed Temp Tables "Invalid Object" I tend to use Temp Tables to pass large amounts of data from one stored procedure into another stored procedure. However, something that's always annoyed me is that, because it's implicitly passed/shared with the session, the sub stored procedure has no knowledge of it and constantly highlights table/column references as "Invalid Object".

Is there any way to "force" knowledge of a Temp Table's schema into a stored procedure?

--Yes, this is awful.  Quick and dirty pseudo code.
create procedure usp_myProc
(
    create table #temp
    (
        id int
        ,data int
    )

    insert into #temp(id, data)
    values(1, 1)

    exec usp_mySubProc;
)

create procedure usp_mySubProc
(
    --#temp and Data will both be highlighted as Invalid Objects
    --Because I'm updating the table, a Table Valued Parameter won't work
    while(1)
    (
        update
            #temp
        set
            Data = 1
    )
)

Is there any way to "force" knowledge of a Temp Table's schema into a Stored Proc?

You could pass the data using a Table-Valued Parameter instead.  Or you could try something like this:

create proc usp_myProc
as
begin
    create table #temp
    (
        id int
        ,data int
    )

    exec usp_mySubProc;
end
go

create proc usp_mySubProc
as
begin

    --table creation will be skipped at runtime
    if 1=0 create table #temp(id int, data int)

    update
        #temp
    set
        data = 1

end
go


MySQL: Overlay modifications on original data while indexing on a field that itself has modifications I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct errors in the original data entry, and view the overall composite dataset (changes overlaid on original data), without making any modifications to the table of original_data.

My fields are a mix of int, varchar, and datetime; nothing bizarre.

The system currently accomplishes this with the following:

  A table original_data (which is never modified). This is indexed
  on a datetime field.
  A table modifications, which includes all fields that
  original_data has. Each row in modifications references the primary
  key of the row in original_data that is modified.
  A view mods_overlay that joins original_data to modifications on
  a key, displaying modified data (where it exists) in place of original
  data. I'm using CASE WHEN statements to return fields from
  modifications where they exists for a particular row, else return the
  field from original_data.

So far, so good! The problem is, I have millions of rows, and one of the fields I need to modify is the datetime field on which original_data is indexed. As soon as my mods_overlay view overlays this datetime field, I can no longer efficiently select my data on a specific period of time; runtime for select statements increases from a few seconds to 30+ minutes. MySQL does not support indexing for views, nor does it support materialized views, either of which could help here.

One workaround: I could implement mods_overlay as a table that is pre-computed overnight, but then new modifications cannot be seen until the next day, and I'm not sure if this will be acceptable.

Are there other ways to solve this problem without making significant tooling changes?

Stated differently: is there a best practice in MySQL for providing a way to overlay a set of modifications on a set of original data (in a table that is never modified), while indexing on a field that can itself have modifications?

Very few users use VIEWs.

The typical approach is to ALTER the table to make the desired schema changes.

pt-online-schema-change is the tool of choice if ALGORITHM=INPLACE is not available for the desired change.


Can I tell mariadb which query plan to use for a given query? I have a query which gets different query plans over time and one of them takes less then a minute to execute, the other one takes days. Can I tell the db to use a given plan (taken from explain or using inline instructions) so that the good plan is always used or at least the catastrophical one gets always avoided? The good plan has only ref joins while the bad one also has index and eq_ref and a different ordering.

There are al lot of passibilities to force mariadb

for example:

Forcing Join Order
Forcing Usage of a Specific Index for the WHERE Clause
USE INDEX: Use a Limited Set of Indexes
IGNORE INDEX: Don't Use a Particular Index
FORCE INDEX: Forcing an Index

how they work and how to use it

https://mariadb.com/kb/en/index-hints-how-to-force-query-plans/


How to reduce table partition timing for existing table in SQL Server Repost from: https://stackoverflow.com/questions/48169487/how-to-reduce-table-partition-timing-for-existing-table-in-sql-server

I have a problem with table partition, I am working in a very big database, It is around 2 terabyte. So we planned to split the large tables into different partitions at last year. On that time we were divided a table into two partitons. One partition was located in primary file group and another one was located in secondary file group named as PartitionFG1. So we got lots of benefits from this activity like index maintenance and performance.

And then we have planned to create another one partition for the same table for this upcoming year records. So we have created a new secondary file group and we have altered the partition scheme to use new secondary file group named as PartitionFG2. So we have alter the partition function to split the range, here is the problem. Here the new range value split and moves the data into new partition but not into the new file group. For example table’s max PK value is 1000, so I used split range value as 1000. So it should not be taken any time to move data to new partition, because new partition will contains 0 records only.

Now I am going describe the scenario by sequence.

Table Creation

CREATE TABLE Tbl_3rdParatition(PK NUMERIC(18,0) identity(1,1),Line Varchar(100))

CREATE CLUSTERED INDEX CidxTbl_3rdParatition ON Tbl_3rdParatition (pk ASC)

Insert record into table

DECLARE @I INT DECLARE @CNT INT SET @I=1 SET @CNT = 1000

WHILE (@I< = @CNT)
BEGIN
  INSERT INTO Tbl_3rdParatition (Line) VALUES ('Primary')
  SET @I=@I+1
END

Now we have inserted 1000 records into the table. We can verify the table’s row and partition by below query.

SELECT p.partition_number, fg.name, p.rows
FROM sys.partitions p
INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id
INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id
WHERE p.object_id = OBJECT_ID('Tbl_3rdParatition')

So it ensures primary file group contains all records of the table. Now I am going to split the table into 2 partitions.

CREATE PARTITION FUNCTION PF_Tbl_3rdParatition(NUMERIC(18,0)) AS RANGE LEFT
FOR VALUES(500);

CREATE PARTITION SCHEME PS_Tbl_3rdParatition AS PARTITION PF_Tbl_3rdParatition TO ([PRIMARY],[SWPPartitionFG1])

CREATE UNIQUE CLUSTERED INDEX CidxTbl_3rdParatition ON dbo.Tbl_3rdParatition(PK) WITH(DROP_EXISTING = ON)ON PS_Tbl_3rdParatition(PK) ;

Now it is divided into 2 partitions. It took some time to move 500 records to SWPPartitionFG1.

Now I am going to create a new partition on another file group.

ALTER PARTITION SCHEME PS_Tbl_3rdParatition
NEXT USED [SWPPartitionFG2]

ALTER PARTITION FUNCTION PF_Tbl_3rdParatition()
SPLIT RANGE (1000)

ALTER INDEX [CidxTbl_3rdParatition] ON [dbo].[Tbl_3rdParatition ] REBUILD  WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1)

As of above query, 3rd partition should have 0 records. That is correct. But 3rd partition should be stored in SWPPartitionFG2 correct? But second partition’s data fully moved into SWPPartitionFG2. And the 3rd partition is allocated in SWPPartitionFG1 it is wrong!. So it takes too much of time to transfer the data from FG1 to FG2.

I desired to store the 3rd partition data in third file group (SWPPartitionFG2). Then only it will not take large time in partitioning process. For this reason we want lots of time to create a new partition. Our client will not give that much of down time for us.

Our Actual table size is 300 GB. 2nd partitions SWPPartitionFG1 File group contains 200 GB of data. So it requires 2:30 hrs time to move the data From FG1 to FG2. Please help me reduce the time in this activity.

ALTER PARTITION SCHEME PS_Tbl_3rdParatition  NEXT USED
  [SWPPartitionFG2]

  ALTER PARTITION FUNCTION PF_Tbl_3rdParatition()  SPLIT RANGE (1000)

  ALTER INDEX [CidxTbl_3rdParatition] ON [dbo].[Tbl_3rdParatition ]
  REBUILD  WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1)

Why are you rebuilding the clustered index after creating the new partition?

When you SPLIT a RANGE LEFT function, the partition containing the new boundary is split. The new partition on SWPPartitionFG2 is created to the left of the SWPPartitionFG1 partition. The new partition on SWPPartitionFG2 becomes partition 2 and the original SWPPartitionFG1 partition is renumbered as partition 3. Existing rows in the spilt partition that are less than or equal to the new boundary are moved into the new partition during the process. This is a very expensive operation, requiring about 4 times the logging of normal DML. That is why one should plan to split only empty partitions, or at least ensure no rows must be moved.

It would be best to use a RANGE RIGHT function here so that incremental boundaries are created to the right of the SPLIT partition. Create a new RANGE RIGHT partition function and scheme and rebuild indexes specifying the new scheme with the 'DROP_EXISTING = ON` option. I would expect this to take much less time than the current operation and, going forward, the SPLIT to create incremental partition boundaries will be a fast meta-data only operation.

CREATE PARTITION FUNCTION PF_Tbl_3rdParatition_Right(NUMERIC(18,0)) AS RANGE LEFT
FOR VALUES(500,1000);

CREATE PARTITION SCHEME PS_Tbl_3rdParatition_Right AS
    PARTITION PF_Tbl_3rdParatition_Right TO ([PRIMARY],[SWPPartitionFG1],[SWPPartitionFG2]);

CREATE UNIQUE CLUSTERED INDEX CidxTbl_3rdParatition ON dbo.Tbl_3rdParatition(PK)
WITH(DROP_EXISTING = ON)
ON PS_Tbl_3rdParatition_Right(PK);

See Table Partitioning Best Practices for more information.


How to apply a patch in PostgreSQL on Windows? How to apply a patch in PostgreSQL? I found an advice, but it seems to be suited for *NIX only. My PostgreSQL (9.3.1 EDIT: 9.3.4 now) was installed from an exe installer on Windows XP (32 bit), so I didn't find my case in the advice.

I'm not talking about minor version upgrade now - I can download the installer through Stack Builder, and there is already a well answered question on what to do with it. But what can I do if I need something that is not available through the StackBuilder, or even without an executable installer (in my case, a patch distributed only in a .patch file)?

This answer applies to modifying the PostgreSQL source code by applying a "diff" or "patch". It's not about installing minor version updates; to do that, just download and run the installer.

To alter the PostgreSQL server its self or its procedural language runtimes, you will generally need to recompile PostgreSQL from scratch.

On Windows that's a bit of a rough job. The postgresql documentation covers the process, but it doesn't really deal with the dependencies etc.

I've written some tools and documentation to make it easier, but they don't yet compile all the dependencies like OpenSSL, so the resulting binaries aren't as feature-complete as the stock installer. They're also not guaranteed to be binary-compatible with extensions.

If you just need to patch an extension that's a little bit easier, though far from trivial. I spent some time a while ago figuring out how to build PostgreSQL extensions on Windows without recompiling all of PostgreSQL. A similar approach might work for recompiling things like pg_dump and pg_restore but I have not tested this.

Add-ons, clients and utilities like PostGIS, PgRouting, PgAdmin-III, psqlODBC, pgJDBC, psycopg2, the Ruby Pg gem, DBD::Pg, PgPool, etc etc etc are all compiled separately using different procedures. You'll want to look up the tool in question for details.

Frankly, if you're doing any kind of development work on PostgreSQL, I don't recommend Windows as a platform.


Cannot drop database with logical replication I cannot drop database having logical replication SUBSCRIPTION, when i try to dropdb i get an error saying that the database contains a subscription but when i try to drop the SUBSCRIPTION i get an error saying the replication slot does not exist.

Check out the fine documentation:

  If this fails, either because the remote host is not reachable or because the remote replication slot cannot be dropped or does not exist or never existed, the DROP SUBSCRIPTION command will fail. To proceed in this situation, disassociate the subscription from the replication slot by executing ALTER SUBSCRIPTION ... SET (slot_name = NONE).


What can be used with SQL Server 2014 native tools, for Business Inteligence? We're trying to achieve better reports using native tools from SQL Server 2014.

I'm a DBA, and I don't know so much about c#, c++ and etc.

I know also, that we have Analysis services, SQL Server Data tools, but, long story short, programmers here where I work don't care about anything. They don't want to learn.

Is there a way to help my company, being a DBA ? I love to learn but I have some doubts about what service I can use to achieve reports with graphics, and those reports that bosses love.

If this is a bad question I can delete it with no problem.

My past job, we had an awesome application that uses views and procedures to ( I helped them with views and procedures and query tunning, but not with Visual Basic code ), in real time, update graphics and etc on a web page ( it was awesome ) but we can make it here, because as i said, they don't know and don't have the motivation to learn.

If SQL Server 2016 is on your road-map it provides significantly improved SSRS visuals. This should address the "pretty" factor of reports while also incorporating Mobile support and a few additional out-of-the-box charts/graphics/indicators for users. Report Builder is still available for the elusive "power user" to create their own reports.

The "Power" suite of applications (Power View, Power Map) can be incorporated into Excel and SharePoint to mixed degrees and effects and are free depending on version(s). PowerBI is the evolution, combination and refinement of these tools.

PowerBI is a Microsoft technology that works with a variety of data sources with strong emphasis on both "pretty" and actual insight. This is another tool that has placed emphasis on ease of use to help reduce IT load and specialization.

There are plenty of competing non Microsoft solutions that will undoubtedly work with SQL Server 2014.

How to implement? Show a pretty screen cap to senior management and money may free up? Never under-estimate the value of a prototype. Or note the fringe benefits that may become paramount investment points later: SQL Server 2016 offers increased performance, security... oh and they also have some nifty reporting that's included in the license costs.


SQL Server Backup transaction error Hi I am attempting to backup the transaction log file to setup mirroring in SQL Server

I execute

BACKUP LOG CUSTOMER TO DISK ='K:\JonDB\CUSTOMER.trn' WITH INIT
GO

And I receive

Processed 6587361 pages for database 'CUSTOMER', file 'CUSTOMER' on file 1.
Processed 0 pages for database 'CUSTOMER', file 'CUSTOMER_log' on file 1.
Processed 6 pages for database 'CUSTOMER', file 'CUSTOMER_log2' on file 1.
BACKUP DATABASE successfully processed 6587368 pages in 969.013 seconds (46.948 MB/sec).
Msg 3049, Level 16, State 1, Line 5
BACKUP detected corruption in the database log. Check the errorlog for more information.
Msg 3013, Level 16, State 1, Line 5
BACKUP LOG is terminating abnormally.

Where is the error log? I have a lot of disk space available so it cannot be this. I need to create the backup of the database and transaction log file to setup mirroring I believe. If I can get around this step, it would be ideal.

You appear to have corruption in your transaction log file. This needs to e resolved before you continue with you mirroring configuration.

  Where is the error log?

Using SSMS the error log can be found in the management folder.

I would check the log for more information and run a full DBCC CHECKDB to ensure the database is OK.

Paul Randall is an expert in data corruption in SQL Server and he mentions a fix to your problem in this article. I would read this through to get a good understanding of the situation you are in. In brief he says that the situation may be solved by switching the database to SIMPLE recovery mode, back to FULL recovery mode and then performing a FULL backup. The full backup restarts the log chain.

I would also inspect the hard drives that the transaction log file is running on as they may have caused the corruption if they are faulty.


MySQL replication: purge_logs giving error On a MySQL replication instance I have this error message:

[ERROR] MYSQL_BIN_LOG :: purge_logs Was called Expired with file / db-space-no-connected-cpa / DBMS / dxnc6sla / arch / not listed in the index .

Can you give me an idea about the solution? thank you in advance

ANALYSIS
When log-bin is enabled, MySQL will log completed SQL transactions into binary logs.
Binary Logs are maintained by a text file whose name would be the same as the binary log but have file extension of .index.
For example, say you had log-bin=/mysqllogs/mysql-bin and started mysqld.
There will be files placed in /mysqllogs
mysql-bin.000001
mysql-bin.index

The index file will contain the name of all the binary logs.
In this example, it would contain mysql-bin.000001
Over time, binary logs will accumulate and you will have something like this in /mysqllogs
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004
mysql-bin.index

and mysql-bin.index will contain this
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004

Based on the variables expire_logs_days (if it is greater than 0) and max_binlog_size, mysqld will do its own rotation and expiring of binary logs.
If you do the following
cd /mysqllogs
rm -f mysql-bin.000001
rm -f mysql-bin.000002

this will disable mysqld's log rotation mechanism. Why ?
The /mysqllogs folder will have
mysql-bin.000003
mysql-bin.000004
mysql-bin.index

but the mysql-bin.index will still contain this
mysql-bin.000001
mysql-bin.000002
mysql-bin.000003
mysql-bin.000004

mysqld actively uses the index file to verify that every file in /mysqlvar listed in mysql-bin.index exists before doing any rotation or expiration.
Therefore, you should never let the OS delete binary logs. Let mysqld do it.
I wrote about this before

May 23, 2016 : Error 1236 - "Could not find first log file name in binary log index file"
Apr 23, 2013 : Is it safe to delete mysql-bin files?

SOLUTION
Shutdown mysqld, edit your .index file so the contents matches the exist binary logs on disk, and start mysqld. I have suggested this before (Mysql binlogs exists, show binary logs is empty)
GIVE IT A TRY !!!


oracle crash - not starting We have a
Windows Server 2008 R2 Enterprise x64
Oracle 10.2.0.3
Adempiere - ERP

We had a power breakdown at 03.00 AM.
At 07.30 AM we started the server
At 08.00 our colleagues observed that the ERP is not functioning
We started Adempiere
ERP was not able to connect to Oracle DB
We entered EM Console and we checked the DB status
The listener was functional - up and running
DB was shotdown in progress
We tried to abort shot down via SQL Plus using command :
shutdown abort
Nothing happen
We tried shutdown - no result
We tried shutdown immediate - no result
Then we made Windows Shutdown from start menu
After restart of the server we checked in Windows Services.MSC the Oracle running services
Oracle JoobscheduleORCL was disabled, the rest were running. We set this one on automatic
The listener was functional - up and running
DB was shotdown in progress
We tried to abort shot down via SQL Plus using command :
shutdown abort
Nothing happen
We tried shutdown - no result
We tried shutdown immediate - no result
We tried to restart the Oracle Services with no result
We searched on the forum.oracle.com and we found out that the problem could be that the undotablespace is corrupted.
We tried to fix the table
We used the following command line :

alter database datafile  'xxxxx\undotbs01.dbf'  offline drop;

http://www.dbas-oracle.com/2011/06/recovery-from-undo-tablespace-block.html

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582

The current situation is :

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate
ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-01092: ORACLE instance terminated. Disconnection forced

SQL> startup
ORA-01031: insufficient privileges

Be sure the ORACLE_HOME and ORACLE_SID environment variables are correctly set. Then run sqlplus / AS SYSDBA as a user who is a member of the oradba group on the computer.

Get the machine down forcibly:

SHUTDOWN ABORT

Start it up, but try not to let stuff run wild:

STARTUP RESTRICT

Shut it down more gracefully. Don't stop until recovery is complete.

SHUTDOWN IMMEDIATE

Start it up.

STARTUP


How can I tell when InnoDB recovery is complete/check its progress? I'm writing a process to bring up a new instance from our nightly database snapshots, and I want to watch its progress and perform some actions once the database is up. When mysql first comes up, it spends some time in recovery, and I'd like to know when this is complete, and preferably how far along it is.

Linux "service" claims mysql is in "start/running" state, so that's no help. I can just try to connect until a connection is successful, but then I could be hung forever if something goes wrong during recovery. The closest I've been able to get so far is to tail MySQL's error.log, then use a regex to pull the last number off the last line to determine recovery progress. But that seems a bit fragile and hacky.

Is there any sort of tool MySQL (or third party) provides to know for sure that MySQL is currently in recovery and how far into the process it is?

The right way to get MySQL status regarding recovery is monitoring its error log. There are 3 phases on InnoDB recovery:

Checking partially written pages (very fast)
REDOing committed and uncommitted transactions written to the transaction log. This can take some time, but its time is IO bound (as fast as reading/writing to the transaction log). If you are concerned about recovery time -and not about writing performance-, you can reduce the transaction log file size (or put those files on a faster media). After this, InnoDB will allow MySQL start accepting connections, so the server will continue as usual.
Rollbacking uncommitted transactions (potentially very slow). For example, if an alter table failed, it will have to rewrite all modified pages to its original state, but the server is up and running. The larger is your buffer pool, the faster this will execute.

All this phases are marked on the log.

There are not many 3rd party tools to monitor this. One I can think about is Percona XtraBackup, that provides you a percentage of the replayed log  but a) this is a backup/recovery tool for InnoDB and b) it does its own recovery process, independent of the mysql service (you cannot run both at the same time). It may not be what you are looking for, but it may allow you to do the recovery with a copy of the data to make sure it runs before starting mysql again.

Having said that, InnoDB has been very resilient to me, and the only reasons why recovery was to be done in a more "forced" level is because of harware/filesystem level corruption, or InnnoDB misconfiguration, never on normal operation. A different thing would be it taking a long time -rollbacking can be very expensive, an in some cases it is faster to recover from a backup than doing everything transactionally (DROP large_table).


How do I use pg_dump and pg_restore to move a large database to another machine? I'm a bit unclear on how to use the pg_dump/pg_restore duo to move a large-ish database.

Here is the command I use to dump the database (the host and username are taken from environmental variables):

pg_dump --dbname=mydb --format=custom > mydb.dump

I use the custom format because I do this over the network and want to use compression.

When I now try to restore it, I tried this:

pg_restore --dbname=mydb --no-owner mydb.dump

But I get:

pg_restore: [archiver (db)] connection to database "mydb" failed: FATAL:  database "mydb" does not exist

I tried next with the --create option, but that yields the same error message.

Finally I tried passing template1 as a --dbname option:

pg_restore --dbname=template1 --no-owner --create mydb.dump

But here is what I get:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2125; 1262 16395 DATABASE mydb mydbuser
pg_restore: [archiver (db)] could not execute query: ERROR:  invalid locale name: "en_US.utf8"
    Command was: CREATE DATABASE mydb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8';

pg_restore: [archiver (db)] could not reconnect to database: FATAL:  database "mydb" does not exist

What am I doing wrong here?

When you do

pg_restore --dbname=mydb --no-owner mydb.dump

You are trying to connect to the mydb database which still does not exist. Connect to an existent one and use the --create parameter:

pg_restore --dbname=existentdb --no-owner --create mydb.dump

http://www.postgresql.org/docs/current/static/app-pgrestore.html


Oracle DBAI Theory : is it REALLY not possible to have Oracle Database Corrupted after Instance Crash? I'm studying a little DBA stuff and I've come across instance failure/recovery.

Let's say User A does DML and User B also does DML.

At this point, I believe it's safe to assume DB buffer cache contains changed table blocks and undo segments, and also that log buffer contains redo information for the new table blocks value and the new undo segments value.

Case 1 : No user issues a COMMIT and no writes to disk by DBWn and instance crash. Result : DB not corrupted. It's as if their DML never occurred.
Case 2 : Only User A issues a COMMIT. User B's uncommitted changes are written to disk then the instance crashes. LGWR will have written enough data in the redo log , especially User B's undo segments, so that the recovery's rollback phase will make the database consistent again
Case 3 : No user issues a COMMIT but their changed table blocks are old enough to make DBWn write them to disk. Then BOOM! Instance crashes. How will SMON perform rollback to clear dirty blocks from datafiles here? Especially since there is no undo segment values for them in the redo log here.

A dirty block can only be written to disk if all changes until the modification time of this block was already written to the redo logs. The Oracle 12g2 Database Concepts Guide says

  Before DBW can write a dirty buffer, the database must write to disk the redo
  records associated with changes to the buffer (the write-ahead protocol). If DBW
  discovers that some redo records have not been written, it signals LGWR to write
  the records to disk, and waits for LGWR to complete before writing the data
  buffers to disk.

So after a database was recovered it contains all modification until to a certain time and no modifications that where made after this time. If a transaction was committed the redo logs contain the modification oft his transaction and so its modifications where written to the data files during the recovery. Transactions that weren't committed either only modified memory structures (buffer cache) and so all there modifications are lost because of he crash, or they did some modifications of the file but then the undo information was also written to the files that contains the undo segments and this undo information will be used to undo the changes of this transaction.

case 3:  If a dirty block of a data file is written back to disk then all changes done before the modification of this block is already in the redo logs and so the undo information is already in the redo logs

case 1: If no user issues a commit that does not mean that changes are written to data file or redo log. But if something was written to disk it can be undone as described above.

case 2: Uncommitted changes must not be written to disk. It is only guaranteed that committed changes are written to disk.


SQL Server sync databases between enterprise - web edition We have an enterprise version of  sql server 2012. we need to synchronize its data to a web edition database. what would be the best option t do this ?  further to add on to this we need to provide high availability on the website. so the plan is to use 2 identical database on the web edition. only one will  be active at a time. periodically the enterprise version needs to push the data to the inactive database. make it active. and then sync the same data to the other one.

what would be the best solution to achieve the above ?

What Neil has suggested is a viable option, but it is geared more towards Cloud environments and will be more complex than what you are trying to achieve.

OPTION 1 :

The simplest solution would be Transactional Replication (if you want near to real time data) or snapshot replication depending on your NEED.

You can use Replication from Enterprise Edison to Web edition. Important to note that web edition does not support PULL Subscription. It only supports PUSH Subscription.

Depending on your business needs, you can schedule to sync every 1 hr or 1 day.

Replication support matrix is here

OPTION 2

Use 3rd party applicaitons like Redgate schema and data compare for syncing data between 2 databases. It also has command line options, so you can write up your own custom scripts that will periodically sync data. You can get started here

SQL Server also has an inbuilt data sync functionality which is commandline called "TableDiff".

As you see, there are many options, which one will suffice your need depends on your Business requirements.


Galera cluster node fails with error. Brand new cluster + master / node. Access denied or can't connect I am having issue.

Previous cluster fatally crashed, so I've created a new one with pretty much same settings (changed cluster name only).

Here is a galera.cnf for a node:

[mysqld]
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
# Galera Provider Configuration
wsrep_on=ON
wsrep_provider=/usr/lib/galera/libgalera_smm.so
# Galera Cluster Configuration
wsrep_cluster_name="cluster"
wsrep_cluster_address="gcomm://y.y.y.139, x.x.x.25, 10.5.2.1"

# Galera Synchronization Configuration
wsrep_sst_method=rsync
# Galera Node Configuration
wsrep_node_address="x.x.x.25"
wsrep_node_name="xxx002"

Galera.cnf for master differs only in wsrep_node_address (y.y.y.139) and wsrep_node_name (yyy001).

I have add permissions for debian-sys-maint but as soon I connect to master node, I will get:

Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: 1.0 (xxx002): State transfer from 0.0 (yyy001) complete.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Shifting JOINER -> JOINED (TO: 926)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Member 1.0 (xxx002) synced with group.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 0 [Note] WSREP: Shifting JOINED -> SYNCED (TO: 926)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 2 [Note] WSREP: Synchronized with group, ready for connections
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 2 [Note] WSREP: wsrep_notify_cmd is not defined, skipping notification.
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 11 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 12 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)
Jan 09 13:44:31 xxx002 mysqld[2609]: 2019-01-09 13:44:31 13 [Warning] Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)

Of course I won't be able to login to mysql as I will get:

  ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded

I have no idea what is happening. It worked fine (I had to reinstall all servers, but I am using same template).

I cannot create cluster for 4 days now :(

I have added those as soon I've installed MariaDB on the node:

GRANT ALL PRIVILEGES on *.* TO debian-sys-maint@localhost IDENTIFIED BY  'your password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'PASS';
CREATE USER 'admin'@'10.%' IDENTIFIED BY 'PASSS';

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';

The master data was restored from .sql (backup from a previous cluster).

Not sure what it could cause it ?

It doesn't sound like the problem is related to Galera. Maybe the debian-sys-maint@localhost user had already been created with the unix_socket as the defined authentication plugin before you did the GRANT for it? And the error message is pretty clear on what is the issue with that: ERROR 1524 (HY000): Plugin 'unix_socket' is not loaded.

So, you can either recreate the debian-sys-maint@localhost user with the default authentication plugin - mysql_native_password (option 1), or you can install the unix_socket plugin (option 2):

Option 1

DROP USER debian-sys-maint@localhost;
CREATE USER debian-sys-maint@localhost IDENTIFIED BY 'your password'; `
GRANT ALL PRIVILEGES on *.* TO debian-sys-maint@localhost WITH GRANT OPTION;

Option 2

INSTALL PLUGIN unix_socket SONAME 'auth_socket';

The advantage of option 2 is that you don't need a password for the user, assuming debian-sys-maint is the name of your OS user. MariaDB will use that OS username as the MariaDB username.


How can I lock row for reading/updating in DB2? I am trying to resolve a race condition in my application.

I have a table that is used as a work queue and many threads and read/update it. The following query is executed from application. First it reads the ID's of available jobs, then it reserves them and gets the info.

SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL LIMIT 5; --Check available jobs
UPDATE JOBS LOCKED_BY = 'Thread#1' WHERE ID IN (?); --Lock jobs
SELECT * FROM JOBS WHERE ID IN(?); --Get info about jobs to process

Issue arises when multiple threads go at this table at the same time. This if happens if 'Thread#2' reads the row before 'Thread#1' runs the UPDATE. Causing same jobs to run twice.

I have found a solution by wrapping the queries in:

LOCK TABLE JOBS;
--Queries from above
COMIT;

This works fine and prevents race conditions but it is a bit extreme, because threads have to wait for each other to finish.

How can I make sure that I can place a lock only on records from the following query?

SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL LIMIT 5;

This is on AS400 DB2 database, now LUW

Why are you using a table?  The IBM i provides a native data queue object...

If your application is running off platform in a language that doesn't offer direct access to the native objects, you could always build a stored procedure or user defined function on the I to provide access to the data queue.

Having said that, the answer to your question is the same for any RDBMS, use isolation levels.

In your case, you want to use Read Stability

  Like level RR, level Read Stability (RS) ensures that:

  Any row read during a unit of work is not changed by other activation
  groups that use different commitment definitions until the unit of
  work is complete. 1 Any row changed (or a row that is currently locked
  with an UPDATE row lock) by another activation group using a different
  commitment definition cannot be read until it is committed.

Simply add WITH RS to your statements...

SELECT ID FROM JOBS WHERE LOCKED_BY IS NULL WITH RS LIMIT 5
UPDATE JOBS LOCKED_BY = 'Thread#1' WHERE ID IN (?) WITH RS
COMMIT

The first statement will lock (up to) 5 rows..till the COMMIT is done.

Note that the table will need to be journaled if it is not already.


Possible to do OPENQUERY without named pipes? When I try to do OPENQUERY I get an error about not being able to open named pipe, which makes sense because that's not an option (and isn't going to be an option any time soon) for the way the network is currently setup between the client machine and the server.

Does this mean that I won't be able to use OPENQUERY, or can it be configured to work without named pipes?

Most likely, the error is just because you didn't change the order for the netlibs to be tried on the calling SQL Server. It will then try Shared Memory, TCP and finally NP. If all fails, then you will get the error for the last one tried, i.e., Named Pipes.

I.e., this is a general "cannot connect to the SQL server" (from the source SQL server to the destination server) error and should be troubleshooted as such. This and this are good resources.

In other words, OPENQUERY does not require Named Pipes.


Change normal user to admin in MySQL I have set up a MySQL database. My problem now is I can't add new users with the user I have created.

I try to grant access but I can't still add new users to my system. I use Workbench to add new users, so here is my screenshot:

Now I need to get access to my user account, but I can't remember how I can do this from MySQL command line.

Do you have access to the root account? Are you pointing to the right host? Sometimes it is useful to identify the host with the -h option to avoid DNS issues

mysql -u root -pSOMEPASSWORD -h127.0.0.1

If you still can't get into MySQL, you can reset the root password by starting MySQL with
--skip-grant-tables option

Stop MySQL

In Linux, as root user or equivalent

/etc/init.d/mysqld stop

Start MySQL with --skip-grant-tables option, you can find the mysqld_safe binary file with the which command

which mysqld_safe

/usr/bin/mysqld_safe --skip-grant-tables &

Login to MySQL without password

mysql -u root

Login to MySQL database

use mysql;

Update the root user password

update user set password=PASSWORD("mynewpassword") where User='root';

This will reset the password for any account called root, regardless of the host.

Flush privliges

flush privileges;

quit

Stop and Start MySQL

/etc/init.d/mysql stop
/etc/init.d/mysql start

If you are using Windows, you can simply update the my.cnf file with this option under the [mysqld] section

[mysqld]
skip-grant-tables

If you are not sure where the my.cnf file is, you can find the path by going to Administrative Tools available in Control Panel

In this case, you would most likely find the my.cnf file in a subdirectory of C:\Program Files\MySQL\MySQL Server 5.5\

And then restart MySQL using the Windows Services control panel in Administrative Tools.

From there you would be in safe mode and the commands would be the same.

update user set password=PASSWORD("mynewpassword") where User='root';
flush privileges;
quit

remove the skip-grant-tables from the my.cnf file and then restart MySQL

Conclusion

Now that you have root access again to MySQL, you can setup new users again


Restoring a Postgresql database Last week one of my sites server crashed.  I was not able to access the ssh server and could only use the basic linux console on digitalocean.com.  Because I could not get into my ssh server I was not able to perform a pg_dump to backup my database.  I was however able to use rsync to pull those files out onto my hard drive.  The problem is that I have no idea how to put those files back into my server.  I don't believe I can use:

>psql -U username -f backupfile.sql

to restore those files because the rsync command only got me a main dir with tons of db files in it.  I so not see any .sql files in there so I have no idea what to do to get that database back into my site.  Does anyone have any advice or know if this is even possible? It might be better to launch my site clean and manually rebuild the database.  Thank you for checking this out.

What you have is a physical dump of your database, not a logical dump.  So you can't reload it using psql.

What you would do is copy the files over to a new server (don't move them, keep a clean copy for future efforts) which has the same OS, architecture, and version of PostgreSQL, and try to start the database.  If the database were down while you did the rsync backup (and if you rsynced all the necessary files, and whatever crashed the server didn't corrupt things in the process) then the database should come back up and automatically go through its internal recovery process, get to a consistent state, and reopen.

If the database were running during the rsync, then it is very likely that starting it up will fail, or that it will report success but the data will be corrupt, perhaps in hard to detect ways.

Rebuilding it from scratch might be the best option, as then you have a known-good state.


MongoDB: Replica Set - master vs. slave We think about moving from a master-slaves to MongoDB with replica-set.
Now I wonder if MongoDB replicas are also set up like read-only slaves or if each mongos is able to write and sync the others like a master-master farm?

This might reduce the single source of error (master down) and also balance the write-load for scaling with best performance (not only for read, but also write...).

A replica set can only have one primary at any particular time (one master) with the other nodes being secondaries (slaves) and so they are nothing like master-master (i.e. multi-master) replication.

You definitely should move to a replica set from master/slave configuration because that is the preferred and recommended way to replicate.  From MongoDB docs: "replica sets are a functional superset of master/slave, and newer, more robust code".

When a primary fails, one of the secondaries will be automatically elected as a new primary (you can see the docs to see the details of how that happens).

You cannot get write balancing from having a replica set - you can only distribute writes by sharding in MongoDB.


Web server accessing the database server in Always on availability group We have a web server that is on the DMZ but that will need to query a database that resides inside the network. Now the network team wont allow port 1433 from the web server to the Database server.
The database server is on the Always on availability group and uses a specific listener port. Now my question is if my network team only allows only the Always on availability group listener port will that be a problem and what if the network team dont allow access to that port what are my options.

Now my question is if my network team only allows only the Always on availability group listener port will that be a problem

You can configure an AG Listener to use whatever port you want, and you can configure your application to connect using a non-standard port.

what if the network team dont allow access to that port

A "normal" DMZ has external access to hosts in the DMZ and limited access from the DMZ to the internal network.  If the network team won't allow connectivity from the web server to the databse server, you can put the database server in the DMZ or use a cloud hoster instead of a DMZ.
For instance you can use Azure App Service for hosting, and use Azure App Services Hybrid Connections for secure access to on-prem resources


Dynamic select column name on the basis of a flag I have a config table where there are config values are stored as key, value pair in each row.
 And there is another table which have two columns (lets say column1 and column2). Now I have to select column1 value in select statement if value for flag1 is present and equal to 1 in config table and select column2 if value for flag1 is absent or equal to 0.

I fetched the flag value in a variable-

DECLARE @flag smallint
SELECT @flag = CASE WHEN EXISTS (SELECT 1
                                 FROM   dbo.ACCOUNT_CONFIG WITH (nolock)
                                 WHERE acct_id = #{accountId}
                                 AND key_name = 'flag1' AND key_value = 1) THEN 1
                                 ELSE 0 END

Now I have to do some thing like:

SELECT col1, col2
From   ACCOUNT_USER table
where  acct_id = #{accountId}
AND    CASE @flag1 = 1 THEN column1 = #{COLUMN_VALUE}
       ELSE column2=#{COLUMN_VALUE} END

But above statement is not working for me.

If your final query returns 2 columns, it returns 2 columns. You can move CASE to the SELECT sentence but it will return 2 columns.

SELECT CASE WHEN @flag1 = 1 THEN #{COLUMN_VALUE} END AS col1,
       CASE WHEN @flag1 <> 1 THEN #{COLUMN_VALUE} END AS col2
FROM   ACCOUNT_USER table
WHERE  acct_id = #{accountId};

In case you need only one column named as COL1 or COL2 I'd suggest to use a dynamic query.


How to Recover My Oracle 9i Database From Just The Datafiles The state of the system when I started was that there was a single, working instance of Oracle Database 9i. I proceeded to install Oracle 11g choosing to Install Software Only. Upon completion, everything still worked fine. I created a new 11g listener on port 1522 called LISTENER, which was the same name as the 9i listener listening on port 1521. I don't know if things still worked at this point. Next, I started creating an 11g database which I gave the same SID as the pre-existing 9i database. The database creation process failed at first, and I didn't read the error messages. I ran it again, and no error messages came up. It was at this point when people started complaining about being kicked out of the database.

Upon investigation, I found that it was completely impossible to connect to the once flawless db instance. I tried a myriad of things to get connected to no avail. Finally, I decided I would be better off creating a separate instance and accessing the datafiles from there. This is what brings me here. I have tried creating an instance with the same datafile names and copying the originals into the directory, but I don't know what the original configuration is so that inevitably failed. If anyone has any advice on how I can gain access to the datafiles without knowing the original configuration, I would greatly appreciate it.

OS: Windows Server 2003 R2

Pastebin of 9i alert.log here.

Seeing as you have stated that the original data files look like they're untouched, all you need to do is recreate the service with oradim, making sure you're using the 9i ORACLE_HOME service and tools.


ssms does not let me change to server authentication mode and I can not log in I have been using SSMS and logging in through server authentication for a week now and yesterday when I tried to login it suddenly told me "no process on the other end of the pipe".
I researched online and it said the problem is that the security property for the server is set to windows authentication and has to be changed to server authentication. So I log in to SSMS through windows authentication first to change it, however it said that I do not have permission to change the authentication mode, which is weird because I ran the SSMS as admin and it was on server authentication previously.

Now I can not access my database at all as when I created the database it was under the SA account and it will just tell me I do not have permission to open the database or do anything basically.

Also just for a side info the problem arised after I restarted my computer I believe, the server property is suddenly changed to windows authentication and I cannot log in through the SA account nor can I change it back to server authentication

Check this pinal dave solutions - https://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/


Display child records in single column by comma separted with parent records Have parent & child table like below for Oracle

T_OPT_PARENT

OPT | OPT_DESC| STATUS
XY  | XY_DESC | A
YZ  | YZ_DESC | A
ZZ  | ZZ_DESC | A

T_OPT_CHILD

OPT | CODE
XY  | ONE_CODE
XY  | TWO_CODE
XY  | THREE_CODE
ZZ  | ONE_CODE
ZZ  | TWO_CODE

Expected OutPut

OPT | OPT_DESC| CODE                 | STATUS
XY  | XY_DESC | ONE_CODE,TWO_CODE,THREE_CODE | A
YZ  | YZ_DESC |                              | A
ZZ  | ZZ_DESC | ONE_CODE, TWO_CODE           | A

Could some one help me to construct SQL to obtain the expected result.

Setup tables and data:

create table T_OPT_PARENT
(OPT       char(2)
,OPT_DESC  varchar2(10)
,STATUS    char(1));

insert into T_OPT_PARENT
select 'XY','XY_DESC','A' from dual union all
select 'YZ','YZ_DESC','A' from dual union all
select 'ZZ','ZZ_DESC','A' from dual;

create table T_OPT_CHILD
(OPT       char(2)
,CODE      varchar2(10));

insert into T_OPT_CHILD
select 'XY','ONE_CODE'   from dual union all
select 'XY','TWO_CODE'   from dual union all
select 'XY','THREE_CODE' from dual union all
select 'ZZ','ONE_CODE'   from dual union all
select 'ZZ','TWO_CODE'   from dual;

Here's one solution based on a left join and the listagg() function:

select p.OPT,
       p.OPT_DESC,
       listagg(c.CODE, ', ') within group (order by ROWNUM) "CODE",
       p.STATUS

from T_OPT_PARENT p

left
join T_OPT_CHILD c
on   c.OPT = p.OPT

group by p.OPT,
         p.OPT_DESC,
         p.STATUS

order by 1;

 OPT | OPT_DESC | CODE                           | STATUS
 --- | -------- | ------------------------------ | ------
 XY  | XY_DESC  | ONE_CODE, TWO_CODE, THREE_CODE | A
 YZ  | YZ_DESC  | null                           | A
 ZZ  | ZZ_DESC  | ONE_CODE, TWO_CODE             | A

NOTE: I'm able to sort XY's CODE as desired using ROWNUM only because I inserted the records into the T_OPT_CHILD table in the same order as the desired sort order.  The use of ROWNUM may not work for your real world data so you'll likely need to modify the order by ROWNUM clause accordingly.

Here's a dbfiddle


Why pt-duplicate-key-checker suggests removing a composite index? Here's a snippet from the output of the Percona Tool pt-duplicate-key-checker which searches for redundant indexes:

# Key myidx ends with a prefix of the clustered index
# Key definitions:
#   KEY `myidx` (`bar`,`foo`)
#   PRIMARY KEY (`foo`),
# Column types:
#         `bar` mediumint(8) unsigned not null default '0'
#         `foo` mediumint(8) unsigned not null auto_increment
# To shorten this duplicate clustered index, execute:
ALTER TABLE `mydb`.`mytable` DROP INDEX `myidx`, ADD INDEX `myidx` (`bar`);

Why does the tool suggest this? Can't the original composite index be useful?

As far as I understand, an index on bar would be worth of deletion given a PK (bar,foo), but it is not the case here.

The primary key is a part of any secondary key in InnoDB.


'publish' is a string

+ is an _arithmetic operator

So  'publish' + _pad_term_counts()  is probably not anything useful.

How long does each SELECT take?

Please provide SHOW CREATE TABLE and EXPLAIN SELECT and how big the tables are.

Descending index -- Not a problem; MySQL simply scans through the data in reverse order.

However, no index is useful for the one ORDER BY you mentioned -- This is because the GROUP BY is needed first.  Can that be removed?


Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project I currently use the Visual Studio Professional Edition which has the database project as a project template, but some of its features are not available, for example Schema Compare tool. The schema comparison and database update scripts generation are only available in Visual Studio 2010 Premium/Ultimate versions.

But are the schema comparison and update scripts generation features in the Visual Studio as rich as those in Redgate SQL Compare tool? (I didn't use it either) I didn't manage to find the feature comparison list. Could anybody, who used both of them, help to make it clear?

I tried to use the VS tool yesterday with my production SQL 2000 instance, comparing to my dev 2008 instance, and it refused to work with anything prior to SQL 2005.  Red Gate definitely does not have such a restriction.  It even works reasonably well (not 100%) with another database we have that runs in 6.5 Compatibility Mode.


sys.dm_exec_query_stats - what causes total_worker_time to be reset? How to work around that? I am monitoring stored procedure executions, mainly the execution times and the CPU usage, using sys.dm_exec_query_stats in the script below.

SET NOCOUNT ON;

declare @lastex datetime
select @lastex = max(datestamp) from tablebackups.[dbo].[_dm_exec_query_stats]

--Get proc counts since the starting point table creation
insert into DBA.dbo.SPStats
SELECT  bcp.datestamp as StartTime
        ,getdate() as EndTime
        ,@@Servername as 'Server'
    ,isnull(DB_NAME(st.dbid),'') DBName
    ,isnull(OBJECT_SChEMA_NAME(st.objectid,st.dbid),'') [Schema]
    ,isnull(OBJECT_NAME(st.objectid,dbid),'') StoredProcedure
      ,max(cp.usecounts)-max(bcp.usecounts) as Execution_count
      ,--case
      --when sum(qs.total_worker_time)-sum(bqs.total_worker_time) < 0
        --  then (9223372036854775807 - sum(convert(bigint,bqs.total_worker_time))+sum(convert(bigint,qs.total_worker_time)))
    --else
    sum(qs.total_worker_time)-sum(bqs.total_worker_time)
    --  end
    AS 'total_cpu_time'

      ,case max(cp.usecounts)-max(bcp.usecounts)
        when 0
            then NULL
        else
            (sum(qs.total_worker_time)-sum(bqs.total_worker_time))/(max(cp.usecounts)-max(bcp.usecounts))
        END
            as 'CPU_Per_execution'
      ,cast(convert(float,(max(cp.usecounts)-max(bcp.usecounts)))/convert(float,(datediff(s,bcp.DateStamp,getdate()))) as Numeric(18,3)) as 'Executions_Per_Second'
      ,sum(qs.Total_Logical_Reads)-sum(bqs.Total_Logical_Reads) as Total_Logical_Reads
      ,(sum(qs.Total_Logical_Reads)-sum(bqs.Total_Logical_Reads))/datediff(s,bcp.DateStamp,getdate()) as Total_Logical_Reads_per_sec
      ,sum(qs.Total_Logical_Writes)-sum(bqs.Total_Logical_Writes) as Total_Logical_Writes
      ,(sum(qs.Total_Logical_Writes)-sum(bqs.Total_Logical_Writes))/datediff(s,bcp.DateStamp,getdate()) as Total_Logical_Writes_per_sec
      ,datediff(n,bcp.DateStamp,getdate()) as 'Elapsed Time (m)'
 FROM sys.dm_exec_cached_plans cp
    inner join tablebackups.dbo._dm_exec_cached_plans bcp
        on  cp.plan_handle = bcp.Plan_handle
    join sys.dm_exec_query_stats qs
        on cp.plan_handle = qs.plan_handle
    inner join tablebackups.dbo._dm_exec_query_stats bqs
        on cp.plan_handle = bqs.plan_handle
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
where
    cp.objtype = 'proc'
    and  qs.last_execution_time > @LastEx

group by DB_NAME(st.dbid)
        ,OBJECT_SCHEMA_NAME(objectid,st.dbid)
        ,OBJECT_NAME(objectid,st.dbid)
        ,bcp.DateStamp
order by max(cp.usecounts)-max(bcp.usecounts) desc

--Create baseline tables to get a starting count point
if object_id(N'tablebackups.dbo._dm_exec_query_stats') is not null
    drop table tablebackups.dbo._dm_exec_query_stats
if object_id(N'tablebackups.dbo._dm_exec_cached_plans') is not null
    drop table tablebackups.dbo._dm_exec_cached_plans

select getdate() as DateStamp,* into tablebackups.dbo._dm_exec_query_stats
from sys.dm_exec_query_stats
select getdate() as DateStamp,* into tablebackups.dbo._dm_exec_cached_plans
from sys.dm_exec_cached_plans

I basically saves the contents of DMVs into tables (tablebackups) so that I can compare the situation as it is now (straight from the DMVs) and the situation as it was (from the tablebackus tables).

works fine.

Only problem and hence this question:

Sometimes the total_worker_time now is less that what it used to be, for a reason I haven't identified, and therefore it gives me negative values.

I have been run this script as a stored procedure that is called by a job every 5 min.

so in the last 5 min I know how many times each procedure has been executed, that has been accurate, but the CPU usage has not been, for the reason above.

what can I do to tackle this?

I'm pretty terrible at these DMVs however:

When you're joining on the history you can have one plan_handle with multiple sql_handle (where the plan executes with different options), so possibly this is skewing your stats.
The plan handle is for an entire batch, with individual statements inside it, which can be recompiled separately, and this might (I haven't confirmed) overwrite the stats for the batch. You might be able to confirm by capturing with traces/events for plan recompilations and seeing if the times correlate with your counter resets.


First Insert is skipped by the trigger I am facing a very strange issue as i have implemented a trigger on tableA which does encrypt data and insert data to another tableB and update column to 4 digit.
The issue is First insert is somehow skipped and it gets corrected from the 2nd insert onward, don't know why the first insert is skipped.
Any help would be great.

Os is Linux and MySQL version is 5.7.9

        create table tableA(c1 varchar(10));

    create table tableB(c1 varchar(60));

    delimiter //
        CREATE TRIGGER trig1 before INSERT
        ON  tableA FOR EACH ROW
        BEGIN
              IF LENGTH(LTRIM(RTRIM(new.c1))) > 4 then
                BEGIN
                insert into tableB(c1) select AES_ENCRYPT(new.c1,'test') from tableA;
                Set  new.C1 = RIGHT(LTRIM(RTRIM(new.C1)),4) ;
                 END;
           end if;
        END;
        //
delimiter ;

insert into tableA values('423423544');

select count(*) from tableA;--1
select count(*) from tableB;--0

I think it is because you are selecting from tableA.
I guess the right way to do this is:

insert into tableB(c1) values (AES_ENCRYPT(new.c1,'test'));

So, the trigger will become like this:

delimiter //
    CREATE TRIGGER trig1 before INSERT
    ON  tableA FOR EACH ROW
    BEGIN
          IF LENGTH(LTRIM(RTRIM(new.c1))) > 4 then
            BEGIN
            insert into tableB(c1) values (AES_ENCRYPT(new.c1,'test'));
            Set  new.C1 = RIGHT(LTRIM(RTRIM(new.C1)),4) ;
             END;
       end if;
    END;
    //
delimiter ;


Create new Read Only user on DB2 on the whole database I am an oracle/sql server DBA having limited knowledge in IBM DB2. We have DB2 9.7 fp4 running on AIX 6. I would like to create a new user and grant him READ only privilege on a particular database. Please help!

Thanks & Regards
Mohammed

There is no read only privilege on a database in DB2. You will need to grant SELECT privileges on specific tables, preferably via a role to avoid doing this multiple times.

Create a new database role, e.g. create role readonly.
Grant SELECT privileges on the required tables to that role:
grant select on myschema.mytable to role readonly.
Create a new user in the operating system, e.g. # mkuser newuser.
Grant the role to the user: grant role readonly to user newuser.

Note that the new user will likely need to change the initial password before he or she can connect to the database.


Should we always prefer clustering over a master-slave configuration if the goal is high-availability? Given: I have two virtual machines on a SAN that I can use to host RavenDB.

Question: If my goal is high-availability, then should I always prefer clustering over a master-slave configuration?

From my vantage point, master-slave seems like more of a maintenance headache. Clustering seems much easier. [But perhaps I'm missing something.]

Generally, Master-Slave is a disaster recovery setup: when master dies, slave becomes master. Manual intervention is needed or you need some redirection layer, You may also have replication lag built in so that master corruption can be stopped before it hits the slave.

Clustering is high availability: failover is automatic.

You need both HA and DR, not just one. You can have distributed clusters that mix it a bit, but generally the concepts are different.

If the 2 VMs are on the same physical host, this is neither HA nor DR of course.

If you do use clustering, then use Percona. MySQL cluster is broken in RDBMS terms


uninstalling SSRS and SSIS from clustered SQL Server 2008 R2 SP1 Due to the security recommendation, i will need to uninstall the unused SSRS and SSIS component from a production cluster SQL Server 2008 R2 SP1, so what is the caveats and what do I require to do to ensure the Database engine still up and running ?

Thanks,
Al

Neither of these components are cluster aware and they will be separate from the database install, so you would just run the SQL 2008 R2 Add/Remove programs from the control panel of each cluster node and select uninstall for both of these components.  A reboot may be required to take effect.


HeidiSQL: Import CSV A few lines of my CSV:

"id","date","date_","pow","ene","reac"
"ES0134000563024539AM0F","2013-05-02","2013-06-05","62","43","43"
"ES0134000573024539AM0F","2013-05-02","2013-06-05","62","43","43"
"ES0134000563224539AM0F","2013-05-02","2013-06-05","62","43","43"

I tried to import text file but I've received an error message with code 1261. I used the following control characters:

Fields terminated by: "
Fields enclosed by: ,
Fields escaped by: "
Lines terminated by: \r\n

As per documentation your error is:

  Error: 1261 SQLSTATE: 01000 (ER_WARN_TOO_FEW_RECORDS)
  Message: Row %ld doesn't contain data for all columns

Thus it appears that your table have more column than the file. You will have to specify the column-names explicitly. Also the remaining columns should be nullable. (You might want to share table definition if below command doesn't work)

Try this:

LOAD DATA INFILE 'path/to/file' INTO TABLE your_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (id,date,date_,pow,ene,reac);

If you are interested you might want to refer this blog post which explains various use-cases for loading delimited data to mysql.


Why does MongoDB extend FileSize if it is already 5x larger than DataSize I recently had to assign more diskspace to my MongoDB 2.4.8 instance.
This instance continually receives transactions, makes some updates and then deletes them after 3 months. I would therefore expect that the disk usage was relatively constant.
The documents have a relatively uniform size of 5KB.

db.stats()
{
"db" : "mydb",
"collections" : 16,
"objects" : 4.71578e+006,
"avgObjSize" : 5368.2594088278856000,
"dataSize" : 25315551828.0000000000000000,
"storageSize" : 111230508336.0000000000000000,
"numExtents" : 128,
"indexes" : 41,
"indexSize" : 1398799136.0000000000000000,
"fileSize" : 122280738816.0000000000000000,
"nsSizeMB" : 16,
"dataFileVersion" : {
    "major" : 4,
    "minor" : 5
},
"ok" : 1.0000000000000000
}

I understand that disk usage will be larger than data size due to preallocation and fragmentation, but I cannot see any reasonble explanation for a 5 to 1 ratio other than a large historical delete or a bug.

Is MongoDB unable to reuse space properly so that we must schedule manual repair-jobs on otherwise completely stable systems, or do I have another problem somewhere?

Based on the comments I have received the following actions seem to address my concerns:

Migrate existing collections to power of 2 sizes.
Run repair or compress periodically to optimize the free list search so that default allocation of new disk space on timeout is avoided.
Only capped collections should be considered "100% maintenance-free".


Building a query programatically I have three variables $color, $size and $category. Each oh them can be a specific value or may refer to all entries.I want to fetch all products that are $color = "red", $size = "Medium" and any category OR any color, any size and $category = "Tshirts". I can do this using if/elseif or switch and treat all cases but this means 12 cases + the one with any color, any size and any category, so 13 total cases.

Is this even possible in mySql ??

Sample query

SELECT
*
WHERE color = $color AND size = $size AND category = $category

It seems you want to keep you PHP code simple and make the SQL code more complicated, while it should be otherwise.

Example: lets say that
 - user A chooses: "color = 'red', size = 'Medium', category = any"
 - user B chooses "color = any, size = any, category = 'Tshirts'"

The conditions could be respectively as simple as:

-- userA
WHERE color = $color AND size = $size
-- or the equivalent (if columns are NOT NULL):
WHERE color = $color AND size = $size AND category = category

and for

-- userB
WHERE category = $category
-- or the equivalent (if columns are NOT NULL):
WHERE color = color  AND size = size  AND category = $category

I don't think it's so difficult to write PHP code that produces those conditions when a user selects some "any", no matter how that "any" is coded in your PHP/web form.

But if you really want to pass that to MySQL, you can do something like this. Pass the "any" as a fixed value (say 'any') and use:

WHERE (color    = $color    OR $color    = 'any')
  AND (size     = $size     OR $size     = 'any')
  AND (category = $category OR $category = 'any')


Get child record from specific root I have two tables articles and topics and I am using MySQL 5.6

Articles

id    topic(fk on Topics.id)   featured
1     1                        1
2     2                        0
3     3                        1
4     4                        1
5     5                        1

Topics

id    parent(fk on Topics.id)
1     NULL
2     1
3     2
4     3
5     NULL

My question is, I need to get all featured articles in specific root topic

i.e when I query about featured articles in topic id 1, I get article 1, 3, 4

MySQL does not feature recursive queries. In fact, Bill Karwin just wrote a blog post about how MySQL is the only major RDBMS without recursive queries. Notwithstanding, what you need is a Stored Procedure that performs the query as a preorder tree traversal using a queue.
YOUR SAMPLE DATA
mysql> drop database if exists ayman;
Query OK, 2 rows affected (0.03 sec)

mysql> create database ayman;
Query OK, 1 row affected (0.00 sec)

mysql> use ayman
Database changed
mysql> create table topics
    -> (id int not null primary key,
    -> parent int default null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into topics (id) values (1),(5);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into topics (id,parent) values (2,1),(3,2),(4,2);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> create table articles
    -> (id int not null,
    -> topic int not null,
    -> featured int not null
    -> ) engine=MyISAM;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into articles values
    -> (1,1,1),(2,2,0),(3,3,1),(4,4,1),(5,5,1);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Here it is loaded
mysql> select * from articles order by id;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
|  1 |     1 |        1 |
|  2 |     2 |        0 |
|  3 |     3 |        1 |
|  4 |     4 |        1 |
|  5 |     5 |        1 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> select * from topics order by id;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  2 |      1 |
|  3 |      2 |
|  4 |      2 |
|  5 |   NULL |
+----+--------+
5 rows in set (0.00 sec)

mysql>

STORED PROCEDURE
DELIMITER $$

DROP FUNCTION IF EXISTS `ayman`.`GetFeaturedArticles` $$
CREATE FUNCTION `ayman`.`GetFeaturedArticles` (GivenID INT) RETURNS VARCHAR(1024)
DETERMINISTIC
BEGIN

    DECLARE rv,rvlist,q,queue,queue_children VARCHAR(1024);
    DECLARE queue_length,front_id,pos INT;

    SET rv = '';
    SET queue = GivenID;
    SET queue_length = 1;

    WHILE queue_length > 0 DO
        SET front_id = FORMAT(queue,0);
        IF queue_length = 1 THEN
            SET queue = '';
        ELSE
            SET pos = LOCATE(',',queue) + 1;
            SET q = SUBSTR(queue,pos);
            SET queue = q;
        END IF;
        SET queue_length = queue_length - 1;

        SELECT IFNULL(qc,'') INTO queue_children FROM
        (
            SELECT GROUP_CONCAT(AA.id) qc
            FROM articles AA INNER JOIN topics BB
            ON AA.topic = BB.id
            WHERE BB.parent = front_id
        ) A;
        IF LENGTH(queue_children) = 0 THEN
            IF LENGTH(queue) = 0 THEN
                SET queue_length = 0;
            END IF;
        ELSE
            IF LENGTH(rv) = 0 THEN
                SET rv = queue_children;
            ELSE
                SET rv = CONCAT(rv,',',queue_children);
            END IF;
            IF LENGTH(queue) = 0 THEN
                SET queue = queue_children;
            ELSE
                SET queue = CONCAT(queue,',',queue_children);
            END IF;
            SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
        END IF;
    END WHILE;

    SET rvlist = CONCAT(GivenID,',',rv);
    SET rv = '';
    SET @comma = '';

    #
    # Strip Out Non-Featured Articles
    #
    SET @searching = 1;
    WHILE @searching  = 1 DO
        SET @searching = (LOCATE(',',rvlist) > 1);
        SET front_id = FORMAT(rvlist,0);
        SELECT COUNT(1) INTO @ItsFeatured FROM articles WHERE id = front_id AND featured = 1;
        IF @ItsFeatured = 1 THEN
            SET rv = CONCAT(rv,@comma,front_id);
            SET @comma = ',';
        END IF;
        SET pos = LOCATE(',',rvlist) + 1;
        SET q = SUBSTR(rvlist,pos);
        SET rvlist = q;
    END WHILE;

    RETURN rv;

END $$

DELIMITER ;

Here is the execution of it
mysql> select * from articles;
+----+-------+----------+
| id | topic | featured |
+----+-------+----------+
|  1 |     1 |        1 |
|  2 |     2 |        0 |
|  3 |     3 |        1 |
|  4 |     4 |        1 |
|  5 |     5 |        1 |
+----+-------+----------+
5 rows in set (0.00 sec)

mysql> select * from topics;
+----+--------+
| id | parent |
+----+--------+
|  1 |   NULL |
|  5 |   NULL |
|  2 |      1 |
|  3 |      2 |
|  4 |      2 |
+----+--------+
5 rows in set (0.00 sec)

mysql> select GetFeaturedArticles(1);
+------------------------+
| GetFeaturedArticles(1) |
+------------------------+
| 1,3,4                  |
+------------------------+
1 row in set (0.03 sec)

mysql>

I borrowed this code from my earlier post : Find highest level of a hierarchical field: with vs without CTEs
The Stored Procedure first collects all the descendants of a given article's topic hierarchy. Then, it iterates through the return list and strips non-featured articles.
Give it a Try !!!


sp_Blitz taking too long to complete I just installed sp_Blitz and it has been running for an hour and still won't complete.  I am able to run sp_BlitzFirst and it takes about 3 minutes to run but does complete.
Any idea why it won't complete?
It finally failed after 1 hour and 11 minutes

There is insufficient system memory in resource pool 'internal' to run this query.

I have one small test database in Recovery Pending state, could that be the issue?
I have Activity Monitor open but don't see any category maxed out, Proc 32%, Waiting Tasks 0, DB I/O .2 MB/Sec, Batch Req. a few hundred.
I ran sp_Blitz; GO and also selected the common settings in the script at the bottom and tried that with the same results.
Environment specifications

sp_Blitz ver 6.4.
SQL Server Std 64 bit, 2014, SP2 Build 9600
128GB of RAM with max set to 116GB
Single server with about 25 databases, one large Dynamics database, about 300 GB.

This can happen when the server is under heavy load, or some part of Blitz is getting blocked.

Rather than jump to Activity Monitor, run sp_BlitzWho, which you should have also gotten with the First Responder Kit. Go ahead and update your question with the exact command you ran "and also selected the common settings in the script at the bottom" doesn't tell me exactly what you ran. - Erik Darling

Try using @CheckUserDatabaseObjects = 0 to skip what's inside the user databases altogether. Then, when it's taking over, say, 90 seconds, run sp_BlitzWho in another window to see what query is currently running. Then you can copy/paste that into your question to help track it down.

Also, try sp_BlitzFirst SinceStartup = 1, and copy/paste the top result sets into a spreadsheet (or take a screenshot) - that'll help us use the top wait stats to understand what's happening on the server. - Brent Ozar


How to increase fast backup and restore of 500GB database using mysqldump? There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables.

Backup and restore using mysql dump taking quit a long time or days.

max_allowed_packet=1G
foreign_key_checks=0
auto_commit = off

But the link also recommends XtraBackup from Percona, which is faster, no locks on tables that are already in use and gains no time for restoration. For 500GB of data I prefer Percona XtraBackup to be quick and efficient unless you want to convert them to innodb_file_per_table model if it wasn't from the existing DB server. Below link explains on how to proceed.

http://www.percona.com/doc/percona-xtrabackup/innobackupex/creating_a_backup_ibk.html


Column doesn't exist when using CASE expression in WHERE clause I have this SQL

SELECT
    price_amount,
    price_currency,
    CASE
        WHEN price_currency='USD' THEN price_amount * 3.9024
        WHEN price_currency='EUR' THEN price_amount * 4.413
        WHEN price_currency='ILS' THEN price_amount * 1
        ELSE price_amount * 1
    END AS final_price
FROM items
WHERE final_price BETWEEN 1 AND 100

but I get:

  ERROR:  column "final_price" does not exist
  LINE 1: ...ce_amount * 1 END AS final_price FROM items WHERE final_pric...

It is getting selected using alias, so why is it trying to find it in the table?

The WHERE clause is evaluated before aliases in the SELECT clause. WHERE is used to locate rows from the base table which are the input to all expressions in the SELECT. Only after locating the rows your CASE can be evaluated with real values and the final_price alias is assigned its value.

There are multiple ways to solve that:

repeat the same expression in the WHERE clause - that way it will be used to locate the rows
wrap your query as a subquery in another query and move the WHERE to the outer query - as you select from a derived table, the alias becomes a "real" column that way
(this would work in MySQL, but not in PostgreSQL) use the HAVING clause instead of WHERE - HAVING (in MySQL) works after the SELECT is computed to do a "second wave" of filtering. In standard SQL (and in Postgres), SELECT is logically processed after HAVING, so this won't work.

create a generated/virtual column from your CASE expression, that way it becomes a part of your base table and you might even be able to index it

Warning: your query will need to do fulltable/index scan unless you can add some "simpler" WHERE conditions or be able to use the indexed virtual column.


I've modified your query and I used CASE statement to COUNT:

Query:

select
    survey_categories.survey_cat_id,
    survey_question.ques_id,
    survey_details.store_id,
    count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent,
    count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good,
    count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good,
    '' AS Average,
    count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor
from survey_categories
INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id
INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id
group by survey_categories.survey_cat_id,survey_question.ques_id
order by ques_id,answer;

Test:

mysql> select
    -> survey_categories.survey_cat_id,
    -> survey_question.ques_id,
    -> survey_details.store_id,
    -> count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent,
    -> count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good,
    -> count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good,
    -> '' AS Average,
    -> count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor
    -> from survey_categories
    -> INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id
    -> INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id
    -> group by survey_categories.survey_cat_id,survey_question.ques_id
    -> order by ques_id,answer;
+---------------+---------+----------+-----------+-----------+------+---------+------+
| survey_cat_id | ques_id | store_id | Excellent | Very_Good | Good | Average | Poor |
+---------------+---------+----------+-----------+-----------+------+---------+------+
|             1 |       1 |      102 |         2 |         0 |    0 |         |    0 |
|             1 |       2 |      102 |         0 |         2 |    0 |         |    0 |
|             1 |       3 |      102 |         0 |         0 |    0 |         |    0 |
|             2 |       4 |      102 |         0 |         2 |    0 |         |    0 |
|             2 |       5 |      102 |         0 |         0 |    0 |         |    2 |
|             3 |       6 |      102 |         2 |         0 |    0 |         |    0 |
|             3 |       7 |      102 |         0 |         0 |    0 |         |    0 |
+---------------+---------+----------+-----------+-----------+------+---------+------+
7 rows in set (0.00 sec)

mysql>

By the way, for AVG, what is your expected calculation against which fields?


InnoDB: secondary key that's 'parallel' to primary--do joins wherein the resulting primary keys are contiguous perform better? Say I have a table

(
person_id INT AUTO_INCREMENT,
birthday_timestamp BIGINT,
country,
state,
...,
PRIMARY KEY (person_id),
KEY (birthday_timestamp)
)

where people are added the instant they're born.  In other words, the order of person_id is the same as that of birthday_timestamp.  More formally, if I had two rows, A, and B,

A.person_id < B.person_id --> A.birthday_timestamp <= B.birthday_timestamp

Would it be reasonable to assume that performance on a WHERE by birthday_timestamp range should not be significantly worse than the equivalent WHERE on person_id range?

My rudimentary understanding of how this would be executed is the engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index.  Since these person_ids will be 100% contiguous, this should lead to minimal fetching of clustered index pages, right?

In contrast, if instead of birthday_timestamp, I had something whose order was completely irrelevant...say, favorite_number, this wouldn't be the case.  In general, the person_id's corresponding to favorite numbers between 15 and 1,000,000 would be completely spread out and non-contiguous.

Back to the former case.  The one alternative I can think of that wouldn't involve a join at all is the following.  Say I want person_id, country, etc., for people born between 0 and 12345678.  I could do

select * from people where birthday_timestamp >= 0 and birthday_timestamp <= 12345678

Which is what I described above and I assume would necessitate a join since MySQL doesn't actually know the keys are parellel, OR I could do two (almost)-point queries into the secondary index to grab the primary key range myself, i.e.

start = $(select person_id from people where birthday_timestamp >= 0 order by birthday_timestamp asc limit 1)

end = $(select person_id from people where birthday_timestamp <= 12345678 order by birthday_timestamp desc limit 1)

select * from people where person_id >= start and person_id <= end

Which seems like it could be a bit more optimal, though not by an order of magnitude.  The downside is it's less concise.

Any thoughts?

Thanks

a) .. engine would fetch the list of person_id in the secondary index on birthday_timestamp and then fetch those results from the clustered index .. - Only InnoDB uses clustering by PK, MyISAM uses HEAP tables (no clustering) and the primary key is just another index with all indexes using pointer/offset to the heap to find the right row.

b)  sequential vs random order of fetches only matters when you read data pages from disk, but when your query by birthday fetches first row from new page, innodb loads the entire page anyway and fetching next row of following born is "free" - no read from disk is needed because the page has already been loaded - it does not matter that mysql does not know the primary and secondary indexes are "parallel", it will have the right effect even without this knowledge - but the effect might be diminished if you have long rows so not many of them in one page. And MySQL won't use such index if it leads to reading more than ~20% of the table because it will prefer the clustered index for full scan in such case.

c) clustering by primary key does not actually mean that all the rows are stored totaly sequentialy in some file. It means that the table is organized as a B-Tree ordered by the PK. That B-Tree is stored as separate pages (nodes), each one containing some number of rows (depending on row size). So one page contains rows whose PK values are close together. But there is no guarantee that the logically next page will be stored physically next to the previous one. It may be "anywhere" in the table file (or in the big ibdata file if tables are not stored separately). When you only append to the table and never delete, you may expect that the new page will usually be allocated at the end so after the previous one, but there are possibilities of page splits even in that case and more so if the records are updated and deleted. That leads to "fragmentation" and non-continuous storage of the B-Tree pages. Afaik there is no direct defragmentation the way MyISAM offers (but rebuild might do that) for this but from what I gathered in the most extreme cases it may slow down table scans significantly. This is separate from file-level fragmentation but is very similar to that.

Mostly I agree with your assesment of the situation, the 3-query version looks promising and might be worth benchmarking for some cases. But you asked for some thoughts and this is what I got.


Alternative to slow [LEFT JOIN + OR] without UNION? I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables.

The query using only left joins runs extremely slow, as in - 11 minutes.
And if instead of an "OR", I run two separate queries, joined by a UNION, they yield the same exact output in 4 seconds.
Although possible, it would be preferable to avoid using UNION, due to external factors¹.

Am I missing something obvious that's causing this huge disparity?
Is there a reasonable way to fix the LEFT-JOIN-only query so that it returns in less than, say, 10 seconds?

Standard query:

SELECT
  purchase.id
FROM
  purchase
  LEFT OUTER JOIN user on purchase.buyer = user.id
  LEFT OUTER JOIN user_documents on user.id = user_documents.user
  LEFT OUTER JOIN buyer_info on purchase.id = buyer_info.purchase
WHERE documents.value = '123' OR buyer_info.document = '123'

Example of UNION used:

SELECT
  purchase.id
FROM
  purchase
  LEFT OUTER JOIN user ON purchase.buyer = user.id
  LEFT OUTER JOIN user_documents ON user.id = user_documents.user
WHERE documents.value = '123'
UNION
SELECT
  purchase.id
FROM
  purchase
  LEFT OUTER JOIN buyer_info ON purchase.id = buyer_info.purchase
WHERE buyer_info.document = '123'

All fields used in all tables during the query are indexed fields.

PURCHASE and BUYER_INFO table: ~32 million records each.
USER table: ~16 million records.
USER_DOCUMENTS table: ~8 million records.

This is the simplified tables' descriptions:

PURCHASE
| Field | Type | Null | Key | Default |
| id | bigint(20) | NO | PRI | NULL |
| buyer | bigint(20) | YES | MUL | NULL |

USER
| Field | Type | Null | Key | Default |
| id | bigint(20) | NO | PRI | NULL |

USER_DOCUMENTS
| Field | Type | Null | Key | Default |
| id | bigint(20) | NO | PRI | NULL |
| USER | bigint(20) | NO | MUL | NULL |

BUYER_INFO
| Field | Type | Null | Key | Default |
| id | bigint(20) | NO | PRI | NULL |
| purchase | bigint(20) | NO | UNI | NULL |
| document | varchar(14) | YES | MUL | NULL |

I wasn't sure if this should be on StackOverflow or here, but I've read the Help Center and it mentioned query-performance is adequate here. Given that I've already gone through dozens of questions in StackOverflow while researching this and didn't found anything that helped, I thought this could be the right place.

I've already tried:

Pushing predicates into the OUTER JOIN clause, to no avail;
Removing two LEFT JOINs entirely and using WHERE EXISTS instead -- this helped but not nearly enough;
Using inner joins where applicable -- also helped but again not even close.

1 - External Factor: legacy critical production codebase that builds that SQL would need vastly greater changes in order to implement that as an UNION.

EDIT: addressing some of the comments
Both queries work perfectly as far as correct data retrieval goes, including the query using UNION.

Also, the UNION query is already fast enough for our purposes.

The only reason why I would prefer to avoid it is because it would require way more changes in a production codebase to get working. (The queries here are vastly sanitized and oversimplified.)
(That's why the very title says without UNION)

If that's the only sensible way to go, then we'll go for it, but I don't understand why adding an "OR" breaks the performance so much, and I thought maybe there was something I was overlooking.

If we brake the first query into two, they each take 2,5 seconds to run. But make it a single query using an OR operator and suddenly it takes 11 minutes.

I was hoping to get a better understanding of why is that, and if it was possible to fix it.

If you select from the results of a union all you will be able to filter the results you are looking for in a speedy fashion and only have to have a single where clause.

SELECT info.id
FROM
  (SELECT
    purchase.id
  FROM
    purchase
  LEFT OUTER JOIN user ON purchase.buyer = user.id
  LEFT OUTER JOIN user_documents ON user.id = user_documents.user
  UNION ALL
  SELECT
    purchase.id
  FROM
    purchase
  LEFT OUTER JOIN buyer_info ON purchase.id = buyer_info.purchase) info
WHERE info.document = '123'


ODBC Calling Fill - Unexpected closed connection after 2 hours Using PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
I use driver PostgresSQL Unicode(x64)  version 13.00.00.00
I have a query that is executed through an ODBC connection in a Powershell 5.1 script. I use the Fill() method to retreive about 3500 records daily. When the script works, it takes 2-5 minutes to execute and retrieve data when it works.
Problem is that the script "fails" half of the time approx. When this occurs, the Powershell script does stop only after 2h and 30 seconds.
We double checked the postgres logs and when this occurs, we see that the query successfully completed within 6 minutes always.  I don't know what to look for. any Idea?
Below is the error got:
Executed as user: NT Service\SQLSERVERAGENT. A job step
received an error at line 94 in a PowerShell script. The
corresponding line is '(New-Object system.Data.odbc.
odbcDataAdapter($cmd)).fill($ds) | out-null  '. Correct
the script and reschedule the job. The error information
returned by PowerShell is: 'Exception calling "Fill" with
"1" argument(s): "The connection has been disabled."  '.

Process Exit Code -1.

Not too familiar with postgreSQL.
Thanks!

This must be the TCP keepalive timeout: if there is no traffic on a TCP connection, the kernel will automatically start sending TCP keepalive packages after a while. If there is no response to those, it determines that the other side has hung up without closing the connection and reports an error message. The default time until the kernel starts sending these packages is two hours, which matches your observation.
Since PostgreSQL is not in the habit of hanging up on the client (look into the PostgreSQL log for a corresponding message), you must have a misconfigured firewall or router that cut off connections after a while. If you cannot fight that problem at the source, you might be able to work around it by reducing that keepalive idle time substantially.
One way to do that is to change the PostgreSQL parameter tcp_keepalives_idle to something like 120, which will cause it to send keepalive packages after two minutes of no traffic on the connection. Depending on which client you use, you might alternatively change the keepalive settings on the client side.