How to optimize query with order by Have query which generate OpenERP ORM. Table have 100k rows.

SELECT "tbl".id
FROM "tbl"
WHERE (("tbl"."active" = 'True')  AND  ("tbl"."is_company" IS NULL or "tbl"."is_company" = false ))
ORDER BY "tbl"."display_name"

With indexes :

    "ix_tbl_pkey" PRIMARY KEY, btree (id)
    "ix_active" btree (active)
    "ix_displayname" btree (display_name)
    "ix_iscompany" btree (is_company)

Query with order by takes 57735.775 ms.
Plan is :

 Sort  (cost=13031.73..13269.13 rows=94960 width=47) (actual time=57711.753..57725.079 rows=94967 loops=1)
   Sort Key: display_name
   Sort Method: quicksort  Memory: 12918kB
   ->  Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=47) (actual time=0.009..57.056 rows=94967 loops=1)
         Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
         Rows Removed by Filter: 623
 Total runtime: 57735.775 ms
(7 rows)

When i try without order by it takes 65.969 ms.
Plan is :

 Seq Scan on tbl (cost=0.00..5180.90 rows=94960 width=4) (actual time=0.026..60.782 rows=94967 loops=1)
   Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
   Rows Removed by Filter: 623
 Total runtime: 65.969 ms
(4 rows)

With  set enable_sort = off; it takes  1206.157 ms plan is :

 Index Scan using ix_displayname on tbl(cost=0.00..21479.14 rows=94960 width=47) (actual time=29.912..1194.954 rows=94967 loops=1)
   Filter: (active AND ((is_company IS NULL) OR (NOT is_company)))
   Rows Removed by Filter: 623
 Total runtime: 1206.157 ms
(4 rows)

Any way to optimize it with indexes ? Because we cant change something in ORM .

Using an index consisting of the columns display_name, id, active and is_company would enable the query to be run using the index alone.  Ordering the columns in the order provided would remove the sort requirement.  This comes at a slightly update cost, and could replace the ix_displayname index.

If active were rare, it would be faster to order the columns active, display_name, id, 'is_company.  However, updating the active column would have higher update costs.

You may need to select the display_name in an inner query, or use an index hint.


Query on Linked Server in SQL Active-Passive We have plan to implement Active-Passive SQL server cluster with shared storage with SQL 2014 version.
A-P: There will be two DB servers (DB1 and DB2) and one will be working (DB1) and the other (DB2) does not operate until there is any problem in the first one. Once the working cluster SQL server fails (fail – reboot/hardware failure/OS corruption, etc.), the other server (DB2) takes over from it and starts functioning as regular SQL server.

Just I want to know behaviour of linked server in A-P environmnet during below cases.

We have configured linked server on DB1 (active node) to fetch remote SQL data. If we perform failover from DB1-DB2, Will linked server work automatically on DB2 active node? or Is there any manual modifications are required to work linked server properly?
When we failback from DB2 to DB1, Will linked server work automatically on DB1 active node? or Is there any manual modifications are required to work linked server properly?
Can we use SQL Virtual IP in linked server configuration in A-P environment?

No extra configuration is needed for the linked server to work on
DB2 and that's one of the Benefits of a Failover Cluster
Instance:

  In case of a failure (hardware failures, operating system failures,
  application or service failures), or a planned upgrade, the resource
  group ownership is moved to another WSFC node. This process is
  transparent to the client or application connecting to SQL Server and
  this minimize the downtime the application or clients experience
  during a failure. [...]

  Zero reconfiguration of applications and clients during failovers

Yes, it will work automaticaly on DB1 when it fails back and the reason is that:

  Only one of the nodes in the FCI owns the WSFC resource group at a
  time.

In fact you must use that virtual ip address or network
name
in order for the failover to work transparently:

  An FCI runs in a WSFC resource group with one or more WSFC nodes. When
  the FCI starts up, one of the nodes assume ownership of the resource
  group and brings its SQL Server instance online. The resources owned
  by this node include:

  Network name
  IP address
  Shared disks
  SQL Server Database Engine service
  SQL Server Agent service
  SQL Server Analysis Services service, if installed
  One file share resource, if the FILESTREAM feature is installed

This image from Derek Seaman's IT Blog might help you understand it better:

You never configure your applications (or linked servers) to reference DB1 (NodeA) or DB2 (NodeB), you must always reference the virtual ip address or network name (VirtualNode1) and it shall transparently address the active node of the cluster.


Error running PostgreSQL query from SQL Server 2016 The below query runs well in SQL Server 2016:
select ResellerId, vCompanyName ,x.*
from wlt_tblReseller AS main
outer apply ( select (count (ipkReportTypeId)) AS "count", vReportTypeName
              from wlt_tblReseller _all
              inner join wlt_tblClient clients  on clients.ifkParentResellerId = _all.ResellerId
              inner join wlt_tblReport_CompanyMaster reporslogs on reporslogs.ifkCompanyId = clients.ClientId
              inner join wlt_tblReports_TypeMaster rpt_types on rpt_types.ipkReportTypeId = reporslogs.ifkReportTypeId
              where RootResellerId = main.ResellerId
                and rpt_types.bStatus =1
                and bIsStatic =1
                and vReportTypeName  is not null
              group by ipkReportTypeId,vReportTypeName
            ) AS x
WHERE IsMiniReseller = 0
  and ResellerId <> 1
  and vReportTypeName is not null
order by  vCompanyName desc

But when I take it to PostgreSQL and change OUTER APPLY to LEFT JOIN LATERAL, it does not run and produces the following error:

ERROR:  syntax error at or near "WHERE" LINE 9:    )AS x WHERE
IsMiniReseller = 0 and ResellerId <> 1  and v...
SQL state: 42601 Character: 649

What could I be missing?

select main.ResellerId, main.vCompanyName , x."count", x.vReportTypeName
from wlt_tblReseller AS main
LEFT JOIN   ( select (count (ipkReportTypeId)) AS "count", vReportTypeName, RootResellerId
              from wlt_tblReseller _all
              inner join wlt_tblClient clients  on clients.ifkParentResellerId = _all.ResellerId
              inner join wlt_tblReport_CompanyMaster reporslogs on reporslogs.ifkCompanyId = clients.ClientId
              inner join wlt_tblReports_TypeMaster rpt_types on rpt_types.ipkReportTypeId = reporslogs.ifkReportTypeId
              where
                    rpt_types.bStatus =1
                and bIsStatic =1
                and vReportTypeName  is not null
              group by ipkReportTypeId,vReportTypeName
            ) AS x ON x.RootResellerId = main.ResellerId
WHERE IsMiniReseller = 0
  and ResellerId <> 1
  and vReportTypeName is not null
order by  vCompanyName desc

PS. Add table aliases to all column names for to remove potential ambiguity...


Use Natively-Compiled Stored Procedures in disk-based tables Is it completely worthless to use a Natively-Compiled Stored Procedure over disk-based tables? (SQL Server 2016)

Should all the queries inside a Natively-Compiled Stored Procedure point only to in-memory tables? I ask, because I am inserting and updating table T1, but the source for such tasks are disk-based tables.

Natively-compiled stored procedures can only reference:

in-memory tables
in-memory table variables / TVPs
in-memory natively compiled inline TVFs

Sounds like what you want is interop, and that can't be done inside a natively-compiled procedure. If it could, you would be discarding much of the performance benefits of the natively-compiled procedure.

This article is a couple of years old but not much about the core architecture has changed in 2016 / 2017:

The Promise – and the Pitfalls – of In-Memory OLTP


How to solve "Servlet error" when using database control in Oracle 12c? When I want to use Oracle 12c GUI, I receive an error as written below:

  Servlet error: An exception occurred.

  The current application deployment descriptors do   not allow for including it in this response. Please consult the application log for details.

My browser is Google Chrome, and the firewall and antivirus are also deactivated. I should also mention that I have started all Oracle services in services.msc section.
I have tried to use GUI by entering the address https://pedcom-pc:1158/em , too, but I received the same error.

That would be very helpful of you if you show me a solution to this problem.

Database control has been desupported in 12c. You need to use EM Express or EM Could Control


DEFAULT EMPTY_CLOB() CREATE TABLE TEST
(
  ID    NUMBER,
  COL1  NCLOB                                   DEFAULT 'EMPTY_CLOB()',
  COL2  NCLOB                                   DEFAULT EMPTY_CLOB(),
  COL3  NCLOB                                   DEFAULT (EMPTY_CLOB())
);

What is the diferrence between DEFAULT 'EMPTY_CLOB()' and DEFAULT EMPTY_CLOB() and DEFAULT (EMPTY_CLOB()). All empty_clob() output same or are they different?

INSERT INTO TEST(ID) VALUES(1);

SELECT ID, LENGTH(COL1), LENGTH(COL2), LENGTH(COL3) FROM TEST;

output:

1   12  0 0

DEFAULT 'EMPTY_CLOB()' is useless. It inserts the text 'EMPTY_CLOB()' to your table. String "EMPTY_CLOB()" has 12 characters as you see.

EMPTY_CLOB() and (EMPTY_CLOB()) should be the same, i.e. an empty CLOB.


MongoDB Sharded Cluster on AWS reliability I have setup one mongodb cluster on aws with 3 shards as replicaset, 3 mongos and 3 configs.

All the 3 EC2 Instance are t2.medium (4GB, 2CPU, 50GB EBS 150 IOPS (io1 not gp2))

Our database is only 1.65GB but on disk + replication it is around 30GB per Instance (including journarls ... etc) and each shard holds around 550MB of the data

It only functions perfectly when the all instances are online!

Do you think it is because of some misconfiguration or is it because of that these instances are small, not EBS optimised.

It was much better to use standalone, as it is easier to maintain when something happen!

as shard key we used _id : hashed

is it because the mongos and configs are hosted with the mongod ?

is it because when stepping down a primary then mongodb must sync the data to the new primary which because of the network of aws, memory and IO make this slowness?

The issue was actually in using the T2 instance, we upgraded to M4 instances and it is running well


MySql: check and remove duplicate rows How can I check if a table contains duplicate rows, with the same value of the couple  (date, time) ?
How can I remove all but one row ?
Id is a sequential number that I increment after every post, how can I restore it after removing a row ?

the table tableTen is:

+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int(11)     | YES  |     | NULL    |       |
| symbol    | varchar(25) | YES  |     | NULL    |       |
| date      | date        | YES  | MUL | NULL    |       |
| time      | time        | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+

1. How can I check if a table contains duplicate rows, with the same value of the couple (date, time) ?

SELECT *  FROM db.table HAVING COUNT(symbol) > 1

2. How can I remove all but one row ?

a) If you want to keep the row with the lowest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id > n2.id AND n1.symbol= n2.symbol

b) If you want to keep the row with the highest id value:

DELETE n1 FROM table n1, table n2 WHERE n1.id < n2.id AND n1.symbol= n2.symbol

3. Id is a sequential number that I increment after every post, how can I restore it after removing a row ?

Please try http://befused.com/mysql/reset-auto-increment


Cannot set innodb_buffer_pool_size above 1023M in my.ini Windows server 2012 R2 with 64GB RAM, MySQL 8. The Database is about 5GB.

I am using a buffer pool of 1023M, as shown below:

innodb_buffer_pool_size=1023M

If I enter anything above 1023M e.g. 1024M or 2G then MySQL will not start and the following appears in the errorlog:

  "Configuring persisted options failed: "Variable
  'innodb_buffer_pool_size' can't be set to the value of '8388608'".

If I use the value 8388608, then it starts (which is ironic) but that's not enough.

When running using:

SET GLOBAL innodb_buffer_pool_size=10737418240;

It starts fine and sets the pool to 10GB - but that reverts back when rebooted.

Any pointers would be great.

"Persisted options" is new in 8.0.  See if this works:

SET PERSIST innodb_buffer_pool_size=10737418240;

For more info:

https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html


View active MySQL connections per user I need a query that will give me the active or open number of connections to a certain database for a certain user. I have been searching for hours and have come up with nothing so far. I am close, so here is what I have tried.

SHOW STATUS WHERE `variable_name` = 'Threads_connected';
SHOW STATUS LIKE '%onn%';
SELECT * FROM information_schema.processlist WHERE USER='database_user';

I work for a web hosting company, and one of our clients keeps hitting his max_user_connections limit, so in troubleshooting why I need to know how many connections his user is using right now since he is no a shared server. I am currently using MySQL (InnoDB) version 5.5.36. Any help would be greatly appreciated!

What you need is a breakdown by user and hostname along with a total

SELECT IFNULL(usr,'All Users') user,IFNULL(hst,'All Hosts') host,COUNT(1) Connections
FROM
(
    SELECT user usr,LEFT(host,LOCATE(':',host) - 1) hst
    FROM information_schema.processlist
    WHERE user NOT IN ('system user','root')
) A GROUP BY usr,hst WITH ROLLUP;

This will handle host address that have a colon separating hostname and port number

I hope everybody is not logging in as root


MDX - Average for a Cube I am trying to find the average value(val) for every month(1,2,3,4,...10) for a,b,c on the given cube below.

Here is my query:

select
  ([Measures].[VAL1],[DIMPROPERTY TIME].[TIMEDIM].[MONTH])  on 0,
{[DIMPROPERTYLENDER].[Hierarchy].[NAME]} on 1

from [PropertyData2016]

I am pretty new to this, so any help is appreciated.

Have you read the msdn documentation and looked at the examples?

https://msdn.microsoft.com/en-us/library/ms146067.aspx

Edit: Try this.

WITH MEMBER Measures.[AVGSALE] AS
AVG (
    DESCENDANTS(
          [DIMPROPERTYLENDER].[Hierarchy].[NAME],
          [DIMPROPERTY TIME].[TIMEDIM].[MONTH].ALLMEMBERS)
          ),
          [Measures].[VAL1]
    )

SELECT NON EMPTY Measures.[AVGSALE] ON 0,
    NON EMPTY ([DIMPROPERTYLENDER].[Hierarchy].[NAME], [DIMPROPERTY TIME].[TIMEDIM].[MONTH]) ON 1

from [PropertyData2016]

PS: you might need to use [DIMPROPERTY TIME].[TIMEDIM].[MONTH].[MONTH] in your final select.


Select Oracle clob field from remote table over dblink I am trying to select a clob field thru flink from a remote table.I am able to see the values in editor result but not able to select thru program or any export.

SQL Error: ORA-22992: cannot use LOB locators selected from remote tables
22992. 00000 -  "cannot use LOB locators selected from remote tables"
*Cause:    A remote LOB column cannot be referenced.
*Action:   Remove references to LOBs in remote tables

I am able to select local fields and use them in application but failed in these.I can view in the toad  result perfectly when running queries in editor.there also not able to export that particular field..

Remote database:

SQL> create table t1(id number, c1 clob);

Table created.

SQL> insert into t1 values(1, 'HELLO WORLD!');

1 row created.

SQL> insert into t1 values(2, LPAD('A', 4000, 'A'));

1 row created.

SQL> update t1 set c1 = c1 || c1 where id = 2;

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> /

1 row updated.

SQL> commit;

Commit complete.

SQL> select id, length(c1) from t1;

        ID LENGTH(C1)
---------- ----------
         1         12
         2     128000

Local database:

SQL> desc t1@s112
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 C1                                                 CLOB

SQL> select c1 from t1@s112 where id = 1;
ERROR:
ORA-22992: cannot use LOB locators selected from remote tables

no rows selected

SQL> select to_char(c1)  from t1@s112 where id = 1;

TO_CHAR(C1)
--------------------------------------------------------------------------------
HELLO WORLD!

The problem is, CHAR types are limited to 4000 bytes, so:

SQL> select to_char(c1) from t1@s112 where id = 2;
select to_char(c1) from t1@s112 where id = 2
*
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual:
128000, maximum: 4000)
ORA-02063: preceding line from S112

A possible workaround:

SQL> create global temporary table t1_tmp on commit preserve rows as select * from t1@s112 where 1 = 2;

Table created.

SQL> insert into t1_tmp select * from t1@s112 where id = 2;

1 row created.

SQL> commit;

Commit complete.

SQL> select id, length(c1) from t1_tmp;

        ID LENGTH(C1)
---------- ----------
         2     128000

The above is from a 11.2 database.

Starting with 12.2, the limitation of selecting a LOB through a dblink was removed, no workaround needed.


Mysql Sharding. Application Layer vs Mysql Layer I am going to create shards in mysql table and I am stuck at a dileman whether to go for application layer sharding or let MySQL handle it.
I mean, wether I should be figuring  out which shard to go and query at application layer or pass on the query to the MySQL driver and let it decide.

I read about some pros and cons of both here but I am unable to come up with a conclusion.

My personal opinion is to go for the MySQL driver handling it. Open for discussion on this.

Please share your views/experiences.

Sharding does not speed up searching.
Sharding is useful when you hit a write-scaling limit.  Most social media apps end up with some form of sharding to handle many thousands of writes per second.
MySQL has no sharding capability.  However, add-ons, such as Spider and various Proxies can help.
Or you can build it into the application layer.  In this case, each client must first figure out which database shard to talk to, then talk only to that one.  This assumes all the activity can be handled in a single shard.
Sure, the application layer may make use of database tables to keep track of what shards exist, and which item is in which shard.


MariaDB 10.5 How to Prevent High Memory Usage on Windows I am running MariaDB 10.5 on I am having trouble with MariaDB using too much memory on my Windows 10 machine. I have 16GB of RAM, and MariaDB ends up using around 8GB.
SHOW VARIABLES; https://pastebin.com/zCuQw2Ds
I have attempted to change innodb_buffer_pool_size to small or large values. The memory usage is the same.
The program using the DB saves around 2k - 5k records into one table once per second (1 query)
around every 5 seconds a transaction runs which deletes 10k different records from the same table and inserts 10 records into another table.
All of these queries run in under 1 second.
Is the data being inserted also being cached somehow? If so, how do I limit that behavior?
Also, here is SHOW GLOBAL STATUS output: https://pastebin.com/ubCMY6Me
Any help is appreciated, thanks!

Hmmm...  I don't see "high" memory usage.  Instead, I see "low" usage.
Analysis of GLOBAL STATUS and VARIABLES:

Observations:

Version: 10.5.9-MariaDB
16 GB of RAM
Uptime = 1d 22:31:42
Are you sure this was a SHOW GLOBAL STATUS ?
You are running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:
key_buffer_size = 20M           -- save some RAM
innodb_buffer_pool_size = 11G   -- more caching (mostly saves I/O)
table_open_cache = 3000
query_cache_size = 0            -- to turn off

Why is innodb_lru_scan_depth so high (1536)?
There were a lot of rows deleted; do you think there is a problem with DELETEs?  (There may be techniques to make them more efficient.)
Details and other observations:
( Key_blocks_used * 1024 / key_buffer_size ) = 0 * 1024 / 128M = 0 -- Percent of key_buffer used. High-water-mark.
-- Lower key_buffer_size (now 134217728) to avoid unnecessary memory usage.
( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) ) = ((128M / 0.20 + 128M / 0.70)) / 16384M = 5.0% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory
( Table_open_cache_misses ) = 543,542 / 167502 = 3.2 /sec
-- May need to increase table_open_cache (now 2000)
( Table_open_cache_misses / (Table_open_cache_hits + Table_open_cache_misses) ) = 543,542 / (274450 + 543542) = 66.4% -- Effectiveness of table_open_cache.
-- Increase table_open_cache (now 2000) and check table_open_cache_instances (now 8).
( innodb_buffer_pool_size ) = 128M -- InnoDB Data + Index cache
-- 128M (an old default) is woefully small.
( innodb_buffer_pool_size ) = 128M / 16384M = 0.78% -- % of RAM used for InnoDB buffer_pool
-- Set to about 70% of available RAM. (To low is less efficient; too high risks swapping.)
( innodb_lru_scan_depth ) = 1,536
-- "InnoDB: page_cleaner: 1000ms intended loop took ..." may be fixed by lowering lru_scan_depth
( innodb_io_capacity ) = 200 -- When flushing, use this many IOPs.
-- Reads could be slugghish or spiky.
( innodb_io_capacity_max / innodb_io_capacity ) = 2,000 / 200 = 10 -- Capacity: max/plain
-- Recommend 2. Max should be about equal to the IOPs your I/O subsystem can handle. (If the drive type is unknown 2000/200 may be a reasonable pair.)
( Innodb_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead ) = 7,541 / 158 = 4772.8% -- Utility of read_ahead.
-- Turn off innodb_random_read_ahead (now OFF).
( Innodb_dblwr_pages_written/Innodb_pages_written ) = 73,383/706683 = 10.4% -- Seems like these values should be equal?
( innodb_log_files_in_group ) = 1 -- Number of InnoDB log files
-- 2 is probably the only reasonable value.
A large number may cause performance problems.
( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 24,911,135,232 / (167502 / 3600) / 1 / 96M = 5.32 -- Ratio
-- (see minutes)
( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 167,502 / 60 * 96M / 24911135232 = 11.3 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size (now 100663296). (Cannot change in AWS.)
( innodb_flush_method ) = innodb_flush_method = unbuffered -- How InnoDB should ask the OS to write blocks. Suggest O_DIRECT or O_ALL_DIRECT (Percona) to avoid double buffering. (At least for Unix.) See chrischandler for caveat about O_ALL_DIRECT
( default_tmp_storage_engine ) = default_tmp_storage_engine =
( innodb_flush_neighbors ) = 1 -- A minor optimization when writing blocks to disk.
-- Use 0 for SSD drives; 1 for HDD.
( innodb_io_capacity ) = 200 -- I/O ops per second capable on disk . 100 for slow drives; 200 for spinning drives; 1000-2000 for SSDs; multiply by RAID factor.
( innodb_adaptive_hash_index ) = innodb_adaptive_hash_index = OFF -- Usually should be ON.
-- There are cases where OFF is better. See also innodb_adaptive_hash_index_parts (now 8) (after 5.7.9) and innodb_adaptive_hash_index_partitions (MariaDB and Percona). ON has been implicated in rare crashes (bug 73890).
( innodb_print_all_deadlocks ) = innodb_print_all_deadlocks = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.
( join_buffer_size ) = 262,144 / 16384M = 0.00% -- 0-N per thread. May speed up JOINs (better to fix queries/indexes) (all engines) Used for index scan, range index scan, full table scan, each full JOIN, etc.
-- If large, decrease join_buffer_size (now 262144) to avoid memory pressure. Suggest less than 1% of RAM. If small, increase to 0.01% of RAM to improve some queries.
( innodb_ft_result_cache_limit ) = 2,000,000,000 / 16384M = 11.6% -- Byte limit on FULLTEXT resultset. (Possibly not preallocated, but grows?)
-- Lower the setting.
( character_set_server ) = character_set_server = utf8
-- Charset problems may be helped by setting character_set_server (now utf8) to utf8mb4. That is the future default.
( net_buffer_length / max_allowed_packet ) = 16,384 / 16M = 0.10%
( local_infile ) = local_infile = ON
-- local_infile (now ON) = ON is a potential security issue
( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (79156 + 8 + 43352 + 0) / 67922 = 1.8 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.
( ( Com_stmt_prepare - Com_stmt_close ) / ( Com_stmt_prepare + Com_stmt_close ) ) = ( 22104 - 0 ) / ( 22104 + 0 ) = 100.0% -- Are you closing your prepared statements?
-- Add Closes.
( Com_stmt_prepare - Com_stmt_close ) = 22,104 - 0 = 22,104 -- How many prepared statements have not been closed.
-- CLOSE prepared statements
( Com_stmt_close / Com_stmt_prepare ) = 0 / 22104 = 0 -- Prepared statements should be Closed.
-- Check whether all Prepared statements are "Closed".
( Com__biggest ) = Com__biggest = Com_stmt_execute -- Which of the "Com_" metrics is biggest.
-- Normally it is Com_select (now 143366). If something else, then it may be a sloppy platform, or may be something else.
( slow_query_log ) = slow_query_log = OFF -- Whether to log slow queries. (5.1.12)
( long_query_time ) = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2
( back_log ) = 80 -- (Autosized as of 5.6.6; based on max_connections)
-- Raising to min(150, max_connections (now 151)) may help when doing lots of connections.
( Max_used_connections / max_connections ) = 19 / 151 = 12.6% -- Peak % of connections
-- Since several memory factors can expand based on max_connections (now 151), it is good not to have that setting too high.
( Aborted_clients / Connections ) = 263 / 306 = 85.9% -- Threads bumped due to timeout
-- Increase wait_timeout (now 28800); be nice, use disconnect
( Aborted_connects / Connections ) = 136 / 306 = 44.4% -- Perhaps a hacker is trying to break in? (Attempts to connect)
( thread_cache_size / Max_used_connections ) = 151 / 19 = 794.7%
-- There is no advantage in having the thread cache bigger than your likely number of connections. Wasting space is the disadvantage.
You have the Query Cache half-off. You should set both query_cache_type = OFF and query_cache_size = 0 . There is (according to a rumor) a 'bug' in the QC code that leaves some code on unless you turn off both of those settings.
Abnormally small:
Aria_pagecache_write_requests = 1.5 /HR
Com_set_option = 1.2 /HR
Com_show_fields = 0
Created_tmp_files = 0.086 /HR
Handler_icp_attempts = 0.34 /HR
Handler_icp_match = 0.34 /HR
Handler_read_rnd = 33 /HR
Handler_tmp_update = 0
Handler_tmp_write = 0.13 /sec
Handler_update = 0.17 /HR
Innodb_buffer_pool_pages_misc = 0
Innodb_buffer_pool_pages_misc * 16384 / innodb_buffer_pool_size = 0
Innodb_buffer_pool_pages_total = 8,065
Innodb_ibuf_merged_inserts = 0
Innodb_ibuf_merges = 0
Innodb_log_writes / Innodb_log_write_requests = 0.35%
Innodb_max_trx_id = 688,304
Innodb_mem_adaptive_hash = 0
Innodb_mem_dictionary = 892,392
Innodb_num_open_files = 17
Innodb_rows_updated = 0.17 /HR
Innodb_secondary_index_triggered_cluster_reads = 0.41 /HR
Memory_used_initial = 3.52e+7
Rows_tmp_read = 0.13 /sec
Sort_priority_queue_sorts = 0
Sort_rows = 33 /HR
Sort_scan = 1.8 /HR
Table_locks_immediate = 3.2 /HR
aria_checkpoint_log_activity = 1.05e+6
aria_pagecache_buffer_size = 128MB
innodb_adaptive_max_sleep_delay = 0
innodb_background_scrub_data_check_interval = 0
innodb_background_scrub_data_interval = 0
innodb_buffer_pool_chunk_size = 128MB
innodb_max_undo_log_size = 10MB
innodb_online_alter_log_max_size = 128MB
innodb_sort_buffer_size = 1.05e+6
innodb_spin_wait_delay = 4
lock_wait_timeout = 86,400
query_cache_limit = 1.05e+6

Abnormally large:
Com_create_index = 0.021 /HR
Com_drop_index = 0.064 /HR
Com_rename_table = 0.064 /HR
Com_show_open_tables = 0.13 /HR
Handler_delete = 447 /sec
Handler_discover = 1.7 /HR
Innodb_buffer_pool_pages_flushed / max(Questions, Queries) = 2.27
Innodb_buffer_pool_pages_lru_flushed = 1,606
Innodb_buffer_pool_reads * innodb_page_size / innodb_buffer_pool_size = 35029.2%
Innodb_buffer_pool_write_requests / Innodb_buffer_pool_pages_flushed = 673
Innodb_dblwr_pages_written / Innodb_dblwr_writes = 71.4
Innodb_os_log_pending_writes = 1.84e+19
Innodb_rows_deleted = 447 /sec
Innodb_rows_deleted + Innodb_rows_inserted = 1274 /sec
Innodb_rows_inserted = 826 /sec
Memory_used = 42.6%
Open_streams = 4
Prepared_stmt_count = 967
Select_range / Com_select = 71.8%
Threadpool_threads = 4
aria_sort_buffer_size = 256.0MB
innodb_lru_scan_depth / innodb_io_capacity = 7.68
max_relay_log_size = 1024MB
performance_schema_max_cond_classes = 90
performance_schema_max_statement_classes = 222

Abnormal strings:
aria_recover_options = BACKUP,QUICK
binlog_row_metadata = NO_LOG
disconnect_on_expired_password = OFF
have_crypt = NO
histogram_type = DOUBLE_PREC_HB
innodb_fast_shutdown = 1
innodb_log_optimize_ddl = OFF
log_slow_admin_statements = ON
lower_case_file_system = ON
lower_case_table_names = 1
myisam_stats_method = NULLS_UNEQUAL
old_alter_table = DEFAULT
opt_s__optimize_join_buffer_size = on
optimizer_trace = enabled=off
slave_parallel_mode = optimistic
thread_handling = pool-of-threads
time_zone = +00:00
use_stat_tables = PREFERABLY_FOR_QUERIES
version_compile_machine = x64


mysql ibdata1 file corrupted or increased unusually We are using MySQL 5.2 version all InnoDB tables.

I have observed that ibdata1 file got increased very unusually like occupied 500 GB in 2 days.

In last 6 months, it had taken only 6 GB though there were no inserts at all. I also deleted the ibdata1, ib_logfile0 and  ib_logfile1 and restarted mysql.

It worked fine. but again after 6 months same issue, and the ibdata1 got increased to 550 GB. There are no errors found in error log file.

I would like to know what may be the reason in general ?

Please keep in mind that ibdata1 is the system tablespace. It is home of the InnoDB Infrastructure. Here are the following classes of information stored in ibdata1.

Table Data Pages (if innodb_file_per_table disabled)
Table Index Pages (if innodb_file_per_table disabled)
Data Dictionary (Tablespace IDs, Logical-to-Physical Mapping to Tables)
Double Write Buffer (Handles Data Redundancy to Support Crash Recovery)
Insert Buffer (Handles Updates to Secondary Indexes)
Rollback Segments (Manages Reverse Engineering of Uncommitted Transactions)
Undo Space (Containers of Information for Rollback Operations)
Click to See Pictorial Representation of the InnoDB Infrastructure

As you can see, ibdata1 has a lot of moving parts. The fastest growing section of ibdata1 would be the undo space, especially when there are a lot of read transactions and a few write transactions.

Please read me past posts on doing InnoDB Cleanup:

Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Jun 19, 2012 : whether a big (but not used) ibdata1 slow anything down
Jan 07, 2013 : Database space doesn't match ibdata1 size


SQL Server recommended MAXDOP settings for NUMA I have an OLTP SQL Database running on SQL 2008 R2 Enterprise Edition that may be fairly busy (5000 batches/sec) in next couple of months after going live production. Looking into server configuration I have question about setting MAXDOP however now a days what I find as a regular reader of this forum general consensus is that to leave server wide MAXDOP setting to 0 for tasks that need parallel operation (e.g. index rebuild and change cost threshold of parallelism to a higher number (15 or 20 instead of default 5).

However, this server has NUMA nodes and based on these MS support articles
http://support.microsoft.com/kb/329204

http://support.microsoft.com/kb/2023536

to prevent costly foreign memory access it is recommended to set MAXDOP = number of cores in the physical processor.

This server has 2 physical processors with 16 logical (8 physical) cores on each socket. Does guideline suggested by that MS support KB is still good?

I know everything need to be tried and tested but my goal is to set MAXDOP to the best theoretical number and adjust it if needed later on. Based on it should I set MAXDOP setting to 8?

Typically I recommend to my clients leaving the MAXDOP alone.  SQL Server is NUMA aware so it knows which NUMA node the processors are in and which NUMA node the memory is in and it'll do it's best to assign the work to a scheduler on the correct NUMA node for the data that you are looking for.

Looking into this sort of stuff also requires knowing how many NUMA nodes you have.  Is each physical processor in its own NUMA node or are they node in the same NUMA node?


What is the fate of VODKA Indexing, is it dead? I see numerous mentions online of Vodka indexing with regard to PostgreSQL.

On the Wiki it's defined as,

  (Korotkov) Vodka maintains two index files, one for keys and one for heap item pointers (postings). Each index file can be, in effect, managed by a different access method. This is a generalization of GIN, which uses btree (albeit its own btree code, not src/backend/access/nbtree) for both keys and postings. The keys index file has item pointers that refer to the postings index file. When the postings file's access method needs to move an index item, it must arrange to update any item pointer in the keys file. To that end, proposing a new "amupdateiptr" access method function. Calls to that function, when necessary, will arrive right after an amgettuple call.

Aside from that, you can see this post on the list

  We are on the way to postgres-centric company and we'll continue our
  development in that company. We certainly don't need funding for the VODKA, our national product :)  I thought about crowd-funding, but didn't find any good cases for products similar to postgres. So, we decided to go
  traditional way - built our own company and get money from industry. In
  short perspective we delay our development, since many-many other
  non-developers business, but in long perspective we'll have more russian
  contributors for the postgres project.  Postgres in Russia was popular
  database, but now it gets much more interests from business and goverment,
  so I'm quite optimistic.

What ever happened to this and is it dead or did it get merged into something else?

I asked this question when I last saw Alexander (who is Vodka's developper).
To him, Vodka was more a Proof of Concept than an actual thing suitable for production. He needs to rewrite it all to achieve production-ready code.

But, he has a lot of things to do and so the project is frozen for now.

Cheers,

Arkena


How can I group the null results in this FULL OUTER JOIN with non-null responses? I am looking at the overlap and non-overlap (unique values) of users-ids from two different select statements using a full join. The main differentiation being that one table will have a deal_id = 0 and the other will have any deal_id greater than or equal to one.
I am joining the select statements on exchange_id, pub_id, and user_id but not on deal_id.

The field incremental value is trying to calculate users who are present in the pool deal_id >= 1 and not present in the pool of deal_id = 0 (a main reason for the full join).

Here is a simplification of the query I've typed up, it's in two select statements :

SET
hive.auto.convert.join = TRUE
;

SELECT
    First.deal_id
    ,COALESCE( First.exchange_id, Second.exchange_id ) as exchange_id
    ,COALESCE( First.pub_id, Second.pub_id ) as pub_id
    ,COUNT (DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
    ,SUM (First.imps) AS First_imps
    ,SUM (Second.imps) AS Second_imps
    FROM
        (
            SELECT
                a.deal_id
                ,a.exchange_id
                ,a.pub_id
                ,a.user_id
                ,1 AS imps
            FROM
                logs a
            WHERE
                a.deal_id >= 1
            AND a.event_type = 'TRUE'
        ) First
        FULL JOIN (
            SELECT
                a.exchange_id
                ,a.pub_id
                ,a.user_id
                ,1 AS imps
            FROM
                logs a
            WHERE
            a.deal_id = 0
            AND a.event_type = 'TRUE'
        ) Second
        ON (
            First.exchange_id = Second.exchange_id
            AND First.pub_id = Second.pub_id
            AND First.user_id = Second.user_id
        )
        GROUP BY
        First.deal_id
        ,COALESCE( First.exchange_id, Second.exchange_id )
        ,COALESCE( First.pub_id, Second.pub_id )
;

Here are the results I am seeing:

DEAL_ID    EXCHANGE_ID    PUB_ID    INCREMENTAL    FIRST_IMPS    SECOND_IMPS
/N         4              1780      0              0             15
/N         4              1560      0              0             32
3389       4              1780      2              7             6
1534       4              1560      4              9             8

And here is what I would like to see:

DEAL_ID    EXCHANGE_ID    PUB_ID    INCREMENTAL    FIRST_IMPS    SECOND_IMPS
3389       4              1780      2              7             21
1534       4              1560      4              9             40

Where the results with a null deal id match up to the results with a non-null deal id based on exchange_id and pub_id.

What can I do?

Similar to this problem but this solution isn't working for this problem.

Note: I've posted this question on stackoverflow here but thought I might try dba instead

Edit: Here is a sqlfiddle that replicates the problem, note that it's using PostgreSQL while I'm using hql

All this approach does in to make your original query a derived table then group by pub_id.

SET hive.auto.convert.join = TRUE;

SELECT max(DEAL_ID) as DEAL_ID
     , EXCHANGE_ID
     , PUB_ID
     , sum(INCREMENTAL) as INCREMENTAL
     , sum(FIRST_IMPS) as FIRST_IMPS
     , sum(SECOND_IMPS) as SECOND_IMPS
  FROM (

    SELECT First.deal_id
         , COALESCE( First.exchange_id, Second.exchange_id ) as exchange_id
         , COALESCE( First.pub_id, Second.pub_id ) as pub_id
         , COUNT(DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
        , SUM(First.imps) AS First_imps
        , SUM(Second.imps) AS Second_imps
    FROM (SELECT a.deal_id
               , a.exchange_id
               , a.pub_id
               , a.user_id
               , 1 AS imps
            FROM logs a
           WHERE a.deal_id >= 1
            AND a.event_type = 'TRUE'
         ) First
FULL JOIN (SELECT a.exchange_id
                , a.pub_id
                , a.user_id
               , 1 AS imps
            FROM logs a
           WHERE a.deal_id = 0
             AND a.event_type = 'TRUE'
          ) Second
      ON (   First.exchange_id = Second.exchange_id
         AND First.pub_id = Second.pub_id
         AND First.user_id = Second.user_id
         )
   GROUP BY First.deal_id
          , COALESCE( First.exchange_id, Second.exchange_id )
          , COALESCE( First.pub_id, Second.pub_id )

  ) group by pub_id, exchange_id

;


Need to change existing design to accomodate more detailed tracking of time Currently I have a relationship between contact and time_log (contact is a verb here - could be a meeting/phonecall/email).  Time_log holds both a date and time, each contact is associated with one date and amount of time spent on that contact.  I also have a list of several contact_services_provided at each contact.  This was fine until I learnt that time spent on each of the specific services provided at a contact was also required.  I thought of linking time_log to contact_services_provided instead of directly to contact, but then the date is repeated for each service provided which is not correct (there should be only one date per contact), so I'm not sure how to proceed.  Also, I've added a sad image of my user interface and associated database design which might help to visualize the problem (you'll have to zoom in...)

Can't see the attachment, but if I understand correctly, any one contact can only have one date. Each contact is made up of (potentially) more than one contact_services, each having it's own duration. In this case, a suggestion would be:

contact_table:
contact_id
time_stamp
duration (of the contact)

contact_service_table:
unique_id
contact_id
contact_service_type_id (or something along those lines)
duration (of the service)

Elaborate further if I am misunderstanding.


pgpool-II configuring multiple master databases I could not find a solution for this anywhere, not even in the pgpool manuals.

How do I configure multiple - master postgresql databases with pgpool-II? Additional requirement is that each master should have its own set of slaves using streaming replication. The reason for multiple master is that each master holds a partition of a large data set.

It is a large data set (say about 100 million users, their account information, plus additional information about their accounts) which is partitioned; so it is siloed. The data model is being designed so that the silos do not need to talk to each other.

Any help and guidance is much appreciated.

OK your first decision is WHERE to split the silos out, in the application layer, or in the db layer. If your application will keep track of the silos then you can run individual pgbouncer or pgpool servers on different ports for each silo.

If you want the application to not have to know or deal with how siloing is done, then something like plproxy can work wonders. Basically you have 1 or more plproxy servers that distribute all the queries / data based on some pre-determined modulo math or some other method for breaking it up, and your app just calls the plproxy server with it's queries and the plproxy server is where the logic for siloing exists.

Each method has it's pluses and minuses. The nice thing about it being in the app layer is that your database layer is fairly simple. just a bunch of master/slave pairs. If you add a new silo you just edit the config file for it in the app and it goes online. The disadvantage is that all apps hitting your data HAVE to be silo aware etc.

With plproxy the advantage is that the app never knows its siloed. If you write a fairly simple SOAP type interface to talk to the plproxy servers then every app your write can use that single interface and never have to be told which silo does what. Now though the complexity is moved into the db layer. Any changes to the farm structure require work on the db level. Also plproxy works by basically wrapping all your database work into functions ahead of time. If you want to select data from a table joined to another table you need to create a function to do that and call that function on the plproxy machine. It seems overly complex at first but actually it's not that hard, just quite different from slinging random SQL at your db servers.


Mysql sub query returns more than 1 row SELECT * FROM wp_posts WHERE ID IN
(
(SELECT courses  FROM wp_category WHERE CatID =401) OR
(SELECT  meta_value FROM wp_postmeta WHERE  post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )
)
AND
ID IN (SELECT post_id FROM wp_locations_courses WHERE location_id = 1120)

I am having the problem with above query while i am using IN with sub queries gave me the error Subquery returns more than 1 row why it happens when i am using IN clause

(SELECT courses  FROM wp_category WHERE CatID =401)
        OR
        (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )

This is a condition, but you need n values.

This should work:

(
    ID IN
        (
            (SELECT courses  FROM wp_category WHERE CatID =401)
        )
    OR
    ID IN
        (
            (SELECT  meta_value FROM wp_postmeta WHERE  post_id IN
                (SELECT courses FROM wp_category WHERE CatID =401) AND meta_key ='post_id' )
        )
)

Also take care about subqueries with MySQL. In your case a sub- subquery.

Take a look here: https://stackoverflow.com/questions/12356784/mysql-dependent-sub-query-with-not-in-in-the-where-clause-is-very-slow


How to prevent trigger from overwriting values that were explicitly set? I tried this but doesn't work because NEW.updated is never NULL (I suppose NEW gets populated by the values currently in the row in addition to the values in the update-statement?):

create temp table test (
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE FUNCTION update_timestamp() RETURNS TRIGGER AS
$$
BEGIN
    IF NEW.updated IS NOT NULL THEN
        NEW.updated := now();
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER asdf BEFORE UPDATE ON test
FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

How can I make it set NEW.updated := now(); only when updated is not explicitly set in an update-statement? So that the trigger doesn't overwrite me when I want to set the updated column manually (eg. updated=updated).

No way to determine does some field was set in UPDATE query text. You may operate with NEW.updated and OLD.updated values only.

Actions table:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           NULL           nothing
NULL           now()          nothing
NULL           NOT now()      nothing
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?
NOT now()      NULL           NEW.updated=OLD.updated
NOT now()      now()          NEW.updated=OLD.updated
NOT now()      NOT now()      NEW.updated=OLD.updated

And compacted form:

OLD.updated    NEW.updated    Action
------------------------------------
NULL           any value      nothing
NOT now()      any value      NEW.updated=OLD.updated
now()          NULL           NEW.updated=OLD.updated
now()          now()          nothing
now()          NOT now()      ?

Define the action unknown to me. Then convert this table to proper If-ElseIf-Else form...

PS. now() means that the difference between field value and current timestamp is negligible small.

--

UPDATE

The final expression will be like:

IF   (OLD.updated != NOW())
  OR (     (OLD.updated = NOW())
       AND (NEW.updated IS NULL)
     )
THEN SET NEW.updated=OLD.updated
ELSEIF     (OLD.updated = NOW())
       AND (NEW.updated != NOW())
THEN ?????
END IF;


column {table_name} of relation {table_name} does not exist SQL state: 42703 I am getting an error while running an update query with table name specified along with column name:

UPDATE Temp SET Temp.Id='234',Temp.Name='Test'WHERE Id='245'

This is the error:

ERROR:  column "temp" of relation "temp" does not exist
LINE 1:      UPDATE Temp SET Temp.Id='23...
                               ^
********** Error **********

ERROR: column "temp" of relation "temp" does not exist
SQL state: 42703
Character: 24

You cannot (and need not) use table aliases (or tablename qualified column names) in the SET clause of an UPDATE.  This even makes sense, as you can only update a single table in a single UPDATE, so there is no ambiguity in column names there.

Fortunately, the ever helpful documentation explicitly mentions your case:

  column_name

  The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript,
  if needed. Do not include the table's name in the specification of a
  target column — for example, UPDATE tab SET tab.col = 1 is invalid.

So, the solution is to simply remove temp. from the SET clause:

UPDATE temp SET id = '234', name = 'Test' WHERE id = '245'

Notes:

Are you really storing numbers as text?  If yes, why?  It is usually a recipe for disaster.  For example, how do you prevent something like 'mkjcvnd7y78r3tgbhvcjh' entering your id column?
The way you are using object names starting with capital letters is confusing.  Without double-quoting its name, your table in reality is called temp as opposed to Temp.  Using it the latter way may decrease readability (depending on your preferences and habits, of course).


Create extension hstore I want to use hstore in my postgresql server running on CentOS 7 but hstore.control is not in my postgresql extension folder. I searched for that and came up with the solution to install postgresql93-contrib for postgresql version 9.3. But when I want to install the package, there is no package available for that. How can I solve this issue?

CentOS 7 was released on 2014-07-07.
PostgreSQL 9.3 was released on 2013-09-09. You would think it would have 9.3, but nope. CentOS 7 uses PostgreSQL 9.2 which was released on 2012-09-10 and hits End-Of-Life in September 2017.

Find the official instructions for using the non-CentOS repos here. PostgreSQL maintains it's own packages too. The official packages from PostgreSQL for CentOS are available here,

https://www.postgresql.org/download/linux/redhat/

Or just use something that updates more than once every 5 years -- like Debian.


Understanding Oracle's ALL_TAB_COLUMNS I'm new to Oracle and database administration in general.

As context, I want to create a Java class that will give me the information I would get using DESC SOME_TABLE.

I cannot find any specific way to do that in Java, however I found that ALL_TAB_COLUMNS could give me similar info.  I tried it in SQL Developer to see how different the output was.  It turns out the results are a lot more different that I was expecting.

I was hoping someone could walk me through how to interpret the following:

desc SOME_TABLE;

select
       COLUMN_NAME
     , DATA_TYPE
     , DATA_LENGTH
     , NULLABLE
  from ALL_TAB_COLUMNS
 where TABLE_NAME='SOME_TABLE'
 order by column_id;

Gives the output:

Name            Null    Type
--------------- ------- ----------------------
UIDPK                   NUMBER(20)
NAME                    VARCHAR2(255)

2 rows selected

COLUMN_NAME      DATA_TYPE     DATA_LENGTH    NULLABLE
--------------- -------------- -------------- --------
UIDPK            NUMBER        22              N
UIDPK            NUMBER        22              N
UIDPK            NUMBER        22              Y
NAME             VARCHAR2      255             N
NAME             VARCHAR2      255             Y
NAME             VARCHAR2      255             N

6 rows selected

Why is each column repeated 3 times?  Why is the datatype and length different on UIDPK and why is NULLABLE not the same?

Most probably there are three schemas (users) that contain that table. You need to include OWNER = 'FOOBAR' in your query to all_tab_columns (or use user_tab_columns). You can also include the OWNER column in your select list to verify this.

For VARCHAR (and other character columns) the size is stored in CHAR_LENGTH as documented in the manual. Note that you also need to check CHAR_USED to find out if the definition is in bytes or characters (VARCHAR(10 Bytes) vs. VARCHAR(10 Char)).

For numeric columns the definition is stored in DATA_SCALE and DATA_PRECISION.

This is all described in the manual: http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2103.htm#I1020277

Are you aware that you can retrieve the full definition of a table using `DBMS_METADATA using a SELECT statement:

SELECT dbms_metadata.get_ddl('TABLE', 'EMPLOYEE', 'SCOTT')
FROM dual;


Conditional query between tables I'm trying to fill a table with the results of a query, however, the results of said query depends on the result of other queries and then it ends up having several thousand selects making the process really long. I was wondering if what i'm trying to achieve is possible through a single query.

What i want to do is this.

Table: Store
fields: storeid, store_name

Table: Product
fields: codproduct, product_name, price1, price2

Table: PriceList
fields: pricelistid, codproduct, storeid, price1, price2

I'm keeping base prices in the product record itself and custom prices per store in a the table called "PriceList". i get the storeid from the user that is logged in.

I'm currently running a query to let me know if there is a custom price from where the user is logged on, if there is none, then i use the base prices in the "productos" table. But i'm running several thousands of selects each time.

Is there a way to acchieve that in a single query?

the final query needs to return:
codproduct, product_name, price1, price2

price1 and price2 being the base price from "product" if there is no custom price in the "PriceList" table.

If I have interpreted correctly your question, here is a query that produces the result given the storeid X:

SELECT codproduct,
       product_name,
       COALESCE(l.price1, p.price1) AS price1,
       COALESCE(l.price2, p.price2) AS price2
FROM Product p LEFT JOIN PriceList l ON p.codproduct = l.codproduct
WHERE storeid = X


Meaning of SERVERPROPERTY ('ResourceLastUpdateDateTime') I have researched a while, a lot of articles name it and tell us to use it - but none really explains the meaning of the date and WHEN exactly it changes. Does anyone know?

Samples for articles not really explaining the meaning of this property:

https://msdn.microsoft.com/en-us/library/ms190940.aspx
http://www.mytechmantra.com/LearnSQLServer/How-to-Identify-the-Location-of-Resource-Database-in-SQL-Server/

Edit: The backstory behind the question simply does not exist. I just want to know what event forces this information to get a new timestamp. What makes the resource db getting updated?

Based on the BOL article you referenced to, the date is not going to change unless a Service Pack or CU creates new system objects. However the date returned may not necessarily coincide with the date you install that CU or SP.

  The Resource database makes upgrading to a new version of SQL Server
  an easier and faster procedure. In earlier versions of SQL Server,
  upgrading required dropping and creating system objects. Because the
  Resource database file contains all system objects, an upgrade is now
  accomplished simply by copying the single Resource database file to
  the local server.

The only time you will notably see that value change is when you apply a service pack or upgrade SQL Server. It will likely change the same time you see the ResourceVersion value change. Which if I recall I only see the version number change with service packs.

I have an instance of SQL Server 2008 R2 that shows a build of 10.50.6220, but the resource database shows 10.50.6000. Which the date returned is 08/09/2014 13:40:43, that is not when I applied the SP but probably the time frame that Microsoft's SQL Server Team finalized their testing. SP3 was released 09/26/2014.


Better way of handling incorrect date format in a column with "char(10)" data type / TRY_CONVERT equivalent in PLSQL I have a source table with below structure:
create table customer_info
(customer_num    number,
 birth_date      char(10))

Unfortunately the birth_date column data type is char(10) instead of date. Some example data for this table would be like below:
customer_num    |  birth_date
--------------------------------
  1             |  2001/01/01
  1             |  2010/01/01
  1             |  2021/01/01
  1             |  12hhhhuu6   --> Incorrect date
  1             |  2001/01/01
  1             |  2001/23/01  --> Incorrect date

what I've done is writing a function to evaluate every single record and return it's correct format but as you know , using a function for every single record is not a good idea and it somehow kills performance. I was wondering if you could suggest a better way for this.
create or replace function new_to_date_en(d varchar2) return DATE
is
  v_date date;
begin
  select to_date(d,'yyyy/mm/dd' ) into v_date from dual;
  return to_date(d,'yyyy/mm/dd');
  exception when others then return to_dateto_date('2021/03/07', 'yyyy/mm/dd');
end;

Using the function:
 select customer_num,
        new_to_date_en(birth_date)
 from customer_info;

There is a way in T-SQL COALESCE(TRY_CONVERT(date, @date, 111), '2012-01-01'). Is there a similar way in oracle plsql?
Thanks in advance

TRY_CONVERT is a function that is called for every record, how is that different?
Below works since 12.2:
SQL> alter session set nls_date_format='YYYY/MM/DD';

Session altered.

SQL> select cast('2021/03/07' as date default date'2012-01-01' on conversion error) from dual;

CAST('2021
----------
2021/03/07

SQL> select cast('X021/zz/99' as date default date'2012-01-01' on conversion error) from dual;

CAST('2021
----------
2012/01/01

SQL> select to_date('X021/zz/99' default date'2012-01-01' on conversion error, 'YYYY/MM/DD') from dual;

TO_DATE('X
----------
2012/01/01

SQL>

On your version, you can use your function.


SYSDBA privileges of viewing users table Is there anyway which would restrict sysdba from watching the users tables. It sounds ridiculous but we sort of need this functionality. We only want sysdba to act as administrator only, no peeping into user's tables. Is it possible?

This is what Oracle Database Vault was made for.

http://www.oracle.com/us/products/database/options/database-vault/overview/index.html

Note that, it is an extra cost option for Enterprise Edition.


How to copy a table during replication in MySQL? I have two MySQL servers; db1 and db2. db1 is production and db2 is for reporting. I have setup replication where db1 is master and db2 is slave. This current setup works fine.

I was given a new requirement to not replicate a field from the USER table because it contains sensitive information.

Here are the steps I implemented:

I created a copy of USER, called USER_CLONE that does not have the field to be omitted
I set up a stored proc (UPDATE_USER_CLONE) to DELETE FROM USER_CLONE and INSERT INTO USER_CLONE SELECT  FROM USER
I added the following to my.cnf on db2:
  replicate-ignore-table = USER
  replicate-ignore-table = UPDATE_USER_CLONE

When replication starts, I get an error on the INSERT statement because USER does not exist on db2. I understand the error since replication will rerun all the db statements.

What is the right way to set this up so USER is ignored or has a field ignored, or USER_CLONE can be setup to replicate?

Thanks for any advice.

replicate-ignore-table and the related configuration options are problematic at best.  Not because they don't do what they're supposed to do -- because they do -- but rather because they have side effects that require a deeper understanding of MySQL replication.
The problem you have stems from the way the events are replicating.  MySQL can replicate table data changes using two different mechanisms, statement-based and row-based, based on the setting of the global configuration variable binlog_format.
STATEMENT -- the actual queries are replicated from server to server, with each server executing the query and modifying its data set.
ROW -- the actual queries are not sent, but instead, tightly-packed row images of the changes actually caused by each query are sent.
MIXED -- the server chooses on a query-by-query basis how to replicate the events.
Importantly, when a query is replicated as a statement event, triggers on the affected tables fire on the slave in order to make the same changes to other tables that would have been made on the master.  When a query replicates as a row event, triggers do not fire on the slave.  Instead, the master server sends over additional row images from the affected tables so that the additional impacted tables also get updated to match the way they were on the master.  The same thing is true when procedures are called -- either the individual statements in the procedure are replicated and have to be valid and execute on the slave (statement-based replication), or the rows changed by the procedure will be replicated (row-based replication).
The scenario you described cannot work unless your master server's binlog_format is set to ROW, because the queries running on the slave have to have access to the "user" table data.
Once that is set, if it were me, I would remove the replicate-ignore-table from the configuration, and instead provision the user table on the slave with the BLACKHOLE storage engine, which discards data sent to it, returning "success" on inserts and 0 rows found/affected on updates and deletes.
However, keep reading, because you are doing this the hard way.
The solution is much simpler: alter the table to move the confidential data to the far right side of the table, and then drop those columns from the slave's copy of the table.  No, really:

Source and target tables for replication do not have to be identical. A table on the master can have more or fewer columns than the slave's copy of the table.
You can replicate a table from the master to the slave such that the master and slave copies of the table have differing numbers of columns, subject to the following conditions:
Columns common to both versions of the table must be defined in the same order on the master and the slave. (This is true even if both tables have the same number of columns.)
Columns common to both versions of the table must be defined before any additional columns.
— http://dev.mysql.com/doc/refman/5.6/en/replication-features-differing-tables.html

I have linked to the documentation from MySQL 5.6 since that's the current GA version, but this feature is available as far back as 5.1.21.  When doing things this way, you would still be advised to switch your master to binlog_format = ROW.


Innodb composite primary key with 4 columns vs MyIsam I've created "MY TABLE" based on Innodb with composite primary key because I am storing many trees inside this table. The PK is for example (a,b,c,d).

1- Does Innodb store rows with same (a,b,c) near together?

2- Does every (a,b,c) add one 'page' (at least 4 kb) and so increase my table size?

3- Does this query almost checks all table?
   Select * from mytable where a='1' and b='2'
So it would be faster if I user 'MyIsam' and use secondary index (a,b) ?

Many Thanks

PS: I edited and removed that question. (yeah,that was really funny :) )

(For InnoDB)  All the rows with the same a will be next to each other; all rows with same a and b, will be adjacent, etc.  So, I guess "yes" is the answer to #1.
No.  An InnoDB "block" is 16KB.  If the entire table fits in one block, then everything will be in that one block.  Note:  The PRIMARY KEY is "clustered" with the table.  So talking about the layout of the PK and the layout of the table are one and the same.
(Q3 has been changed; this edit addresses the new Q3.)   For WHERE a='1' AND b='2', whether MyISAM or InnoDB, any index beginning with (a,b) or (b,a) will be optimal.  Note "beginning with"; since the PRIMARY KEY already begins with (a,b), adding another index not be useful.  See my cookbook on creating indexes.

MyISAM and InnoDB differ (for this discussion) in the following ways:

MyISAM does not "cluster" the PK with the data.  The PK is a separate BTree and the data is (mostly) appended to without regard to PK order.
MyISAM index blocks are 1KB.

Did you have other questions about the difference between MyISAM and InnoDB?

In order to determine the optimal indexes, first determine what the queries will look like.  WHERE c = 124 begs for INDEX(c), or at least an index that starts with c.  Rearranging the PK to PRIMARY KEY(c,a,b,d) would suffice, but might hurt other queries.


For every insert statement I see sp_executesql in SQL Server Profiler Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows.

I saw for every insert I called, a call was made to sp_executesql with the text of the insert statement.

This seems to be a big hit, is there a way to turn this off?

Environment:

SQL Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
32 GB RAM
2x4 Xeon

I wouldn't worry too much.

The idea is to parameterise statements for re-use. That is, avoid compiling

Consider you have 3 different clients run 3 separate statements:

SELECT col1, col2 FROM SomeTable WHERE col3 = 1

SELECT col1, col2 FROM SomeTable WHERE col3 = 15

SELECT col1, col2 FROM SomeTable WHERE col3 = 42

Each of these has different text therefore will be compiled to a different plan. That is, 3 compiles and 3 plans in cached.

Now, these 3

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '1'

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '15'

EXEC sp_executesql 'SELECT col1, col2 FROM SomeTable WHERE col3 = @p0', '@p0 int', '42'

The re-usable plan here is for the statement SELECT col1, col2 FROM SomeTable WHERE col3 = @p0 so you have one plan is cache.

In busy systems, this compile time matters. And most queries are more complex then this of course: you can often see this for 1st run vs subsequent run times (see Testing Query Speed)

Also in complex and busy systems then plan cache size may be limited or takes RAM away from data cache (buffer pool) which can hinder things too. The extreme could be a 10000 plans or one for a busy web app. (The effect depends on 32 vs 64 bit subtleties)

Some ORMs and drivers allow you to switch this off, usually "prepared statements" or some option: but be warned it can slow things down. You said "this seems to be a big hit" which implies you are concerned about load...


Duplicate records returned from table with no duplicates I have a stored procedure that queries a busy queue table that is used to distribute work in our system. The table in question has a primary key on WorkID and no duplicates.

A simplified version of the query is:

INSERT INTO #TempWorkIDs (WorkID)
SELECT
        W.WorkID

    FROM
        dbo.WorkTable W

    WHERE
        (@bool_param = 0 AND
        ((W.InProgress = 0
         AND ISNULL(W.UserID, -1) != @userid_param
         AND (@bool_filtered = 0
              OR W.TypeID IN (SELECT TypeID FROM #Types AS t)))
         OR
         (@bool_param = 1
          AND W.InProgress = 1
          AND W.UserID != @userid_param)
        OR
        (@Auto_Param = 0
         AND W.UserID = @userid_param)))
         OR
         (@bool_param = 1 AND W.UserID = @userid_param)
    OPTION
        (RECOMPILE)

The #Types table is populated earlier in the procedure.

As I said, WorkTable is busy, and sometimes while this query is running I SUSPECT one of the records is moving from one set of filters in the WHERE to another. Specifically, this happens when someone starts to work on an item, and the W.InProgress changes from 0 to 1. When this happens I get a duplicate key violation when I try to add a primary key to the temp table this query is inserting into.

I have confirmed in the query plan generated when the error occurs that there is no parallelism, the isolation level is READ COMMITTED, and there are no duplicate records in the source table. You can also see there are no JOINs or other way to get cartesian products in here.

This is the anonymized query plan:

The question is, what is causing the duplicates and how can I get it to stop?

I think READ COMMITTED should work here, I need locking. I am almost positive the dupes occur when the InProgress bit on a record changes while I'm querying. I know this because the table stores the time of that change and it's within milliseconds of when I query and get the error.

There are some tricky scenarios which can result in the same row being read twice from an index, even under the READ COMMITTED isolation level.

Your query does not qualify for an allocation order scan, so the storage engine will read the data from the table in the order of the clustered key.

For your table, you have the InProgress as the first column of the clustered key. It's likely that you're getting row or page locks as you scan through the table. If you read a row near the start of the scan, release the lock on it, that row is updated such that InProgress changes from 0 to 1, and then the row is read again in a different page then you could see duplicate WorkID values from your query.

There are lots of workarounds. You could insert into a heap and simply remove duplicate values. You could add a DISTINCT to the query. You could also enable a row-versioning isolation level, to provide a stable view of the committed state of the database, either as at the beginning of the transaction (snapshot isolation), or as at the beginning of the statement (read committed snapshot isolation).

Perhaps it's appropriate to add locking hints or to change the structure of the table. For a rather fun solution (probably not appropriate for production), you could try reading the index backwards. This can be done with a superfluous TOP along with an ORDER BY. Below is a very simple demo to illustrate the point:

CREATE TABLE #WorkTable (
    InProgress TINYINT NOT NULL,
    WorkID INT NOT NULL
    , PRIMARY KEY (InProgress, WorkID)
);

INSERT INTO #WorkTable WITH (TABLOCK)
SELECT (RN - 1) / 5000, RN
FROM
(
    SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t
OPTION (MAXDOP 1);

The following query has the Ordered:false property but it'll still read the data in clustered key order:

SELECT WorkId
FROM #WorkTable;

However, the following query will read the data in reverse clustered order:

SELECT TOP (9223372036854775807) WorkId
FROM #WorkTable
ORDER BY InProgress DESC, WorkId DESC;

We can see this by looking at the scan properties:

For your table, this means that if a row is updated such that InProgress changes from 0 to 1 it will be far less likely that it'll show up twice. It may not show up at all which could be a different problem.


Struggling with MySQL character set, collation I am facing problems with inserting Spanish characters from my application.

When I am inserting on the DB the characters are inserting properly, but when I am trying passing the same data in from the application, the data stored in the table isn't correct.

For example, when I insert directly on DB server:

CREATE TABLE  prod_inns(`prod_name` varchar(100) COLLATE latin1_bin DEFAULT NULL)

insert into prod_inns values(Otoño);

I see this:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| Otoño       |
+-------------+

delete  FROM  prod_inns ;

However, when I insert the same value from the application form, I see this instead:

select * from prod_inns ;

+-------------+
| prod_name   |
+-------------+
| OtoÃ±o      |
+-------------+

Here are my MySQL settings:

MySQL>  show global variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | latin1                     |
| character_set_connection | latin1                     |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | latin1                     |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

MySQL>  show global variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | latin1_swedish_ci |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

What's wrong with my settings? What should I do to store the data and read the data properly?

Note: I am using mysql-5.7.19

Don't use latin1, use utf8.  You have "Mojibake" because the bytes in the client are utf8-encoded, yet you said they were latin1.

More on Mojibake and what to do about it:  https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored


Enforcing lowest transaction isolation level across concurrent sessions? I am trying to learn transactions better in PostgreSQL and came across this observation.

I have a situation where I am creating multiple tables using an SQL statement across multiple sessions. I am using CREATE TABLE *table_name* AS *sql_stmt*  (CTAS) syntax. I would consider this a safe operation to execute concurrently (across my multiple sessions) because it is not updating any data, thus I would want to ensure that I have the least restrictive lock possible at all times.

That being said, multiple sessions are still creating data (the table) so I am quite worried that if the SQL statements contain references to the same table, I might be invoking a lock, even if I know that it is safe for the operations I have in mind. In other words, I know that all my operations are read-only (so can use a lock that is not restrictive) and that no table will have data which is updated, with the simple caveat that I am also saving the result as a table (so this is no longer 100% read-only; might invoke some restrictive lock at some point?). Thus, my question is as follows: how can I ensure the lowest lock restriction across all concurrent sessions while I do my CTAS workload?

For an example, let us say I have two sessions. One is issuing the following command:

CREATE TABLE t1 AS SELECT * FROM facttable;

The other, concurrently issues:

CREATE TABLE t2 AS SELECT * FROM facttable;

How can I ensure that such operations do not invoke a lock since they are referring to the same table?

There is no danger of locking with your scenario.

The only lock that is taken on facttable is the ACCESS SHARE lock that prevents others from dropping or altering the table while it is being read. Since ACCESS SHARE doesn't conflict with itself, that is no problem (two people can read the same poster).

The only consideration is the I/O load: if many processes perform a sequential scan on the table, that could be substantial. You should try to benefit from PostgreSQL's synchronized sequential scans by starting all processes at the same time, so that they have a chance to synchronize their sequential scans.


Timestamp and substring with regular expression I am using postgresql and I have the following table

          Table "public.test"
 Column |           Type           | Modifiers
--------+--------------------------+-----------
 name   | text                     |
 time   | timestamp with time zone |

 name  |          time
-------+------------------------
 ticka | 2016-07-08 21:22:58+00
(1 row)

When I do the command:

select substring(name from '.*') from test;

I get a result, but when I do it for the time column I get:

  Error: LINE 1: select  substring(time from '.*' ) from test;
                  ^
  HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Why is this? How can I solve it?

Ok, because the function is called substring, I think it only works for the string data type but not for the timestamp data type

To solve this I use the regular expression on the timestamp by converting the timestamp into a text first using the to_char function and then I use the substring function as follows:

select substring(to_char(time,'YYYY-MM-DD HH:MM:SS') from '.*') from test;


How to make MySQL 8 alert when integer overflow happens during INSERT ... ON DUPLICATE KEY? Let's consider we have a simple table with auto-incrementing integer ID and some unique column:

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `value` tinyint DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `value` (`value`)
);

Imagine we inserted into it so many times that ID reached the maximum:

INSERT INTO test VALUES
(4294967294, 1),
(4294967295, 2);

Now we are trying to insert one more row and if it's duplicate then change the value to 3:

INSERT INTO test (`value`) VALUES (1) ON DUPLICATE KEY UPDATE `value` = 3;

What is the expected behaviour?

        id     value
----------  --------
4294967294         3
4294967295         2

What we get in fact:

        id     value
----------  --------
4294967294         1
4294967295         3

How? Because of the integer overflow the auto-generated ID of the new row was again 4294967295.

So now the question. Is it possible to make MySQL 8 alert if the overflow happens during query? In my case such silent overflow caused huge data loss. Because all newly added rows just overwrote the last row.

PS I'm not asking how to fix or prevent such overflow. That's obvious. I'm asking exactly how to catch such errors.

USE a BEFORE INSERT TRIGGER to check for all fields where you can reach their limits.

With the newest mysql incarnation you can actually can use the CKECK CONSTRAINT to
prevent an INSERT at all, but with the TRIGGER you can manage and define What to do


Automate update columns in a view with trigger function in plpgsql Im attempting to update several columns in a view very similar to this post. The difference is that Im combining in some cases up to 3 tables, and for each update on the view I would like the trigger to do an INSTEAD OF UPDATE on the appropriate table.

From the Documentation I've figured out how to update columns by hand, but with some views having up to 20 or 30 columns this will get tedious. I would like to automate the process similar to the previously mentioned post.

SQL Fiddle

PostgreSQL 9.6 Schema Setup:

CREATE TABLE poi
(
  poi_id SERIAL PRIMARY KEY,
  col_a integer DEFAULT 0,
  col_b integer DEFAULT 0,
  col_c integer DEFAULT 0
 )
/

CREATE TABLE points
(
  poi_id integer REFERENCES poi(poi_id),
  col_d varchar(50),
  col_e varchar(50),
  col_f varchar(5)
 )
/

-- Insert some example values
INSERT INTO poi (col_a, col_c) VALUES(1,1)
/
INSERT INTO points (poi_id, col_d, col_e, col_f)
VALUES(1, 'col d description', 'col e description', 'name')
/

CREATE VIEW poi_view as
SELECT poi.poi_id,
poi.col_a,
poi.col_b,
points.col_d,
points.col_e
FROM poi
JOIN points
ON poi.poi_id=points.poi_id
/

-- Create Instead of Trigger
CREATE OR REPLACE FUNCTION poi_view_update()
RETURNS TRIGGER as
$$
BEGIN
  IF (OLD.col_a != NEW.col_a) THEN
    UPDATE poi SET col_a = NEW.col_a
    WHERE poi_id = OLD.poi_id;
    IF NOT FOUND THEN RETURN NULL; END IF;
    RETURN NEW;
  END IF;
  RAISE NOTICE 'No update performed';
  RETURN NEW;
  END;
$$
LANGUAGE plpgsql
/

CREATE TRIGGER poi_update_trigger
INSTEAD OF UPDATE ON poi_view FOR EACH ROW EXECUTE PROCEDURE poi_view_update()
/

Query 1:

select * from poi

Results:

| poi_id | col_a | col_b | col_c |
|--------|-------|-------|-------|
|      1 |     1 |     0 |     1 |

Query 2:

select * from points

Results:

| poi_id |             col_d |             col_e | col_f |
|--------|-------------------|-------------------|-------|
|      1 | col d description | col e description |  name |

Query 3:

select * from poi_view

Results:

| poi_id | col_a | col_b |             col_d |             col_e |
|--------|-------|-------|-------------------|-------------------|
|      1 |     1 |     0 | col d description | col e description |

Query 4:

UPDATE poi_view SET col_a = 0

Results:

Query 5:

select * from poi_view

Results:

| poi_id | col_a | col_b |             col_d |             col_e |
|--------|-------|-------|-------------------|-------------------|
|      1 |     0 |     0 | col d description | col e description |

UPDATE 3/7/18

In attempting to answer my own question, Ive decided to approach it as plainly as possible, by creating the appropriate variables, building a query, then simply using the EXECUTE command. So far I have

DECLARE
   v_schema  text := quote_ident(TG_TABLE_SCHEMA);
   v_name text := quote_ident(TG_TABLE_NAME);
   column_name text;
   t_schema text := 'my_schema';
   t_name information_schema.tables.table_name%TYPE;
 BEGIN
    column_name = ???
    t_name = SELECT DISTINCT table_name
    FROM information_schema.view_column_usage
    WHERE view_schema = v_schema
    AND table_schema = t_schema
    AND column_name = column_name;

My current issue is how do I find the column name from the original table the column came from?

I was able to solve my problem in a more manual way. It took some time to complete for all columns but at least it is done. Heres an example, of what I did.

-- update col_d
        IF (NEW.col_d != OLD.col_d) THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d was % , changed to %',OLD.col_d,NEW.col_d;
        END IF;
-- in case col_d is NULL
        IF OLD.col_d IS NULL AND NEW.col_d IS NOT NULL THEN
          UPDATE poi
          SET col_d = NEW.col_d
          WHERE poi_id = NEW.poi_id;
          RAISE NOTICE 'col_d updated NULL to %', NEW.col_d;
        END IF;


Choosing primary keys: Scientific names of species or system-assigned numeric identifiers? Scientific names are well accepted unique identifiers of species. However, scientific names have special characters such as spaces and dots. Can it be a good unique identifier for databasing or should one assign/implement unique numerical identifiers?

If it is with the latter, is there a globally known numeric identifier? Or should one assign own identifiers for the rows?

I would use my own identifier.

The species name may be unique but
- it's too long
- it's a string

For example, in SQL Server, if used as the clustered primary key it will be used in non-clustered indexes, thus repeating the long string. And typically foreign keys omn child tables will go to the primary key, thus repeating it again

As a string, you have the overhead of sorting and comparison (case, accents, etc).

Using a surrogate numeric key avoids these problems: but you must create a unique non-clustered index on the species name.

Is species name a good long term identifier too? Not my area of expertise but don't many species have alternate names, or controversies, or get reclassified, or "maybe this species"?

Example: how many Giraffe species are there? 9? 2? 6? 8? 4?


Why full-text-search returns less rows than LIKE I don't get full-text-search working as I want it to, and I don't understand the differences in the resultlists.
Example statements:

SELECT `meldungstext`
FROM `artikel`
WHERE `meldungstext` LIKE '%punkt%'

returns 92 rows. I receive rows which have matches, for example, like "Punkten", "Zwei-Punkte-Vorsprung" and "Treffpunkt" in column meldungstext.

I set a fulltext-index on the column "meldungstext" and tried this:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*')

this returns only 8 rows. I receive only rows which have matches to "Punkt" itself or words which I think are taken as "Punkt" as in "i-Punkt".

I then tried boolean mode:

SELECT `meldungstext`
FROM `artikel`
WHERE MATCH (`meldungstext`)
AGAINST ('*punkt*' IN BOOLEAN MODE)

returns 44 rows. I receive rows which have "Zwei-Punkte-Vorsprung" or "Treffpunkt" in column meldungstext, but not those with "Punkten".

Why does this happen and how can I set a "fully" working full-text-search to prevent using LIKE '%%' in the where-clause?

I took the three strings in your question and added it to a table plus three more string with pankt instead of punkt.

The following was executed using MySQL 5.5.12 for Windows

mysql> CREATE TABLE artikel
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     meldungstext MEDIUMTEXT,
    ->     PRIMARY KEY (id),
    ->     FULLTEXT (meldungstext)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO artikel (meldungstext) VALUES
    -> ('Punkten'),('Zwei-Punkte-Vorsprung'),('Treffpunkt'),
    -> ('Pankten'),('Zwei-Pankte-Vorsprung'),('Treffpankt');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>

I ran these queries against the table using 3 different approaches

MATCH ... AGAINST
LOCATE as in the LOCATE function
LIKE

Please note the differences

mysql> SELECT id,meldungstext,
    -> COUNT(IF(MATCH (`meldungstext`) AGAINST ('*punkt*' IN BOOLEAN MODE),1,0)) PunktMatch,
    -> IF(LOCATE('punkt',meldungstext)>0,1,0) PunktLocate,
    -> meldungstext  LIKE '%punkt%' PunktLike
    -> FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PunktMatch | PunktLocate | PunktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          1 |           1 |         1 |
|  2 | Zwei-Punkte-Vorsprung |          1 |           1 |         1 |
|  3 | Treffpunkt            |          1 |           1 |         1 |
|  4 | Pankten               |          1 |           0 |         0 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           0 |         0 |
|  6 | Treffpankt            |          1 |           0 |         0 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.01 sec)

mysql>

All the PunktMatch values should bee 3 1's and 3 0's.

Now watch me query them as normal

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE MATCH (`meldungstext`) AGAINST ('*punkt*' IN BOOLEAN MODE);
+-----------------------+
| meldungstext          |
+-----------------------+
| Zwei-Punkte-Vorsprung |
| Punkten               |
+-----------------------+
2 rows in set (0.01 sec)

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE LOCATE('punkt',meldungstext)>0;
+-----------------------+
| meldungstext          |
+-----------------------+
| Punkten               |
| Zwei-Punkte-Vorsprung |
| Treffpunkt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql> SELECT `meldungstext` FROM `artikel`
    -> WHERE `meldungstext` LIKE '%punk%';
+-----------------------+
| meldungstext          |
+-----------------------+
| Punkten               |
| Zwei-Punkte-Vorsprung |
| Treffpunkt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql>

OK using MATCH .. AGAINST with punkt does not work. What about pankt ???

mysql> SELECT `meldungstext` FROM `artikel` WHERE `meldungstext` LIKE '%pankt%';
+-----------------------+
| meldungstext          |
+-----------------------+
| Pankten               |
| Zwei-Pankte-Vorsprung |
| Treffpankt            |
+-----------------------+
3 rows in set (0.00 sec)

mysql>

Let's run my big GROUP BY query against pankt

mysql> SELECT id,meldungstext,
    -> COUNT(IF(MATCH (`meldungstext`) AGAINST ('*pankt*' IN BOOLEAN MODE),1,0)) PanktMatch,
    -> IF(LOCATE('pankt',meldungstext)>0,1,0) PanktLocate,
    -> meldungstext  LIKE '%pankt%' PanktLike
    -> FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PanktMatch | PanktLocate | PanktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          1 |           0 |         0 |
|  2 | Zwei-Punkte-Vorsprung |          1 |           0 |         0 |
|  3 | Treffpunkt            |          1 |           0 |         0 |
|  4 | Pankten               |          1 |           1 |         1 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           1 |         1 |
|  6 | Treffpankt            |          1 |           1 |         1 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.01 sec)

mysql>

This is wrong also because I should see 3 0's and 3 1's for PanktMatch.

I tried something else

mysql> SELECT id,meldungstext, MATCH (`meldungstext`) AGAINST ('+*pankt*' IN BOOLEAN MODE) PanktMatch, IF(LOCATE('pankt',meldungstext)>0,1,0) PanktLocate, meldungstext  LIKE '%pankt%' PanktLike FROM `artikel` GROUP BY id,meldungstext;
+----+-----------------------+------------+-------------+-----------+
| id | meldungstext          | PanktMatch | PanktLocate | PanktLike |
+----+-----------------------+------------+-------------+-----------+
|  1 | Punkten               |          0 |           0 |         0 |
|  2 | Zwei-Punkte-Vorsprung |          0 |           0 |         0 |
|  3 | Treffpunkt            |          0 |           0 |         0 |
|  4 | Pankten               |          1 |           1 |         1 |
|  5 | Zwei-Pankte-Vorsprung |          1 |           1 |         1 |
|  6 | Treffpankt            |          0 |           1 |         1 |
+----+-----------------------+------------+-------------+-----------+
6 rows in set (0.00 sec)

mysql>

I added a plus sign to pankt and I got different results. What 2 and not 3 ???

According to the MySQL Documentation, notice what it says about the wildcard character:

  *

  The asterisk serves as the truncation (or wildcard) operator. Unlike
  the other operators, it should be appended to the word to be affected.
  Words match if they begin with the word preceding the * operator.

  If a word is specified with the truncation operator, it is not
  stripped from a boolean query, even if it is too short (as determined
  from the ft_min_word_len setting) or a stopword. This occurs because
  the word is not seen as too short or a stopword, but as a prefix that
  must be present in the document in the form of a word that begins with
  the prefix. Suppose that ft_min_word_len=4. Then a search for '+word
  +the*' will likely return fewer rows than a search for '+word +the':

  The former query remains as is and requires both word and the* (a word
  starting with the) to be present in the document.

  The latter query is transformed to +word (requiring only word to be
  present). the is both too short and a stopword, and either condition
  is enough to cause it to be ignored.

Based on this, the wildcard character is applicable for the back of tokens and not for the front. In light of this, the output must be correct because 2 of the 3 punkt's start tokens. Same story with pankt. This at least explains why 2 out of 3 and why less rows.


SQL Server Propagation I am working in a SQL Server 2014 environment where there are multiple databases where the table structure (column names, data types, sizes) matches.  However, the indexes do not necessarily match.  For instance one database might have a clustered index on table X, but the next database does not, there may be clustered indexes in one and not in the other, non-clustered indexes may exist in one place where all columns are part of the index key columns while other cases have a mix with included columns, etc.

What I am in need of is a script which will allow me to level set indexes, without removing indexes which exist on the "target" database but not in the "template", while minimizing duplicate and overlapping indexes.  In lieu of such a script, some suggestions or guidance in creating one would be a viable option.

Thanks!

Use Visual Studio Sql Server Data Tools. It has a schema compare tool that you can use to view differences between any two databases. I have used this tool to do things very similar to what you are asking.

Instructions for Using the Schema Compare Tool
General Instructions are

Create Database Project
Connect Databases to Project
Click "Schema Compare" in SQL Menu
Select Source and Target Database
Hit Compare Button
Select What you want to keep/get rid off
Click on Generate Script


How to filter maximum results entries I have a mysql table

table name 'results'

Reg.No  SubjectCode  Attempt  Grade  Marks
112108  CMIS 1113    1        D      17
112110  CMIS 1114    1        A      85
112119  CMIS 1114    1        D      18
112108  CMIS 1113    2        D+     25
112110  CMIS 1113    1        D+     25
112107  CMIS 1113    1        B      70
112108  CMIS 1113    3        C      40
112110  CMIS 1113    2        C      40
112119  CMIS 1114    2        C      42
112120  CMIS 1114    1        D      17

This is about students' results. Students need at least a "C"
    (marks>40) to pass the subject. Until pass, students can have
    several attempts. But from the second attempt maximum grade they
    can get is "C" So students who was unable to pass the subject in
    first attempt,used to have another try until they pass the subjects
    So for final results sheet the highest result will be take into account in each subjects.(highest results is given by the highest marks)
So for example highest marks of 112108 for CMIS 1113 is 40.
So likewise I want to get the students results for ecch subjects(But always maximum marks will be take into account)
Actually my expected results table should be like this

Reg.No  SubjectCode  Attempt  Grade  Marks
112110  CMIS 1114    1        A      85
112107  CMIS 1113    1        B      70
112108  CMIS 1113    3        C      40
112110  CMIS 1113    2        C      40
112119  CMIS 1114    2        C      42
112120  CMIS 1114    1        D      17

So how can I retrieve these data using sql query?

This is a way of doing it. Can be tested in http://sqlfiddle.com/#!2/7c4525/4

SELECT R1.*
FROM Results R1
INNER JOIN
(
  SELECT RegNo, SubjectCode, Max(Marks) AS MaxMark
  FROM Results
  GROUP BY RegNo, SubjectCode
) R2
ON R1.RegNo = R2.RegNo
  AND R1.SubjectCode = R2.SubjectCode
  AND R1.Marks = R2.MaxMark

This previous solution could output several rows for the same Reg.No and SubjectCode in case that student tied for her best result in more than one attempt.
In this case, if only the row from the last attempt is wanted to be shown you can use the following variation.
Can be tested in http://sqlfiddle.com/#!2/070c5/3. Note that I have added an extra row to the sample data you provided in order to reproduce the situation.

SELECT R1.RegNo, R1.SubjectCode, Max(R1.Attempt), R1.Grade, R1.Marks
FROM Results R1
INNER JOIN
(
  SELECT RegNo, SubjectCode, Max(Marks) AS MaxMark
  FROM Results
  GROUP BY RegNo, SubjectCode
) R2
ON R1.RegNo = R2.RegNo
  AND R1.SubjectCode = R2.SubjectCode
  AND R1.Marks = R2.MaxMark
GROUP BY RegNo, SubjectCode, Grade, Marks

However, if in case of ties, the prefered result to be shown is the lower attempt number Max(R1.Attempt) should be replaced by Min(R1.Attempt)


only use extended join-clause if some variable is TRUE I am working with Postgis (extension to postgres) where there are several different geometry-types, i.e. 'polygons', 'points' 'linestrings', and multi-variants of these.
I am trying to create a function which should only use extra join-clauses if the geometry-type is a (multi)-polygon.
in all cases, with all geometrytypes, the join should look like this:
--drop function if exists test_return(geometry, double precision);
CREATE OR REPLACE FUNCTION test_return(
      PAR_geom                      geometry
     ,PAR_tolerance                 double precision DEFAULT 0.0000001
) RETURNS setof geometry
AS $$
DECLARE
    VAR_main_id int;
BEGIN

CASE ST_GeometryType(PAR_geom)
WHEN 'ST_LineString'  THEN
    VAR_main_id = 0;
WHEN 'ST_Polygon', 'ST_MultiLineString', 'ST_MultiPoint'  THEN
    VAR_main_id = 1;
WHEN 'ST_MultiPolygon' THEN
    VAR_main_id = 2;
ELSE
    raise EXCEPTION 'this function does not work on single-points';
END CASE;

return query
with repeated_pts as (
select (st_dumppoints(PAR_geom)).path, (st_dumppoints(PAR_geom)).geom
where st_npoints(st_snaptogrid(PAR_geom, PAR_tolerance)) <> st_npoints(PAR_geom)
)

select b.geom from
repeated_pts a
inner join
repeated_pts b
on

--- Here is how it should behave for all geometry-types---
a.path[1:VAR_main_id] = b.path[1:VAR_main_id]
and
a.path[array_upper(a.path, 1)]< b.path[array_upper(b.path, 1)]
and
st_dwithin(a.geom, b.geom, PAR_tolerance);
    END;
$$
LANGUAGE plpgsql;

In case its a (multi)-polygon, i want to extend the join-clause with additional join-clauses:
and

(
(
a.path[VAR_main_id] = 1 and
a.path[array_upper(a.path, 1)] > 1 and
b.path[array_upper(b.path, 1)] > 1
)
or
(
a.path[VAR_main_id] > 1 and
b.path[array_upper(b.path, 1)] <= 4
)
)

Is there a smart way to do this?
I know its possible to insert the query into a case-when-then structure like this:
case
when st_geometrytype(PAR_geom) in ('ST_MultiPolygon', 'ST_Polygon')
then --- run query with additional join-clause --

else
-- run query WITHOUT the extra join-clause --
end case;

however, this structure requires I write the first part of the query twice - is there a way to avoid this?

In case its a (multi)-polygon, I want to extend the join-clause with additional join-clauses:

What you ask for can be achieved with plain boolean logic in the JOIN clause. Append:
AND ((st_geometrytype(PAR_geom) IN ('ST_MultiPolygon', 'ST_Polygon')) IS NOT TRUE

  OR a.path[VAR_main_id] = 1
 AND a.path[array_upper(a.path, 1)] > 1
 AND b.path[array_upper(b.path, 1)] > 1

  OR a.path[VAR_main_id] > 1
 AND b.path[array_upper(b.path, 1)] <= 4
)

Meaning, either the type is not among the listed ones ('ST_MultiPolygon', 'ST_Polygon'), or the following conditions have to be met.
Operator precedence makes AND bind before OR, so we need no additional parentheses.
The function might look like this:
CREATE OR REPLACE FUNCTION test_return(par_geom      geometry
                                     , par_tolerance float8 = 0.0000001)
  RETURNS SETOF geometry
  LANGUAGE plpgsql AS
$func$
DECLARE
   var_main_id int;
BEGIN
   CASE ST_GeometryType(par_geom)
   WHEN 'ST_LineString'  THEN
       var_main_id = 0;
   WHEN 'ST_Polygon', 'ST_MultiLineString', 'ST_MultiPoint'  THEN
       var_main_id = 1;
   WHEN 'ST_MultiPolygon' THEN
       var_main_id = 2;
   ELSE
       RAISE EXCEPTION 'this function does not work on single-points';
   END CASE;

   RETURN QUERY
   WITH repeated_pts AS (
      SELECT d.path, d.geom
      FROM   st_dumppoints(par_geom) d  -- evaluate once
      WHERE  st_npoints(st_snaptogrid(par_geom, par_tolerance)) <> st_npoints(par_geom)
   )
   SELECT b.geom
   FROM   repeated_pts a
   JOIN   repeated_pts b ON a.path[1:var_main_id] = b.path[1:var_main_id]
                        AND a.path[array_upper(a.path, 1)] < b.path[array_upper(b.path, 1)]
                        AND st_dwithin(a.geom, b.geom, par_tolerance)
                        -- append here!
                        AND ((st_geometrytype(PAR_geom) IN ('ST_MultiPolygon', 'ST_Polygon')) IS NOT TRUE

                          OR a.path[VAR_main_id] = 1
                         AND a.path[array_upper(a.path, 1)] > 1
                         AND b.path[array_upper(b.path, 1)] > 1

                          OR a.path[VAR_main_id] > 1
                         AND b.path[array_upper(b.path, 1)] <= 4
                        );
END
$func$

Might be possible to simplify further. I lack insight.