texts:
in this scheme

id  uid     content
1   1       A
2   1       B
3   1       C
4   1       D
5   1       E

If the User decided that he doesn't need the rows with id = 3, 4, 5 Is it better to make him set their content to NULL or just delete the row?

This process occur many time And he maybe use the NULL slots again. Do I still use UPDATE and set it to NULL in case he will change it in the future? Or just DELETE the entire row?

The question is like: is it better to

DELETE the Row? INSERT| -> DELETE -> INSERT .. DELETE -> INSERT

or

UPDATE the Row? INSERT| -> UPDATE to NULL -> UPDATE to value .. UPDATE to NULL -> UPDATE to value


texts: MySqldump Error 2013: Lost Connection MySQL During create database backup

texts:
I am use Prestashop.
am create a script for auto backup of whole database of my site
but from last few given this error and backup terminate at this table.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table ps_layered_filter_block at row: 107002
am change some value in my.cnf file but not work for me
my.cnf file
#Copyright 2019 Bitnami.com All Rights Reserved

#Note: This file will be modified on server size changes

[mysqld]
net_read_timeout = 1200
net_write_timeout = 1900
wait_timeout = 320
long_query_time = 1
query_cache_limit=20M
query_cache_type=1
query_cache_size=512M
innodb_buffer_pool_size=4096M
innodb_force_recovery = 1
innodb_log_file_size=128M
tmp_table_size=64M
max_connections = 2500
max_user_connections = 2500
innodb_flush_method=O_DIRECT
key_buffer_size=64M

[mysqladmin]
user=root

[mysqld]
sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
basedir=/opt/bitnami/mysql
datadir="/opt/bitnami/mysql/data"
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
tmpdir=/opt/bitnami/mysql/tmp
max_allowed_packet=512M
bind-address=127.0.0.1
skip-name-resolve=1
default-authentication-plugin=mysql_native_password

character-set-server=UTF8
collation-server=utf8_general_ci
[client]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=3306
socket=/opt/bitnami/mysql/tmp/mysql.sock
pid-file=/opt/bitnami/mysql/tmp/manager.pid
default-mysqld-path=/opt/bitnami/mysql/bin/mysqld.bin

!include /opt/bitnami/mysql/bitnami/my.cnf

but Problem Stil Same.
mysqldump -u root -p bitnami_prestashop > /home/{username}/dbbackup/db_13_07_2020.sql
also try
mysqldump -u root -p --extended-insert bitnami_prestashop > /home/{username}/dbbackup/db_13_07_2020.sql
our database size
| bitnami_prestashop | Size in (MB) |
+--------------------+--------------+
| bitnami_prestashop | 8515.77 |
|
| information_schema | 0.16 |
| mysql | 3.82 |
| performance_schema | 0.00 |
| sys | 0.02 |
+--------------------+--------------+```

**on-disk** size

11G /opt/bitnami/mysql/data/bitnami_prestashop
21M /opt/bitnami/mysql/data/mysql
11G /opt/bitnami/mysql/data

Please Provide solution as soon as posibal thanks


texts: MySQL query WHERE IN + ORDER BY, which EXPLAIN is better and how to avoid filesort?

texts:
This is my table definition, without any special index for now:

CREATE TABLE `filter` (
  `field_name` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
  `position` smallint(6) NOT NULL,
  `options` longtext COLLATE utf8_unicode_ci COMMENT '(DC2Type:json_array)',
  PRIMARY KEY (`field_name`,`category_id`),
  KEY `IDX_702C956612469DE2` (`category_id`),
  CONSTRAINT `FK_702C956612469DE2`
      FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Sample data:

+------------+-----------------------+-------------+----------+
| field_name |        options        | category_id | position |
+------------+-----------------------+-------------+----------+
| color      | {"label": "Color"}    |        1895 |        1 |
| material   | {"label": "Material"} |        1895 |        2 |
| color      | {"label": "Color"}    |        1896 |        1 |
| color      | {"label": "Color"}    |        1897 |        1 |
+------------+-----------------------+-------------+----------+

Most of the time I do a query with WHERE IN and ORDER BY position and selecting only field_name and options:

EXPLAIN SELECT
  field_name
  options
FROM
  filter
WHERE
  category_id IN ('1895', '1896', '1897')
ORDER BY
  position ASC

This gives me Using index condition; Using filesort, quite bad uh?

{
   "query_block":{
      "select_id":1,
      "ordering_operation":{
         "using_filesort":true,
         "table":{
            "table_name":"filter",
            "access_type":"range",
            "possible_keys":[
               "IDX_702C956612469DE2"
            ],
            "key":"IDX_702C956612469DE2",
            "used_key_parts":[
               "category_id"
            ],
            "key_length":"98",
            "rows":5,
            "filtered":100,
            "index_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
         }
      }
   }
}

With an index on category_id, position Using where; Using index; Using filesort:

{
   "query_block":{
      "select_id":1,
      "ordering_operation":{
         "using_filesort":true,
         "table":{
            "table_name":"filter",
            "access_type":"range",
            "possible_keys":[
               "IDX_702C956612469DE2",
               "IDX_CATEGORY_POSITION"
            ],
            "key":"IDX_CATEGORY_POSITION",
            "used_key_parts":[
               "category_id"
            ],
            "key_length":"98",
            "rows":5,
            "filtered":100,
            "using_index":true,
            "attached_condition":"(`filter`.`category_id` in ('1895','1896','1897'))"
         }
      }
   }
}

So which explain is better? I would say the first because of using index condition (there is no "where" part). Is there any way to avoid the filesort?


texts: Sort spills to tempdb but estimated rows equals to actual rows

texts:
On a SQL Server 2016 SP2 with max memory set to 25GB we have a query that executes about 80 times in a minute. The query spills about 4000 pages to tempdb. This causes for a lot of IO on the disk of tempdb.

When you take a look at the query plan (simplied query) you'll see that the number of estimated rows is equal to the number of actual rows but still spills occur. So out-of-date statistics can't be the cause of the problem.

I did some testing and following query spills to Tempdb:

select id --uniqueidentifier
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

But if I select a different column no spills occur:

select startdate --datetime
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

So I tried to 'enlarge' the size of the id column:

select CONVERT(nvarchar(512),id)
from SortProblem
where [status] ='A'
order by SequenceNumber asc
option (maxdop 1)

Then also no spilling occurs.

Why is the uniqueidentifier spilling to tempdb and a datatime column not?
When I delete about 20000 records then also no spilling happens when I select the id column.

With following script you can reproduce the problem:

CREATE TABLE SortProblem
  (
     id             UNIQUEIDENTIFIER,
     startdate      DATETIME,
     sequencenumber BIGINT,
     status         VARCHAR(50),
     PRIMARY KEY CLUSTERED(id)
  )

SET nocount ON;

WITH nums(num)
     AS (SELECT TOP 103000 ROW_NUMBER()
                             OVER (
                               ORDER BY 1/0)
         FROM   sys.all_objects o1,
                sys.all_objects o2)
INSERT INTO SortProblem
SELECT newid(),
       DATEADD(millisecond, num, GETDATE()),
       num,
       CASE
         WHEN num <= 100000 THEN 'A'
         WHEN num <= 101000 THEN 'B'
         WHEN num <= 102000 THEN 'C'
         WHEN num <= 103000 THEN 'D'
       END
FROM   nums

CREATE NONCLUSTERED INDEX [IX_Status]
  ON [dbo].[SortProblem]([status] ASC)
  INCLUDE ([sequencenumber])


texts: Recurring background job error 9101/9104 auto create stats

texts:
I was spelunking through a quick trace on SQL 2012 SP3 CU3 when I noticed the database engine is generating thousands of these internal notifications every hour or so and in large chunks. I've also reproduced it in Extended Events but they do not add any additional information.

I'm aware that auto create stats can timeout when queries timeout, but I checked and the database is configured to create them asynchronously (so that could not be the cause).

I came across this MSDN thread in reference to the error number explaining that auto create stats can fail due to deadlock or lack of resources, but I don't see any deadlocks recorded and the server is not resource constrained or under heavy load (CPU, memory ring buffers).

I had a look at the underlying table. It has a clustered identity primary key , mundane simple data types, is only about 4,000 rows and < 1MB in size. The indexes are nothing special. Integrity checks are fine. Stats are updated on a weekly schedule (a few days ago). Trace flags 2371 and 4199 are enabled.

Obviously these aren't real errors and don't surface anywhere but now I'm curious.

Q. What could be going on to cause auto auto create stats to be triggered and also fail over and over in very quick succession?

Thanks


texts: How to rollback the identity seed after deadlock

texts:
Now that's an approximate sequence of operations Im performing:

SET IDENTITY_INSERT <table-name> ON;
INSERT SOMETHING to <table-name> with explicitly specifyed id
DECLARE @oldID bigint
SELECT @oldID = <some simple logic here>
DBCC CHECKIDENT ('<table-name>', RESEED, @oldID) WITH NO_INFOMSGS;
SET IDENTITY_INSERT <table-name> OFF;

So, as you see, everything's simple: basically, I insert record with explicit ID, then I want to return the identity seed back to its previous value (because the insert will obviously shift it).

I need this kind of strange logic (from the first POW) becase I can have ID-s in a large range (billions of rows) of values where the first digit has some special meaning.

For instance, I have current identity seed 1000007777. Then I want to insert a record with ID 2000007777 (this will shift identity to 2000007778). But I want to turn it back to 1000007777, because I believe that I will never reach the point when an identity collision will occur (I just don't operate such volumes of data). As I said, the first digit serves special purposes.

Now, the problem is, if I rollback the transaction after insert, the identity will not get rolled back (as i got it, that's the design of SQL server, and I believe many DB providers).

I can handle this manually if I am the one who conciously performes the rollback.
But what if there is deadlock (or some error) right at the point before reseed?

How can I handle that (except handling the exception and performing unconditional reseed)? Any ideas?


texts: Permission error when using sys.dm_exec_input_buffer with @@SPID

texts:
When I run this blob of SQL on SQL Server 2016 or an Azure SQL DB:

--hello world
SELECT      event_info
FROM        sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)
WHERE       req.session_id = @@SPID

I get this error, followed by the expected results:

Msg 300, Level 14, State 1, Line 5
VIEW SERVER STATE permission was denied on object 'server', database 'master'.
Msg 297, Level 16, State 1, Line 5
The user does not have permission to perform this action.
--hello world
SELECT      event_info
FROM        sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_input_buffer(req.session_id, req.request_id)
WHERE       req.session_id = @@SPID

The documentation for sys.dm_exec_input_buffer states (emphasis mine):

  On SQL Server, if the user has VIEW SERVER STATE permission, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session.

Thus, according to the documentation, I am allowed to access this data. Why, then, is the error being raised?


texts: Left Join Not returning rows when date is in range of longer than 1 day

texts:
How do I left join Table A and B?

SELECT dev_hour.hour,rtdogs.delivery_date,rtdogs.delivery_hour,rtdogs.total_offer FROM
(
    SELECT hour FROM
    (
      SELECT * FROM delivery_hour
     ) xx
)dev_hour
LEFT JOIN
(
    SELECT sum(total) as 'total_offer',delivery_hour,delivery_date
                            FROM
                            (
                                SELECT greatest(quan1,quan2,quan3,quan4,quan5,quan6,quan7,quan8,quan9,quan10,quan11) as total,
                                    region_id, resource_id,delivery_date,delivery_hour
                                FROM
                                (
                                    SELECT delivery_hour,delivery_date,resource_id,region_id, quan1,quan2,quan3,quan4,quan5,quan6,quan7,quan8,quan9,quan10,quan11
                                    FROM
                                    (
                                        SELECT ifnull(quantity1,0) as quan1, ifnull(quantity2,0) as quan2,ifnull(quantity3,0) as quan3,ifnull(quantity4,0) as quan4,
                                            ifnull(quantity5,0) as quan5,ifnull(quantity6,0) as quan6,ifnull(quantity7,0) as quan7,ifnull(quantity8,0) as quan8,
                                            ifnull(quantity9,0) as quan9,ifnull(quantity10,0) as quan10,ifnull(quantity11,0) as quan11,delivery_hour,delivery_date,resource_id,
                                            region_id
                                        FROM rtdog  WHERE delivery_date BETWEEN '2016-01-15'  AND '2016-01-15'
                                    )z
                                )y
                             )x  GROUP BY delivery_date,delivery_hour ORDER BY delivery_date
                            ) rtdogs
 ON dev_hour.hour = rtdogs.delivery_hour

I want to show all rows and null rows.
What this code is doing is when you limit the BETWEEN to 1 day it shows the null values but when it's 2 days and more it doesn't show. What's wrong with this code?

Update:
The delivery_hour table has fixed 24 rows.


texts:
Operating system used is linux. I have tried Navicat and SQL Power Architect. They did display relations between tables in the same schema. I have some foreign key constraints which reference tables in a different schema.

Am I missing something with respect to Navicat and PostgreSQL Maestro? Can they not display the inter schema relations?
Is there data visualisation tool for postgresql which is capable of displaying inter schema relations as well?


texts: How can I get this "rolling total" query to sum correctly?

texts:
I have a problem with the following query:

SELECT  t1.idcliente,
        c.nombre,c.apellido,
        t1.fecha,
        t1.haber,
        t1.debe,
        SUM(t2.haber -t2.debe) as SUM
FROM estado_cliente t1
INNER JOIN estado_cliente t2 ON t1.idestado_cliente >= t2.idestado_cliente
INNER JOIN cliente c ON t1.idcliente= c.idcliente
where t1.idcliente=t2.idcliente
GROUP BY t1.idcliente ,
         t1.fecha,
         t1.haber,
         t1.debe,
         c.nombre,c.apellido
ORDER BY t1.idcliente,t1.fecha
;

See this sqlfiddle.

Here's part of the results:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-07-03  0       8100   -8100
   6       Matias  Blanco    2017-07-08  0      15000  -23100
   6       Matias  Blanco    2017-09-24  4312       0  -38788    <--- problem
   6       Matias  Blanco    2017-09-25  0      10000  -33100
   6       Matias  Blanco    2017-09-26  0      10000  -43100

Each result row should have the SUM column return the cumulative total of all haber values minus all the debe values, up to and including the given date. These represent payments and debits, and I want the total amount still outstanding on that date.

However in the third line, that's not what I'm getting. The results should look like:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-07-03  0       8100   -8100
   6       Matias  Blanco    2017-07-08  0      15000  -23100
   6       Matias  Blanco    2017-09-24  4312       0  -18788  <-- correct
   6       Matias  Blanco    2017-09-25  0      10000  -28788
   6       Matias  Blanco    2017-09-26  0      10000  -38788

Given the sqlfiddle link above, can anyone help me figure out what I'm doing wrong?


texts: Eliminate duplicates in different order from all combinations, can I order returned columns alphatically?

texts:
this should actually be not a too hard task, but it seems to be a little too early for me to see the solution:

I have 4 different string values.

I want to have all combinations of those 4 values where not all values have to be present, so combining all values by cross joining those values zero times, one time, two times and three times. Like I also demonstrate in this fiddle:

CREATE TABLE BaseValues (ID INT PRIMARY KEY IDENTITY(1,1), Title VARCHAR(63));

INSERT BaseValues (Title)
  SELECT 'One'
  UNION
  SELECT 'Two'
  UNION
  SELECT 'Three'
  UNION
  SELECT 'Four';

SELECT Title AS T1 FROM BaseValues;

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  WHERE BV1.Title <> BV2.Title;

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV2.Title <> BV3.Title;

SELECT
    BV1.Title AS T1,
    BV2.Title AS T2,
    BV3.Title AS T3,
    BV4.Title AS T4
  FROM BaseValues BV1
  CROSS JOIN BaseValues BV2
  CROSS JOIN BaseValues BV3
  CROSS JOIN BaseValues BV4
  WHERE
    BV1.Title <> BV2.Title
    AND BV1.Title <> BV3.Title
    AND BV1.Title <> BV4.Title
    AND BV2.Title <> BV3.Title
    AND BV2.Title <> BV4.Title
    AND BV3.Title <> BV4.Title;

Now this is almost the result I want, except I still have duplicates inside of that.
I do not care in what order values appear in my result set I just want to have rows in which those combination of values are unique; for example from the second result set here the rows containing One Two and Two One would be equivalent to me and I just want one of them.

I thought maybe I could concatenate all columns from my result set into one column that contains those values in alphabetical order so a SELECT DISTINCT would eliminate my undesired duplicates.

But I have not been able to figure out how I could do that or maybe there is another more convenient way?

Any hints are appreciated.


texts: Missing right parenthesis after (or before) default 0, Oracle

texts:
I am trying to create a table with Oracle SQL where two columns of the table should have a default value and should not be NULL.

I am getting the error you can see below, but i am not able to understand it, because the are no parenthesis to be closed there!

The Script

 CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos references equipos(code) not null enable,
  fecha date,
  plocal number constraint plocal_mayor_cero check (plocal > 0) default 0 not null enable,
  pvisitante number constraint pvisitante_mayor_cero check (pvisitante > 0) default 0 not null enable);

The Error Message

plocal number constraint plocal_mayor_cero check (plocal > 0) default 0 not null enable,
                                                              *
ERROR at line 5:
ORA-00907: missing right parenthesis

* is where Oracle detected the error

I'm at loss as to why there should be an error at that position.


texts: Error Code 1117 Too many columns; MySQL column-limit on table

texts:
I have a table with 1699 columns and when I'm trying to insert more columns I get,

  Error Code: 1117. Too many columns

In this table I have only 1000 rows. For me the most important thing is the number of columns. Are there any limitations on the table? I want to create 2000 columns. Is that possible?


texts: Error 1045 (28000): Access denied for user 'myuser'@'localhost' (using password: YES)

texts:
This looks like a duplicate of many questions asked here. I haven't found the answer for my specific problem, so I'm posting it here.

I was trying to create a local database and a user with basic permissions to it:

CREATE DATABASE demodb;

  Query OK, 1 row affected (0.00 sec)

USE demodb;

  Database changed

CREATE TABLE demotable(id int, name VARCHAR(25));

  Query OK, 0 rows affected (0.01 sec)

CREATE USER 'demouser' IDENTIFIED BY 'p';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.01 sec)

GRANT SELECT ON demodb.demotable TO 'demouser';

  Query OK, 0 rows affected (0.00 sec)

FLUSH PRIVILEGES;

  Query OK, 0 rows affected (0.00 sec)

With this setup, I wasn't able to log in:

mysql -u demouser -p demodb

  Enter password:
  ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

mysql -u demouser -p -h 127.0.0.1 demodb

  Enter password:
  ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

mysql -u demouser -p -h localhost demodb

  Enter password:
  ERROR 1045 (28000): Access denied for user 'demouser'@'localhost' (using password: YES)

I am using MySQL 5.6 on macOS using the official dmg installer.


texts: I have written some CLR table-valued functions that invoke a web service and I would like to audit their use.  Does anyone know of any way this can be achieved in the CLR code itself?  I would be nice to keep the auditing code together with the CLR code rather than having to write an explicit INSERT statement every time they are used.


texts: Unable to disable SQL statement logging in PostgreSQL 9.1

texts:
I am running PostgreSQL 9.1 on Windows Server 2008 R2.  I enabled SQL statement logging to the stderr log by modifying my postgresql.conf file, but now I can't seem to disable the logging without turning off all logging.  Server was sent SIGHUP signals via pg_ctl reload, and the service has been restarted after each attempt to fix this.  Rebooting the entire machine did not seem to help either.

Specific lines from my current postgresql.conf that should have accomplished this, but aren't:

log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_error_verbosity = default
log_hostname = on
log_line_prefix = '%t %a %u %r '
#log_lock_waits = off
log_statement = 'none'
#track_activities = on
#track_counts = on
#track_functions = none
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'

I've checked the manual; it seems all I need to do is ensure that the log_statement line in the config file is set appropriately.

However, nothing I do seems to have any effect on what is actually being logged.  If I turn off the logging_collector, the text log files are no longer generated.  This would indicate that PostgreSQL is at least pointed to the correct postgres.conf file, and that PostgreSQL is reading it to determine its configuration.

What do I do to turn of SQL statement logging?


texts: How to normalize a lookup table containing NULL? - or should I bother?

texts:
I have a table fee listing different fee descriptions and another table fee_amount holding the amount of the fee. The fee amount is different for each year, whether it's a joint fee or not and what description the fee is for. What is the best way to normalise the two given the issue I describe later?

The original data

fee
===
fee_id | description
-------------------------
1      | subscription
2      | joining

and

fee_amount
==========
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35

To obtain the amount I need to invoice I join the tables fee and fee_amount on the fee.id and look up the fee amount from the fee_amount table using the keys of fee_amount.year, fee_amount.joint and fee.description. i.e something like

SELECT amount
FROM fee_amount
JOIN fee ON (fee_amount.fee_id = fee.fee_id)
WHERE
   fee_amount.year = 2020
AND
   fee_amount.joint = TRUE
AND
   fee.description = 'joining'

The problem

I introduce a new fee description 'booking', whose amount only depends upon the year i.e. I don't care if it's joint or not. This gives rise to N/A or NULL in the fee amount lookup table as shown below which rather complicates looking up fee amounts.

What is the best way to normalise this to avoid the NULLS? or shouldn't I bother and just have a more complicated SELECT.

Example new data with a fee type that doesn't require a 'joint' value.

fee
===
fee_id | description
-------------------------
1      | subscription
2      | joining
3      | booking

 fee_amount
==========
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35
   3   | 2019 |  NULL |  77
   3   | 2020 |  NULL |  88

Edit... Or maybe should I simply add two rows in the fee_amount table for the same year, one with joint = true and one with joint = false, both with the same amount and then it won't matter what I put in the WHERE clause for fee_amount.joint
ie

 fee_amount
==========
fee_id | year | joint | amount
-------|------|-------|-------
   1   | 2019 | FALSE |  20
   1   | 2019 |  TRUE |  15
   1   | 2020 | FALSE |  30
   1   | 2020 |  TRUE |  25
   2   | 2019 | FALSE |  60
   2   | 2019 |  TRUE |  50
   2   | 2020 | FALSE |  40
   2   | 2020 |  TRUE |  35
   3   | 2019 |  TRUE |  77
   3   | 2019 | FALSE |  77
   3   | 2020 |  TRUE |  88
   3   | 2020 | FALSE |  88


texts: Execute UNION and after LEFT JOIN in MySQL

texts:
I have 3 tables

cdc_vw_Marbetes_Catalogo_Exis
tblMarbetesAdicionales
tblMarbetesCongeladas

First I make a UNION of the tables cdc_vw_Marbetes_Catalogo_Exis with tblMarbetesAdicionales.

(SELECT CLAVE_ART, DESCRIPCION_ART, EXISTENCIA_ART, MARCA_ART, exi_num_alm
        FROM cdc_vw_Marbetes_Catalogo_Exis
        WHERE EXISTENCIA_ART > 0 AND exi_num_alm = 25)
    UNION
(SELECT clave, descripcion, existencia, marca, almacen
        FROM tblMarbetesAdicionales
        WHERE almacen = 25)

Result of the union: Column(CLAVE)

2457-84735-001 (cdc_vw_Marbetes_Catalogo_Exis)
107067860P     (cdc_vw_Marbetes_Catalogo_Exis)
BS-113A        (cdc_vw_Marbetes_Catalogo_Exis)
6200           (tblMarbetesAdicionales)
6300           (tblMarbetesAdicionales)

In table tblMarbetesCongeladas have:

BS-113A
CS-23523
CS-120000
CV206ETCS

But from the result of that union I want to make a LEFT JOIN to get the records that are not in tblMarbetesCongeladas.

I tried like that but it does not work

(SELECT CLAVE_ART, DESCRIPCION_ART, EXISTENCIA_ART, MARCA_ART, exi_num_alm
        FROM cdc_vw_Marbetes_Catalogo_Exis
        WHERE EXISTENCIA_ART > 0 AND exi_num_alm = 25)
    UNION
(SELECT clave, descripcion, existencia, marca, almacen
        FROM tblMarbetesAdicionales
        WHERE almacen = 25)
    LEFT JOIN tblmarbetescongeladas ON cdc_vw_Marbetes_Catalogo_Exis.CLAVE_ART = tblmarbetescongeladas.clave WHERE cdc_vw_Marbetes_Catalogo_Exis.CLAVE_ART IS NULL

the result that I expect is

2457-84735-001
107067860P
6200
6300

I appreciate the suggestions


texts:
I have these two tables:

Center

+--------+---+--------+--------+---------+
|IdParent|Row|IdCenter|IdNodeIn|IdNodeOut|
+--------+---+--------+--------+---------+
|1       |1  |470     |1       |5218     |
|1       |2  |22      |5298    |1        |
|1       |3  |20      |5242    |5284     |
+--------+---+--------+--------+---------+

Connection

+--------+---+------------+--------+----------+---------+
|IdParent|Row|IdCenterFrom|IdNodeIn|IdCenterTo|IdNodeOut|
+--------+---+------------+--------+----------+---------+
|1       |1  |470         |5218    |20        |5242     |
|1       |2  |20          |5284    |22        |5298     |

I need to get result like this:

+--+--------+------+
|Id|IdCenter|IdNode|
+--+--------+------+
|1 |470     |1     |
|2 |470     |5218  |
|3 |20      |5242  |
|4 |20      |5284  |
|5 |22      |5298  |
|6 |22      |1     |

Detail:

Center table has 3 rows (can have more, but minimum 2 rows) indicating 3 center, each has tow connection points

As part of network, input point in first center and output point in last center may connect to no node, so it's Id can be 1. Centers are not sorted.

Connection table shows that two connections has connected these three centers. Connection table is sorted, means that first row IdNodeIn is the first connection start point, then if goes to IdNodeOut on the same row ( show start and end of connection). Then this circuit continue to second row, and connect to IdNodeIn and the last end point is IdNodeOut.

So Center by Id = 470 is connected to 20 and then to 22.

Or by connection by Node Id is 5218 to 5242, then to 5284 and last to 5298

I want all of these in above result table, I can get them all in one table but I can not arrange them, here is my select statement:

SELECT
    m.Id, m.IdCenter, m.IdNodeIn
FROM
    Link.LinkCenter m
LEFT OUTER JOIN
    Link.LinkConnection c ON c.IdNodeOut = m.IdNodeIn

UNION ALL

SELECT
    m.Id, m.IdCenter, m.IdNodeOut
FROM
    Link.LinkCenter m
LEFT OUTER JOIN
    Link.LinkConnection c ON c.IdNodeIn = m.IdNodeOut
ORDER BY
    Id, IdC

and the result is:

+----+--------+------+
|Id  |IdCenter|IdNode|
+----+--------+------+
|NULL|470     |1     |
|1   |470     |5218  |
|NULL|22      |1     |
|2   |22      |5298  |
|1   |20      |5242  |
|2   |20      |5284  |

here is a graph:

+---------------+       +---------------+       +---------------+
|IdCenter : 470 |       |IdCenter : 20  |       |IdCenter : 22  |
|IdNodeIn : 1   |   +-->|IdNodeIn : 5242|   +-->|IdNodeIn : 5298|
|IdNodeOut: 5218|>--+   |IdNodeOut: 5284|>--+   |IdNodeOut: 1   |
+---------------+       +---------------+       +---------------+

Please help, although it may sound easy.


texts:
I have this query:

SELECT *
FROM (
  SELECT COUNT(lead_status) as 'Pending files'
  FROM vw_file_registrationlist
  WHERE lead_status_id = "1" || lead_status_id="2" || lead_status_id="4" || lead_status_id="5" || lead_status_id="6"
)A,
(
  SELECT COUNT(lead_status) as 'Completed files'
  FROM vw_file_registrationlist
  WHERE lead_status_id = "3"
)B
, (
  select DISTINCT employee_name
  From vw_file_registrationlist
  where employee_name='anna'
)C

I am getting the result as

Name  Pending files  Completed files
----  -------------  ---------------
anna  3              2

I need to calculate the pending files / total files * 100. Is it possible?


texts: Figure out what IP address MySQL resolves a host name to

texts:
I'm setting up a MySQL slave, but it can't connect to master (error 2003). Master is available. I suspect that MySQL resolves Master's hostname to some unexpected IP address and consequently chooses different route which is not allowed to connect to the master. Both master and slave are RDS MySQL in different VPCs :)

So how can I figure out what IP address MySQL gets using only MySQL commands or logs?


texts: ODBC connection for Active Directory user who doesn't log in to Windows

texts:
Do you think it is possible to create a SQL Server ODBC Connection for an Active Directory user who doesn't log in to Windows.

Ideally this type of users will be used in a batch process. So, another person logs in and creates a batch process and runs it with another user.

Note: I don't want to enable SQL Server authentication. Instead would like to use Active Directory.


texts: Does increased Data Dictionary size affects performance

texts:
As I understand, Data Dictionary

Is a collection of tables, part of SYS schema (if not the whole SYS
schema)
It is stored as 'regular' tables, so whatever is applied to
database tables, also applies to data dictionary (e.g. indexes,
statistics)
Its statistics should be updated
It is always cached

Can the the size and the complexity of oracle data dictionary affect the database performance?

When I add a new object on my database a new (or multiple) entries is/are added on data dictionary. A scenario I can think of has to do with partitioning. So if I have a table which have a partition and subpartitions, I will have an entry for the table, an entry for each partition and an entry of each partition subpartitions in data dictionary. Those entries are spawned among different 'tables'. Imagine know having tables with thousands of partitions and hundreds of subpartition. Again this number of records may not be something that a dbms can handle, but how it will affect its performance as it grows?

Note: I have read a lot of discussion on if and how partitioning affects performance in general. This is out of scope for this question. Partitioning is just an example I gave, to show how data dictionary volume can grow.


texts: Synchronize two MySQL databases between two servers

texts:
Is there any way to connect a database on two servers inside a trigger?

I have 2 databases on 2 servers.  The first server is the primary, and the second server is the secondary.  I want to sync both databases with each other.

I've tried using a trigger but it looks like that can only update data in the same server, however I want to sync the data from server one to server two.

Is there any way to do that?

MySQL version :- 5.0.12 (10.1.36-MariaDB)

Purpose of replication: read-write and read data spritely (for performance)


texts: Auto Increment Key Pair with Start Digit at 200000 SQL SERVER 2008

texts:
I'm currently rewriting a front end for a program that handles interviews. I want to prevent an issue the older program had were during the tiny amount of time it was doing a lookup for the next control number, another client could get the same number and create a conflict. I think it's best to do this in the database during the initial insert rather than the code.

The way I want it to work is with a two part key were one part is a Client's abbreviated name and the other part is an auto increment integer that starts at 200000. I would like it to do the 200000+ part per client. I want to auto fill the columns instead of having the program do it so there isn't any way the program can create a duplicate Client Key. It would be awesome if I could use this as the primary key(s). I have to leave it in two columns due to management.

Client1    200000
Client1    200001
Client2    200000
Client1    200002
Client2    200001
Client1    200003

How would I go about doing this?


texts: What to do when archive logs are lost/corrupted that are not backed up

texts: All recently generated archive logs were corrupted but datafiles are fine.What if recently generated archives corrupted and the archives backup is not available, so what would be the strategy to resolve the issue and is there any possible way to rectify the archive log corruption.


texts: Understanding Logging Priority & Options in Oracle DB

texts:
From my understanding once we switch on database logging, the entire DB goes into logging mode and generates redo logs.
I want to categorically exclude/include some tables/tablespaces from this logging as they are not required for recovery incase of failures.
Is there any priority on the logging options and to exclude certain tables/tablespaces from logging so as to reduce some of the traffic going to redo logs.


texts:
I have the following dataset in SQL Server:

Name     Pattern
-------  -------
Peter    DRF
Peter    DRF
Peter    BBB
Peter    DRF
Peter    CCC
Peter    CCC
Sue      DRF
Sue      CCC
Sue      DRF
Sue      VVV
Sue      SSS

I need to count the number of times a pattern changes per Name (not only distinct changes). So my end result will be:

Peter 3
Sue 4

In other words, only counting the distinct changes. Peter changed from DRF to BBB to CCC and Sue changed from DRF to CCC to DRF to VVV to SSS.

Is this possible without using a cursor?

I have a start date and end date per row, but no unique id. Patterns gets logged by the system per person per day and sometimes multiple times per day per person.


texts: Mysql Master-Master with additional slaves

texts:
We have two datacenters with a copy of our web app (online training platform) at each. The primary datacenter receives all of the traffic at any given time and the second datacenter serves as a failover(using DNS failover). The databases are set up in master-master with different auto-increment-increment values. The server at datacenter 1 also has its own local slave server on which we do backups, that way we can pause replication, do the backup with mysqldump, then start replication again.

 SERVER 1 <=========> SERVER 2
    |
  Slave 1

This weekend we did a failover to server 2 because datacenter 1 was doing planned network maintenance. Everything went smoothly and after a few hours we brought everything back to DC1. Last night I start getting replication errors on the slave saying it failed to DELETE an entry that didn't exist. I found it odd that the Slave 1 was getting the error while Server 2 was not (since they're both acting as slaves to Server 1). After digging, I found out that we still have a user that's going to Server 2.. for whatever reason their DNS hasn't updated for over 2 days. So the new data that they are producing is being replicated to Server 1, but Slave 1 is not getting these updates because they're not in Server 1's binlogs.

I now see that this was poor planning on my part for not foreseeing this issue. My question is, how can I make this work? We want to keep the master-master and we want to allow Slave 1 to exist, not only for backups but for additional read only reporting servers and such. Those of you that run Master-Master, what have you done to remedy similar issues? Or is this just a bad setup and I'm SOL? The app was not built with redundancy in mind, so everything is having to be done system side.


texts: Error log folder location and OLA's SQL maintenance solution

texts:
Looking at one of our dev sql servers, it has a -e startup parameter to define the error log folder at this location : E:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG.  However, only E:\DATA\MSSQL13.MSSQLSERVER\MSSQL exists on disk.  I can see Ola's SQL maintenance output files on disk at E:\DATA\MSSQL13.MSSQLSERVER\MSSQL\Log
Who / what is responsible for creating the error log folder as defined by the -e startup parameter?
Does OLA's maintenance solution have a fallback if it can't find the log folder defined with -E startup parameter?


texts: Trigger xp_LogEvent based on inserted cell value

texts:
I need to create a trigger on a table (after insert) that simply checks for a string in a cell. If the string exists, I need to log an event in the windows event logger. I am not really sure if I am able to use the inserted tables this way, but I thought I would try. This is what I have so far:

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @@Message varchar(255), @@go int;
    SET @@go = 0

    CASE
        WHEN inserted.STAT_MESSAGE LIKE '%bcp.exe%' THEN @@go = 1
    END

    IF @@go == 1 {
        @@Message = inserted.STAT_MESSAGE
        EXEC xp_logevent 60000, @@Message, ERROR;
    }

END
GO

I get incorrect syntax at the CASE statement but I don't quite see why. Perhaps the issue is actually how I'm accessing the inserted table. Where am I going wrong here?

Thank you.

EDITED CODE:

SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER bcpCatchTrigger
   ON  dbo.TABLE_NAME
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @@Message varchar(255), @@go int;
    SET @@go = CASE
        WHEN (SELECT STAT_MESSAGE FROM INSERTED) LIKE '%bcp.exe%' THEN 1
        ELSE 0
    END

    IF (@@go = 1) BEGIN
        SET @@Message = (SELECT STAT_MESSAGE FROM INSERTED)
        EXEC xp_logevent 60000, @@Message, ERROR;
    END

END
GO


texts: PostgreSQL 9.3: Dump a database excluding the data of some tables

texts:
Consider a production database with a few dozens of normal-size tables, and a handful of huge tables.

I am looking for a convenient way to pg_dump the database content, excluding the few huge tables, for my own local laptop. I can easily dump a small sample of the huge tables using \COPY, if I occasionally need to address these tables.

If I use the --exclude-table=table argument, both the schema and the data of the huge table is omitted, which breaks queries expecting these table on the local development environment.

Is there a way to dump a data base with a complete schema, excluding the content of some given tables?


texts: How to load Excel file into temp table in ORACLE

texts:
Is it possible to load a excel file with the headers into a temp table on Oracle?

I use TOAD for the interface.

I am needing to do some comparison on a a excel file with what we have in our database. Unfortunately, the data that is in the excel file is NOT in our database but in order for me to report on data, I need to bring it in for comparison.

Is this doable? If so, what would the appropriate approach be for this?


texts: SQL Server clustered index high fragmentation

texts:
I've got a Table with an Integer (4bytes) as primary key. it's defined as an identity. it is also the clustered index.

Inserts are working perfectly fine. After inserting 2000 rows the fragmentation is at around 4%.

However, each records will be updated at least 3 times afterwards. This generates a fragmentation of this clustered index of more than 99% (with default Fill factor)..

Or with other fill factors I tested:
- Setting a Fill factor of 80 on the clustered index (Fragmentation > 98%)
- Setting a Fill factor of 50 on the clustered index (Fragmentation > 94%)

So this doesn't look like it would help a lot...

I tested each fill factor setting on a newly created table with 2000 inserts (and the 3+ updates)

there is no select or update which does not include the primary key in the query.

Has anybody an idea why this index has such a high fragmentation?

Updates are made through ADO.NET this is the generated command (traced by the SQL Profiler):

exec sp_executesql N'UPDATE MyTable SET MyValue = @MyValue WHERE MyId = @MyId',
N'@MyValue varchar(50),@MyId int',@MyValue='This is some random value',@MyId=1234

Other updates follow the same pattern (update other values, but alway providing the @MyId parameter)

Thank you for your help :)


texts: SQL Server 2008 user, group, or role already exists in the current database

texts:
I am trying to migrate an old, problematic Plesk server to a new one but there are some problems with SQL Server 2008.

Plesk couldn't migrate one of the DBs. So, I backed up the DB and restored on the new server. Then I tried to restore 'User Mappings' for the existing user but I got "User, group, or role already exists in the current database" error. I googled for solutions and found a few but they didn't work.

I tried to run the following procedure but no luck.

sp_change_users_login 'Auto_Fix', 'UserName'

I also tried to delete it's scheme as a solution but couldn't do it because - according to SQL Server - it's referenced by an object.

I don't want to change that object's user/scheme because I am afraid of to broke something.

What should I do?


texts: Will installing a second SQL Instance cause an outage on existing instances?

texts: Like the title says, I just want to verify if we have the need to install a secondary SQL instance on a server that already has one SQL Instance, will the installation of the secondary instance cause an outage on the first?


texts: How do I get a list of "large data users" from my table?

texts:
This situation is a little specific so bear with me.

I have a (very) large table with many users (from a sync server). The users are stored by a hash identifier "username". Each user has many rows in the table where data is stored for them with a common username field.

I can get the total size used per user with the following (example) query:
select username,round(sum(length(payload))/1024) from wbo where username = 's5i7goejhuxdfhulhpdqjubxgkskfdve';
This gives me the output I'm looking for for a single, known user.

What I'm looking for is a way to get a similar output, but then listing the users that have a total data (payload) size larger than a certain amount, so I can find the username IDs of all large data users, so they can be addressed.
What query would I need for this?


texts: Analysis Services backup error: File system error: Access is denied

texts:
This isn't your typical file system access is denied error. I am pretty perplexed by this issue.

I recently started getting errors from my nightly cube backup job.

File system error:
The following error occurred during a file operation: Access is denied.

I'm not aware of any recent changes to the permissions and they all seem to be intact.

I tried logging into the servers where the backups are located since. I find that even copying a file into the folder in question gives me an "Access is denied" dialog box.

The kicker is that I only get "Access is denied" when I try to copy the .abf file into the folder.

This also happens with the parent folder. I can copy .bak extensions and other files into the folder just fine. The account that is performing the backup has full permissions to the folder as well.

Why am I getting "Access is denied" errors, but only for .abf extensions?

I disabled the antivirus. Still getting access denied.


texts:
Need some help with the results from the sp_blitzindex. Below is one of the suggestion but not able to understand the comment.

http://BrentOzar.com/go/AbnormalPsychology

  dbo.ACCT_RECV_ADDRESS has 18 out of 20 columns in one or more
  publications.

Could anyone clarify what does it mean..? Does table participating in replication has any effect on indexes..?


texts: To which phase of database design is Database Denormalization formally placed

texts: Formally speaking, to what stage of the database design process does database denormalization belong--logical design? or physical design? The process of joining relations into one, adding redundant attributes, etc seems like a logical design. However, denormalization seems to be done after the physical design of the db, which seems to make it fall under physical design. Which leads me to the question, where does denormalization belong?


texts: How to compare write speed before and after adding index?

texts: I am planning to add a non-clustered index (composite) to my table with around 5 million records and 7 columns. Table has only clustered index on id column. The first thing I am going to do is test on a development server. I just want to know the methodology of testing the writing speed.


texts: Deadlock debugging with node: log responsible queries

texts:
I have a system of many node scripts that read and/or write automatically on a Postgres database all the time. One of those scripts randomly gets a deadlock. I would like to debug this, but the problem is that I don't know which other query cause the deadlock. (I use pg)

So my question is:

If I catch an error like this

{ error: deadlock detected
at Connection.parseE (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:604:13)
at Connection.parseMessage (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:403:19)
at Socket.<anonymous> (/data/jenkins/workspace/03-10-Lotti-watcher-lotti-mod/import/node_modules/pg/lib/connection.js:123:22)
at Socket.emit (events.js:197:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:150:17)
  name: 'error',
  length: 336,
  severity: 'ERROR',
  code: '40P01',
  detail:
   'Process 2376 waits for ShareLock on transaction 55837412; blocked by process 22585.\nProcess 22585 waits for ShareLock on transaction 55837411; blocked by process 2376.',
  hint: 'See server log for query details.',
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: 'while locking tuple (226684,50) in relation "lotti"',
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'deadlock.c',
  line: '1146',
  routine: 'DeadLockReport' }

can I get the query and/or user of the other process in order to isolate and identify the problem? Or anything like this, if it makes sense.


texts: Efficiently get records with latest timestamp per group

texts:
I have a table which roughly looks like this

uuid uuid,
id id,
data text,
last_updated timestamp

Each row has an associated unique uuid. However, the key of interest is the id (indexed). Whenever we do an update, it happens that a row with the same id is inserted, the timestamp is the moment of putting it in the database, and it gets its own uuid. I would like to retrieve several of these rows in one query, but retrieving only the latest version.

So far I have

SELECT * FROM table WHERE id IN (16638051, 14727883)
GROUP BY id, uuid;

(ids are examples). However, I'm curious how to efficiently retrieve only the rows with the latest timestamp in that query?

Or would doing a single row at a time and retrieving it as a transaction be better?

UPDATE

This seems to do the trick (from another question).

SELECT * FROM (
   SELECT DISTINCT ON (id) *
   FROM   table
   WHERE  id IN (14727883, 16638051)
   ORDER  BY id, last_updated DESC NULLS LAST
   ) sub
ORDER BY last_updated DESC NULLS LAST

However, I wonder if a materialized view wouldn't be easier. It's a read heavy & low write database with a couple million rows, with the updates only happening daily.


texts:
I'm replicating database master to master and everything works ok except for mysql.user table.

When I create user in DB-1, that user is not created om DB-2.

I have this on both servers in cnf

binlog_ignore_db=information_schema
binlog_ignore_db=performance_schema
binlog_ignore_db=ssl

binlog_do_db=mysql

replicate_do_db=mysql
replicate_do_table=mysql.user

Status is showing that replication is ok.

SHOW SLAVE STATUS \G

Thank you for help.

EDIT 1:
I have also tried to sync all databases:

binlog_do_db=mysql
binlog_do_db=information_schema
binlog_do_db=performance_schema
binlog_do_db=ssl

replicate_do_db=mysql
replicate_do_db=information_schema
replicate_do_db=performance_schema
replicate_do_db=ssl

EDIT 2:

mysql  Ver 15.1 Distrib 10.2.26-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Ubuntu 16.04


texts:
When deleting, should I use LIMIT, or not (MySQL INNODB tables)

Each customer has a unique hash.
Each customer can have one or more records in a single table.

If I know in advance a customer can have up to 100 records maximum, and my DELETE query has LIMIT 100, then I presume once 100 records were deleted, the query will end without going to end of table.

However, if my customer only has 50records, and my DELETE query has LIMIT 100, the delete will continue until end of table is found since it will spend effort chasing the 51st record which does not exist.

Thus... If I do not use LIMIT does MySQL INNODB know in advance that after having deleted 50 records it need not continue further?

The answer can either create or eliminate performance issues...


texts: Alternative to slow [LEFT JOIN + OR] without UNION?

texts:
I have a reasonably simple query that returns all purchases for a given user, searching by his government issued id. The document could be in two different tables.

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

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

Standard query:

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

Example of UNION used:

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

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

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

This is the simplified tables' descriptions:

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

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

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

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

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

I've already tried:

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

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

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

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

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

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

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

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


texts: Two types of data, so two type of databases?

texts:
For a social network site, I need to propose a DB. The application is written in Java & will be hosted on VPS(s) initially.

Broadly classified there is two type of data to be stored at backend:

 1. dynamic lists which are:

     - frequently appended to
     - frequently read
     - sometimes reduced

 2. fixed set of data keyed by a primary key(sometimes modified).

 "For serving any page, I need to have access to both kind of data!"

As demanded by every other SN site, we need to consider for easy scaling in the future, but in addition to that our team & resources are also very very limited. We would like to start with a 1 or 2 medium sized VPS(s) & add more servers as data & load grows.

Personally I usually prefer something that is used by a large community, so ofcourse MySQL is big option but it doesn't fit our entire needs. It could be used for 2nd kind of data(among the list above) ie for storing fixed set of columns/data but not ideal for storing dynamic lists(ie 1st kind).
So should I use a 2nd database just to fit in only that type of data (two database each containing only data best suited for them)? (Some suggested Cassandra to store the 2nd kind of data.)
 What is the way to go ?


texts: Remove Data From Memory when Using SSAS Tabular In-Memory Mode

texts:
I use SSAS Tabular in In-Memory mode. I also use partitions to divide my data. So I will have one partition per month.

AFAIK, when using In-Memory mode, server keeps processed data in memory forever.

My concern is, if after a couple of years, my data get big so that SSAS ran out of RAM, what should I do?

Is it possible to remove old partitions from RAM?
Can I convert old partitions to use DirectQuery, while newer partitions still use In-Memory mode?


texts: How to automate Oracle DB installation (like a 'hands-free' install)?

texts:
I installed the Oracle 11g2 'standard' edition using the following zip-files:

linux.x64_11gR2_database_1of2.zip
linux.x64_11gR2_database_2of2.zip

I used the installation guide by Oracle which I found quite tedious, e.g. you need to install/configure an X-Server because the default installer is a GUI which you have to 'babysit' for some time.

Is there a way to automate a default Oracle installation?

I mean something like a script where I can specify: tell me what yum/apt-packages are missing, here are my oracle-zip-install-files, install it under $MY_BASEDIR and for everything else use some sane defaults!

And no GUI.

I know that there is an express-edition which comes as an rpm - this looks convenient on the first glance, but it seems to exclude some stuff the 'standard' edition includes, e.g. Pro*C.


texts: Why might a table's data space take up 4x the size of the raw data?

texts:
I have a table with 490 M rows and 55 GB of table space, so about 167 bytes per row.  The table has three columns: a VARCHAR(100), a DATETIME2(0), and a SMALLINT.  The average length of the text in the VARCHAR field is about 21.5, so the raw data should be around 32 bytes per row: 22+2 for the VARCHAR, 6 for the DATETIME2, and 2 for the 16-bit integer.

Note that the space above is data only, not indices.  I'm using the value reported under Properties | Storage | General | Data space.

Of course there must be some overhead, but 135 bytes per row seems like a lot, especially for a large table.  Why might this be?  Has anyone else seen similar multipliers?  What factors can influence the amount of extra space required?

For comparison, I tried creating a table with two INT fields and 1 M rows.  The data space required was 16.4 MB: 17 bytes per row, compared to 8 bytes of raw data.  Another test table with an INT and a VARCHAR(100) populated with the same text as the real table uses 39 bytes per row (44 K rows), where I would expect 28 plus a little.

So the production table has considerably more overhead.  Is this because it's larger?  I'd expect index sizes to be roughly N * log(N), but I don't see why the space required for actual data to be non-linear.

Thanks in advance for any pointers!

EDIT:

All of the fields listed are NOT NULL.  The real table has a clustered PK on the VARCHAR field and the DATETIME2 field, in that order.  For the two tests, the first INT was the (clustered) PK.

If it matters: the table is a record of ping results.  The fields are URL, ping date/time, and latency in milliseconds.  Data is constantly appended, and never updated, but data is deleted periodically to cut it down to just a few records per hour per URL.

EDIT:

A very interesting answer here suggests that, for an index with much reading and writing, rebuilding may not be beneficial.  In my case, the space consumed is a concern, but if write performance is more important, one may be better off with flabby indices.


texts: Sum values at a group level rather than detail

texts:
I have an invoice report I'm developing for some project billing. The main tablix has several groups, in this order:

ProjectGroup
TicketGroup
NoteGroup
Details (actual billing details)

The data can end up looking something like this:

project_id  ticket_id   note_id     hours        billing_agreement_id billed_hours
----------- ----------- ----------- ------------ -------------------- ------------
1           1           1           0.50         4                    0.75
1           1           2           0.50         5                    1.00
1           1           3           1.00         7                    1.00
1           1           8           5.00         7                    3.00
1           1           8           5.00         2                    2.00
2           2           4           4.00         1                    4.00
2           2           5           2.00         6                    3.00

In other words, a ticket is assigned to a project, notes are entered on a ticket (with a corresponding number of hours worked), and note hours are then billed to one or more agreements/contracts (if a block time agreement is exhausted, the remainder may spill over to another agreement, for example). This is all working fine, as far as the application is concerned.

So now I'm creating the invoice report, and I need to provide a grand total of hours worked at the end of the report. Take a look at the 4th and 5th rows (with note_id = 8). The report footer uses Sum(hours), which then ends up counting that note as 10 hours. Both note_id and hours come from the note row, and should be summed accordingly. Including hours in the grouping expression for NoteGroup has no effect on the behavior of Sum, naturally.

Is there a straight-forward way to make Reporting Services sum up hours, looking only at the grouped values for that group, rather than the detail rows? I can't think of anything particularly elegant as of yet. I can fall back to weird custom code stunts if necessary.


texts: Why is this column order in my non-clustered index better for my query?

texts:
I am working on a query for a table that contains movie tickets. The database holds 380k rows. A rows represents a showing of the movie (which cinema, when, how many tickets  and at what price among other things).

I need to compute a few totals for every row: Admissions Paid, Admissions Revenue, Admissions Free and Total Admissions.

For a given row Admissions Paid is the sum of all tickets for that movie up until that point where price>0. The other 3 columns are computed similarly.

I wrote a query and created an index:

 SELECT [ID]
      ,[cinema_name]
      ,[movie_title]
      ,[price]
      ,[quantity]
      ,[start_date_time]
      ,* --I need all the columns for reporting
     ,(select SUM(quantity)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time
        and price=0) as [Admissions Free]
        ,(select SUM(quantity)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time
        and price>0) as [Admissions Paid]
        ,(select SUM(quantity*price)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time
        and price>0) as [Admissions Revenue]
        ,(select SUM(quantity)
        from [movies] i
        where i.movie_title=o.movie_title
        and i.start_date_time<=o.start_date_time) as [Total Admissions]
  FROM [movies] o

I created the following index which brought the query time down to 5 minutes:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]
(
    [movie_title] ASC,
    [start_date_time] ASC,
    [price] DESC
)
INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

But this index brought the query time down to 1:30:

CREATE NONCLUSTERED INDEX [startdatetime_movietitle_price] ON [dbo].[movies]
(
    [start_date_time] ASC,
    [movie_title] ASC,
    [price] DESC
)
INCLUDE (   [quantity]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

So my question is: why? From my understanding, it makes more sense to first gather all the movie titles and then look at the start times because there are more start times then there are movies. Distinct movies: 51, distinct start_date_times: 8786

Doesn't the underling B-Tree not cut off more branches if it eliminates the unnecessary start_date_times first?

Here are the execution plans:

The first picture shows the execution plan for the index with movie_title first, the other picture shows start_date_time first.


texts:
I need some information on how to synchronize all tables and views in sql server. What I wanted to do is basically I want all production server has the same tables and views structures as the staging server.

Can anyone point me to the right direction?


texts: Oracle AWS RDS supermaster user cant grant select on some tables

texts:
I'm dealing with some sort of issue beyond my Oracle knowledge. A customer wants to execute some queries to detect issues, locks, etc in a Oracle RDS database. I assume this queries needs database administration privelege to be executed, in fact, is pretty clear that me, as AWS Oracle supermaster can execute without error any query, for example:

select S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
and s.status = 'ACTIVE'
and s.username <> 'SYSTEM'
order by s.sid,t.piece;

the same query in normal user returns an error

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:
*Action:
Error at Line: 3 Column: 6

so my initial idea was to let normal user to SELECT some tables, one by one, adding privileges one by one. It worked to me with the first query, I was able to add this to the normal user (by sqldeveloper plus)

GRANT SELECT ON v$session to "TESTUSER";

so I decided to continue this approach. Bad for me, next SQL commands to assign privileges are returning an error, for example:

Error starting at line : 14 in command -
GRANT SELECT ON v$sqltext_with_newlines to "TESTUSER"
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

So is like I'm not "powerful" enough as supermaster of Oracle RDS to assign some privileges to other Users. I tried to use the GUI of sqldeveloper and run "Grant all" and it returns to me the same error. Most of the privileges were not able to be granted.

Is this normal behavior? I can assure I have the supermaster user of AWS RDS Oracle. I double checked before.

Thanks


texts: Oracle 12c, Will dropping partition which has 25% of total data, rebuild the indexes?

texts:
I have Oracle 12c database and partitioned table. There is a old partition which has 68734MB which is 27% of total.

I need to drop this partition. This is a production database and I does not have a downtime window for this.

I want to know if I drop this partition, will the indexes of this table be unusable or rebuild ? What would be the best strategy to drop this partition with minimum impact on the live traffic?


texts: Can SQL Server 2014 Standard Edition Trial be upgraded to a full Core license?

texts:
If I install a copy of SQL Server 2014 Standard Trial edition can that version be upgraded (in place without a full re-install) to the full version (core license) afterwards? I'd love to get this stuff installed and configured while I'm waiting for the licensing but I don't want to cause myself grief if it can't be easily licensed.

EDIT: More info, for anyone else that finds this question -

The Microsoft site doesn't specify anything beyond "SQL Server 2014" when you download the evaluation edition. But since they don't let you pick the specific edition I think Shanky's comment is correct (it is the Enterprise edition).

As Thomas pointed me to, here are the upgrade paths from SQL Server 2014 Evaluation Enterprise (since Microsoft loves moving/killing links):

SQL Server 2014 Evaluation Enterprise 2
SQL Server 2014 Enterprise (Server+CAL or Core License)
SQL Server 2014 Business Intelligence
SQL Server 2014 Standard
SQL Server 2014 Developer SQL Server 2014
Web Upgrading from Evaluation Enterprise (a free edition) to any of
the paid editions is supported for stand-alone installations, but is
not supported for clustered installations.


texts: Differences between Latin1_General_CI_AS and Latin_General_BIN collations?

texts:
We want to write a test which proves that the collation of a column in the database is indeed Latin1_General_CI_AS. The database's default collation is Latin_General_BIN.

So we need to have some observable difference between those two databases, write a test using comparing/ordering and assert the correct outcome (which should be compared/ordered by Latin1_General_CI_AS).

Anybody know what kind of difference we could observe?


texts: Using a combination of block storage and object storage for serving photos. Good idea?

texts:
I have a real estate app and I need to create a database for listing photos.  These photos will be actively accessed by users.

The full-size photos are about 50-70KB in size.  I will create thumbnails for the first photo of each listing, which will be about 20KB.

Here's my plan:

Store thumbnail photos directly on server (block storage), for faster access
Store all the full sized photos in object storage

Is this a good optimization idea?  Am I adding unnecessary complexity?  Should I simply store all photos in object storage?


texts: Resolve "stuck" system process of dropping indexes

texts:
I am re-vamping some indexes, and have decided to drop 3 on a certain table. The process has been running for almost 2 hours now. The table does have quite a few million rows, but I still feel that is a little excessive.

I am new to this, so I had to do some research and came across the SP_WHO2 command, which shows me this:

However, I am having trouble finding out how to resolve the issue of getting those indexes dropped. Should I just cancel and try again? Will that harm anything? Is there a better way?

Thank you for your time

UPDATE: I now also see the below. I see a little more info on what is happening, but still not sure the best way to resolve. I am having trouble finding resources on how to interpret SP_WHO2 results.


texts:
I created a table with the following columns:

CREATE TABLE INDICADOR_DADOS
(

  ID       VARCHAR2(10 CHAR)   NOT NULL,
  DATE_START       DATE        NOT NULL,
  DATA_END         DATE        NOT NULL,
)

Now wanted before adding, checks if there are dates between which intend to add the following query:

 select COUNT(*) as count from indicador_dados
        where id='23' and TO_DATE(data_inicio,'yyyy/mm/dd')
            between to_date('2013/05/15','yyyy/mm/dd')
            and to_date('2013/05/16','yyyy/mm/dd')

The problem is that this query is not to be returned to 1, and because they must have in the database 05/03/2013 (date_start) and 17/05/2013(date_end). Someone can help me?

maybe duplicate: oracle-datetime-in-where-clause but not it helped me to resolve


texts: Migration of chunks not taking place in sharding

texts:
I am trying sharding in MongoDB. I am working with

1 config server
1 Query router
2 shards

I tried hash based sharding. The data is getting partitioned but sharding is not successful. In one shard some documents are present but in another shard the number of documents is zero. In logging I could see the warning:

  warning: moveChunkCmd deleted data already in chunk

I can get a point that the data is lost while the chunk is migrating from one shard to another shard. But I am not sure how to avoid the data lost while the chunk is migrating.


texts: Connection between SQL Server 2014 and Oracle 12c

texts:
I have to server:

1.Server_A with Oracle 12c

2.Server_B with SQL Server 2014

I want these servers see each other, though I can SELECT anything I want in Server_A from Server_B and vice versa.

I only want SELECT queries (no possibility to run INSERT, UPDATE, INSERT queries), and also if possible EXECUTE any procedures.


texts: how big is the server load when client hit information_schema for monitoring

texts:
when I create some small application monitor for mariadb, and then I send query like
select variable_value from information_schema.global_status where variable_name = 'THREAD_RUNNING'

or
select variable_value from information_schema.global_status where variable_name = 'UPTIME'

and will be hit every 5 seconds. This is effective for application monitor and how big impact to server ?


texts: Is it possible to use Management Studio in Linux? I want to connect to my SQL Server instances with an IDE. How can i do that?


texts: Connecting to MS SQL server from a Linux box

texts:
I am trying to connect to MS SQLServer from an application that I have written (using Qt) which runs on Linux. Here are some details of the setup:

The SQLServer box (SQLBox) is part of my domain (mydomain.local). This domain uses Active Directory running on ADBox.mydomain.local, The Linux box is not part of the domain. The user on the Linux box is logged on locally. ie mvincent@linuxbox.

I have installed kerberos on Linux and configured it to connect to ADBox.mydomain.local. Using kinit mvincent@mydomain.local I get a ticket returned which can be seen using klist. mvincent@mydomain.local is setup with permissions to access SQLServer on SQLBox.

I am using ODBC to connect to SQLServer. SQLServer is configured to accept connections only from ActiveDirectory users. A user name and password cannot be used to connect to the SQLServer. My ODBC connection string includes Trusted_Connection=yes

My Qt application will run as the logged on user on Linux. This will be mvincent.LinuxBox. I can't get a ticket for this user, so the connection is refused.

I can get a ticket for mvincent@mydomain.local, but I can't figure out how to enter this ticket or this user into the ODBC connection string.

Can I do what I'm trying to do?  If so, how?

Thank you.


texts: Is it possible to retrieve/get the query hash of a query without searching the DMOs?

texts:
Sometimes I want to compare the performance of two queries by running them multiple times while an extended events session is capturing the metrics.
To avoid too much filtering and/or noise I would like to filter my extended events based on the query hashes.

In order to do that I need the query hash. So far I have had to look them up in the DMOs based on the text. But then I spend as much time finding the query hashes as I do just filtering the extended event session in other ways.

So is there an easier/quicker way to retrieve/get the query hash?


texts:
When I run the latest version of sp_blitz I get the following:
Warning: Null value is eliminated by an aggregate or other SET operation.

  Msg 1934, Level 16, State 1, Procedure sp_Blitz, Line 3467

  INSERT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

It seems no one else is having this problem except me. What have I done wrong?


texts: create a related row if no related row exists

texts:
I have two mysql tables with a one-to-many relationship.

I want to find all rows in the 'one' table which do not have a row in the 'many' table and add a single row in the 'many' table for each one.

How can I edit my query to achieve this?

INSERT INTO many_table SET
    id = ( SELECT id
           FROM one_table
           JOIN many_table ON one_table.id = many_table.related_id
           WHERE many_table.related_id is null
         )
    name = 'george'


texts: Sql server table insert performance optimization

texts:
Setting

In a datawarehouse, I am joining a fact table to 20 dimensions. The fact table has 32 million rows and 30 columns. This is a temporary staging table so I don't have to deal with other users reading or writing to the table. I select 10 columns from the base table and 20 columns from the respective dimensions. The dimension tables are small (between 3 and 15.000 rows). The fields on which are joined are both integers and nvarchars. I use a SELECT ... INTO statement. There are no indexes on the tables.

The execution speed of this query is too slow to be useful.

Tried-out solutions

Because the query takes too long to process, I tried out following solutions:

Split the 20 joins into 4 joins on 5 tables. The query performance remains low however.
Put indexes on the foreign key columns. No significant time decrease.
Make sure the fields of the join condition are integers. I noticed a performance increase of 25%. Not quite what I am searching for.
Use an insert into statement instead of select into. Worse performance because of log file growth although the database is in simple recovery mode.

These findings led me to including the actual execution plan which shows that 89% of the cost lies in the table insert. The other costs are 8% table scan on the fact table and 2% on hash matching for the inner joins.

Questions

What are the possible reasons of the slow table insert?
What are ways to identify this bottleneck without the execution plan?
What actions can I take to reduce the cost of the table insert?


texts: Performance Implications of Explicit Collation Conversions across Linked Servers

texts:
I locally host host databases from two vendors on two different SQL instances, but on the same network. These servers frequently communicate with each other (through joins and whatnot), so we have them configured as linked servers.

Currently, both databases use the SQL_Latin1_General_CP1_CI_AS collation. However, one of the databases will need to be changed to Latin1_General_CI_AS soon in order for our vendor to continue fully supporting it.

My expectation is that we could manage this at the linked server level by setting "Use Remote Collation" to false. However, I've read that we should expect performance to plummet, if we take that approach. Is that correct? Short of testing this in an internal development environment (which would prove difficult for a variety of reasons), is there a way for us to estimate/calculate the sort of performance hit we would experience?

Thanks for your feedback! Hopefully this question isn't too ambiguous. I've done a bit of research on the topic already but am hard time finding a concrete response.


texts:
Using MySQL 5.6.24.

What is the best way to extract the text of queries using full-table scans? The "Full Table Scans" report in MySQL Workbench doesn't include the complete query (some text is replaced with ...), for example:
SELECT Db AS scope , SYSTE ... sql.dbWHERE ? LIKEdb`

I also tried querying the sys database directly but the same limitations exist there:
select * from sys.statements_with_full_table_scans

Any suggestions?

Thanks!


texts: Oracle allows inserting into table even after revoking quota

texts:
I am studying creating users and granting roles and I have come across this scenario where I grant unlimited quota to a user, insert into the table and then revoke the quota. I expected that after setting quota to 0, the user would not be able to insert into the table. However, the user is still able to insert. I wonder if this is expected, if there is another command to really prevent the user from inserting again. I am using Oracle 19c and user's current priviledges are create session and create table.
sqlplus system\oracle

SQL> CREATE TABLESPACE libgeneral datafile '/u01/app/oracle/oradata/ORCLCDB/orcl/libgeneral.dbf' SIZE 20M AUTOEXTEND ON NEXT 40M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

sqlplus library\libuser

SQL> create table t1 ( idx number ) tablespace libgeneral;

Table created.

SQL> insert into t1 values (1);
insert into t1 values (1)
            *
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace 'LIBGENERAL'

sqlplus system\oracle

SQL> alter user library quota unlimited on libgeneral;

User altered.

sqlplus library\libuser

SQL> insert into t1 values (1);

1 row created.

sqlplus system\oracle

SQL> alter user library quota 0 on libgeneral;

User altered.

sqlplus library\libuser

SQL> insert into t1 values (1);

1 row created.


texts: I'd like to know if there are certain configurations of Mysql that should be considered to keep the effect of the power failure as low as possible. Which configuration points should be noted and what values should they have.


texts: The function fn_my_permissions is not showing ALTER permission even when appears assigned to the user

texts:
I have a DB user in SQL Server (2012) that I have granted ALTER permission for the DB (via the UI: DB name > Secuity > Users > affected user > Properties > Securables wherein I can see the ALTER perm is checked by Grantor "dbo"). I can see that they have Explicit ALTER privileges for the DB when looking at the user's properties in the DBMS UI (DB name > Secuity > Users), but when that user queries their permissions like...
SELECT permission_name FROM fn_my_permissions(NULL, 'DATABASE')

...they are not seeing the ALTER permission. Why would this be happening? Am I misunderstanding something (is this info somewhere else)?


texts: use function arguments or variable in postgres function

texts:
I want to set the default value for the column building and floor in my table points in the below function called test(b,f)

Create or replace function test(b text, f text) returns void as
$$
Begin
Alter table points alter COLUMN building set default b;
Alter table points alter COLUMN floor set default f;
End;
$$
language plpgsql;

When I run select test('Shopping Mall','03') it gives me below error:

 column b does not exist

It seems that I cannot use a function arguments b in the alter ... query?


texts: COPY TO file in Postgres fails because of write permissions

texts:
I'm using Postgres 9.3 in a CentOS 7 and another in CentOS 6 server and the copy table to /path/file command fails.
I receive an error message that tells that I don't have write permissions.
The postgres service is run under the postgres account (the name of the Linux user).
The parent folder of the file already exists.

If I do this:

su - postgres
touch /path/file

It succeeds, as the user Postgres has write permissions.

The postgres user belongs to the copia Linux group.
I have set in /path the permissions 770, and the owner is another user called ema, also in the copia group.

If I change the permission to 777 then I can use successfully the copy to command, but then any user can see my data.

Another workaround is to change the owner of the folder to the postgres user, but don't understand why have to do this.

# ls -ld /mnt/copias
drwxrwx---  3 ema copia 4096  7. Feb 12:36 .
drwxr-xr-x. 3 ema copia 4096 27. Jan 10:10 ..
drwxrwxrwx  3 ema copia 4096  7. Feb 12:36 historico

/mnt/copias is the real name of /path, and it is mapped to an NFS storage.

Why doesn't it work?

Notes:

I know I can use the psql \copy command, but I prefer the copy to file command, because it is run inside a Postgres PL/pgSQL procedure.


texts: Performance problem using mysql fulltext search

texts:
I have a fulltext index on a column that contains an absolute file path (file_path), for a file indexing application.

There are 500 000 rows in the table. The MySQL version is 5.7.19.

Most, if not all rows begins with 'c:\data\' (ie: 'c:\data\clients\microsoft\private\emails.xls')

When I perform a search for 'microsoft private emails', ie;

MATCH(file_path) AGAINST('microsoft private emails' IN BOOLEAN MODE)`

everything is fine and I get the results in less than a second.

When I perform a search with the root of the file path, ie;

MATCH(file_path) AGAINST('"c:\\data\\clients"' IN BOOLEAN MODE)
-- note that it's an exact match with quotes ""

... the query runs forever (443 seconds last time I waited long enough), and the CPU hits 100% during the time of the query.

I use a LIMIT 0, 10 on both queries. The problem happens both with MyISAM and Innodb.

In fact, I have to execute $ service mysql restart to get things back to normal, otherwise the whole server becomes more-or-less responsive.

My question is:

Why is this happening and is there a way to fix it?

My theory, and I have very little knowledge about how the fulltext index works, is the fact that c:\data is in every row, the index fails and it's somehow doing a full table scan but with so many '\' in every row, it causes mysql to just loose it.

If I am right about the '\' thing, how is it possible to search for absolute file paths with mysql's full text index?

I hope I provided enough details.


texts: Why is this query creating 300+ temporary tables in Mysql?

texts:
SHOW  global STATUS LIKE '%tmp%';

set @UID = 132;

set @param2 = 'cde';

set @param3 = 20130331;

set @param4 = 'C';

select  p.id, p.column44, p.column42, ue.column35, u.id, p.column43, p.column45, p.column46, pp.column63, ue.column36

             from table1 a1
                join table2 u on  u.id = a1.column11
                    join table4 p on p.id = u.column23
                    left join table5 lee on lee.column51 = u.column23
                join table3 ue on ue.uid = u.id  and ue.column32 = @param3
                    join table6 pp on pp.column62 = ue.column33  and  pp.column21 = ue.column21
            where a1.column12 = @UID
               and a1.column13 in ( 'A11', 'A22', 'A33')
               and u.column22 = true
               and ue.column35 >= @param3
               and 1 = (CASE
                            WHEN @param2 is null THEN 1
                            WHEN @param2 = 'ABC' THEN 1
                            WHEN @param2= 'CDE' THEN ue.med_lob_column62 = @param2 AND (@param4 = 'C' OR ue.ue.column33 in ('123', '456'))
                             WHEN @param2= 'DEF' THEN ue.med_lob_column62 = @param2 AND column33 != 'abcde-f'
                            WHEN @param2= 'GHI' THEN ue.med_lob_column62 = @param2
                        END);

SHOW  global STATUS LIKE '%tmp%';

I measure the amount of global temp tables created before and after executing the above query. Everytime i execute the query i see an increase of around 500+ in 'Created_tmp_tables' status variable value. I'm confused as to why so many temp table are getting created ? or is it normal Mysql behavior. If so why ? The schema of the above tables look like this:

CREATE TABLE `table1` (
  `column11` int(11) NOT NULL,
  `column12` int(11) NOT NULL,
  `column13` enum('A11','A22','A33','A44','A55','A66') NOT NULL DEFAULT 'A11',
  PRIMARY KEY (`column11`,`column12`,`column13`),
  KEY `table11_index1` (`column12`,`column11`),
  KEY `table11_index2` (`column12`)
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `table2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column21` int(11) DEFAULT NULL,
  `column22` tinyint(1) DEFAULT '0',
  `column23` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `table2_index1` (`column23`)
) ENGINE=InnoDB AUTO_INCREMENT=1547471 DEFAULT CHARSET=latin1

CREATE TABLE `table3` (
  `uid` int(11) NOT NULL,
  `column32` date NOT NULL,
  `column33` varchar(10) DEFAULT NULL,
  `column21` int(11) NOT NULL DEFAULT '4',
  `column35` date DEFAULT NULL,
  `column36` char(5) DEFAULT NULL,
  PRIMARY KEY (`column32`,`uid`),
  KEY `table3_fk2` (`med_lob_column62`),
  KEY `table3_fk5` (`column21`,`column33`),
  KEY `table3_fk3` (`column21`),
  KEY `table3_fk1` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `table4` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `column42` char(1) DEFAULT NULL,
  `column43` varchar(50) DEFAULT NULL,
  `column44` date DEFAULT NULL,
  `column45` varchar(100) DEFAULT NULL,
  `column46` varchar(100) DEFAULT NULL,
   PRIMARY KEY (`id`),
  KEY `table4_index1` (`column45`),
  KEY `table4_index2` (`column46`)
) ENGINE=InnoDB AUTO_INCREMENT=1564327 DEFAULT CHARSET=latin1

CREATE TABLE `table5` (
  `column51` int(11) NOT NULL,
  `column52` date DEFAULT NULL,
  PRIMARY KEY (`column51`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `table6` (
  `column21` int(10) NOT NULL,
  `column62` varchar(10) NOT NULL,
  `column63` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`column21`,`column62`),
  KEY `table6_fk1` (`column21`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts: Postgres: What happens if your slave DB went down temporarily?

texts: I have a master DB that is replicated to a slave DB through binary replication. What happens if this slave DB were to go offline for a period of time? Would it eventually catch up with the master as soon as it goes back online (without much intervention)?


texts: Postgresql on Windows and custom extensions

texts:
I'm running Postgresql on Windows 10 and trying to install custom extension (https://github.com/eulerto/wal2json) for logical decoding.

However, I have to build and install this extension manually. While building is possible via Visual Studio I can't find a way how to install extension via pgxs.  Pgxs seems to be missing in Postgresql distribution package for Windows and I can't find a viable way how to create extension.

pg_config --pgxs shows the following path:

C:/Program Files/PostgreSQL/10/lib/pgxs/src/makefiles/pgxs.mk

but this path doesn't exist.

Does anybody know how to install Postgresql extensions from source code on Windows?


texts: Dynamic quorum and Dynamic witness in Always On AG 2016

texts:
I have a Always On AG 2016, configured on Windows Server 2012 R2 – 2 node cluster. Quorum configured as 2 nodes + Fileshare witness, totally 3 votes.

Scenario 1:
Now, either 1 node fails – remaining vote = 66.66 (majority) and cluster is up.

Scenario 2:
Now, fileshare witness fails – remaining = 66.66 (majority) and cluster is up

Kindly help me understand the dynamic quorum and dynamic witness on scenario 2.

My doubts, post scenario 2:

Will it automatically take away 1 vote from either one of the nodes? If yes, remaining vote will be 33% not majority and single point of failure.

What will be the role of dynamic witness in this scenario?

Thanks in advance!.


texts: Is it possible to set SQLite DB on server and connect to it in an Android app?

texts:
I'm creating an app as a study project. One of requirements is not to hardcode. For this reason, I want to get data from DB with every start of the app and change data stored in DB outside of the app code. I wanted to use Redis and store data in JSON at first, but there were too many problems, because Redis doesn't work with Android by default.

So, the question is: is it possible to use SQLite set on server? I know that this DB is not supposed to work in client-server mode, but I have found out some SQLite hostings in the Internet. If it is possible, how to perform it? If not, which way should I store data? SQL format is pretty convenient for me, because it is literally a table.


texts: Accidentally drop table in database that has no backup

texts:
One Table is accidentally dropped in my database. I had not taken backup of the database since it is in development environment. I want to restore the table. Kindly please provide any solution. The last backup was taken 6 months ago.
The table has related data to other tables.


texts: Function Based Index with XPATH extractValue

texts:
// Oracle Version 11.2.0.4  Standard Edition
// OS RedHat

I am trying to create a function index on a large table, based on using an XML object.

So I have done stuff such as:

CREATE INDEX test_function_itemgroup_id
ON test_function
(
    extractValue
    (
        RECORD,
        '/Product/PublishingDetail/ItemGroup/ItemGroupId',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    )
)

So when I have a query such as:

select PRODUCT_ID
from TEST_FUNCTION pr
where
    extractValue
    (
        RECORD,
        '/Product/PublishingDetail/ItemGroup/ItemGroupId',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    ) = 'NAME-CLASS'

The function Index is used and I know I have the right namespace for the xml.

When I do stuff such as:

CREATE INDEX test_function_nbd
ON test_function
(
    extractValue
    (
        RECORD,
        '/Product/Extras[ExtrasIDType="RECORDCREATEDBY"]/IDValue',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    )
)

select PRODUCT_ID
from TEST_FUNCTION pr
where
    extractValue
    (
        RECORD,
        '/Product/Extras[ExtrasIDType="RECORDCREATEDBY"]/IDValue',
        'xmlns="http://www.editeur.org/onix/3.0/reference"'
    ) = 'Other'

The system does a full table scan instead of using the function index.

I have used an XMLIndex, but the performance of that has been so slow and does not seem to allow the function based indexes to be used.

Has anyone got any suggestions about how to proceed with this?

Update:

Added the full xmlns as have enough reputation now

There are no other indexes on the table.

What appears is that the Optimizer seems to be converting the XPATH to
two different queries.

/Product/Extras/ExtrasIDType  and
/Product/Extras/IDValue

which are not indexed by the function.

In my particular case I can't index these individually as these can have multiple values and function based indexes can't use multiple values.

Also if I have to use two function based indexes the system will do a full table scan. (According to oracle 11 documentation)


texts: By using SQL Window Functions, how can I get the lead of the last row in a partition?

texts:
Considering the following simple example:

select lead(c) over (partition by a order by a)
from (values
      (1,'1','date1'),
      (1,'2','date1'),
      (2,null,'date2')
) t(a,b,c)

The first two rows are records with the same ID (column a) and "date1" (column c). The ID duplication exists due to an unnest operation made in an array column that might have different sizes or number of elements. The unnest operation results in column b.

Now, I want to get the lead date of the next ID, "date2". The query I wrote outputs "date 1" for the first record, but I actually want to get "date 2". I'm not interested in the static solution to this problem, which would be lead(c,2) over (order by a), but on how to get the lead of a dynamic sized window.

Expected output:

date2
date2
NULL


texts: how do i print a record coming from joinig query as follows in mysql


texts:
I have MySQL Cluster instance and want one column (BLOB) to be stored on disk instead of memory.
I tried to follow this tutorial: http://mikaelronstrom.blogspot.com/2006/02/how-to-define-table-that-uses-disk.html
And this is output I am getting:

mysql> CREATE DATABASE test1;
Query OK, 1 row affected (0.00 sec)
mysql> use test1;
Database changed
CREATE LOGFILE GROUP lg1
    -> ADD UNDOFILE 'undofile.dat'
    -> INITIAL_SIZE 16M
    -> UNDO_BUFFER_SIZE = 1M
    -> ENGINE = NDB;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '16M
UNDO_BUFFER_SIZE = 1M
ENGINE = NDB' at line 3

Here is syntax for this command: http://dev.mysql.com/doc/refman/5.5/en/create-logfile-group.html
Any ideas why this is not working? I cannot find where I am violating this command syntax.


texts:
I'm in the process of designing the Calendar table. I took some inspiration from Database design book by Louis Davidson.

What I'm confused about are the data types.

Example:

MonthName varchar(10) NOT NULL,
Year varchar(60) NOT NULL,
Day tinyint NOT NULL,
DayOfTheYear smallint NOT NULL,
Month smallint NOT NULL,
Quarter tinyint NOT NULL,

I think the Year might be a typo, but not sure why it's VARCHAR when other numeric dateparts are of numeric data types.

But why is Month SMALLINT when it could be TINYINT as well?

I thought I should design table to use the smallest correct data type to save some page space (even insignificant).

On the other hand the DATEPART function in TSQL returns INT data type, so maybe I should make all the numeric columns INT to avoid conversion cost?


texts: Error on Oracle calling external procedures

texts:
I have a C library which should be called from a pl/sql function so I make a .so library and after that I create a pl/sql function to call it.

the C source(libcprog.c) is like this:

int exec_hmmftg ( char *p1 , char *p2, char *p3 ){}

the library in oracle database is:

CREATE LIBRARY CPROGLIB UNTRUSTED AS '/libcprog.so';

the created pl/sql function is:

CREATE OR REPLACE FUNCTION CALL_C_PROGRAM(P1    IN     VARCHAR2,
                                          P2    IN     VARCHAR2,
                                          P3    IN OUT VARCHAR2)
    RETURN BINARY_INTEGER
    IS EXTERNAL NAME "exec_hmmftg"
    LIBRARY CPROGLIB
    LANGUAGE C
    PARAMETERS(P1 STRING, P2, P3 BY REFERENCE STRING, RETURN INT);

When I tried to call CALL_C_PROGRAM I catch:

ORA-01405: fetched column value is NULL

How can I know where is the problem?

New

I changed library definition to address somewhere wrong and I still catch the same exception!

Is there a parameter or something which I have to set before calling the function?


texts: Why is my view running extremely slow in comparison to the regular query?

texts:
I have a sql server view that has a number of joins in there. These joins are pulling and comparing records of 60,000+ when aggregating all three tables. The problem is the view takes 1.5 hours to run in comparison to the regular query which is maybe 2-5 mins. I tried using paging and same results so I am not sure what else to do at this point.

Here is a copy of the code in the view which I tested in the query also:

ALTER VIEW [dbo].[dashboardview]
WITH SCHEMABINDING

AS
SELECT         o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
FROM            dbo.output AS o LEFT OUTER JOIN
                         dbo.jobs AS j ON o.Job = j.ID LEFT OUTER JOIN
                         dbo.scripts AS s ON  j.Script = s.ID
Where o.DateRan < GETDATE()

ORDER BY o.ID DESC
OFFSET 0 ROWS FETCH NEXT 10000 ROWS ONLY
GO

Any thoughts on what I can do to optimize this?