texts: N'Șc' considered duplicate key of N'C' using Latin1_General_CI_AS collation

texts:
I have a table with a unique key that includes an NVARCHAR(50) column (correct or not, but is there). So, when trying to insert Șc or C (doesn't matter the order of the insert) it breaks on the 2nd insert due to collation issues. Here is the error:

  (1 row(s) affected) Msg 2601, Level 14, State 1, Line 16
  Cannot insert duplicate key row in object 'dbo.testT' with unique index 'IX_TestT'. The duplicate key value is (C).

Select returns:

Database default collation is Latin1_General_CI_AS. Spent some time looking on how to solve it, without changing too much the already existing structure, but can't find a way to getting working. Tried different collations and combinations, everything fails. Read (here and here) about character expansions and so on, still stuck. Here is a sample code that I'm using to replicate the issue, feel free to modify and recommend anything that could help solve this.

CREATE TABLE testT (
    [Default_Collation]     [NVARCHAR] (50) COLLATE DATABASE_DEFAULT,
    [Latin1_General_CI_AS]  [NVARCHAR] (50) COLLATE Latin1_General_CI_AS,
    [Latin1_General_CI_AI]  [NVARCHAR] (50) COLLATE Latin1_General_CI_AI,
    [SQL_Collation]         [NVARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS);
CREATE UNIQUE CLUSTERED INDEX [IX_TestT] ON [dbo].[testT] ([Default_Collation])
ON [PRIMARY]
GO

INSERT INTO testT
SELECT  N'Șc',  --COLLATE Latin1_General_CI_AS
        N'Șc',  --COLLATE Latin1_General_CI_AS
        N'Șc',  --COLLATE Latin1_General_CI_AS
        N'Șc'   --COLLATE Latin1_General_CI_AS

INSERT INTO testT
SELECT  N'C'    --COLLATE Latin1_General_CI_AS
        ,N'C'   --COLLATE Latin1_General_CI_AS
        ,N'C'   --COLLATE Latin1_General_CI_AS
        ,N'C'   --COLLATE SQL_Latin1_General_CP1_CI_AS

SELECT * FROM testT;

DROP TABLE testT;


texts: Non-Clustered Index takes much longer to create than clustered Index

texts:
I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap.  The problem I have is the clustered index takes about 2 minutes to create:

CREATE UNIQUE CLUSTERED INDEX UCX_Idx ON myTable (
    IdCol1 ASC
    ,Col2 ASC
    ,IdCol4 ASC
    ,IdCol3 ASC
    ,Col1 ASC
    ,Col6 ASC
    ) ON PS_1 (IdCol1) END

But the non-clustered index takes about 1.5 hours to create:

CREATE NONCLUSTERED INDEX IX_Idx1 ON myTable (
    IdCol1 ASC
    ,IdCol2 ASC
    ,IdCol3 ASC
    ,IdCol4 ASC
    ,Col1 ASC
    ) INCLUDE (
    Col2
    ,DateCol1
    ,Col2
    ,Col3
    ,Col4
    ,Col5
    ,Col6
    )
    WITH (SORT_IN_TEMPDB = ON) ON PS_1(IdCol1) END

I'm not seeing this behavior with SQL Server 2014 but I am with SQL Server 2016.  Same amount of RAM and CPU.   I've tried it with out the SORT_IN_TEMPDB = ON but it has a similar problem.  I've actually been seeing this in different places in my environment and all with SQL Server 2016 (Standard Edition) installations.


texts:
Possible Duplicate:
  Files - in the database or not?

I Want to store a file into sql server 2008 from TSQL (Management Studio).
I Know the file saving into varbinary column can be done by using the .NET

But I want to do this from Management Studio.

In C# and VB.NET we saving file into varbinary column by using ReadAllBytes()

How I can achieve this in Management Studio


texts: How can I detach a specific relationship to a node then "move" it to another node in a graph database?

texts:
I'm trying to make a game using the neo4j graph database as an experiment. Some nodes are Users and some nodes are Rooms. Rooms have a CONTAINS relationship to Users so the database has a lot of this: (someroom)-[CONTAINS]->(someuser)

When a User leaves a Room and enters another in the game, the CONTAINS relationship needs to reflect that by detaching from the Room and reattaching to a different Room. Is there a way to do this without deleting and recreating that relationship? Like some sort of advanced, more efficient SET command that changes the origin of the relationship?


texts: Why do my SQL Server 2016 transaction logs keep growing?

texts:
I'm trying transaction replication for the first time on SQL Server 2016

I have the distribution database on C:\ (I know, but I'm waiting on a new drive), and my bhdb database on D:\.

My problem is that within 6 hours, in spite of the fact the log files started as 1Gb in size, it takes both the C: and D: drives empty space.

How do I stop that from happening?

Thanks.


texts: What units used in DBA_TABLESPACE_USAGE_METRICS?

texts:
What units are used in the usage metrics (USED_SPACE and TABLESPACE_SIZE columns) described in Oracle's DBA_TABLESPACE_USAGE_METRICS static data dictionary view?

The Oracle Database Reference only tells they are the "Total space consumed by the tablespace" and "Total size of the tablespace", without mentioning units. Are they in bytes? Megabytes? Blocks?


texts:
I'm having problem in Oracle, where I have two procedures that are executed one after the other.

Where the first procedure, I have a cursor that does insert into a table.
This insert has about 30 million records, which are inserted at a time fast and acceptable.

But when the following procedure is executed, which makes the update is rather delay. In a last execution took about 38 hours. And it has a single slider update, less than the first and run the cursor separately, it runs in less than one minute.

One detail is that when the procedures are being performed, and see that the second procedure is "locked" if I stop running, and execute analyze table, and only perform the second procedure again, it is executed typically taking about 1 or 2 hours to complete.

can someone help me with this problem?


texts: PL/pgSQL regclass quoting of table named like keyword

texts:
I want to create a new table name based on an existing one by appending a suffix to it. A Postgres 9.5 PL/pgSQL function gets the existing table name passed in as regclass type and returns the new name as a string. I am using format() to construct the new name, and would typically use the %I placeholder. This works as long as the passed in table name doesn't match any PL/pgSQL keyword. In this case, no matter what type component I choose (%I or %s), the quoting is wrong.

Consider the following function:

CREATE OR REPLACE FUNCTION make_name(old_name regclass)
RETURNS text
LANGUAGE plpgsql AS
$$
BEGIN
    RETURN format('%I_new', old_name);
END;
$$;

Further assume that there are two tables: treenode and overlay. Calling this function for both results in the following new names:

SELECT make_name('overlay'::regclass);
     make_name
-------------------
 """overlay"""_new
(1 row)

SELECT make_name('treenode'::regclass);
  make_name
--------------
 treenode_new
(1 row)

As it turns out overlay is also a PL/pgSQL function (just like format, but treenode is not), which seems to change quoting behavior. If %s is used with format(), the result would be "overlay"_new. The same happens when I use the || operator. If I use text as input parameter type, everything works as expected, but I would prefer using regclass.

Is there a way to format a string with a keyword-matching regclass table name (e.g. overlay) without quotes, just like it is the case for a non-keyword matching regclass table name (e.g. treenode)?


texts: MySQL 5.7 foreign keys constraint invalid

texts:
I have a database with around 150 tables, and many relations between them.

Now sometimes the foreign key relation of some tables becomes invalid. So while the foreign keys and indexes are set, and also set to cascade on update/delete; I get invalid indexes.

As example Table A has as row with a value in the foreign key relation 1. So Table B should have 1 as its primary key. Yet it's another value, usually 0 (not null).

Now I am not sure when this does happen, that is why I am asking here. I thought setting a foreign key, enforces the constraint to be valid. But apparently there must be some exception to it.

Things that I suppose could be the cause (I hope it's one of those, if these problems can happen during "normal" usage .. I will be scared):

rebuild / optimize table
export & import of the database through HeidiSQL or phpMyAdmin

The biggest problem with this is, that the database looks like it's running fine, as most queries work, only a few inserts/updates fail. So usually it gets by unnoticed, till some bug arises.

The actual questions:

Why does this happen?
How can I prevent this from happening?
If it can't be prevented, what are the "best practice" workarounds?

shop_award_product.shop_product_id is referenced with shop_product.id.

And well, shop_award_product.shop_product_id had 0 while shop_product.id was different 1 or 3.

CREATE TABLE `shop_award_product` (
    `shop_award_id` INT(11) UNSIGNED NOT NULL,
    `shop_product_id` INT(11) UNSIGNED NOT NULL,
    `sort_order` INT(11) UNSIGNED NOT NULL DEFAULT '1000',
    `created_at` DATETIME NOT NULL,
    `created_by` INT(11) UNSIGNED NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `updated_by` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`shop_award_id`, `shop_product_id`),
    INDEX `shop_product_id` (`shop_product_id`),
    CONSTRAINT `fk_shop_awardproduct$shop_product` FOREIGN KEY (`shop_product_id`) REFERENCES `shop_product` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `shop_award` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `website_id` INT(11) UNSIGNED NOT NULL,
    `created_at` DATETIME NOT NULL,
    `created_by` INT(11) UNSIGNED NOT NULL,
    `updated_at` DATETIME NOT NULL,
    `updated_by` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=4
;

Additional information:
MySQL version: 5.7.15-0ubuntu0.16.04.1

According to a comment, the problem has to be with import and disabling the check for foreign keys. But I am not sure yet that is the case, as I have always exported a db, and imported it again without actually modifying the SQL code. And the source DB is apparently ok, while the new DB has the problem (which I discover weeks later).

I am glad I got no response like "this stuff just happens from time to time".
But still have to figure out why it happens, I suppose it has to be when an export & import is being done, but I never edit the export. Still it's the only moment when FOREIGN_KEY_CHECKS is disabled.

No change of collation or character set during the import. I am not the only person able to edit the tables, but the chance that someone else changed that is very small. It has happened to me like 4 times in the past 6 months, and I have imported/exported about 100 db's in that time. This time, the tables involved and the project involved it is IMHO below 1% chance that someone else caused it.


texts: SQL Server 2008: use AVG() and an inner join

texts:
Here's what I have so far. I know this works, but I need to get the average of the pulse and depressionlevel instead of returning actual values for each row:

SELECT
  Patients.LastName
  , PatientVisit.Pulse AS [avg Pulse]
  , PatientVisit.DepressionLevel AS [avg Deplevel]
FROM Patients
    INNER JOIN PatientVisit on Patients.PatientKey = PatientVisit.PatientKey
ORDER BY
  Patients.LastName


texts: What's causing [ERROR] Invalid (old?) table or database name 'd:q'?

texts:
I've two entries in my MariaDB 10.5 error log file last night:
[ERROR] Invalid (old?) table or database name 'd:q'

I've looked at my nginx access log around that time and can't see any unusual entries.  I've searched for that error with the d:q bit and got no hits.  Anyone know what this could be?  Does it suggest my server has been exploited in some way...?
Thanks


texts: SQL Server 2014 - Disabled All Admin Users

texts:
I made the most ridiculous thing. I accidentally disabled both remote logins and database accesses for the Administrator accounts (including the one with Windows Authentication (Administrator account) and sa account on the production server! Please tell me a way that there is a way to access the server with administrator privileges! :(

Is it possible from an installation or some kind of command line etc?

Again, Administrator account IS disabled for SQL Server (but I have remote connection to server, the account itself is working...) So I literally cannot access to the sql instance with admin privileges.


texts: mysql update sed: -e expression #1, char 17: unknown option to `s'

texts:
When running the mysql rpm mysql-community-server-5.7.14-1.sles12.x86_64.rpm I get this error:

  sed: -e expression #1, char 17: unknown option to `s'

That far everything works but I noticed immediately after the update there were 712MB! less on the system disk and I suspect the clean up process did not work well because of the error.

My question:

Has anybody an idea how to fix the problem even it is a rpm file I download directly from MySQL?
Where can I remove manually the temporary files created while the update process?

Info:

Linux: SLES 12 SP1

MySQL: 5.7.14

Clarification:

I do not want to know what the problem means in the sed program. I do not have any access to the sed parameters [I did not write it; it is delivered by the MySQL organization] due to the fact it is ran by the rpm program controlled by the rpm file.


texts: SELECTing min, max (maybe length) of continuous id ranges in a table

texts:
I have a table foo in Postgres (10.3)

# select * from foo;
 id | cust  |  load_dt
----+-------+------------
  1 | cust1 |
  2 | cust1 |
  3 | cust1 |
  4 | cust2 | 2018-05-15
  5 | cust2 | 2018-05-15
  6 | cust1 |
  7 | cust1 |
  8 | cust3 | 2018-05-17
(8 rows)

from which I've been trying to get the start and end ids for each continuous range of ids:

 min | max | cust  |  load_dt
-----+-----+-------+------------
   1 |   3 | cust1 |
   4 |   5 | cust2 | 2018-05-15
   6 |   7 | cust1 |
   8 |   8 | cust3 | 2018-05-17

but all my tries have ended basically to this:

   1 |   7 | cust1 |
...

Length of each range would be a nice touch.

Test stuff:

create table foo (
  id serial,
  cust text,
  load_dt date
);

insert into foo(cust,load_dt)
values ('cust1',NULL),('cust1',NULL),('cust1',NULL),
       ('cust2','2018-05-15'::date),('cust2','2018-05-15'::date),
       ('cust1',NULL),('cust1',NULL),
       ('cust3','2018-05-17'::date);


texts:
How to set a FK value NULL upon receiving error for foreign key constraint?

Consider a simple table a

CREATE TABLE test
(
    id int(11) NOT NULL AUTO_INCREMENT
    ex_key int(11),
    FOREIGN KEY(ex_key) REFERENCES ex_table(ex_key) ON DELETE SET NULL,
    PRIMARY KEY (id)
)

If receiving FK error for an invalid ex_key

INSERT INTO test (id, ex_key) VALUES ('22', '22');

we can insert by setting NULL value for the foreign key

INSERT INTO test (id, ex_key) VALUES ('22', NULL);

It can be done by an IF statement, but is there a way to do so in the same query?

I hope to find a solution similar to available ON DUPLICATE KEY UPDATE for UNIQUE columns.


texts: I need to remove duplicate values SQL Left Join

texts:
My SQL code:

SELECT
users.id,
users.username,
job.category,
locations.location
FROM users
INNER JOIN job ON users.username = job.username
INNER JOIN locations ON job.username = locations.username

Results:

5   Don   Fashion/Design/Beauty         Mr
5   Don   Eng                           Mr
5   Don   Arts/Audio/Video Technology   Mr
5   Don   Fashion/Design/Beauty         Colombo
5   Don   Eng                           Colombo
5   Don   Arts/Audio/Video Technology   Colombo
5   Don   Fashion/Design/Beauty         gm
5   Don   Eng                           gm
5   Don   Arts/Audio/Video Technology   gm

I need to remove those 6 duplicate results in SQL to get the below results. Please kindly help me to figure out this.

5   Don   Fashion/Design/Beauty         Mr
5   Don   Eng                           Colombo
5   Don   Arts/Audio/Video Technology   gm


texts: I'm considering removing SQL Server Partitioning from a Database. Is there anything I should watch out for?

texts:
I am considering removing SQL Server partitioning from a Table. I believe that it was implemented for performance reasons, which is not required due to the low volume of data involved. The version of SQL Server involved makes this require Enterprise Edition (where Standard Edition would have been sufficient) and the Application cannot be upgraded.
Are there any specific Partition-related keywords that might be in use that would cause problems (other than those involved in partition management)?
I have always seen SQL Server Partitioning as being transparent to the user queries, but wanted to see if I missed anything.
Many thanks for any thoughts / suggestions.


texts:
I am confused what this autogrowth property does for any database or log file in SQL Server.

According to my understanding:

If we set autgrowth for database as restricted then its size will increase with data up to this limit and after stops increasing its size which will result in database failure.
If we set autgrowth for database as unrestricted then it will grow until disk space is full.
If we set autgrowth for log file as restricted then logs size will increase upto specified limit and then it will start discarding old logs and new logs will be there in logs.
If we set autgrowth for log file as unrestricted the then it will grow until disk space is full.

Please help me to know if this assumption is correct and if it is wrong then please correct me.


texts: Query takes long time to execute while there is an index configured

texts:
We have a table with 190M records. there is an index on a fairly unique column but still, if I run a query on the table selecting top 10 with only the indexed column in the where and select clause it still takes ages to complete.
If I turn on live query plan sql server shows the 'waiting for query plan" waiting spinner. If the query finally finishes and if I run it again it then completes instantly. Even if I run DBCC FREEPROCCACHE before So it feels like the creation of the query plan is taking all the time but then i cannot reproduce by running DBCC FREEPROCCACHE before the query. After a while the same issue will reoccur.

Note: The database has 2 filegroups. Indexes and system databases are stored on SSD disk while normal table data is stored on a non SSD disk.


texts: Postgresql - how to do backup/restore on a database level?

texts:
I'm a developer learning Postgresql and I'd like to know more about backups.
As long-time Sql Server user, I understand backup and restore usually is done on a database level.
So far every tool I see for Postresql (except pg_dump) - barman, wal-e, wal-g, pgbackrest, etc. - seems to work cluster-wide. How does that work?
If I have 20 databases inside my cluster and I deployed an application with a bug, how do I restore just that one broken DB? Is that possible?


texts: Migrate Jira to MySql - Unknown system variable 'storage_engine'

texts:
I'm migrating JIRA in Windows from HSQL to MySQL and I got stuck with  the message Unknown system variable 'storage_engine'. Though I created a backup, installed MySQL and copied J connector to libs of JIRA.

Then I set the system variable storage_engine = InnoDB and rebooted the machine but the message is still there.

Could you please point me to a good direction.


texts: Why TRUNC() date field range constraint is faster?

texts:
I have a fairly large (10s of millions rows) Oracle 10g table with a date field in it, which in a number of queries is constrained to a range like this:

...
AND date_field >= TO_DATE(:lowerDate, 'YYYY-MM-DD')
AND date_field < TO_DATE(:upperDate, 'YYYY-MM-DD')
...

This constraint is usually restricting the table content to between thousands and hundreds of thousands records. There are also indexes to support those queries as well which also include date_field and are used by Oracle according to EXPLAIN.

Then I realised that user input dates in that constraint never include time, so the variance of date_field can be cut down greatly by using TRUNC().

So I tried changing that constraint to this (without changing the index which still only lists the field and not the function):

...
AND TRUNC(date_field) >= TO_DATE(:lowerDate, 'YYYY-MM-DD')
AND TRUNC(date_field) < TO_DATE(:upperDate, 'YYYY-MM-DD')
...

When written like this, the query performs quicker and its cost in EXPLAIN is 3 times lower (varies between the exact queries, but a typical example is down from 12000 to 4000). The index is still shown as employed.

But when the index is also adjusted from including date_field to TRUNC(date_field), and this is paired with a constraint rewritten like above, the cost of the query goes down to about 200 (60 times lower!)

This is of course good news for me, but I don't understand why is that happening, since a range constraint should in my understanding make no difference. Regardless of whether its a date + time of just the date, the effort needed to tell whether a value is question is earlier or later than that point in time remains the same.

Clearly there is something I don't see yet, so what can that be? Could it be, for example, just a side effect of the index now being smaller and so more efficiently navigated or anything like this?


texts: Collation issue after update (KB3072779) of SQL Server 2012

texts:
If found the following error while starting my SQL Server 2012 instance, after the update KB3072779 performed by Windows Update (Windows Server 2012 Foundation).

Creating procedure sp_sqlagent_get_perf_counters...
2016-11-14 12:09:07.84 spid5s      Error: 468, Severity: 16, State: 9.
2016-11-14 12:09:07.84 spid5s      Cannot resolve the collation conflict between "Latin1_General_CS_AS" and "Latin1_General_CI_AS" in the equal to operation.

So now I'm unable to start the SQL Server instance. I managed to run it with -t902 and perform some queries.

I did check the following:

select serverproperty('collation')

which returned:

Latin1_General_CS_AS

also

select name, collation_name from sys.databases

which returned:

master  Latin1_General_CI_AS
tempdb  Latin1_General_CI_AS
model   Latin1_General_CI_AS
msdb    Latin1_General_CI_AS

From what I've read so far, system databases must have the same collation as the server. What I'm not aware of is what happened here and what should I do now.

Did this update change the server collation?

What can I do now to run my instance normally?

===========================

EDIT: I finally arrive to solve the problem by following the steps described in one of the links posted by @hot2use

https://www.mssqltips.com/sqlservertip/3519/changing-sql-server-collation-after-installation


texts: What's the best way to translate a website

texts:
I have a website in Django using MySQL, and I have more than 4.000 entries that I want to translate.

What's the best way to do it? Create a new database for every language? Create other table for every language? Or create more entries?

I want 6 or 8 languages so I thought it might be bad to have a table with many records.


texts: How to use / as sysdba in pluggable database?

texts:
I'm trying to make a export, usually I use userid=\"/ as sysdba\" syntax in Linux command line (I don't know sys password in my production environment.
How can I do this using pluggable database? I tried something like this: userid=\"/ as sysdba@mypdb_oracle_net\" but it does not work.
expdp userid=\"/ as sysdba@mypdb_oracle_net\" dumpfile=full_%U.exp logfile=full.log directory=teste full=y parallel=4 version=11.2.0

Export: Release 19.0.0.0.0 - Production on Tue Mar 10 11:18:06 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
UDE-00002: invalid username or password

Username:


texts: why locks a table will improve performance during transaction?

texts:
I just started to read about table lock in SQL (from this page)

It saids:

  table lock may reduce the overhead of acquiring a large number of row
  or page locks and save overall locking time

From my understanding it will prevent other query to access the table I have locked, but why will this improve the performance? Isn't it will slow down over roll queries when there is mutiple user trying to use the table?  Can someone explain little bit more?


texts: VARCHAR(MAX) taking more space than NVARCHAR(MAX)

texts:
I have a table in SQL Server 2012 Enterprise Edition that has ~100GB of data. Most of this data is attributed to a single NVARCHAR(MAX) column which has an average length of 4500 characters. It stores data compressed by an application that has no need for Unicode characters.

Since NVARCHAR takes two bytes per character and VARCHAR takes one, I surmised I could cut down on some storage by switching the table over. However, once I altered the column to be VARCHAR(MAX), the table ballooned to 155GB.

I am aware SQL may use Unicode Compression but the documentation states this does not apply to NVARCHAR(MAX) so it couldn't have been in use.

So why has my table grown so much in size?

Table:

CREATE TABLE [dbo].[Table](
    [RecordLocator] [char](6) NOT NULL,
    [CreateDate] [date] NOT NULL,
    [SystemId] [varchar](40) NOT NULL,
    [EventXML] [nvarchar](max) NULL, --Not XML, compressed XML as characters
    [UpdateUTCTimeStamp] [datetime2](7) NOT NULL,
    [DeleteDate] [date] NULL,
 CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED
(
    [RecordLocator] ASC,
    [CreateDate] ASC,
    [SystemId] ASC,
    [DeleteDate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 75) ON [PRIMARY]
) ON [PRIMARY]
GO

Additional notes:

Table design is not ideal, I am also working on getting that changed with the third party developers.
Table is partitioned on DeleteDate - also not ideal but required as of now to efficiently swap out old data
Table does not have row or page compression enabled
Table row growth before and after table alter is negligible
D'oh, I did run sp_spaceused and failed to add the output:

 name   rows        reserved        data            index_size  unused
 Table  11407899    160531008 KB    159465328 KB    525200 KB   540480 KB


texts: Index for inequality on multiple columns (col1 >= 'const_1' AND col2 <= 'const_2')

texts:
I have the following query:

SELECT * FROM plane_tracks
    WHERE created_at >= '2016-12-29 08:00:00' AND updated_at <= '2016-12-30 00:00:00';

I've created the following indexes:

create index plane_tracks_on_created_at on plane_tracks(created_at);
create index plane_tracks_on_updated_at on plane_tracks(updated_at);
create index plane_tracks_on_created_at_updated_at on plane_tracks(created_at,updated_at);
create index plane_tracks_on_created_at_updated_at_desc on plane_tracks(created_at,updated_at DESC);

Yet when I run the query, it scans all elements in plane_tracks.

# EXPLAIN ANALYZE SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00' AND updated_at <= '2016-12-30 00:00:00';
                                                                      QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on plane_tracks  (cost=0.00..582.78 rows=5425 width=471) (actual time=1.507..3.435 rows=1978 loops=1)
   Filter: ((created_at >= '2016-12-29 08:00:00'::timestamp without time zone) AND (updated_at <= '2016-12-30 00:00:00'::timestamp without time zone))
   Rows Removed by Filter: 15941
 Planning time: 0.366 ms
 Execution time: 3.618 ms
(5 rows)

When I drop the updated_at part, like this:

SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00';

Then I get this:

# EXPLAIN ANALYZE SELECT * FROM plane_tracks WHERE created_at >= '2016-12-29 08:00:00';
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Seq Scan on plane_tracks  (cost=0.00..537.99 rows=8595 width=471) (actual time=0.022..3.261 rows=8593 loops=1)
   Filter: (created_at >= '2016-12-29 08:00:00'::timestamp without time zone)
   Rows Removed by Filter: 9326
 Planning time: 0.093 ms
 Execution time: 3.901 ms
(5 rows)

At least this last part makes sense.

But I don't understand why

the first query isn't at least as efficient as the second one.
why first query isn't much better than the second one, given that there are also indexes on updated_at.
what index to create to make it really efficient

Tom


texts: How to hot merge MariaDb databases from multiple servers into a single one

texts:
Current Configuration
I currently have 3 servers:

server australia with the database canberra
servers belgium and germany are a galera cluster with the database bruxelles.

Target Configuration
My final goal is to merge both databases into a single galera cluster:

servers australia, belgium and germany will be a galera cluster with the databases canberra and bruxelles.

Constraints and leeways
australia, belgium and germany are public production servers. They all handle read and write queries from various clients 24/7 (SELECT, INSERT, UPDATE, DELETE), so I cannot allow neither downtime nor read-only for any of them.
canberra and bruxelles schemas are obviously distinct, so there is no risk of collision when merging.
canberra and bruxelles schemas are stable (no ALTER), so I can copy the schemas from one server to another at any time, without any need to keep the schemas in sync.
The servers are adressed by clients through DNS aliases :

australia is behind pacific.example.com
belgium and germany are behind europe.example.com (round-robin handled by HAProxy).

It is easy to add or change DNS aliases (eg. edit europe.example.com to include australia in the round-robin, or to exclude germany).
I am not sure that the galera information is relevant for this particular issue, so any answer working for 2 servers (let's say australia and belgium) should be easily translatable to my issue.
Naive approach
My plan was the following:

Initiate a replication where australia is the master and belgium is the slave.
Have pacific.example.com target germany instead of australia (no more write on the master, all writes on the slave)
Shut down australia
Overwrite australia as a new galera cluster member behind europe.example.com.
Reboot belgium removing slave config
Have pacific.example.com target the galera cluster.

However, I found myself unable to initiate the replication. Trying to make belgium a slave of australia results in the destrution of bruxelles database.
Question
How would you do it?


texts:
I'm a newbie to PL/pgSQL ... I use Postgres 9.5.0, and need to update a column every time a new record is inserted. The column shall be filled in from the values entered in area_pol and area_ofi.

I'm trying to create this function to suit my case:

CREATE OR REPLACE FUNCTION sch_cap.fc_atualiza_dif_area()
  RETURNS trigger AS
$$
BEGIN
    UPDATE
        sch_cap.tbl_cap
    SET
        dif_area = abs(100 - (tbl_cap.area_pol / (tbl_cap.area_ofi * 100)));
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER tg_atualiza_dif_area
  BEFORE INSERT OR UPDATE ON sch_cap.tbl_cap
  FOR EACH ROW EXECUTE PROCEDURE sch_cap.fc_atualiza_dif_area();

But when I try to insert a record, the following error appears:

  ERROR: stack depth limit exceeded
  HINT: Increase the configuration parameter "max_stack_depth" (currently 2048kB), after ensuring the platform's stack depth limit is adequate.

How to get it right?


texts:
CSV format cannot be used for importing text files to a database unless the table for the data is already defined in the database.

is this true or false?


texts:
I want to update my my.cnf without restarting mysql, is it possible?

In fact I just want to set lower_case_table_names = 1, but I do not have the permission to restart MySQL.


texts: How to monitor activity in SSMS only with T-SQL?

texts:
I use the following statement to monitor activity in SQL Server (This is out of some book)

SELECT
    des.session_id ,
    des.status ,
    des.login_name ,
    des.[HOST_NAME] ,
    der.blocking_session_id ,
    DB_NAME(der.database_id) AS database_name ,
    der.command ,
    des.cpu_time ,
    des.reads ,
    des.writes ,
    dec.last_write ,
    des.[program_name] ,
    der.wait_type ,
    der.wait_time ,
    der.last_wait_type ,
    der.wait_resource ,
    CASE des.transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommitted'
        WHEN 2 THEN 'ReadCommitted'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot'
    END AS transaction_isolation_level ,
    OBJECT_NAME(dest.objectid, der.database_id) AS OBJECT_NAME ,
    SUBSTRING(dest.text, der.statement_start_offset / 2,
    ( CASE WHEN der.statement_end_offset = -1
    THEN DATALENGTH(dest.text) ELSE der.statement_end_offset
    END - der.statement_start_offset ) / 2 + 1)
    AS [executing statement] ,
    deqp.query_plan
FROM sys.dm_exec_sessions des
LEFT JOIN sys.dm_exec_requests der
ON des.session_id = der.session_id
LEFT JOIN sys.dm_exec_connections dec
ON des.session_id = dec.session_id
CROSS APPLY sys.dm_exec_sql_text(der.sql_handle) dest
CROSS APPLY sys.dm_exec_query_plan(der.plan_handle) deqp
WHERE des.session_id <> @@SPID
ORDER BY 3

Some activities are not shown here: create index, alter table...

How to monitor all activities in SQL Server? (Activity Monitor/Profiler is no option - only plain T-SQL, no sysadmin - only view server state)


texts:
I'm working in an access database with the following fields (and others):

Asset; Asset_Date; Start_Date; Start_Time; Office_Stop.

Asset is a # field, Asset_Date is text; Start_Date is Date/Time; Start_Time is Date/Time; and Office_Stop is # (1 or 0).

Asset numbers are unique vehicle identifiers and all of the data are from GPS software. Using the GPS data and ArcGIS, I added the binary "Office_Stop" so I could tell whether each point was an "Office_Stop" or not.

I would like to know where each truck started (Office_Start = 1 or 0).

Logically, the start would be where the truck stopped last. I know I can export to excel, do a (B2=A1) type formula and move on. The problem is I have to import new data on a regular basis and I want to avoid multiple excel files and imports/exports in Access - I have too many working files as it is.

Is there a way to have access query the "previous record" for each asset?

Thanks!

Ps.  I can read some SQL, but I'm really not great writing it.


texts: MySQL trigger after insert with insert and delete instructions does not work properly

texts:
I have the following trigger created in phpMyAdmin:

Table: tb_agenda
 Time: AFTER
 Event: INSERT

BEGIN
    INSERT INTO tb_realizacao (dt_agenda,
                               titulo,
                               titulo_en,
                               descricao,
                               descricao_en,
                               dt_cadastro)
        SELECT  dt_agenda,
                titulo,
                titulo_en,
                descricao,
                descricao_en,
                dt_cadastro
        FROM tb_agenda
        WHERE dt_agenda < NOW();

    DELETE FROM tb_agenda
     WHERE dt_agenda < NOW();
END

The trigger works 50%. The INSERT instruction works perfectly but the DELETE one doesn't execute. I tried to execute it in the SQL panel and it works fine.


texts: How can I determine the maximum possible number of threads?

texts:
What factors determine the maximum possible number of threads mysqld will create?

I only care about consideration for mysql and not limitations that may be imposed by the operating system. Further, I only care about Linux, though if you want to include information specific to Windows, feel free.

As far as I can tell, threads can be created for replication, aspects of innodb, one per client connection up to max_connections, with an additional +1 to handle super connections. What else is there, or is that it?

threads_max =   max_connections
              + 1 for super
              + 2 for replication slave threads
              + ? for InnoDB ?


texts: Teradata 15: Failed [5315 : HY000] An owner referenced by user does not have SELECT WITH GRANT OPTION access to DBC.RoleMembersV.RoleName

texts:
I am using Teradata 15.  Using a powerful database user sysdba, I created the following view:

REPLACE VIEW DBA_TABLES_TEST.ROLE_ALL_APP_USERS AS
SELECT   A.ROLENAME
   FROM  DBC.ROLEMEMBERSV A
   WHERE A.ROLENAME LIKE 'TG%';

I got the following errors:

Executed as Single statement.  Failed [5315 : HY000] An owner referenced by user does not have SELECT WITH GRANT OPTION access to DBC.RoleMembersV.RoleName.
Elapsed time = 00:00:00.015

STATEMENT 1: Select Statement failed.

Then I used DBC to grant sysdba the select right, but try the view again, it did not work:

GRANT SELECT ON DBC TO SYSDBA WITH GRANT OPTION;

Any idea how can I grant the correct rights to sysdba?

Thank you so much!


texts:
I have a data set and an example of the field is below. The values of these fields is "Yes" or "No". I would like to replace the value of "Yes" with 1 and the value of "No" with 0.
This file will be exported from Sharepoint each month and at the moment it is 50,000 rows and will increase each month. I have 48 fields which I would like to do the replace of "Yes" and "No".
Example of the field
t1.'Domestic Violence'n,

I'm using the below to accomplish what I'd like, but was hoping there is a better way to do this. Ideally, I don't want to create another column.
I am using SAS. For example:
 t1.'Domestic Violence'n as DomesticViolence,
      case when(DomesticViolence='Yes') then 1
        else 0
        end as Domestic_Violence,

I would like a more efficient way (less coding). With the above I'll have a column called 'DomesticViolence' (values of "Yes" and "No") and  a column called 'Domestic_Violence'. Ideally I'd only want the column called 'Domestic_Violence'.


texts:
PostgreSQL supports the DOMAIN specification, from SQL 2011 working draft spec,

  A domain is a named user-defined object that can be specified as an alternative to a data type in certain places where a data type can be specified. A domain consists of a data type, possibly a default option, and zero or more (domain) constraints.

This allows us to do really cool stuff like implement a domain for the HTML5-spec for email over a case-insensitive text type. It ensures that all clients accessing the database have an integrity check on the data inserted.

CREATE DOMAIN email AS citext
  CHECK ( value ~ '^[a-zA-Z0-9.!#$%&''*+/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*$' );

Does SQL Server support any such functionality outside of the trigger system?


texts:
I googled and found this old web page, which said just to change the name of the directory.  I tried it but it didn't work:

$ pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
8.4     main      5432 down   postgres /var/lib/postgresql/8.4/main       /var/log/postgresql/postgresql-8.4-main.log
9.1     main      50420 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log$ pg_ctlcluster 9.1 main stop

$ pwd
/var/lib/postgresql/9.1

$ ls
main

$ mv main oldmain

$ pg_ctlcluster 9.1 oldmain start
Error: specified cluster does not exist

PostgreSQL 9.1.4, Ubuntu 11.10


texts:
Just installed a new 24 core server and it is noticeably slower when doing truncates and drops.  Example:

$ mysql -u root lmcdental_test2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.6.23-72.1 Percona Server (GPL), Release 72.1, Revision 0503478

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop database lmcdental_test6;
Query OK, 66 rows affected (6.65 sec)

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000022 |
| checking permissions | 0.000548 |
| Opening tables       | 0.000017 |
| System lock          | 6.651106 |
| Opening tables       | 0.000061 |
| System lock          | 0.000041 |
| Opening tables       | 0.000571 |
| System lock          | 0.000668 |
| query end            | 0.000009 |
| closing tables       | 0.000044 |
| freeing items        | 0.000025 |
| cleaning up          | 0.000043 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)

Please notice the "System lock".  This is running Ubuntu 14.04LTS.

The same database on a 16 core system is almost instantaneous.  Any ideas on how to mitigate the 6.65 second system lock?

Thank you.


texts:
I've abc.sql file exported from MySQL Table.
This abc.sql file is 16 GB in size with around 28 million insert statements.

I'm looking for option to store it in SQL Server Database.

Couple of questions:

How to open 16GB file?
Is it possible to load 28 Millions Insert statements from MySQL to SQL Server?


texts: SQL Server Deadlock on two updates due to index lock order

texts:
I have two UPDATEs - one locks the CI first and then the NCI (on status) because the status column is also being updated.  The other already owns a U lock on the NCI because it knows it is changing and then tries to get a U lock on the CI.

What is the easiest way to force these to serialize?  It seems odd to use a TABLE-level hint since this is an internal indexing issue - there is only one table involved - will UPDLOCK, HOLDLOCK automatically just apply to all indexes needed on that table and thereby force it to be serialized?

Here are the queries:

UPDATE htt_action_log
SET status = 'ABORTED', CLOSED = GETUTCDATE()
WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
AND status = 'OPEN';

That one X locks the row in the CI (on CREATED column) and then attempts to X lock on the NCI which includes the status column.

UPDATE htt_action_log
SET status = 'RUNNING {36082BCD-EB52-4358-E3D3-4D96FD5B9F0F} 1360094342'
WHERE action_uuid = (SELECT TOP 1 action_uuid
                     FROM htt_action_log
                     WHERE transition_uuid = '{F53ADDDA-E46B-4726-66D8-D7B640B66597}'
                         AND status = 'OPEN'
                     ORDER BY action_seq)

This one U locks the same NCI - for the nested query I guess, then goes to lock the CI for the update.

Thus the order produces the deadlock.

Easiest solution is to force the two queries to completely block - i.e. serialize.  What's the easiest way to force that, just put WITH (UPDLOCK, HOLDLOCK) on the references to the table (one in the first and two in the second)?

DDL:

Note client has more indexes on this table which should be affected by this update, but are not mentioned in the deadlock graph.

CREATE TABLE [dbo].[HTT_ACTION_LOG](
    [ACTION_UUID] [varchar](128) NOT NULL,
    [TRANSITION_UUID] [varchar](128) NOT NULL,
    [STATUS] [varchar](128) NOT NULL,
    [CREATED] [datetime] NOT NULL,
    [CLOSED] [datetime] NULL,
    [ACTION_SEQ] [int] NOT NULL,
    [ACTION_TYPE] [varchar](15) NOT NULL,
    [ACTION_NAME] [varchar](50) NOT NULL,
    [ACTION_RESULT] [varchar](8000) NULL,
    [PENDING_SINCE] [datetime] NULL,
    [ACTION_SQL] [varchar](8000) NULL,
    [ERROR_OK] [int] NULL,
    [ERROR_COND] [varchar](2048) NULL,
    [RETRY] [varchar](128) NULL,
 CONSTRAINT [PK_HTT_ACTION_LOG_1] UNIQUE NONCLUSTERED
(
    [ACTION_UUID] ASC
)
)

CREATE CLUSTERED INDEX [IK_HTT_ACTION_LOG_2] ON [dbo].[HTT_ACTION_LOG]
(
    [CREATED] ASC
)

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_1] ON [dbo].[HTT_ACTION_LOG]
(
    [TRANSITION_UUID] ASC,
    [STATUS] ASC
)
INCLUDE ( [ACTION_UUID],
[ACTION_SEQ])

CREATE NONCLUSTERED INDEX [IK_HTT_ACTION_LOG_4] ON [dbo].[HTT_ACTION_LOG]
(
    [ACTION_UUID] ASC,
    [STATUS] ASC
)

CREATE NONCLUSTERED INDEX [missing_index_11438530_11438529_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG]
(
    [TRANSITION_UUID] ASC,
    [ACTION_TYPE] ASC
)
INCLUDE ( [ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_7207590_7207589_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG]
(
    [STATUS] ASC
)
INCLUDE ( [CREATED],
[PENDING_SINCE],
[ACTION_NAME])

CREATE NONCLUSTERED INDEX [missing_index_8535421_8535420_HTT_ACTION_LOG] ON [dbo].[HTT_ACTION_LOG]
(
    [TRANSITION_UUID] ASC
)
INCLUDE ( [ACTION_UUID],
[STATUS])

ALTER TABLE [dbo].[HTT_ACTION_LOG] SET (LOCK_ESCALATION = AUTO)

ALTER TABLE [dbo].[HTT_ACTION_LOG]  WITH CHECK ADD  CONSTRAINT [FK_HTT_ACTION_LOG_1] FOREIGN KEY([TRANSITION_UUID])
REFERENCES [dbo].[HTT_TRANSITION_LOG] ([TRANSITION_UUID])

ALTER TABLE [dbo].[HTT_ACTION_LOG] CHECK CONSTRAINT [FK_HTT_ACTION_LOG_1]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ('OPEN') FOR [STATUS]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT (getutcdate()) FOR [CREATED]

ALTER TABLE [dbo].[HTT_ACTION_LOG] ADD  DEFAULT ((0)) FOR [ERROR_OK]


texts: How to Block normal user access to mysql

texts:
user can access mysql by command mysql.
I want to change it so that only root users can run mysql.

ex) mysql // denied
ex) mysql -u root // access


texts: Postgresql: Aggregate objects into an (json) array (subquery issues)

texts:
I'm sorry for the vague title, but I simply don't know the right words to describe this.

I have this query that transforms a bunch of columns into an object that works just fine:

SELECT row_to_json(t)
FROM (
  SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t

However, I want to group the objects that fall within a certain category into an array. This category is defined by a fourth column inside my table named "cargoProductId". The array should have the value of "cargoProductId" as the key. So:

"961":[
{"type":"Feature",....
{"type":"Feature",....
{"type":"Feature",....
],
"962":[
.....
]

So I've been struggling with this for the last 1 1/2 hours or so. I really have no clue how to do this. This is what I have right now:

SELECT array_agg(row_to_json(t))
FROM (
  SELECT type, properties, geometry FROM "bgbCargoMinardJSON"
) t) FROM "bgbCargoMinardJSON" GROUP BY "carProductId"


texts:
Edit 1:
From article posted by first answer > this:

  Additionally, if you are using memory optimized table in the context of table valued parameters repeatedly, you may encounter this error even you use them in a separate batch and memory gets released.  We are looking to address this issue in future cumulative updates.  For now, you either need to use enterprise edition or periodically restart SQL or offline/online the database.

I use in-memory table valued parameters continually.  However as a troubleshooting step, I re-deployed the solution and removed all memory optimization.  I still received this error message.  After restarting the service the message is gone and I'm able to use in-memory tables once again.

Original Post:

I'm receiving this message (Sql Server Standard 2016 sp1 on VM):

  Could not perform the operation because the database has reached its quota for in-memory tables.

However, in-memory table usage is zero as shown here.  Any idea how to recover from this state or even start to investigate what is going on?


texts: How to use db2look to get the ddl of sequence?

texts:
I was wondering how to use db2look to get the ddls of sequence.

db2look -d my_db_name -z my_proc_schema - I tried this but it is not showing any results.

Is there an option to directly mention the sequence name ?


texts: Oracle big datafile restore fails using Netbackup (ORA-19511, ORA-27190)

texts:
I am restoring datafiles about 1000GB in size. I am using Netbackup as a backup server.

After 2 hours reading Netbackup fails with the following error:

7/25/2012 3:27:50 PM - Error bpbrm(pid=1176) socket read failed, An existing connection was forcibly closed by the remote host.  (10054)
7/25/2012 3:27:50 PM - restored image r2n1_1342889172 - (file read failed(13)); restore time 02:01:41
7/25/2012 3:27:54 PM - end Restore; elapsed time: 02:01:50
the restore failed to recover the requested files(5)

RMAN shows the following error:

channel ch1_tape: ORA-27192: skgfcls: sbtclose2 returned error - failed to close file
ORA-19511: Error received from media manager layer, error text:
   VxBSAEndTxn: Failed with error:
   The sequence of calls is incorrect.
ORA-19870: error while restoring backup piece
ORA-19501: read error on file , block number  (block size=512)
ORA-27190: skgfrd: sbtread2 returned error

I looked in  ...\NetBackup\logs\bpbrm and found the following:

15:03:03.132 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:03:11.681 [660.7908] <2> bpbrm read_media_msg: read from media manager: MEDIA NOT READY
15:03:11.681 [660.7908] <2> bpbrm signal_bpbrm_child: sending Media Ready to bpbrm child 1176
15:03:11.681 [660.7908] <2> bpbrm read_media_msg: read from media manager: EXIT r2n1_1342889172 0
15:03:11.681 [660.7908] <2> bpbrm process_media_msg: media manager for backup id r2n1_1342889172 exited with status 0: the requested operation was successfully completed
15:03:11.681 [660.7908] <2> bpbrm signal_bpbrm_child: sending Normal Exit to bpbrm child 1176
15:03:13.787 [1176.2356] <2> bpbrm mm_sig: received not ready signal from media manager
15:08:12.044 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 20

15:08:12.106 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:13:13.061 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 21

15:13:13.186 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:18:14.001 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 22

15:18:14.063 [660.7908] <2> bpbrm read_parent_msg: read from parent ACK_KEEP_ALIVE
15:23:15.018 [660.7908] <2> bpbrm send_parent_msg: KEEP_ALIVE 23

............

bpbrm send_parent_msg: CURRENT POSITION C047L4 8
15:26:58.145 [5412.7124] <2> bpbrm read_parent_msg: read from parent CONTINUE RESTORE
15:26:58.145 [5412.7124] <2> bpbrm tell_mm: sending media manager msg: CONTINUE RESTORE
15:26:59.081 [5412.7124] <2> bpbrm read_media_msg: read from media manager: MEDIA READY
15:26:59.081 [5412.7124] <2> bpbrm signal_bpbrm_child: sending Media Ready to bpbrm child 7468
15:27:01.343 [7468.4308] <2> bpbrm mm_sig: received ready signal from media manager
15:27:06.070 [7468.4308] <2> bpbrm handle_restore: from client r2n1t.justice.gov.ge: change timeout to 10800
15:27:50.234 [1176.2356] <16> bpbrm readline: socket read failed, An existing connection was forcibly closed by the remote host.  (10054)
15:27:50.265 [660.7908] <2> bpbrm brm_child_done: child done, status 13
15:27:50.265 [660.7908] <2> bpbrm brm_child_done: child 1176 exited with status 13: file read failed
15:27:50.265 [660.7908] <2> bpbrm send_status_to_parent: EXIT r2n1_1342889172 13 sent to parent process.
15:27:50.327 [660.7908] <2> bpbrm read_parent_msg: read from parent TERMINATE
15:27:50.327 [660.7908] <2> bpbrm tell_mm: sending media manager msg: TERMINATE

Note: I am able to successfully restore a small datafile size about 1.2GB.

Please if you have such kind of experience help me.
The issue is about restoring big files using Netbackup.


texts: Assistance with: SQL Server detected a logical consistency-based I/O error

texts:
I'm having a big problem after querying 1 record table, with the following
error information:

Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: invalid protection option. It occurred during a read of page (1:40607) in database ID 19 at offset 0x00000013d3e000 in file 'D:\database\20001_30082019.mdf'.  Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Can anyone help me?


texts: Best way to explain the importance of Raid 10 to SysAdmins

texts: Hi I'm trying to impress upon a group of administrators as to why the extra money should be spent on raid 10 vs say raid 5.  Other then just pointing at MS Best Practices does anyone know of any good testing documentation to illustrate the point ?


texts:
Is there a way to compare a single byte value to a bytea field in PostgreSQL?

For example: I have a bytea field with the value \x11AA22BB. The comparison to a byte value of AA should be true while the comparison to a byte value of 1A should be false.

The only way I see is to extract each byte from the bytea column separately with something like get_byte(string, offset) and check each one.


texts: Unique constraint on multiple nullable columns Sql Server

texts:
We have a PriceCombo table with the following columns: id (not null), a_id(null),b_id(null),c_id(null), discount(not null).
We are using this table to figure out which price to assign various combos of merchandize.

a_id = id of region
b_id = id of merchandize category
c_id = id of customer category

so basically, we always know a, b and c, but we have a flexible discounting scheme such that every sale is discounted by the most specific rule we can find.

Our problem is our inability to guard against duplicates in the database, we would ideally want to guard against two rows having the same values in a_id, b_id AND c_id, for instance

a_id=1, b_id=1, c_id=1    and
a_id=1, b_id=1, c_id=1

are not to be allowed and

a_id=1, b_id=1, c_id=null   and
a_id=1, b_id=1, c_id=null

are not to be allowed  but

a_id=1, b_id=1, c_id=null  and
a_id=1, b_id=null, c_id=null

ARE to be allowed as these are not identical in respects to these 3 columns.

I have not found an answer anywhere to how to create a unique constraint that BOTH spans multiple columns AND allows nulls BUT disallows "identical" (at least in respect to the columns specified in the constraint) rows


texts: Really slow join, limit and order_by query

texts:
I've got these two tables:

books (about 2 million records in total)

Containing a unique ID (isbn) aswell as an integer value representing popularity (index, but not unique).

category_pointers (also about 2 million records)

Containing a category_id (representing "fiction" [example]) and book_id.

Now, I want to fetch all books in the category "fiction" (category_id 22) and order them by popularity. This is my query:

SELECT * FROM books JOIN (
  SELECT category_pointers.isbn FROM category_pointers
  RIGHT JOIN books ON books.isbn = category_pointers.isbn
  WHERE category_pointers.category_id = 22
  ORDER  BY books.popularity DESC
  LIMIT  0, 30
) AS t ON t.isbn = books.isbn

The query takes about 2 minutes to run. Does anybody know how to point me in the right direction?

Thanks!

UPDATE
Definition for books

CREATE TABLE `books` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `isbn` bigint(20) NOT NULL,
  `title` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `published` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `pages` int(11) NOT NULL,
  `weight` double(8,2) NOT NULL,
  `type` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `added_to_index` int(11) NOT NULL DEFAULT '0',
  `last_refine` int(11) NOT NULL DEFAULT '0',
  `sub_title` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `author` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `author_id` int(11) NOT NULL,
  `publisher` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `publisher_id` int(11) NOT NULL,
  `language` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `details_url` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `image` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `popularity` int(11) NOT NULL,
  `update_authority` int(11) NOT NULL DEFAULT '0',
  `edition` int(11) NOT NULL DEFAULT '0',
  `format` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `band_type` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `saved_image` varchar(255) COLLATE utf8_swedish_ci NOT NULL,
  `has_downloaded_image` int(11) NOT NULL DEFAULT '0',
  `published_year` int(11) NOT NULL DEFAULT '0',
  `last_price_check` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `books_isbn_unique` (`isbn`),
  KEY `books_popularity_index` (`popularity`),
  KEY `books_added_to_index_index` (`added_to_index`),
  KEY `books_published_year_index` (`published_year`),
  KEY `books_last_price_check_index` (`last_price_check`)
) ENGINE=InnoDB AUTO_INCREMENT=1748944 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci

And category_pointers

CREATE TABLE `category_pointers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `isbn` bigint(20) NOT NULL,
  `category_id` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`),
  KEY `category_pointers_isbn_index` (`isbn`),
  KEY `category_pointers_category_id_index` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1866535 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci


texts: SQL Server: Simple database restore with overwrite

texts:
What I'm trying to do is backup one of our production databases and restore them as our dev database (overwriting what's been there).

That's how I back up:

backup database [authfx] to disk = N'f:\db_backups\authfx\authfx-latest.bak'
  with
    noformat,
    init,
    name = N'authfx Latest Full Database Backup',
    skip,
    norewind,
    nounload,
    stats = 1;

Now when I try to restore other database using

restore database [dev-authfx] from disk = N'f:\db_backups\authfx\authfx-latest.bak'
  with
    file = 1,
    nounload,
    replace,
    stats = 1;

it barks all over the place:

Msg 1834, Level 16, State 1, Line 10
The file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf' cannot be overwritten.  It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 10
The file 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf' cannot be overwritten.  It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx_log' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 10
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.

I understand that I need to add with move and specify where original data and log files go for the dev database, but is there any way I can omit this and just say: restore this database from this other backup, do not touch anything else and overwrite what's been here previously.


texts: Insert row wise different images in SSRS

texts:
I am working on SSRS in witch i have data set with [First, Last, Email and Image]
image contains name of image and when ever i use this image i have to put www.something.com/images/ at start of this field

Now I am using this in SSRS something like this

Is it possible that what i am doing if yes some one kindly help

I am using following expression in first column

="www.something.com/images/"+Fields!Image_Path.Value

FYP: I am using SQL server 2012 and Visual Studio 2013

What I am trying to ask: I have image name in database and i want to insert that image in report something like here is my name Taimur and i want to insert image of taimur in first field the problem is, i have only image name in database and when ever i have to use that image i have to put www.something.com/images before image name because i have physically existed images on that path
Hopefully you understand my question


texts:
I'm trying to design an app where users take a new survey everyday (that's not the main part of app but it's an important part). I've never designed a database before so what I'm trying to do might not make any sense.

I'm using MySQL and I have a table for user information (user_id, name, password, etc). What I plan on doing is making a new table for each new survey and linking it with the user_id. So basically, I would have one table having a relationship with hundreds of other tables.

Is that how it would work? I thought about adding a column to the user table for every survey but I read that it's bad design to add columns often.


texts: How to estimate oracle tablespace I would require before loading the data?

texts:
I have huge files ranging from 450 to 750 million rows, that I need to load into our Oracle environment, and DBA asked me to give an estimate on how much tablespace I would need.
How to estimate the required tablespace before loading the data into a table?


texts: Use sp_Blitz to gather information on all servers in a centralized server/database

texts:
I am using sp_Blitz, and want to use it to execute the checks on all of my SQL Servers and have it write the results to a single table.

Do I need to create linked servers on the central instance to connect to the other instances, or is there some other way to input the server names into the sp_Blitz stored procedure?


texts: Our sysadmins keep pushing us to use data domain and DD boost for taking our SQL Backups.  Has anyone successfully used this solution?  I know what Brent Ozar says about Data domain and sql backups. But apparently the DD boost makes it a better solution.  Has anyone used DD boost and also used SQL Backup by redgate? Just wanted to get thoughts on what you would say to your sysadmins and managers apart from this article from Brent Ozar.


texts: How to filter out sales to find customers who have not purchased in the last 2 years

texts:
What I am trying to accomplish is to find all the customers who have sales in the past but have not purchased from us in the last 2 years (SYSDATE - INTERVAL '2' YEAR)

Here is my code currently.

select invc.company
 , invc.customer_id
 , to_char(invc.invdate, 'mm/dd/yyyy') as date
 , invc.name
 , invc.address1
 , invc.address2
 , invc.city
 , invc.state
 , invc.zip_code
 , invc.country
 , invc.region_code
 , invc.site
 , sum(invc.sales) as SlsAmnt
from ifsinfo.hb_invoicing_all invc
where **THIS IS THE PART IM STUCK AT**
group by invc.company
           , invc.customer_id
           , invc.creation_date
           , invc.name
           , invc.address1
           , invc.address2
           , invc.city
           , invc.state
           , invc.zip_code
           , invc.country
           , invc.region_code
           , invc.site

I am basically looking for a query that will return a list of customers who haven't purchased from us in 2 years or more.

SUM(SALES) = 0 within last 2 years+


texts:
I have a SQL Server database that has a pretty important table (join wise) that contains just over 2.1 million rows. For the VARCHAR column that defines the clustered index, 17.5k rows contain unique values, the remaining rows all contain exactly the same non null value. There are some queries that start at this table restricting by this column, expecting to get just one row, so that probably helps performance, but for many queries we are accessing via other fields and nearly always joining by a unique, non nullable (BIGINT) primary key (which is obviously non clustered).

I guess my question is, in light of anything else is it a no-brainer to change the clustered index to be the primary key (in light of there not being a more suitable candidate). Is there any check that I could do to try and predict the outcome (spinning up an exact replica and testing in a real world way is not an option)

I know that any changes I make will require a table rebuild (if I'm changing the clustered key)


texts:
I'm doing a little project and I need to do a plsql to return many rows.

I have someting like:

Table(id, data1, data2, from)

And the user gives me the value of from and I have to return all that.

The SQL is easy:

SELECT * FROM Table WHERE from = dataIntroduced

But, I don't know how the plsql can return that.

I read about collections but I think is not what I need


texts:
I have this query in SQL Server 2008:

declare @nro_credito int
declare @nro_tipo_CtaC int
set @nro_credito = 0
set @nro_tipo_CtaC = 0
declare @pago money

  select @pago = isnull(sum(importe_pago), 0)
    from credito_cuotas_items cci
      join credito_cuotas_pagos ccp on cci.nro_cuota_item = ccp.nro_cuota_item
      join descuentos_sueldo ds on ccp.numero = ds.numero
        where nro_credito = @nro_credito and nro_tipo_ctac = @nro_tipo_CtaC

This is the query plan: https://www.brentozar.com/pastetheplan/?id=SJulvE_GM  (cost 23)

But this other query has a plan with cost 0.0099

 declare @pago money

  select @pago = isnull(sum(importe_pago), 0)
    from credito_cuotas_items cci
      join credito_cuotas_pagos ccp on cci.nro_cuota_item = ccp.nro_cuota_item
      join descuentos_sueldo ds on ccp.numero = ds.numero
        where nro_credito =0 and nro_tipo_ctac = 0

This is the query plan: https://www.brentozar.com/pastetheplan/?id=HkcAv4OMM

Why so much difference in the cost??

Thanks.


texts: Changing database parameters after removing a node from RAC oracle

texts:
Oracle 11.2.0.3 on SunOS 5.1

i have a client who is running a 2 node RAC environment but has shutdown the instance on one of the nodes. I was wondering whether after doing this they should change the database parameters on the remaining instance to reflect this, in particular i am thinking of parameters such as parallel..., cluster_database, cluster_database_instances, gcs_server_processes etc.

also, if they don't remove the node from the cluster services and grid control, is there a performance overhead? is just shutting down an instance a bad idea when it comes to performance if it is not removed correctly from the cluster?

thanks
Dave


texts: Do MySQL (5.6) triggers support 4 byte emojis?

texts:
I'm using MySQL 5.6.34 with the following settings:

"character_set_client","utf8mb4"
"character_set_connection","utf8mb4"
"character_set_database","utf8mb4"
"character_set_filesystem","utf8mb4"
"character_set_results","utf8mb4"
"character_set_server","utf8mb4"
"character_set_system","utf8"
"collation_connection","utf8mb4_general_ci"
"collation_database","utf8mb4_unicode_ci"
"collation_server","utf8mb4_unicode_ci"

3 byte emojis seem to work correctly within a trigger but 4 byte do not. They just become a '?'. Is there a different setting that should be used or is this just not supported within a trigger?


texts: How to get response from stored procedure before it finishes?

texts:
I need to return partial result (as simple select) from a Stored procedure before it is finished.

Is it possible to do that?

If yes, how to do that?

If not, any workaround?

EDIT: I have several parts of the procedure. In the first part I calculate several string. I use them later in the procedure to make addtional operations. The problem is that string is needed by the caller as soon as possible. So I need to calculate that string and pass it back (somehow, from a select for example) and then continue to work. The caller gets its valuable string much more quickly.

Caller is a Web Service.


texts: What will be best (for performance) transaction isolation level?

texts:
There, are two types of queries that are executed in my app:

DELETE FROM tbl1 WHERE user_id = 1;
INSERT INTO tbl1 VALUES (...), (...), ...;

and

UPDATE tbl2 WHERE id = 1 SET ...;
DELETE FROM tbl3 WHERE tbl2_id = 1;
INSERT INTO tbl3 VALUES (...), (...), ...;
DELETE FROM tbl4 WHERE tbl2_id = 1;
INSERT INTO tbl4 VALUES (...), (...), ...;

The ORM that I am using automatically sets transaction isolation level to REPEATABLE READ and I was wondering if it is a right choice for best performance. My goal is to only execute all or nothing (from 2 transaction above) and I don't care about any reads...

So which isolation level will be best for best performance in my case?


texts: Partition pruning with multiple date columns

texts:
I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are all used in queries, so I can't just pick one date column and use it as partition key.

Most of the time dates are close to each other, so I have created partitions for each year, plus one "overflow" partition that holds the rows that cross the year boundary. Here is a simplified example:

create table t (
  start_year int,
  end_year int,
  partition_year int as (case when start_year=end_year then start_year else 0 end),
  data blob
)
partition by range(partition_year) (
  partition poverflow values less than (1000),
  partition p2000 values less than (2001),
  partition p2001 values less than (2002),
  partition p2002 values less than (2003),
  partition p2003 values less than (2004),
  partition p2004 values less than (2005)
);

The problem with this approach is that partition_year must be explicitly referenced in queries or partition pruning (highly desirable because the table is large) doesn't take effect. This table is used for ad-hoc aggregate queries by multiple users; I can't expect that they all remember this logic.

This can be solved with a view

create or replace view v as
select *
from t
where partition_year=start_year
  and partition_year=end_year
  and partition_year>1000
union all
select *
from t partition (poverflow);

Now queries like this one

select * from v where start_year >= 2003 and end_year <= 2004;

Use correct partitions (5-6 + 1 in plan below):

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   1 |  VIEW                      | V    |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|*  4 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|   5 |    PARTITION RANGE SINGLE  |      |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004 AND "END_YEAR">=2003 AND
              "START_YEAR"<=2004 AND "PARTITION_YEAR"<=2004 AND "PARTITION_YEAR"="START_YEAR" AND
              "PARTITION_YEAR"="END_YEAR")
   6 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004)

The problem is that if I replace int types with dates, this doesn't work any more. I have tried to extract the year component from dates and add corresponding constraints to the view, but partitions are not pruned. Changing the type of partition_year to date also didn't help.

Is there any way how I could have multiple date columns in a table and still be able to use partition pruning?


texts:
I have database called Blackacre and two roles who have access to it: johnrole and janerole. janerole has been granted ALL privileges and johnrole has only been granted the SELECT privilege.

Problem is when a user from janerole creates a new table, then a user from johnrole can't SELECT from the new table.

ERROR:  permission denied for relation mopery

I tried to use ALTER DEFAULT in different ways to automate that:

ALTER DEFAULT PRIVILEGES FOR ROLE johnrole IN SCHEMA public
   GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO johnrole;
ALTER DEFAULT PRIVILEGES FOR ROLE johnrole GRANT SELECT ON TABLES TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO GROUP johnrole;

None of them work and I still end up with the same error.

When I use ALTER DEFAULT to automate future tables privileges for user e.g john then everything works, he can SELECT from new tables created in the database without any error.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO john;

This is how I added people to roles:

GRANT johnrole TO adam;
GRANT janerole TO eva;

Granted usage on Blackacre database like this:

GRANT USAGE ON SCHEMA public TO johnrole;
GRANT USAGE ON SCHEMA public TO janerole;

Gave some privileges like that:

GRANT ALL ON ALL TABLES IN SCHEMA public TO janerole;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO johnrole;

After that I tried to give automatic privileges with ALTER DEFAULT as above described don't work. Any ideas how to get default privileges working properly?


texts: What practices will help me write an efficient query against a large partitioned table?

texts:
Table Properties:

Row Count: 276,027,782
Number of Partitions: 68

I'm looking to create a report off of one large table. I have a view which holds customer info. To return data of interest for 1 Client/Customer, it takes about 2 minutes. I have about 2200 Clients that I need to return data for. I'm joining on indexes and as far as I can tell I'm hitting the latest partition. As I'm returning data from 1/2017 onward. I'm looking for solutions or ideas that would speed up the code below:

select distinct
 vc.ClientID
,left(datestamp, 6) Month
,count(*) over (partition by left(datestamp, 6)) Cnt
,avg(delta) over (partition by left(datestamp, 6)) Perf
,percentile_cont (0.5) within group (order by delta) over (partition by left(datestamp, 6)) Median
,percentile_cont (0.90) within group (order by delta) over (partition by left(datestamp, 6)) Perc90
,percentile_cont (0.95) within group (order by delta) over (partition by left(datestamp, 6)) Perc95
,percentile_cont (0.99) within group (order by delta) over (partition by left(datestamp, 6)) Perc99
from schema.Monitor pm
    join Reporting.dbo.vwClient vc
        on pm.Institution_ID = vc.ClientID
            and vc.ActiveFlag = 1
            and vc.TestFlag = 0
            and vc.AccountType = 'Customer'

WHERE pm.LogInfo like ('StringFilter') -- no int or id value to filter on

--and pm.IID = @clientID -- filter for 1 customer
ORDER BY
 left(datestamp, 6) desc


texts: How to only display a variable in PHP inside a MySQL query when it has a content or when a condition is true?

texts:
How do I only display $mon or any variable when it has content? whenever i use this syntax and for example, monday must contain the number 1 and tuesday would contain number 2 and the other ones have empty content, it displays error
1064 - 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 '' at line 8
(select my_date from ( select adddate('2015-02-01', numlist.id) as `my_date`,
                 weekday(adddate('2015-02-01', numlist.id)) as day_no,
                 dayname(adddate('2015-02-01', numlist.id)) as `day_name` from
                 (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM
                  num n1 cross join num as n10 cross join num as n100) as numlist
                 where adddate('2015-02-01', numlist.id) <= '2015-02-28' and
                 weekday(adddate('2015-02-01', numlist.id)) in( $mon, $tue, $wed, $thu, $fri, $sat) ) A)

Legend:
If selected on the php form..(which is not included but i think not important for now, as it is just passing of variable) the following should contain the value.
$mon=1
$tue=2
$wed=3
$thu=4
and so on.. until sunday.
What I need is when the variable doesn't contain any value, it should not appear in the syntax. I don't know how to insert if syntax inside mysql, (or is what am i thinking possible? it does not change its color so I think its not, or am i missing any syntax, i have no luck with google right now.. :D)


texts: Moving a 10g database to 11g with rman backup

texts:
Following Doc ID 369644.1, I'm trying to restore a 10g database into a 11g database ( version 11.2.0.1.0), which is in other server.

What I did on 11g server:
- Generate full rman backup of 10g database
- Copy pfile, control file and full rman backup from 10g to 11g

C:\> oradim -new -sid BDSPROD -intpwd bdsprod
C:\> SET ORACLE_SID=BDSPROD
C:\> rman
RMAN> connect target sys
RMAN> SET DBID=367107039
RMAN> STARTUP force nomount PFILE=C:\app\oracle\product\11.2.0\dbhome_1\database\PFILE_bdsprod.ora
RMAN> restore controlfile from 'C:\app\oracle\product\11.2.0\dbhome_1\database\CTRLFILE_BDSPROD.CTL';
RMAN> alter database mount;
RMAN> crosscheck backupset;
RMAN> delete expired backupset;
RMAN> catalog start with 'D:\RMAN_bdsprod\';
RMAN> restore database;
RMAN> recover database;

After this step, I followed Doc ID 558408.1

SQL> connect sys/bdsprod as sysdba
Connected.
SQL> alter database open resetlogs upgrade;

Now, how can I startup database? It seems that only way is doing:

SQL> STARTUP upgrade PFILE=C:\app\oracle\product\11.2.0\dbhome_1\databas\PFILE_bdsprod.ora;

In that way, only sysdba can connect to database.
Other ways like just "startup" or "startup pfile=C:\app\oracle\product\11.2.0\dbhome_1\database\PFILE_bdsprod.ora;" it gets errors.
Is there any way to start 11g database?

SQL> startup PFILE=C:\app\oracle\product\11.2.0\dbhome_1\database\PFILE_bdsprod.ora;
ORA-32006: BACKGROUND_DUMP_DEST initialization parameter has been deprecated
ORA-32006: USER_DUMP_DEST initialization parameter has been deprecated
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2179136 bytes
Variable Size 201330624 bytes
Database Buffers 499122176 bytes
Redo Buffers 7204864 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Process ID: 12712
Session ID: 5 Serial number: 3

SQL> startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\APP\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITBDSPROD.ORA'


texts: Any problem will all InnoDB and one MyISAM table?

texts:
I have a MySQL 5.5 DB with all InnoDB tables. For some reason, one of the developers decided to create a MyISAM table. It is a log table for user sessions. I don't see why we should mix in a MyISAM table just because this one table doesn't need transactions.

Is there a maintenance or tuning headache here?
Any benefit to it?

Thanks


texts: Restoring development database from production backup triggered production database to restore as well

texts:
We backed up our production database to restore our development database.

When we restored our development database from the backup it triggered the production database to restore as well.

The production database did not actually restore though...

I verified we applied the restore to the correct database by running

WITH LastRestores AS
(
SELECT
    DatabaseName = [d].[name] ,
    [d].[create_date] ,
    [d].[compatibility_level] ,
    [d].[collation_name] ,
    r.*,
    RowNum = ROW_NUMBER() OVER (PARTITION BY d.Name ORDER BY r.[restore_date] DESC)
FROM master.sys.databases d
LEFT OUTER JOIN msdb.dbo.[restorehistory] r ON r.[destination_database_name] = d.Name
)
SELECT *
FROM [LastRestores]
WHERE [RowNum] = 1

This showed that our dev database has been restored and production has not.

Why did SQL Server Management Studio say that the production database was being restored when it has not been?

It looks like someone else has had a similar experience
https://sqlundercover.com/2017/08/14/ssms-source-database-restoring-after-performing-a-restore-to-a-new-destination/


texts: SQL Server ; working around collation mismatches

texts:
Imagine the following scenario: I have two servers which are on two separate private networks.

A linked server has been created, and there is a table in server A which has an updated version of the same table on server B. The updates are not extensive, essentially just a reassignment of some Boolean values.

The connection between the two servers is reliable but relatively slow, and there is a collation mismatch between the two tables.

Is there a recommended or standard way of updating information between two servers with a collation mismatch?


texts: Service accounts for SQL Server 2012 only allow pre-Windows 2000 name?

texts:
I've just set up SQL Server 2012 with domain service accounts. I found that the account name would not validate unless I used the shorter user name.

Is there any way of using the full names?


texts: How to export database structure from Firebird?

texts: I'm doing replication and I need to export db structure from one Firebird db (dialect 3) into another. I've seen a couple tools like IBPump and FBExport, however all I can find is how to export all the data and not the structure. Any advice?


texts: mysql: Is mysqladmin -p create equivalent to create database?

texts:
I am new to mysql administration and I need to setup certain program. At some point, the installation guide states to execute this command:

mysqladmin -p create somedatabasename

Is this equivalent to create database somedatabasename ?

So far I have checked the documentation and I think both commands are equivalent but I don't want to screw my existing databases. Can someone confirm?

https://dev.mysql.com/doc/refman/5.7/en/mysqladmin.html
https://dev.mysql.com/doc/refman/5.7/en/database-use.html


texts: Database Design - Creating Multiple databases to avoid the headache of limit on table size

texts:
I need to design database to store information contained in millions of log files generated by devices on trial on network.

My first approach was storing all the information in tables contained in a single database. But this approach seems to fail as the data to be stored is too large. So, I thought of creating separate database for each device and storing each device log files in separate database. My question is if this approach can spare me of the headache involved with table size limit. Below are given my platform specification

Operating System - Windows 7
File System - NTFS
Storage Engine - InnoDB
Table Type - InnoDB

I want to build my database design in a way so that I need not to apply shrink of tables procedures in future


texts: MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?

texts:
We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between master and slave.

On master:

mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: ROW

On slave:

mysql> show variables like 'binlog_format' \G
*************************** 1. row ***************************
Variable_name: binlog_format
        Value: STATEMENT

Once in a while, the replication breaks with an error similar to the following:

  Last_SQL_Error: Could not execute Delete_rows event on table
  test.pruning; Can’t find record in ‘pruning’, Error_code: 1032;
  handler error HA_ERR_KEY_NOT_FOUND; the event’s master log
  mysql-bin.000002, end_log_pos 1448

We don't know for sure whether this was caused due to the different binlog_format setting between the master & slave.

Could the above error potentially be caused by this inconsistent setting? Should I change the slave to "row" based format? It is hard to reproduce the error so I am looking for the best practice we should be following and we are very new to MySQL.


texts: Prevent a single query from hogging all resources

texts:
I have a production database server running SQL Server 2008 R2. How can I keep a user query on one database from hogging all the system resources and bogging down all the databases on the server?

The specific issue I'm having is with a query with a long running cursor. Whenever the query is run, CPU usage goes to 100% on all cores and even simple queries on other databases slow way down or time out. Is this normal and what are the best practices to prevent this?


texts: How to pass array of UUIDs *OR* empty array and return implicit (inner join)?

texts:
How would I pass a list that is either full of UUIDs or empty, then return only joined records with those id values or all records if empty?

This question (Casting an array of texts to an array of UUIDs) asks for the answer I am looking for, but the selected answer does not answer the question[, it answers the asked text and addresses the errors in the OP's situation].

The closest to correctly fulfilling the need is:

FUNCTION getstuff(id_list text[])
  RETURNS TABLE (a_id uuid, b_id, uuid, <other values)
  ... <snip> ... $$
  SELECT a.id, b.id, <etc.>
    FROM schema.a a, schema.b b
    WHERE <other_tests>
      AND b.id::text = ANY (id_list)
        OR b.id = uuid_or_null(id_list)
  $$;

CREATE OR REPLACE FUNCTION uuid_or_null(str text)
RETURNS uuid AS $$
BEGIN
  RETURN str::uuid[];
EXCEPTION WHEN invalid_text_representation THEN
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Notes:
Also tried has been CARDINALITY(id_list)=0, expecting it to yield TRUE when the id_list array was empty, and thus also return all rows, but it failed due to a syntax error that's since been lost.

*(uuid_or_null modified from this answer)


texts: How to write a query for exact pairs of groups

texts:
I am given the schema:
EMPLOYEE(Fname,Ssn,Dno)
DEPARTMENT(Dname,Dnumber,Mgr_ssn,Mgr_start_date)
PROJECT(Pname,Pnumber,Plocation,Dno)
WORKS_ON(Essn,Pno)
fiddle: https://www.db-fiddle.com/f/9LnJN32WLVu1HsdmG8NUAc/5
Primary keys are given in bold.
The question: Is it possible to define a view that will report the following detail for each employee: Ssn and the number of employees who work on exactly the same set of projects? Explain why not or given an SQL query.
My idea was to find unique pairs of employees and check if projects worked by first employee equal those of the second one (this was done by using set operation except as if s1 except s2 union s2 except s1 is empty than it seems like s1 and s2 are equal)
My attempt for this was:
SELECT e1.ssn,count(e2.ssn)
FROM EMPLOYEE e1, EMPLOYEE e2
WHERE e1.ssn < e2.ssn
AND NOT EXISTS
(
 (
 (SELECT Pno
 FROM WORKS_ON w1
 WHERE w1.Essn = e1.Ssn)
 EXCEPT ALL
 (SELECT Pno
 FROM WORKS_ON w2
 WHERE w2.Essn = e2.Ssn)
 )

 UNION ALL

 (
 (SELECT Pno
 FROM WORKS_ON w2
 WHERE w2.Essn = e2.Ssn)
 EXCEPT ALL
 (SELECT Pno
 FROM WORKS_ON w1
 WHERE w1.Essn = e1.Ssn)
 )
)
GROUP BY(e1.ssn)

Does this query look correct? Also is there a neater way of doing this?
As a final side note what exactly determines if it is impossible for us to construct an SQL query for a problem?


texts: Creating a hot standby with Postgres 9.6

texts:
I'm trying to create a hot standby with Postgres 9.6 on Ubuntu 16.04.

I can successfully ssh from my standby machine to the primary as postgres.

I have removed /var/lib/postgresql/9.6/main and running:

pg_basebackup -h <primary IP> -D /var/lib/postgresql/9.6/main -U rep -v -P --xlog-method=stream

that successfully creates a new main directory.

In /etc/postgresql/9.6/main/postgresql.conf, I have:

added this servers IP address to the listen_addresses
set wal_level = replica
set hot_standby = on

I've created a recovery.conf with:

standby = on

primary_conninfo = 'host=<primary ip> port=5432 user=rep password=<password>

On sudo service postgres start, postgres starts and I see:

2016-12-22 07:24:29 UTC [10728-1] LOG:  database system was interrupted; last known up at 2016-12-22 07:13:44 UTC
2016-12-22 07:24:30 UTC [10728-2] LOG:  redo starts at 3/4D000028
2016-12-22 07:24:30 UTC [10728-3] LOG:  consistent recovery state reached at 3/4D000130
2016-12-22 07:24:30 UTC [10728-4] LOG:  redo done at 3/4D000130
2016-12-22 07:24:30 UTC [10728-5] LOG:  MultiXact member wraparound protections are now enabled
2016-12-22 07:24:30 UTC [10732-1] LOG:  autovacuum launcher started
2016-12-22 07:24:30 UTC [10727-4] LOG:  database system is ready to accept connections
2016-12-22 07:24:30 UTC [10734-1] [unknown]@[unknown] LOG:  incomplete startup packet

Should I not see a message where it tries to contact the master?


texts: Normalization/normal forms - May a field describe an other field?

texts:
Like this:

CREATE TABLE persons(
    id serial8 NOT NULL PRIMARY KEY,
    name varchar,
    -- A lot of other fields
    date_of_birth timestamp with time zone,
    date_of_birth_precision varchar(16),
    CHECK (date_of_birth_precision IN ('Years','Months','Days','Hours','Minutes'))
);

date_of_birth_precision describes the precision of date_of_birth.

I wonder if it violates this rule (because I don't fully understand the rule):

Every non-prime attribute of R is non-transitively dependent (i.e. directly dependent) on every superkey of R.


texts: Is there a more concise way to convert a UTC datetime into a local date only?

texts:
I am trying to write a query that groups records based on the local date part only of a UTC datetime field.

For example, if my table contains 10/19/2012 2:00:00, then it should get grouped as 10/18/2012, since my local time is EST (-5h) and I'm only interested in the date portion of the field.

I know I can use DateAdd(day, DateDiff(day, 0, MyDate), 0) to get the date part only from the datetime field, and I can use DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate) to convert a UTC datetime to a local date time.

But combining the two is seriously offending me.

Is there a better way than this to get just the Date part of a UTC DateTime field, converted to local time, in SQL Server 2005?

  SELECT DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)
       , Count(*)
    FROM MyTable
GROUP BY DateAdd(day, DateDiff(day, 0, DateAdd(minute, DateDiff(minute, GetUtcDate(), GetDate()), MyUtcDate)), 0)


texts: Database Mail on SQL Server Express 2017

texts:
I followed this article to create a database mail for SQL Server Express 2017.

I got it working but before that I had tried to create accounts and different profiles.

How I do I list all database mail profiles id and account ids so I can delete the old messed up ones.


texts: 2 SQL 2019 Servers with High Availability Linux with a shared Linux SQL Express 2019 Witness

texts:
Is it possible to have two SQL Always on High Availability servers in datacentre A replicating to two servers in datacentre B with one Witness using Express with two availability groups (one for each server in Datacentre A/B) on Linux? I tried it but I'm having problems so i am not sure if it is what im doing or is it because it cannot be done and I will need two SQL Express Witness servers?

In addition, all servers are running on Linux Ubuntu

On the SQL Express I'm executing the command ALTER AVAILABILITY GROUP [ag2] JOIN WITH (CLUSTER_TYPE = EXTERNAL) and receiving the following error:

  Msg 47106, Level 16, State 3, Line 125 Cannot join availability group
  'ag2'. Download configuration timeout. Please check primary
  configuration, network connectivity and firewall setup, then retry the
  operation. Msg 41158, Level 16, State 3, Line 125 Failed to join local
  availability replica to availability group 'ag2'.  The operation
  encountered SQL Server error 47106 and has been rolled back.  Check
  the SQL Server error log for more details.  When the cause of the
  error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN
  command.

If I uninstall SQL Express and reinstall and then execute ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL) it takes about 30-40 seconds and works. It is just when I trying to connect to [ag2].

All servers can ping each other.

Thanks


texts: Postgres query performance - hash aggregate costly operation

texts:
I'm running Postgres 9.6.6 and I have a relatively simple query, however, I'm finding that it is slow with the hash aggregation function which is the most costly operation:

    select
      program_schedule_source_count.full_name,
      program_schedule_source_count.country,
      sum(program_schedule_source_count.displays) as displays,
      program_schedule_source_count.source_id,
      program_schedule_source_count.source_region
    from program_schedule_source_count
    where program_schedule_source_count.original_title = 'How I Met Your Mother'
    and program_schedule_source_count.show_type in ('SM', 'SE')
    and program_schedule_source_count.start_date between '20200101' and '20200217'
    group by
      program_schedule_source_count.source_id,
      program_schedule_source_count.full_name,
      program_schedule_source_count.country,
      program_schedule_source_count.source_region;

Below is the query plan:

HashAggregate  (cost=1139676.26..1139725.25 rows=3919 width=46) (actual time=18769.670..18770.066 rows=736 loops=1)
  Group Key: source_id, full_name, country, source_region
  ->  Index Scan using title_date_show_type_country on program_schedule_source_count  (cost=0.70..1139186.45 rows=39185 width=46) (actual time=0.098..18733.005 rows=42654 loops=1)
        Index Cond: ((start_date >= '20200101'::bpchar) AND (start_date <= '20200217'::bpchar) AND ((original_title)::text = 'How I Met Your Mother'::text))
        Filter: (show_type = ANY ('{SM,SE}'::bpchar[]))
Planning time: 0.223 ms
Execution time: 18770.252 ms

The table has indexes on all of the fields in the where clause:

CREATE UNIQUE INDEX program_schedule_pkey ON public.program_schedule_source_count USING btree (source_id, start_date, program_id);
CREATE INDEX title_date_show_type_country ON public.program_schedule_source_count USING btree (start_date, original_title, release_year, show_type, country);

I have tried changing the order of the group by function but this did nothing to alter the performance.
I tried to disable hash aggregation to see if this would speed up the query but it still runs in roughly the same amount of time.
I assume adding an index to the group by fields would not have any benefit because I am not searching these fields.

I've seen that clustering could benefit but in the documentation it says you would cluster by an index does this mean I would have to create another index of the group by fields and then cluster the data by this?

Is there an alternative way I can write the query which could make it faster?

Thanks all for your help


texts: How to create identity field in MySQL without using auto_increment column?

texts:
I am trying to create a dynamic system when a user can create different objects and relate them to another objects.

I need is to be able to generate an identity column similar to auto_increment but in an alpha numeric format.

The ids will be something like
001abcdeFGhJk123

Using a id like above will help me identify the object identity. For example I can say the first 4 characters are the object id and the last 14 charecaters are the record identifier.

How can I do this in MySQL. Or is it even possible in MySQL. I belive MS SQL server and Oracle database have field type of identifier that does the trick.

Thank you in advance


texts:
When to create multiple-column index in Mysql?

Should I measure the slow queries and create multiple-column index for them?


texts: MySQL InnoDB Plugin default engine type not accepting config setting

texts:
I have a strange MySQL InnoDB issue that I've been struggling with for a few hours now. I'm asking this here, and also on the main StackOverflow site - I will cross-post the answer to the other site when answered.

I have enabled the InnoDB plugin as a replacement for the built-in version, and setting the innodb_file_format and innodb_file_per_table are not having the required effect. The MySQL status variables still show Anaconda as the default engine.

Any suggestions would be gratefully received!

Relevant section of my.cnf settings

The settings below do not exist elsewhere in the config overriding these options.

innodb_file_per_table   = 1
innodb_file_format      = Barracuda
innodb_strict_mode      = 1

Relevant entries in SHOW VARIABLES

This is how I know that Barracuda is available

| innodb_file_format              | Antelope               |
| innodb_file_format_check        | Barracuda              |
| innodb_file_per_table           | OFF                    |
| innodb_strict_mode              | OFF                    |
| innodb_version                  | 1.0.9                  |

Section of MySQL Log

The log is also showing that Barracuda is available...

111020 18:10:13 mysqld_safe Starting mysqld daemon with databases from /.../mysql_data/
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
111020 18:10:13  InnoDB: highest supported file format is Barracuda.
111020 18:10:13 InnoDB Plugin 1.0.9 started; log sequence number 5609019
111020 18:10:13 [Note] Event Scheduler: Loaded 0 events
111020 18:10:13 [Note] /.../mysql/libexec/mysqld: ready for connections.
Version: '5.1.48-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution