texts: Indexing strategy for VARCHAR2 LIKE search

texts:
Scenario

Database

Oracle Database 11g

Table

Users

ID Number, PK
CODE Number, Indexed, Unique, Not null
FIRST_NAME VARCHAR(100), Not null
LAST_NAME VARCHAR(100), Not null
EMAIL VARCHAR(50), Not null

Search

Except for ID and CODE, all other columns has mixed case.
I need to search a user by an string. The search should be case insensitive.
The string is a partial value. (ex: 'foo' to search for 'foobar')
The pattern can be present on any part of a value on the colum. (ex: 'ob' for foobar)
The pattern can be present on any of the table fields.

SQL

SELECT *
FROM users u
WHERE
u.code = COALESCE( to_number( REGEXP_REPLACE(:pattern, '[^0-9]+', '') ), -1)
OR upper(u.email) LIKE upper(:pattern)
OR upper(u.last_name) LIKE upper(:pattern)
OR upper(u.first_name) LIKE upper(:pattern)

Query Plan

Results show a full table scan with an OR Filter Predicate

Question

What is the best strategy to optimize this query? Can we reduce the cost somehow?
Is beneficial to use an index on the VARCHAR2 column(s)? Even with the search using the LIKE operator?
Can we use a different SQL(s) to take advantage of the indexes and join the results at the end?

Text Search Engines

I'm aware that we could use an text search engine to handle that. But let's discuss what can be done using the database.

Related Links

Virtual Columns (suggested by: @Michael Kutz) : ToadWorld - Oracle Virtual Columns and Indexes
Oracle Text (suggested by: @Balazs Papp) : Oracle - Oracle Text Development Guide
Oracle Text - Check if it is installed : Oracle Forum Thread
Oracle Text - Avoid Fragmentation : Oracle - Text Index Maintenance Regime


texts: Non-Clustered Indexes - keys and nonkeys

texts:
I just want to make sure I'm on the right track with these concepts, so any feedback would be greatly appreciated.

Here's my theory from the query I've just optimised, through a process of trial and error and reading the MSDN documentation.

The Query

DECLARE @pic_id int
SET pic_id = 1

SELECT ROW_NUMBER() OVER (ORDER BY pic_date desc) AS row_num, *
FROM tbl_pics
WHERE deleted = 0 AND map_id = 1 AND (hidden = 0 OR pic_id = @pic_id)

The Index

CREATE NONCLUSTERED INDEX [IX_tbl_pics] ON [dbo].[tbl_pics]
(
    [map_id] ASC,
    [deleted] ASC,
    [pic_date] DESC
)
INCLUDE ( [hidden], [pic_id] )

There is also a PK index on pic_id

The Theory

The key columns are so, because they are used in either a WHERE clause (but not used in a OR situation) or and ORDER BY.

The nonkey (INCLUDE) columns as so, because they get used in the WHERE, but because they are used in an OR scenario they can't (can't = won't improve performance) be a key column.

Are these presumptions correct? If not, what am I missing?

Thanks!


texts: Is it possible to use ExpressMaint with SQL Server 2012 (Express)?

texts:
When I try running ExpressMaint on a server with SQL Server 2012 installed, it fails with an error saying it cannot find the Microsoft.SqlServer.Smo libraries (SQL Management Objects). From what I've read, this is because ExpressMaint was compiled against the 2008 SMO libraries.

Various websites recommend installing the older SMO libraries. However, I just tried installing the 2008 R2 libraries on my 2012 box, and it wouldn't let me because there is a newer version installed.

There is a post on the CodePlex site where someone claims to have recompiled ExpressMaint against 2012, but I'm loathe to install something I found on the internet on my production box.

Has anyone had success with this? Is there a better inexpensive solution out there?

I've used ExpressMaint before, and I like it because it's so easy to use.


texts: how to update date field in a record to null?

texts:
I have  records in a table which a field named as 'validity' contains date as '8/15/1947 12:00:00 AM'. I need to set this date as null. I am having 1000 records,in that validity field is '8/15/1947 12:00:00 AM'. I wrote a syntax

update ACCESSCODE set VALIDITY=null where VALIDITY='8/15/1947 12:00:00 AM';

which throws error as:

  'Incorrect date value: '8/15/1947 12:00:00 AM' for column 'VALIDITY' at row 1'.

Here is my table skeleton.

CREATE TABLE `ACCESSCODE`
(  `SNO` int(11) NOT NULL AUTO_INCREMENT,
   `ACCESS_CODE` varchar(255) DEFAULT NULL,
   `STATUS` int(11) DEFAULT NULL,
   `VALIDITY` date DEFAULT NULL,
    PRIMARY KEY (`SNO`)
) ENGINE=MyISAM
  AUTO_INCREMENT=10103
  DEFAULT CHARSET=latin1 ;

Please advice and correct me with the syntax.My table name is ACCESSCODE. Am working this TOAD. Thanks in advance.


texts: Decryption not working when SQL Server database copied to new server

texts:
I am copying a database from an old server to a new server. The database has an encrypted column. From my searches I see that I need to backup the master key from the old server and restore it in order to be able to retrieve previously encrypted data, however, I must be doing something wrong.

Please see the process I am following. Can anyone please explain why it isn't working? When the encrypted column is decrypted, the values shows NULL, which is not right.

-- OLD SERVER ---------------------------------------------------------------
BACKUP MASTER KEY TO FILE = 'D:\MasterKey' ENCRYPTION BY PASSWORD = 'MyPassword1'
GO

-- Copied file to new server

-- NEW SERVER ---------------------------------------------------------------
RESTORE MASTER KEY FROM FILE = 'D:\MasterKey'
    DECRYPTION BY PASSWORD = 'MyPassword1'
    ENCRYPTION BY PASSWORD = 'MyDBNameEncryption$3C|_|R3!'
GO
-- Command(s) completed successfully.

select * from sys.symmetric_keys
-- (1 row(s) affected)
-- name principal_id    symmetric_key_id    key_length  key_algorithm   algorithm_desc  create_date modify_date
-- ##MS_DatabaseMasterKey## 1   101 128 D3  TRIPLE_DES  2013-01-17 09:37:05.873 2013-01-17 09:37:05.873

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'MyDBNameEncryption$3C|_|R3!'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO
-- Command(s) completed successfully.

CREATE CERTIFICATE MyDBNameCertificate01 WITH SUBJECT = 'MyDBName Certificate'
GO
-- Command(s) completed successfully.

CREATE SYMMETRIC KEY SSN_Key_01 WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE MyDBNameCertificate01
GO
-- Command(s) completed successfully.

select * from sys.certificates
-- (1 row(s) affected)
-- name certificate_id  principal_id    pvt_key_encryption_type pvt_key_encryption_type_desc    is_active_for_begin_dialog  issuer_name
-- MyDBNameCertificate01    268 1   MK  ENCRYPTED_BY_MASTER_KEY 1   MyDBName Certificate

OPEN SYMMETRIC KEY SSN_Key_01 DECRYPTION BY CERTIFICATE MyDBNameCertificate01
select
    name,
    Password,
    decryptbykey(Password) as [DecryptedPass]
FROM
    [tbl_Users]
CLOSE SYMMETRIC KEY SSN_Key_01
--name  Password    DecryptedPass
--David 0x00CF72EF69A517......34EBC1EDB4F1D0    NULL

The NULL value above should be the original value from the old database, so I assume the encryption objects have not been installed correctly...


texts: How is INDEX on Composite Primary Key in mysql?

texts:
When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually?

The reason that I am asking this question is that when we use UNIQUE INDEX (col1, col2, col3), it acts as INDEX for the first column only, and we need to create additional INDEXs for other columns. I want to know if that's the case for Composite Primary Key too.


texts: Transaction log is full due to 'CHECKPOINT' for DB in simple recovery model

texts:
For one of our DB's on SQL server 2012 RTM,

We could not perform almost any troubleshooting step as the error says:

" Transaction log is full due to 'CHECKPOINT"

This being a dev database, its backup never happened, so we are out of option with that even.

Tried shrinking the log file but no success and got below error:

log is out of space - with a cascade error that the transaction log is full due to 'CHECKPOINT'

DBCC CHECKDB mostly just gives errors because the transaction log is full due to 'CHECKPOINT'

I can't change the recovery model to full - get an error because the transaction log is full due to 'CHECKPOINT'

Cannot take backup: same error:

Even tried detach and attaching back on other server (SQL server 2012 RTM) , but same error:

Tried Manually doing CHECKPOINT on the DB, but still same error:

Have already refred to this post here Simple model database transaction log full 'CHECKPOINT'

and

The transaction log for database 'database_name' is full due to 'XTP_CHECKPOINT', but no success.

Note: We do not have replication set up on the current server or any database.

Please help as i am unable to find a root cause and fix for this!

Thanks!


texts: Can we have OLAP cubes with same OBJECT NAME and ID at different database?

texts:
I have olap database for both production and test. Fortunately I noticed that cube under both production and test olap database has the same object name and Id. But DataSource for production and test olap database pointed towards different database connections.

My question is, having same object name and Id for the cube under different olap database is a problem or if I process the cube under test, does that overwrites the production cube?


texts: Percona's xtrabackup --apply-log fails with "all log files must be created at the same time"

texts:
I'm trying to figure out why applying logs on my xtrabackup files isn't working.
I created a VM running MySQL 5.5.24. Then I created the sakila test database and backed it up with the following command:

innobackupex --user=bckuser --password=XXXX --no-timestamp --compress /var/lib/mysql/dumps/backup/;

This completed successfully. After that I wanted to apply the logs with this command:

innobackupex --user=bckuser --password=XXXX --apply-log /var/lib/mysql/dumps/backup/;

But I get the following error:

140912 21:46:19  innobackupex: Starting ibbackup with command: xtrabackup_55  --defaults-file="/var/lib/mysql/dumps/backup/backup-my.cnf"  --defaults-group="mysqld" --prepare --target-dir=/var/lib/mysql/dumps/backup --tmpdir=/tmp

xtrabackup_55 version 2.1.9 for Percona Server 5.5.35 Linux (x86_64) (revision id: 746)
xtrabackup: cd to /var/lib/mysql/dumps/backup
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(8564748)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
140912 21:46:19 InnoDB: The InnoDB memory heap is disabled
140912 21:46:19 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140912 21:46:19 InnoDB: Compressed tables use zlib 1.2.3
140912 21:46:19 InnoDB: Initializing buffer pool, size = 100.0M
140912 21:46:19 InnoDB: Completed initialization of buffer pool
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
140912 21:46:19  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
140912 21:46:20 InnoDB: Error: all log files must be created at the same time.
140912 21:46:20 InnoDB: All log files must be created also in database creation.
140912 21:46:20 InnoDB: If you want bigger or smaller log files, shut down the
140912 21:46:20 InnoDB: database and make sure there were no errors in shutdown.
140912 21:46:20 InnoDB: Then delete the existing log files. Edit the .cnf file
140912 21:46:20 InnoDB: and start the database again.
xtrabackup: innodb_init(): Error occured.
innobackupex: Error:
innobackupex: ibbackup failed at /usr/bin/innobackupex line 2560.

I have googled a lot, but I can't find out what's wrong. Do you guys have any idea?


texts: Lock mysql table to prevent select and insert at the same time

texts:
I know this question has been asked earlier and I have also read the documentation. But I guess I'm stupid not understand.

I have a code that inserts a row and the row should be unique in several field values. But to make this new data really live delivered to the visitors it updates (if new data, inserting it) via ajax and every 5th second. This means it sometimes insert two or more rows at the same time.

I have a SELECT query before the INSERT to check if the row (a row with the exactly same data) already exist. And I also use BEGIN and COMMIT.
Yet duplicate rows appearing.

I'm using innoDB.

I have googled and found LOCK functions but not figured out how to use it. And does it even work for SELECT? The best would be to LOCK the SELECT query checking if the row already exist.


texts:
My table design is

users (id, username..., parent_id)

Data is

id    username      parent_id
-------------------------------
1  |  admin       | null
2  |  reseller 1  | 1
3  |  client 1    | 1
4  |  reseller 2  | 1
5  |  reseller 3  | 2
6  |  reseller 4  | 2
7  |  client 2    | 5
8  |  client 3    | 6

I want to get all descendants of id 1

I studied Adjacency List, Nested List & Closure table design, but came to conclusion that Session based Adjacency List can be better in my situation.

I found an example at https://stackoverflow.com/questions/8104187/hierarchical-queries-in-mysql

SELECT  @id :=
        (
        SELECT  senderid
        FROM    mytable
        WHERE   receiverid = @id
        ) AS person
FROM    (
        SELECT  @id := 5
        ) vars
STRAIGHT_JOIN
        mytable
WHERE   @id IS NOT NULL

I tried to modify this query to get my result like this

SELECT  group_concat(@id :=
        (
        SELECT  id
        FROM    users
        WHERE   parent_id = @id
        )) AS u
FROM    (
        SELECT  @id := 1
        ) vars
STRAIGHT_JOIN
        users
WHERE   @id IS NOT NULL

But it is not working. Here is the SQLFiddle

Need help in making the query work.


texts: No One Can Login SQL Server Instance Even Though Its Services Are Running

texts:
I have an issue. Today, our developers had reported to me a problem that they couldn't reach SQL Server database server several hours ago. I have checked the services and i saw that they were in running state but it was not reachable remotely. And of course, i have checked the TCP/IP connection settings. They are enabled and settings are correct. What can cause such an issue? Have you ever experienced like this situation before? The error code that i get when i try to connect to server is :

ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while
establishing a connection to SQL Server. The server was not found or
was not accessible. Verify that the instance name is correct and that
SQL Server is configured to allow remote connections. (provider: SQL
Network Interfaces, error: 26 - Error Locating Server/Instance
Specified) (Microsoft SQL Server, Error: -1)

This server is development server. There are 5 different SQL Server instances. And, no one can reach them. By the way, i can connect to the instances on the cmd screen. Can you help me?
The instance is running on a Windows Server Core machine, and I can connect to the instance locally from the server itself.


texts: What are possible ways to migrate SQL Server DB to PostgreSQL DB?

texts:
Currently I am assigned to complete the task of migrating several small to large databases (from 10GB to 200GB) from SQL Server to PostgreSQL RDBMS.

I would like to ask the community to share experience of migration.

Are there free ways to migrate? I am aware of creating SSIS solution
but it seems that there I have to migrate table by table
Which tool have you successfully used to complete such tasks?

Thank you


texts: guideline for calling optimize table after alter table?

texts:
Should I be calling 'optimize table' when changing datatypes of columns, adding indexes or dropping indexes, assuming a table with millions of rows, heavily used, both for reading and writing (InnoDB).

Ignore any possible downtime factor during the optimize-process.


texts: Convert a date range to an interval description

texts:
A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, something like "1 year, 3 months to go."

The built-in DATEDIFF function

  Returns the count ... of the specified datepart boundaries crossed between the specified startdate and enddate.

If used as-is this could produce misleading or confusing results. For example, using an interval of YEAR would show 1999-12-31 (YYYY-MM-DD) and 2000-01-01 to be one year apart whereas common sense would say these dates are separated by only 1 day. Conversely using an interval of DAY 1999-12-31 and 2010-12-31 are separated by 4,018 days while most people would see "11 years" as a better description.

Starting from the number of days and calculating months and years from there would be prone to leap year and size-of-month errors.

I got to wondering how this could be implemented in the various SQL dialects? Example output includes:

create table TestData(
    FromDate date not null,
    ToDate date not null,
    ExpectedResult varchar(100) not null); -- exact formatting is unimportant

insert TestData (FromDate, ToDate, ExpectedResult)
values ('1999-12-31', '1999-12-31', '0 days'),
       ('1999-12-31', '2000-01-01', '1 day'),
       ('2000-01-01', '2000-02-01', '1 month'),
       ('2000-02-01', '2000-03-01', '1 month'),              -- month length not important
       ('2000-01-28', '2000-02-29', '1 month, 1 day'),       -- leap years to be accounted for
       ('2000-01-01', '2000-12-31', '11 months, 30 days'),
       ('2000-02-28', '2000-03-01', '2 days'),
       ('2001-02-28', '2001-03-01', '1 day'),                -- not a leap year
       ('2000-01-01', '2001-01-01', '1 year'),
       ('2000-01-01', '2011-01-01', '11 years'),
       ('9999-12-30', '9999-12-31', '1 day'),                -- catch overflow in date calculations
       ('1900-01-01', '9999-12-31', '8099 years 11 months 30 days');  -- min(date) to max(date)

I happen to be using SQL Server 2008R2 but I am interested to learn how other dialects would handle this.


texts:
I am new to PostgreSQL and I can not found info about this. I am using pgAdmin III and my databse files are kept in instalation directory of PostgreSQL. So far so good. But now, I need to create huge DB, that is out of my HDD capacity. So I create RAID and want to move this single DB to this location. How can I specify path for single database to be somewhere else?

I dont want other on the same disk, since I have them on fast SSDs. The big one will be only for some data mining and I will remove it afterwards.


texts: Create dynamic WHERE clause from a JSON field

texts:
We have tables like this:

people_people (
   "id" integer...
   "attributes" HSTORE
)
object_permission (
    "permissions" JSON
)

An example of an object_permission.permissions is:

{"matricule":{"authorized_keys": ["12"],
              "unauthorized_keys": ["1", "2", "20"]},
 "departement":{"authorized_keys": ["it"],
                "unauthorized_keys": ["finance", "account"]},
 "access":{"authorized_keys": ["superuser", "user", "login"],
           "unauthorized_keys": ["web"]}}

From these data, I'd like to generate the query:

SELECT * FROM people_people
WHERE
(attributes->'matricule' IN ["12"]) AND
(attributes->'departement' IN ["it"]) AND
(attributes->'access' IN ["superuser", "user", "login"]);

How to generate dynamic WHERE clause based on a JSON field?
I don't want to use dynamic SQL because of the possible SQL injection.


texts: Select every n-th row, don't pull the entire table

texts:
In light of the Meta discussion on allowing basic SQL questions on dba.SE I present an issue that I'm having now, for which the answer on Stack Overflow is inadequate and naive. I hope that there is a better solution to the issue than those presented on SO (as I am currently facing this issue in an application), and dba.SE seems to be the perfect place to find a better answer.

Here is the original question on Stack Overflow: How do you select every n-th row from mysql?

Here is the accepted answer:

SELECT *
FROM (
    SELECT
        @row := @row +1 AS rownum, [column name]
    FROM (
        SELECT @row :=0) r, [table name]
    ) ranked
WHERE rownum % [n] = 1

The critical problem with the accepted answer is that it requires pulling the entire table into a temporary table. Thus, I've addressed that concern in the title of this question.

Consider also that the table might have deleted rows, thus an alternative query which were to simply test WHERE MOD on the primary key is not a good solution either. Id est, the primary key cannot be trusted to be sequential.

Is there a better way to phrase a query which would return every second, tenth, or arbitrary n-th row, which does not require pulling the entire table into memory yet also considers deleted rows?

Every n-th row can be defined as such:

n =  2: Rows 0, 2, 4, 6, 8, ...
n = 10: Rows 0, 10, 20, 30, ...
n = 42: Rows 0, 42, 84, 126, ...

My target DB is MySQL 5.5 running on a common Debian-derived Linux distro.

EDIT: In response to Thomas' answer:

The suggested solution does not produce the expected result, see below:

mysql> SELECT
    ->     @i:=@i+1 AS iterator
    ->     , t.name
    -> FROM
    ->     events AS t,
    ->     (SELECT @i:=0) AS dummy
    -> WHERE @i % 10 = 0
    -> ORDER BY name ASC;
+----------+-------+
| iterator | name |
+----------+-------+
|        1 |     0 |
+----------+-------+
1 row in set (0.29 sec)

mysql> select count(*) from events;
+----------+
| count(*) |
+----------+
|   892507 |
+----------+
1 row in set (0.17 sec)


texts: Join two sql's to return articles and its likes

texts:
I am new to Oracle and need help with the following: I am having a sql which returns a list of articles-

SELECT LEADERSHIP.LDRSHP_ID
    , LEADERSHIP.LDRSHP_TITL_DESC
    , LEADERSHIP.LDRSHP_CNTNT_TXT
    , LEADERSHIP.LDRSHP_PUBD_DT
    , LEADERSHIP.LDRSHP_ATHR_NM
    , LEADERSHIP.MEDIA_URL
    , LEADERSHIP.ARTCL_SRC_ID
    , MEDIA.MEDIA_TYPE_DESCRIP
FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP
INNER JOIN RSKLMOBILEB2E.MEDIA_TYPE MEDIA
    ON (LEADERSHIP.MEDIA_TYPE_IDENTIF = MEDIA.MEDIA_TYPE_IDENTIF)
WHERE (LEADERSHIP.LDRSHP_ATHR_NM
    , LEADERSHIP.LDRSHP_PUBD_DT)
IN (SELECT LDRSHP_ATHR_NM
      , MAX(LDRSHP_PUBD_DT)
    FROM RSKLMOBILEB2E.LEADERSHIP_CORNER
    GROUP BY LDRSHP_ATHR_NM)
ORDER BY LEADERSHIP.LDRSHP_PUBD_DT DESC

I have to implement total likes for each articles which is returned by this sql

SELECT LEADERSHIP1.LDRSHP_ID
, COUNT(*) TOTAL_LIKES
FROM RSKLMOBILEB2E.LEADERSHIP_CORNER LEADERSHIP1
, RSKLMOBILEB2E.LEADERS_LIKES LIKES1
WHERE LEADERSHIP1.LDRSHP_ID = LIKES1.LDRSHP_ID
group by LEADERSHIP1.LDRSHP_I

Now I need a single sql by combining above two sql's to return all the articles along with the total likes for each article. I will really appreciate any help on this.


texts: Only allow one checked row in a Column in SQL Server

texts:
Background:

I've only just begun programming in SQL... barely begun even. But I have been thrown into the deep-end left to find a way and learn to program SQL.

Question:

Is is possible to have a boolean column in a table that only allows one checked row.
So even if a user wanted to update the table with another checked row they wouldn't be able to? Suggestions and Ideas and use layman terms please.

Attempted illustration:

ID        Col_1        Col_2       Boolean_Col
----------------------------------------------
1         Data       More Data         0
2         Data       More Data        -1
3         Data       More Data         0


texts: I want to use dataype which can hold more than 19 digits. So is there any datatype available which can hold larger than 19 digits?


texts: How to check if range between two numbers is available in table of ranges

texts:
I have following table of number ranges

Create table Ranges {
  Id int identity(1,1) primary key,
  RangeFrom int not null,
  RangeTo int not null
}

Insert into Ranges(RangeFrom, RangeTo) VALUES (1,4)
Insert into Ranges(RangeFrom, RangeTo) VALUES (5,9)
Insert into Ranges(RangeFrom, RangeTo) VALUES (10,14)
-- here is free space (15 - 19)
Insert into Ranges(RangeFrom, RangeTo) VALUES (20,24)

And I want to insert a new record for example

Insert into Ranges(RangeFrom, RangeTo) VALUES (15,19) -- Ok
Insert into Ranges(RangeFrom, RangeTo) VALUES (15,30) -- Fail

The question is that before insertion I need to check if the numbers between range in the insert statement are not already used in any other range in the table.

Any Idea ?


texts: How to select row and column name where field is null?

texts:
I wanted to show row and column where field is null.

i already see this example  here

but i don't understand xml code.

my code is like this

if exists (select * from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null )
    begin
        declare @error1 as varchar (50)
    select @error1=[id] from Header  where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null

        declare @error2 AS varchar(50)

    DECLARE ocur CURSOR FOR
    select [id] from Header where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null

    OPEN ocur

    FETCH NEXT FROM ocur
    INTO @error1

    WHILE @@FETCH_STATUS = 0

    BEGIN
        --set @error2 = @error2 + @error1 + ', '
        set @error2 = 'error -' + @error1

        insert into yan values(@error2,@uploads, GETDATE() )

        FETCH NEXT FROM ocur INTO @error1
    END

        --insert into table_error values(@error2,@uploads, GETDATE() )
    CLOSE ocur
    DEALLOCATE ocur

    end
else
    begin
            insert code
            end

so i just need row code that show where column is null.

Thanks


texts: Oracle Database Migration using Oracle SQL Developer

texts:
I've 2 Oracle database on different machines [I connect to them via oracle SQL Developer

Now I am planning to have machine 3 which will consolidate both Oracle instances on one new node

Question is what is best way to do this ?

I surfed & found that oracle SQL Developer can help check this Link

Does this will work ??
Or
I have to fallow RMAN cloning option ?

What is best suitable way for doing this ?


texts:
Well, I'm interested is there a way to (someway)automatically organize database tables when you make EER Model from existing database.

In my schema I have 30(and something) tables and 50(and something) relationships and when I do thing noted above all my tables are placed on one spot. So, you could imagine how would it look with bigger schema. This is how it looks in my situation:

Also, I know that there is Arrange on the menu bar but it doesn't give much option.

I'm using 5.2.44 CE version of MySQL Workbench


texts:
I just started testing AWS Redshift and populated a single node with the AWS sample data.

Querying a table with 10 or ~400 rows takes around 2 seconds, uncached.

I'm not sure if I'm misunderstanding something, but I assumed that would be a lot faster. Is there something I'm missing here?

Thanks! Any feedback appreciated.


texts: What does FTS_ file mean after converting from MyISAM to InnoDB?


texts: Should I set up a password for mariadb.sys@localhost?

texts:
I'm not clear what this user is for, but mysqltuner suggests I create a password for it.  I'm unclear if that's actually good practice, or if this is a limitation of mysqltuner simply because it's seen a user that it's not familiar with, that no exception has been coded for?
Running MariaDB 10.5.8
Thanks


texts: How do I access the full text of an error in SSIS GUI?

texts:
I have an Excel task which is providing error text through the GUI, but I don't know how to access the error task without the GUI and the error disappears if I move the cursor from the X

I have the project property Run64BitRuntime set to False (though it is not editable so even if I wanted to, couldn't change it to true)


texts: Adding AD Security group to a SQL linked server

texts:
I have a question about adding permissions for a user group on a linked server in SQL Server. I am not a security specialist, so I appeal to everyone.
Here is the topo:
I have private software that I'll call X installed on a Hyper-V virtual server. Users of this software connect through virtual machines using the Remote desktop.
The application has its own SQL Server database instance. The security for this database is Windows Authenticated. Application X users all belong to a user group in the Windows Server 2019 Active Directory (AD).
On the other hand, I have a Y application (Microsoft Dynamic 365) that is installed locally on each workstation and which has its own SQL instance. The application uses an SQL database which I would also call Y.
As I would like to share information between the 2 SQL instances, I created a linked server on the X instance to the Y instance. I succeeded with some SQL query to insert, update, and delete some information in a database of instance Y from my instance X. To do this, I granted myself DB_owner rights on the database of X.
I have reached the stage of allowing user group X to be able to run from application X using the same queries that interact on SQL server Y.
However, I realize that I need to create each user of X in the SQL instance of application Y from the Security, Connection menu. My requests work when I grant DB_Owner roles. I would have thought that I could have used the same group used in AD for my X application. I tried well but it doesn't work.
I don't know if this is due to the configuration of my linked server.
Here is the configuration I am using:

Could someone point me to a solution to avoid having to recreate every user of my AD from X in my instance Y and just link my group X.


texts: If a table is partitioned by a single column should that column be included in index definitions?

texts:
Elaborate:

CREATE TABLE TEST(
  school_year DATE,
  B VARCHAR2(12),
  C VARCHAR2(15),
  D NUMBER
)

And the table is already partitioned on column school_year.  Should column school_year be part of the index definition?  The most frequently executed query would be:

WHERE school_year = TO_DATE('06/30/2014', 'MM/dd/yyyy')
and B = '020100001000'
and D = 3

Should the index be:

CREATE INDEX IDX_SAMPLE (SCHOOL_YEAR, B, D)

or

CREATE INDEX IDX_SAMPLE (B, D)


texts: MySQL server slow due to too many information_schema queries

texts:
Recently our server has been slow due to too many slow information_schema queries, which I am unable to kill. Please see the screenshot below:

These queries are taking over an hour to run each.

What is causing this issue, and how can I resolve it?


texts: MSSQL over the internet but we connect over RDP

texts:
We connect throguh RDP to our ERP solution which uses MSSQL 2008, right now I can see on windows logs and SQL logs that we have a HUGE amount of connection attempts to sa user and results in slow connections to us, users.

Since we connect through RDP and both ERP and DB are on the same server, I think I can close SQL port to the internet, and that would stop direct attacks to the DB, but our IT consultant it's not sure about it. I think it's safe to say that the only ports needed to be open through internet are RDP ports, besides that all happens in a LAN enviroment.

Am I right or am I missing something?


texts: Changing primary key from IDENTITY to being persisted Computed column using COALESCE

texts:
In an attempt to decouple an application from our monolithic database, we've tried to change INT IDENTITY columns of various tables to be a PERSISTED computed column that uses COALESCE. Basically, we need the decoupled application an ability to still update the database for common data shared across many applications while still allowing existing applications to create data in these tables without the need for code or procedure modification.

So essentially, we've moved from a column definition of;

PkId INT IDENTITY(1,1) PRIMARY KEY

to;

PkId AS AS COALESCE(old_id, external_id, new_id) PERSISTED NOT NULL,
old_id INT NULL, -- Values here are from existing records of PkId before table change
external_id INT NULL,
new_id INT IDENTITY(2000000,1) NOT NULL

In all cases the PkId is also a PRIMARY KEY and in all but one case, it is CLUSTERED. All tables have the same foreign keys and indexes as before. In essence, the new format allows the PkId to be supplied by the decoupled application (as external_id), but also allows the PkId to be the IDENTITY column value therefore allowing existing code that relies on the IDENTITY column  through use of SCOPE_IDENTITY and @@IDENTITY to work as it used to.

The problem we've had is that we've come across a couple of queries that used to run in an acceptable time to now blow out completely. The generated query plans used by these queries is nothing like what it used to be before.

Given the new column is a PRIMARY KEY, the same data type as before, and PERSISTED, I would have expected queries and query plans to behave the same as they did before. Should the COMPUTED PERSISTED INT PkId essentially behave the same way as an explicit INT definition in terms of how SQL Server will produce the execution plan? Are there other likely issues with this approach that you can see?

The purpose of this change was supposed to allow us to change the table definition without the need to modify existing procedures and code. Given these issues, I don't feel like we can go with this approach.


texts: Timestamp and substring with regular expression

texts:
I am using postgresql and I have the following table

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

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

When I do the command:

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

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

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

Why is this? How can I solve it?


texts: how to use a different tmp dir for INTO OUTFILE select with MySQL?

texts:
I am trying to dump a large table (40GB, 600,000 rows) using a SELECT ... INTO OUTFILE query. I want to use that because I want my results sorted in a particular way (so mysqldump is not good enough).

The problem is, I am getting an error code 28. I am pretty sure it is because it is running out of disk space on the default /tmp location. I have enough space on the disk elsewhere, if I could just specify at runtime what location to use for tmp.

I found out I can change the tmpdir by modifying my.cnf, but I do not have sufficient server privileges to modify it or restart mysqld.

What is the solution here?

EDIT: a few answers have suggested using mysql -e. I may have to go this route and post-process my file. The reason I wanted to use SELECT ... INTO OUTFILE is because I want to

fields   terminated by '\t'
         escaped by '\\'
lines    terminated by '\r\n' ;


texts: Changing InnoDB data directory prevents MySQL service from starting

texts:
I have a MySQL instance installed on Windows 7 and I wanted to change the directory of InnoDB tables data.
I modified the "my.ini" adding the following line after "[mysqld]":

innodb_data_home_dir="/ibdata"

After that, I wasn't able to restart the MySQL. Looking at the log, it had the following entries:

130124 12:00:52 [Note] Plugin 'FEDERATED' is disabled.
130124 12:00:52 InnoDB: The InnoDB memory heap is disabled
130124 12:00:52 InnoDB: Mutexes and rw_locks use Windows interlocked functions
130124 12:00:52 InnoDB: Compressed tables use zlib 1.2.3
130124 12:00:52 InnoDB: Initializing buffer pool, size = 106.0M
130124 12:00:52 InnoDB: Completed initialization of buffer pool
130124 12:00:52  InnoDB: Operating system error number 3 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: If you are installing InnoDB, remember that you must create
InnoDB: directories yourself, InnoDB does not create them.
InnoDB: File name \ibdata\ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

I made sure I had created the directory "ibdata" under the MySQL data directory beforehand.
I've tried moving existing files I think are associated to InnoDB to the new directory (from the clue given by the log file) but the problem persisted.

Why mysql cannot start working with the new directory?


texts: Looking for a snippet illustrating ambiguous result of a join in a where clause

texts:
A while ago (around 2004) I worked as a back-end developer on a team where everyone used the where clause joins. As a proponent of using inner/outer joins instead, I showed them an example of a where clause join that illustrated how it could produce ambiguous results depending on data, and how the outer joins were free from that problem.

I have to do the same presentation but cannot find that snippet anymore. It is somewhere in my file archive which I unfortunately do not have access to. Does anyone have an example handy?


texts:
I want to let users 'mark' tickets for later use.

So I created a table 'marked'.

All tickets are located in the table 'ticketdata'.

I have the following query to request all tickets for the logged in account:

$select = mysql_query("SELECT * FROM ticketdata WHERE member_id IN (SELECT member_id FROM users WHERE id=".mysql_real_escape_string($_SESSION['id']).") AND status <='3'");

This is working fine.

Now I want to let users 'mark' a ticket.

What I have done is create a mysql table (marked) with the following columns:

id, member_id, ticketid, active.

So now is the question how to execute both queries in order to get the 'marked' tickets. I tried the following query:

EDIT: this is returning ALL results of the specific member_id in ticketdata, but I only need tickets that are listed in tbl 'marked'.

$select = mysql_query("select a.title, a.discription, a.supportername, a.reportername, a.member_id FROM ticketdata a, marked b WHERE a.member_id IN (SELECT member_id FROM users WHERE id=".mysql_real_escape_string($_SESSION['id']).") AND b.active ='1'");

Unfortunatly with my basic mysql knowledge I am unable to get this query working.

Is there someone who can help me out ?

Thank you in advance.


texts: Anybody know how I can list the prepared statements that are in a SQL Server instance?


texts: I have generated a trace file in SQL Server 2012, not really sure about the unit of duration, it is measured by millisecond?


texts: Need help designing an Access Control List (ACL) database structure

texts:
I need help with a database design for an ACL (Access Control List) system.

Business rules

Let me explain the requirements:

Any business object in the system will get an ACL that explicitelly defines who can access the object.
Our system has groups of users and the ACL will specify which groups can access the object.
Any user can be member in N groups.
The user will not even see the objects if she is not included in the ACL.

Current considerations

There are tables that can hold hundred thousands of these business objects and thus I need a design that can quickly select the "visible" rows for them.

My idea is to add an ACL_ID to the tables with the business objects. Let's say I get a table for Orders and I need to control the accesibility:

CREATE TABLE Orders
(
    ID  int not null,

    ... lots of columns with relevant information

    ACL_ID  int not null
);

I add an ACL_ID column constrained as a foreign key into an AccessControlList table:

CREATE TABLE AccessControlList
(
    ACL_ID int not null,

    ... any needed information
);

Another table could hold the user groups that are included in the list:

CREATE TABLE AccessControlListItem
(
    ACL_ID int not null,
    USER_GROUP_ID not null
);

The AccessControlList defines which groups of users can access the elements. If I had 100 different groups I could define that users from 3 groups can access the row.

As there are potentially millions of business objects I thought that probably would be better to just create AccessControlLists rows for the different combinations. For example, if the user groups "A", "B" and "C" are included in a list with ID = 125 I could reuse this ACL for any business object with the same combination. If someone adds a group "D" to the ACL I would need to create a new list with a new ID. The ACLs would be then "lazy-created": everytime I need a new combination I create the new list. Of course I will need a "garbage collector" to eliminate lists that are not used anymore or I leave them as they could be created later on.

Ok, now I reduced the number of elements in the AccessControlList table from millions of objects to maybe some thousands. In any case I believe that the design will be very expensive for queries. A query for orders without any other filter than the ACL could be something like this:

select <columns> from Orders where ACL_ID IN (
      select ACL_ID from AccessControlList where USER_GROUP_ID IN (
           <select the groups for the current user>)
)

The user could potentially be a member in 100 different ACLs.

The question

Any experiences with a similar design? Any comments about some other way of implementing it? I would be very grateful for any ideas or comments. Thank you very much.


texts: SQL Server read-replica hosted on AWS with ephemeral storage

texts:
I have a temporary need (during transition from on-prem to AWS) to provide a high-performance SQL server instance for analytics.

I would like to purchase two SQL server enterprise instances. One instance will be a standard EC2 type backed by EBS volumes and will be the "primary" database for OLTP.

The second instance, I would like to be of an i3 type with local NVMe storage. I would like to use dynamic disks / storage spaces to put the local NVMe storage in RAID 0 and turn the instance in to a read replica (using availability groups) to do our analytics queries on.

By necessity, the read-replica will be a high end instance, while the primary DB will be a more reasonable instance size where we are less sensitive to delays.

The i3 instance type uses locally attached storage that is ephemeral. If the instance is stopped, fails, or has to move the DB storage will be lost. I would continue to maintain snapshots of the primary database, and I would use DNS to allow us to "fail back" to the primary database if the secondary database goes down for a period of time.

Is it feasible to run an availability group like this and to restore a new instance on new storage in case of a failure? I don't know a lot about availability groups. Recovery time and process is not a big concern here because this is a temporary solution and rebuilding the read-replica from backups is probably not too big of a deal. I just want to make sure I'm not missing anything obvious in a failure scenario. We will need 6 months to a year to restructure our infrastructure and data to better suit our needs.


texts:
I am using pgpool2 and PostgreSQL to get a HA of postgresql service. I have been working in this:

  .-----.                  .--------.
  |     |           W      |   DB   |
  | PGP |-----+----------->| MASTER |----.
  |     |     |            |        |    |
  `-----`     |            `--------`    | STREAMING REPLICATION
              |            .--------.    |
              |     R      |   DB   |    |
              +----------->| SLAVE1 |<---+
              |            |        |    |
              |            `--------`    |
              |            .--------.    |
              |     R      |   DB   |    |
              `----------->| SLAVE2 |<---`
                           |        |
                           `--------`
after failover of Master:

  .-----.                  .--------.
  |     |                  |   DB   |
  | PGP |-----+----        | MASTER |
  |     |     |            |  FAIL  |
  `-----`     |            `--------`
              |            .--------.
              |    W/R     |   DB   |
              +----------->| SLAVE1 |---.
              |            |        |   |
              |            `--------`   |
              |            .--------.   | STREAMING REPLICATION
              |     R      |   DB   |   |
              `----------->| SLAVE2 |<--`
                           |        |
                           `--------`

I have one pgpool (PGP) host, one Master (M) and two Slaves (SLAVE1 and SLAVE2) also called S1 and S2.

When M fail S1 is promoted to master and S2 point to it. And I keep doing my queries to PGP. Everything is ok.

But I have thinking and researching, without result, in how can I get a High Availability (HA) with my PGP machine. It is fail, all my system become critical.

But I want, after failover of Mater and after failover of PGP a second pgpool machine (PGP2) to get the HA of my pgpool service.

  .-----.                  .--------.
  |     |                  |   DB   |
  | PGP |-----+----        | MASTER |
  |     |     |            |  FAIL  |
  `-----`     |            `--------`
              |            .--------.
              |    W/R     |   DB   |
              +----------->| SLAVE1 |---.
              |            |        |   |
              |            `--------`   |
              |            .--------.   | STREAMING REPLICATION
              |     R      |   DB   |   |
              `----------->| SLAVE2 |<--`
                           |        |
                           `--------`
after failover:

  .-----.                  .--------.
  |     |                  |   DB   |
  | PGP |  |--+----        | MASTER |
  |     |  |   |           |  FAIL  |
  `-----`  |   |           `--------`
           |   |            .--------.
      >----+   |    W/R     |   DB   |
   .--|--.     +----------->| SLAVE1 |---.
   |     |     |            |        |   |
   | PGP2|     |            `--------`   |
   |     |     |            .--------.   | STREAMING REPLICATION
   `-----`     |      R     |   DB   |   |
               `----------->| SLAVE2 |<--`
                            |        |
                            `--------`

I have been looking in many posts and blogs but I always find the HA of PostgreSQL services using pgpool, but:

How do I get the HA of pgpool hosts?

I was studying something about watchdogs with pgpool, but I am still lost !!


texts: BULK INSERT runs fine in window, but fails inside sql job

texts:
I have two server (in the same network), Server A, is the web server, with IIS and all, Server B is the second server, the DB (MS SQL Server 2012), both Windows 2012 R2.

In the Server A, files are uploaded to this folder, uploaded-bills. That I have shared with \\web-server-01\uploaded-bills\.

I have given FULL ACCESS (in the security tab) to EVERYONE in that folder!

Now, this bulk insert runs in the SQL Server in Server B

INSERT INTO BillDetails (
    ItemID
    ,Qty
    ,Amount
    )
SELECT ItemID
    ,Qty
    ,Amount
FROM OPENROWSET(BULK '\\web-server-01\uploaded-bills\itemised-bill.csv', FIRSTROW = 2, FORMATFILE = 'D:\itemised-bill-format-file.fmt') AS BulkLoadFile;

Which runs perfectly fine. But if I create a job, to ran at a particular schedule, it always fails. When it is triggered automatically (due to time cycle) or I ran the job manually. When I view the history, it shows me this error:

The job failed.  The Job was invoked by Schedule 16 (InsertItemisedBillsEveryFiveMinutes).  The last step to run was step 1 (BulkInsert).,00:00:00,0,0,,,,0
05/29/2017 15:30:00,InvoiceUploadRawInsertJob,Error,1,SERVER-B,BillsUploadRawJob,BulkInsert,,Executed as user: NT SERVICE\SQLSERVERAGENT. Cannot bulk load because the file "\\web-server-01\uploaded-bills\itemised-bill.csv" could not be opened. Operating system error code 5(Access is denied.). [SQLSTATE 42000] (Error 4861).

As I mentioned, I have given everyone the whole access, even then the sql sever agent can't seem to access it. Also, I can access this folder fine in the windows explorer without any issues, and even the query executes fine when you run this query in a new query window. But for the love of Microsoft I can't figure out why the job is failing, and what should I do to fix that. Can anyone help me figure out what am I doing wrong?

Update
Update after comment and Remus's answer.

Yes Server A and Server B are in domain, in the same domain.

The SQL Server service (MSSQLSERVER) is running under a windows account name Expo (in the log on tab in the service configuration, in services.msc) amd that Expo account is available domain, so I can log in in Server A as well as Server B using that account's credentials.

The SQL Server Agent is running under (in the log on tab in the service configuration, in services.msc) NT Service\SQLSERVERAGENT.

As for Everyone, I grant it Read\Write in the file sharing dialog, under Sharing tab, and in the Security tab, I've added everyone with Full Control checkbox checked. (But I am guessing the Everyone on Server A could be different from the Everyone on Server B, I could be wrong though).

Please let me know if I could provide any more information to help sort out this.

Update 2
I've created the credentials and proxy, but in the job step dialog, Job Step Properties, the Run as option is disabled because the Type I have selected is Transact-SQL script (T-SQL) which is actually it's type. So, how do I specify the proxy account for this?


texts: Select distinct values of two columns as single

texts:
For starters: yes, I've seen there are lots of questions on combining two/multiple columns - but so far I neither found the answer to my question nor something to get me started trying further (which I did).

I have: a table consisting of two INT fields.
I want: a single column holding the distinct values of both fields.

Examle table:

| a | b |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 7 |
| 2 | 3 |
| 3 | 5 |
| 5 | 7 |

My desired result would in that case be:

| 1 |
| 2 |
| 3 |
| 5 |
| 7 |

Can this be done with a single query? What would that look like?

// EDIT: some side info (which I think is irrelevant, however, I don't want to hide it):
b is always greater than a.


texts: Mariadb multi-master replication with old gtids

texts:
I have a multi-master replication setup up and running well in MariaDB.  Each node has its own server-id and gtid_domain_id and is set up with its own master-slave and slave-master (two-way) replication.  All working well.

I'm running into an issue now though as servers come online and offline, the Gtid_Slave_Pos tends to get 'stuck'.  The first set of servers set up, the Gtid_Slave_Pos looked reasonable
Gtid_Slave_Pos: 0-1-45678,1-1-12312,2-2-45645,3-3-12312

Then, for hardware reasons, server-id 3 was taken offline and rebuilt.  When restoring the database from the master, the GTID changed.  The backup taken with mysqldump -v --master-data=2 --single-transaction --lock-tables=false production_db now has GTID 3-3 at a different value.

Attempting to reset the gtid_slave_pos, now throws off the other nodes replication since the GTIDs don't match.  My only workaround is to ditch server-id 3, and create a new server-id 4.  Which worked, but isn't really sustainable.

Here's the specific example:

mysqldump -v --master-data=2 --single-transaction --lock-tables=false apps_production > dump.sql
  Includes the GTID information:

-- SET GLOBAL gtid_slave_pos='9-9-137474,8-8-40418,7-7-82178,2-2-6743,1-1-4836503,0-1-2805613,3-3-12317,6-6-202460,5-5-40139';

Then querying select * from mysql.gtid_slave_pos; at the same time gives:

+-----------+--------+-----------+---------+
| domain_id | sub_id | server_id | seq_no  |
+-----------+--------+-----------+---------+
|         0 | 556017 |         1 | 2805613 |  Match
|         1 | 556023 |         1 | 4836503 |  Match
|         2 | 556019 |         2 |    6743 |  Match
|         3 | 556024 |         3 |   12317 |  Match
|         5 | 556020 |         5 |   20832 |
|         6 | 556021 |         6 |  202460 |  Match
|         9 | 556022 |         9 |  140055 |
+-----------+--------+-----------+---------+

Notice 5-5 and 9-9 are different, and basically prevent replication from restarting when I set the GTID for those servers.

Onto the Question:
Is there a safe/easy way to remove no longer used Gtid domain/servers?  In this case, I'd like to kill 3-3, and have the new slave start the gtid from scratch.  It looks like it's all stored in mysql.gtid_slave_pos, but I'm a bit terrified to touch that table.

Any ideas/suggestions?


texts:
I would like to create a backup of four databases on one SQL Server 2000 instance at the exact same moment in time.

How can I achieve it?

Creating different jobs with the same schedule date and time does apparently produces a difference in the moment when the backup is taken.


texts: HIVE SQL [Error 10025]: Expression not in GROUP BY key

texts:
Here is my SQL statement:

my_table includes 10+ columns (e.g, day, ip_address, user, request, etc), including strings and numbers. I want to GROUP by & HAVING based on column 'ip_address', if more than 20 records.

SELECT day, ip_address, user, request
FROM my_table
WHERE DAY = current_date()
GROUP BY ip_address
HAVING count(client_ip)>20

I got this error message

Error while compiling statement: FAILED: SemanticException [Error 10025]: Expression not in GROUP BY key xxx

But I need to keep all columns.


texts: ORA-00932 - Error when I try to filter all_triggers

texts:
I'm trying to execute the next query:

SELECT * FROM all_triggers t
WHERE UPPER(trigger_name) like UPPER('%ABC%')
AND UPPER(TRIGGER_BODY) LIKE '%DEF%';

But I get the following error:

ORA-00932: inconsistent data types: expected CHAR has been obtained LONG

How can I avoid this error?

Best regards.


texts: Do I need to update an index include columns every time I add a new column

texts:
I work on a team where we have millions of records of data, but not 100s of millions anything crazy.

We have had recent DB issues where we have hung connections due to Page Latch IOs, where the DB is loading data from disk into memory. This is crashing out DBs, and this has happened a few times in the past year.

A little bit about that way we have our data:

We have a "Carton" table and "Items" table. Items are contained inside cartons, so the carton is the parent entity and items are child (one to many relationship). These tables both have many attributes associated with them.

Okay, now moving forward. We look at the query optimizer and it tells us to create missing indexes. Here is an example:

CREATE INDEX missing_index_69 ON dbo.items (carton_number) INCLUDE
(item_number, entered_upc, item_desc, department, item_qty, item_cost_amt, cost_multi_qty,
item_retail_amt, retail_multi_qty, item_sell_amt, sell_multi_qty, mdse_div_nbr, sams_msde_cat_nbr,
base_retail_uom_cd, item_sale_type_co, scan_cnt, donation_item_id, price_embedded, weighted, cvp, gtin,
wm_discretion, category, sub_category, created_date, last_change_date, created_user_id,
last_change_user_id, replenished_by_unit, uom, deleted, qty_prev, qty_delta, bucket_id, bucket_color,
hazmat, hazwaste, cvp_value, claim_value, wmtd_claim_eligible, smart_label_id)

This issue arose when we did a outer fetch to get the items associated with the carton, and we looked for the items via the carton_number (Carton_number is primary key for Carton table, and is a Foreign-Key for the items, also with an index on it!)

Yes, we do Select All so that is why it is requesting to add all those fields, but I digress.

My problem is, this assumes that every single time I add a new column to the Item table, that I will have to update the index to add it in the include column. We have many queries where we search by different criteria (by smart_label_id, created_date, carton_state, etc.) For each one of those queries with indexes on them, do I really have to include all the columns, and update EACH ONE of those queries anytime I add a new column?? That seems extremely excessive, especially since we have many queries and adding a new column would require a LOT of refactoring.

Is this normal? It is my understanding that SQL server can handle billions of records with a lot of attributes, and I am surprised that we are running into this issue with the amount of data we have.


texts: MongoDB - Should I separate the journal and the data to different drives?

texts:
I have seen and heard from multiple sources that it can be a good idea, performance-wise, to write your db journal and your data files to separate disks.

What's the recommended way to do this (e.g. on EC2)?

If we write our data files to RAID (e.g. of EBS drives), where would most people write the journal to?
Should I just use completely separate (RAIDed) set of drives
Should I use a single non-RAID drive?
Should I just leave data and journal on the same RAID?


texts: Loop INSERT EXEC AT through linked servers

texts:
Relating to Inserting EXEC AT linked server into temp table

I am now in the midst of dynamically insert each result of dynamic sql to the temp table for each registered linked server. Below is my query:

SELECT
    @LinkedServer = @LinkedServer + ' INSERT INTO #eapb EXEC(@remotecmd) AT [' + name + '];'
FROM sys.servers s
WHERE server_id > 0;

Below is the error:

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@remotecmd".

Manually, I can achieve this through below script:

insert into #eapb
EXEC (@remotecmd) AT [server_1];
insert into #eapb
EXEC (@remotecmd) AT [server_2];

If I try to get @remotecmd outside like this,

SELECT
    @LinkedServer = @LinkedServer + ' INSERT INTO #eapb EXEC('+@remotecmd+') AT [' + name + '];'
FROM sys.servers s
WHERE server_id > 0;

I get below similar error:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DECLARE'.
Msg 102, Level 15, State 1, Line 52
Incorrect syntax near ')'.


texts:
Below is a stored proc to count the number of widgets made in a month. If no widgets are made, no records will exists.

The execution plan shows an INNER JOIN of the M and A tables, where in the statement I am doing a LEFT OUTER JOIN.

I would like to count the number of widgets made in a time frame, join that to a month table (Jan-Dec), and have the results shown in an SSRS report. The join is because I cannot COUNT data that isn't there.
Currently I am getting:

MonthName   Widget Count
February    2
March       3
April       4
May         6
June        5
July        4
August      6
September   2
October     4
November    1
December    2

I would like to have the months where no widgets were made included in the list.

Here is the code:

DECLARE @OName varchar(50)
DECLARE @Start_Date DATE
DECLARE @End_Date DATE

SET @OName = 'John'
SET @Start_Date = '01/01/2012'
SET @End_Date   = '12/31/2012'

SELECT   M.[MonthName]
    ,COUNT(A.[Widget_ID]) AS 'Widget Count'
FROM [Connector].dbo.[Months] AS M
        LEFT OUTER JOIN  [SERVER].[DATABASE].[dbo].[Widget] AS A
        ON MONTH(A.[Widget_Date]) = M.[MonthID]
WHERE     (A.[Operator_Name] LIKE '%'+ @OName +'%')
          AND A.[PlantID] = '00000001'
          AND (
               (A.Widget_Date >= @Start_Date AND @End_Date IS NULL)
            OR (A.Widget_Datet <= @End_Date AND @Start_Date IS NULL)
            OR (A.Widget_Date >= @Start_Date AND A.Widget_Date <= @End_Date)
            OR (@Start_Date IS NULL AND @End_Date IS NULL)

GROUP BY  M.[MonthName], M.MonthID
ORDER BY  M.[MonthID]

Below is the execution plan for this query.

::UPDATE1::
I just inserted the names of the months into this table. I cannot change the remote table.

CREATE TABLE [dbo].[Months](
    [MonthID] [smallint] IDENTITY(1,1) NOT NULL,
    [MonthName] [varchar](25) NOT NULL,
 CONSTRAINT [PK_Months] PRIMARY KEY CLUSTERED ([MonthID] ASC)
)
GO
INSERT INTO [dbo].[Months]
           ([MonthName])
     VALUES
           (<MonthName, varchar(25),>)
GO

The "widget" table has over 250 fields, that will take me much too long to anonymize.


texts: Copy Azure premium storage to standard storage for redundancy

texts: For our SQL servers we run backups onto a shared premium storage disk. Premium storage is locally redundant and I would like to copy these backups to a standard storage disk in order to take advantage of it's Geo-redundancy. What is the best way to do this?


texts: Get the job scripts out of a MSDB Backup

texts: Long Story Short... my Database server died a month ago. I restored everything but the MSDB as the tape backup failed. I reconstructed some jobs but some essential ones are missing. I finally found the MSDB backup in an old partition, but here's the gist... it isn't a .BAK file but the actual database and log. I already created a new Database in my instance and attached the MSDB (with different name obviously, MSDBJobs) but I have no idea whatsoever on how can I extract the jobs from this database. I don't want to replace the current ones as some are new and essential as well. Thanks for any help provided :)


texts:
I have the following JSON stored in a column in SQL Server 2016.  I am trying to figure out if it's possible to create a computed column for a nested array.

{
"global": {
    "commands": [{
        "commandname": "hostname xxx-yyy"
    }, {
        "commandname": "boot-start-marker"
    }]
},
"interfaces": [{
    "name": "Loopback0",
    "commands": [{
        "commandname": "description Psuedo Interface used for Management Purposes"
    }, {
        "commandname": "ip address 178.100.100.0 255.255.255.255"
    }, {
        "commandname": "no ip redirects"
    }, {
        "commandname": "no ip proxy-arp"
    }]
}, {
    "name": "GigabitEthernet1/1",
    "commands": [{
        "commandname": "no ip address"
    }, {
        "commandname": "shutdown"
    }]
}]
}

I created a computed column for the global object with the following command:

ALTER TABLE [MyTable]
ADD vGlobalCommands AS JSON_QUERY(configinfo, '$.global.commands')

And a full text index to go along with it and it works great.

Now I want to create the same for the $.interfaces.commands but I can't seem to figure out how to create the computed column.  I've tried this path but it returns null entries for the computed column.  Interfaces is an array and global is not, so I guess I just don't know the syntax to get this to work. Or maybe it's not possible?

I basically want to be able to query all the interfaces to see which ones CONTAIN command 'xyz'.  The above JSON is just an example, the real data is much larger, hence the need for a full text index.

Any ideas?

Update: According to this Stack Overflow Q & A it isn't possible to index nested arrays.

If anyone can think of a way to "redesign" the json or other ideas I would love to hear them.


texts: Automatically rollback two-phase transactions on session disconnect

texts:
I'm using two-phase commit in Postgres to reliably work with an external system (Amazon EC2).

I.e. I first do a PREPARE call, then call the external system. If the external system call fails for some reason, I simply rollback the prepared transaction.

It works fine. However, if I terminate my application while a distributed transaction is open, it will hang forever. Is it possible to automatically rollback 2PC transactions on session disconnection?


texts: Trigger: move deleted rows to archive table

texts:
I have a small (~10 rows) table called restrictions in my PostgreSQL database, where values are deleted and inserted on a daily basis.

I would like to have a table called restrictions_deleted, where every row that is deleted from restrictions will be stored automatically. Since restrictions has a serial id, there will be no duplicates.

How do I write such a trigger in PostgreSQL?


texts: How to see what's using TempDb space in Azure SQL DW?

texts:
I'm having problems with TempDb being full. Are there any system views I can see to diagnose what's using up TempDb and/or how much space is still available on it?

It would be awesome to also see the TempDb transaction size limit too if possible.


texts:
We have a SQL Server 2016 SP1 with max memory set to 24GB.
This server has a high numbers of compiles, only 10% of these compiles are from Ad-Hoc queries. So the newly compiled plans should be stored in the plan cache but the size of the plan cache is not increasing (approx 3.72GB).
I suspect that there is local memory pressure that lead to removal of plans from the cache. The plan cache pressure limit is 5GB. (75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory>64GB). When a cachestore reaches 75% of the pressure limit, plans should be removed from the cache. In my case 75% of 5 GB is 3.75GB. So it is plausible this is the cause of the high compiles.
Is there a way to measure (perfmon, extended events, ...) the removal from plans out of the cache? So I can be certain local memory pressure is really the cause of the high compiles?


texts:
I need to produce a daily extract on the individual changes to a dataset. So, there are 14 tables containing product and customer information and the extract must only show records from each table that are newly entered or records that have had any column updated.

What would be the best way to do this please?

We are using SQL Server 2008 R2, and develop in C#.

Thanks


texts: Prevent a stored procedure from being executed outside SSMS

texts:
I'm currently working on a stored procedure which modifies metadata for an application. This stored procedure can only be run when the application is offline. I didn't want developers to accidentally call it and break something, so I thought I was clever and inserted this block in the top of the stored procedure:

IF (APP_NAME() NOT LIKE '%SQL Server Management Studio%')
BEGIN
  RAISERROR(N'The stored procedure must be run interactively.', 10, -1, -1);
  RETURN;
END;

This works for normal executions, but when running over a local server group, the application name changes to ".Net SqlClient Data Provider".

I can't find another way to determine whether a query comes from SSMS. I can't discriminate based on user names because our applications use domain accounts. The host name isn't an option, either, because there are disaster scenarios in which accessing SSMS from the database server itself is the expected course of action (and I'd rather not surprise anyone during disaster recovery, even if it is unlikely).

Are there any better ways of determining if a query is coming from SSMS, either directly to one server or from a linked server group?


texts: Separate databases for front-end and back-end

texts:
The site I am working on has a lot of backend/cron activity, so there are between 5 and 15 queries being executed every second of the day. This can slow down page loads by a second or more.

I was thinking the best thing to do would be create two databases and synchronize them daily. However:

I do not know how to do this
I am worried synchronization will be slow & block db access. I really don't like the idea of taking the site offline, even for 10 minutes at 3 am.

So I am wondering:

Is separate databases the best solution for this problem, or would something else be better?
How would I synchronize the database without interfering too much with the 'user experience'?

Thanks very much!

BTW running PHP/MySQL on 'could' servers.


texts: MySQL 8.0, MacOS, create database issue?

texts:
when I use: CREATE DATABASE test;

  ERROR 3680 (HY000): Failed to create schema directory 'test' (errno: 2 - No such file or directory)


texts: Am I on the right track for a customer user login database design?

texts:
I am designing a back end database that will handle registration and login. I need to be able to deal with different account and authentication types.

The main issue here, that is different from other database design for user login is that a user could have a password to log in, a smart card, both or a combination of other, and I am unsure about null values etc.

Imagine a user registers their company online (on my website), the company details go into my company table, and their details go into my user table.

They then get the "account type" as company administrator (account type table).

That user (company administrator) can now log into their company account online, and register multiple users (in their company).

The issues I have are below, and hopefully my design solves these but I am not sure if I am on the right track.

Each user could have either a password, or a smart card to log in. I have the relationship for password and smart card tables but this means there will be many null objects. Either a user will have a password or a smart card.

Just to clarify my needs. When a user registers the company, they become a company administrator, but they could also set up other administrators and/or users, so that why I have them all in different tables.

I have added further tables as each user must have one or more forms of ID checked, along with being provided one or more access codes that will provide a range of access levels (physical and computer resource based)

I am mainly concerned that I have the right type of database normalization AND relationship design.

Lastly, the Smart Card table will have multiple smart card entries that I create, and I need to be able to assign 1 or more to a company, then, the company administrator can assign one or more of these to a user belonging to that company.

Are my relations OK for this?
What about null values until I have the smart cards assigned, is this OK?
Any help and guidance would be great.

And just to confirm, this will be a back end SQL database on my server, accessed by online web app AND end user desktop applications written in C#


texts: Why merge replication wants to replicate view before replicating related table?

texts:
We have a publication and a subscriber running on 2 SQL 2016 servers. Now the error that we are receiving during subscription initialization is:

Error messages:

  The schema script 'vw_BlackList_961.sch' could not be propagated to
  the subscriber. (Source: MSSQL_REPL, Error number:
  MSSQL_REPL-2147201001) Get help: http://help/MSSQL_REPL-2147201001
  Unable to replicate a view or function because the referenced objects
  or columns are not present on the Subscriber. (Source: MSSQL_REPL,
  Error number: MSSQL_REPL20164) Get help: http://help/MSSQL_REPL20164
  Invalid object name 'StaffBlacklistedSites'. (Source: MSSQLServer,
  Error number: 208) Get help: http://help/208

Where is quite obvious that SQL server wants first to push VIEW instead first to push TABLE. How that is even possible? How to remedy this issue?

Thanks,
Dejan


texts:
We have a process that creates a new sequence using dynamic sql.  We then use dynamic sql to get the next value from the sequence.  We have a lot of these dynamic sequences, but don't call any individual one a lot, but we call a lot of them.

In the plan cache it looks something like this:

(@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName1]
(@new int OUTPUT)SELECT @new = Next Value for dbo.[UniqueSequenceName2]
--etc

These are eating up a lot of our plan cache, and tend to only be called once before they're pushed out.  I have Optimize for Ad Hoc Workload turned on, but that hasn't changed the behavior.  Would turning on forced parametarization help reduce the load on the plan cache?  Is there another setting that might help?


texts: How do I upload an .mdf file to Azure Data Studio for Mac?

texts: How could I access multiple databases at once using Azure Data Studio and import an existing .mdf file as one of those database, with tables and data that had been stored in that .mdf file?


texts: SQL Server 2000 - 'Performance: Deadlock'

texts:
We had to restart our SQL Server today, we had made no changes to it.

When it came back up we immediately started getting this error from the server

  DATE/TIME:    2/27/2014 3:09:31 PM

  DESCRIPTION:  The SQL Server performance counter 'Number of
  Deadlocks/sec' (instance 'Database') of object 'SQLServer:Locks' is
  now above the threshold of 1.00 (the current value is 2.00).

  COMMENT:  (None)

  JOB RUN:  (None)

We ran the DBCC TRACEON (1204) command and have watched the log's but it's not reporting any deadlocks.

Any idea what could trigger this to just go off? We are getting the alert every minute yet can't find any actual deadlocks.

Edit: I should add that before this reboot we had never received this error

Edit 2: We used SQL Server Profiler as well to look for deadlocks, let it run for 5 minutes over which we received 5 error alerts and when we checked the details we had NO deadlocks found.

Edit 3: March 06/2014: Ran the query and it worked, but it reports what our other details have said that we have no locks we where still getting the error above the whole time.

Thanks again for all your help!

Edit 4: March 06/2014: I ran the query and here is a sampling of the result set, I will admit I am not exactly sure what I am looking at here, that is to say I am not sure if it shows me something that I can act on or not.

Edit 5: March 07/2014: Image below shows the Alert that generates this error all of a sudden.

Thanks


texts:
What is the best way to use Multi Tenant Database Architecture when all of the "Tenants" will be from the same company?

We are going to be using AWS and SQL Server for our backend system. We currently have 1000+ facilities that will end up using this new system and are steadily increasing by roughly 10-20% a year on acquiring new facilities that will have to be brought onto the system.

The debate we are currently having is if we should have a single massive database that handles/stores all of the transaction's for all of our facilities, or if we should scale out to a 1 database per facility architecture.

The biggest requirement we have is the ability for Global Accounts. A customer needs to be able to have a rental item with us in a Facility in Florida as well as a Facility in Washington.

The idea of how to set this up we have is this:

Have 1 Master Database that holds all of "global" data for the company. Meaning things like Accounts or config settings that are the same at every facility.
Have 1 SQL Server Instance per time zone we have facilities in (for less latency across the geo locations) and then have 1 database per facility in that time zone on the instance.
All of the individual Facility Databases would have the exact same schema/table structure.
We would also want to set up some sort of Streaming replication with ETL from all of the Databases into a Data Wharehouse that would ideally be located on AWS as well.

Has this type of setup been done before? Is this even considered a good approach?

Thanks!


texts: SQL Server 2012 - Full Text Search [Break word - Underscore]

texts:
Let's say I want to search for a list containing 'PS3':

SELECT * FROM [TABLE] WHERE CONTAINS(Title, 'PS3')

When the Title is proceeded by an underscore it will not return it.

Oblivion-PS3 -> Good
Oblivion PS3 -> Good
Oblivion_PS3 -> Not returning

I suppose the underscore is not a break word.

How can I fix it so that the underscore can be used as a break word?


texts: How to generate replication snapshot without locking tables

texts:
When I reinitialize subscription and generate new snapshot it locks all tables and makes the database unusable for a very long time (never waited it out).

Is there anyway to do this without locking?


texts: Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record

texts:
I have a table called projecthistory,
it is actually a view so I could take advantage of the Rank function to get a unique record number.

From this view I need to create a temp table that has a uniqueid field which would be calculated bases on these rules:

Rule 1 - The next sequential record for the id changes from one
project to another or
Rule 2 - If the id and project match for the next sequential record
, but the days between the end date of the current record and start
date of the next record for the is more than 7 days.

It would also consolidate the start and end dates appropriately for each group.

I believe this would be best done in a stored procedure that can call the data when needed, but I'm fairly new to stored procedures.

Here is an example of the table data, as well as the desired output

Projecthistory

╔════════╦══════╦═════════╦═══════════╦═══════════╗
║ Record ║  Id  ║ Project ║   Start   ║    End    ║
╠════════╬══════╬═════════╬═══════════╬═══════════╣
║      1 ║ 1111 ║ A       ║ 1/1/2016  ║ 1/7/2016  ║
║      2 ║ 1111 ║ A       ║ 1/10/2016 ║ 1/20/2016 ║
║      3 ║ 1111 ║ B       ║ 1/21/2016 ║ 1/25/2016 ║
║      4 ║ 1111 ║ A       ║ 1/26/2016 ║ NULL      ║
║      5 ║ 2222 ║ A       ║ 1/1/2016  ║ 2/1/2016  ║
║      6 ║ 2222 ║ B       ║ 2/2/2016  ║ NULL      ║
║      7 ║ 3333 ║ D       ║ 1/15/2016 ║ 2/1/2016  ║
║      8 ║ 4444 ║ B       ║ 2/10/2016 ║ NULL      ║
║      9 ║ 5555 ║ A       ║ 2/1/2016  ║ 2/5/2016  ║
║     10 ║ 5555 ║ A       ║ 2/20/2016 ║ 2/27/2016 ║
║     11 ║ 5555 ║ A       ║ 2/28/2016 ║ 3/10/2016 ║
║     12 ║ 5555 ║ A       ║ 3/11/2016 ║ NULL      ║
╚════════╩══════╩═════════╩═══════════╩═══════════╝

Desired output

╔════════╦══════╦═════════╦══════════════╦════════════╗
║ unique ║  Id  ║ Project ║ ProjectStart ║ ProjectEnd ║
╠════════╬══════╬═════════╬══════════════╬════════════╣
║ 1111A1 ║ 1111 ║ A       ║ 1/1/2016     ║ 1/20/2016  ║
║ 1111B1 ║ 1111 ║ B       ║ 1/21/2016    ║ 1/25/2016  ║
║ 1111A2 ║ 1111 ║ A       ║ 1/26/2016    ║ NULL       ║
║ 2222A1 ║ 2222 ║ A       ║ 1/1/2016     ║ 2/1/2016   ║
║ 2222B1 ║ 2222 ║ B       ║ 2/2/2016     ║ NULL       ║
║ 3333D1 ║ 3333 ║ D       ║ 1/15/2016    ║ 2/1/2016   ║
║ 4444B1 ║ 4444 ║ B       ║ 2/10/2016    ║ NULL       ║
║ 5555A1 ║ 5555 ║ A       ║ 2/1/2016     ║ 2/5/2016   ║
║ 5555A2 ║ 5555 ║ A       ║ 2/20/2016    ║ Null       ║
╚════════╩══════╩═════════╩══════════════╩════════════╝

Here are some notes about the desired output and some of
more difficult scenarios in this data.

Take note that records 1-2 became 1111A1,
record 3 becomes 1111b1,
record 4 became the second instance of that project for id 1111 so its uniqueid became 1111A2, be careful this doesn’t get lumped
in with records 1&2.

Record 9 become 55555A1 and because of the date gap
between records 9&10, record 10-12 became uniqueid 5555A2

I look forward to any solutions, feedback or questions. I’m
truly grateful in advance.


texts:
While inserting bulk data in sqlserver with query

BULK INSERT Table_name
FROM 'FilePath'
WITH
( FIELDTERMINATOR ',',
ROWTERMINATOR '\n')
;

I'm getting error

  "Incorrect syntax near '('."

I don't see an error in the above statement - could someone please help?


texts: Get maximum, minimum values across columns

texts:
I want to get the minimum and maximum across multiple columns in a row. I found various working queries using CROSS APPLY, UPIVOT and VALUES, e.g. like this one:

select column1, column2, code1,
       (qty2), (qty3), (qty4), (qty5),
       MinVal, MaxVal
from dummydata
    cross apply (select MIN(NULLIF (d ,0)) MinVal,
                        Max(d) MaxVal
                 FROM (VALUES (qty2),
                              (qty3),
                              (qty4),
                              (qty5)
                      ) AS a(d)
                )  A

These all are working in SQL Server 2005 or above, but I need a solution for SQL Server 2000 (database compatibility level 80). How can I do that?

DDL:

CREATE TABLE [dbo].[dummydata](
[column1] [nvarchar](255) NULL,
[column2] [nvarchar](255) NULL,
[code1] [nvarchar](255) NULL,
[qty2] [float] NULL,
[qty3] [float] NULL,
.......
[qty20] [float] NULL)

Query output:


texts: How to promote hidden mongo node to primary?

texts: We are trying to implement DR setup for our mongodb replica set and currently three nodes in DR are configured to run as hidden nodes. We are trying to test failover to DR which will require promoting hidden node to primary and secondary respectively. What is the best way to achieve this?


texts: Best way to implement concurrent table based queue

texts:
I have a table in MySQL that represents a queue of links to be processed. The links are processed by an external app, one by one, and deleted in the end. This is a high volume queue and I have multiple instances of the processing app, spread across several servers.

How can I ensure that each record is picked by one app only? Is there a way to flag/lock the record?

Right now, to avoid two or more picking up the same link, I'm allowing each instance only to pick up a certain set of records (based in the MOD of their ID), but this is not a transparent way to increase queue processing speed just by adding new instances.


texts: Delete with join and condition (3 tables) - MySQL

texts:
Need some assistance on MySQL delete using join with condition (3 tables).

snote (child table)
--------
snote_id
bnote_id

bnote (parent table of snote)
------
bnote_id
note_id

note (parent table of bnote)
----
note_id

Want to delete all snote records with bnote_id=10 and note_id=5


texts:
I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on multiple columns first of which is a date column. Workers normally work with rows from different dates. The unique index column value ranges should not overlap (they were overlapping before, but I changed the index to have date column as the first one).

SHOW ENGINE INNODB STATUS\G shows that 1st transaction (active for 20secs) is waiting for unique_rows lock (mode S), while the 2nd transaction (active for 11secs) holds unique_rows lock (mode X) and is waiting for auto-inc table lock.

How far I understand this: when 1st transaction starts, it acquires auto-inc table lock (I use the default auto-inc lock mode = 1), then it acquires some gap locks on the unique_rows index. The 2nd transaction could be acquiring gap locks on the unique_rows index, but they should not conflict with the locks acquired by 1st transaction.

Having written the question down, I suspect my assumptions about gap locks are wrong.

Another thing is I always have a DELETE FROM WHERE query in the same transaction just before INSERT .. SELECT, but that DELETE query has never been mentioned in deadlock logs.

The whole process works like this: 1) delete a date range from the destination table, 2) insert a date range in the destination table by selecting data from some other table.


texts: How to get pg_archivecleanup on Amazon Linux 2014.03?

texts:
I have an Amazon EC2 instance based on Amazon Linux AMI 2014.03. I installed PostresSQL from their repos:

yum install postgresql-libs postgresql postgresql-server postgresql-devel

I've read about Amazon's unexpected upgrade from 9.1 to 9.2, so I added this line to the end of /etc/yum.conf so hopefully I can control any upgrades:

exclude=postgresql*

So far so good. Now I'm setting up continuous archiving and I want to run pg_archivecleanup.  However I can't find the postgresql-contrib package on the Amazon distro, and it's not listed in the packages:

http://aws.amazon.com/amazon-linux-ami/2014.03-packages/

Is there someplace else to get that pre-compiled?  I tried the following to compile it myself:

sudo su -
cd /usr/local/src
wget http://ftp.postgresql.org/pub/source/v9.2.7/postgresql-9.2.7.tar.gz
tar -xzf postgresql-9.2.7.tar.gz
cd /usr/local/src/postgresql-9.2.7
./configure --without-readline # fails until you add --without-readline
cd /usr/local/src/postgresql-9.2.7/contrib/pg_archivecleanup
gmake
gmake install

but the gmake fails:

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard pg_archivecleanup.o  -L../../src/port -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags  -lpgport -lz -lcrypt -ldl -lm  -o pg_archivecleanup
/usr/bin/ld: cannot find -lpgport
collect2: error: ld returned 1 exit status
gmake: *** [pg_archivecleanup] Error 1

Trying to figure out the -lpgport issue led me to some very obscure bug reports here and here. Tracking bugs in Linux itself seems a little extreme just to compile one file.

What is my best option?

Find another source for the contrib package - where?
Find a way to compile this module - how?
Ditch Amazon Linux and start from scratch with an Ubuntu AMI.
Forget pg_archivecleanup and write a script to delete old WAL files.

EDIT

I'm using Elastic Beanstalk with some pretty tight integration to the Amazon AMI. I'm sure it could be made to work with another AMI, but I expect there would be other gotchas.  What about this option, adapted from this post:

# Change to home directory to download the software
cd ~/
# Get the right postgresql package (Redhat 64 Bit)
wget http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
# Install the package
sudo rpm -ivh pgdg-redhat92-9.2-7.noarch.rpm
sudo yum install postgresql92-contrib

Does that give me the best of both worlds, an Amazon AMI with an official Postgres repo?  If necessary, I could completely re-install Postgres from that RHEL repo.


texts: archive_command: Can hardlink (ln) be used instead of cp?

texts:
We use Postgres 9.6, with 10+TB, with a multi-node cluster setup, managed by Patroni. The WAL archives and backups are managed by home grown tool pgrsync.
The archive_command was initially set to cp %p /archives/%f. There is a background job (pgrsync) that pushes the archives to S3 periodically. The volume of WAL archives was higher (avg around 200 WAL files/min, with the peak being 500/min). The cp also adds to the Disk IO bandwidth, which is precious for us in a cloud environment.
We are looking to optimise this in the application. Also, I noticed that in pg_xlog folder that several files were hard link to other WAL files. (This part is not understood fully, how could Postgres internally have one WAL archive being a link to another -- it is unlikely that so many transactions could be repeated exactly after some time).
Anyway, as an optimisation exercise, we set the archive_command to ln %p /archives/%f. This reduces the disk IO, we are just adding one more link to the same file. When we are done copying to S3, the link is removed and the OS manages deleting the actual file, when Postgres also frees it. Looks good on paper. Except one problem: If Postgres writes to the same file (with the same inode) after completing the archive_command, then we are in a mess. Please refer postgres: WAL ends before end of online backup where we are seeing random WAL corruption and we don't know if using ln caused this.
Question: Is it safe to use ln (hardlink) instead of cp in archive_command?

WAL files are default 16MB. We have a plan to increase it, but I suspect it will reduce the number of files, but the size and IO will likely remain the same. Not likely to help, isn't it?
Backup WAL from a standby node is a good idea - or the home grown consumer for archiving. Or even simply scp to another server, to reduce disk write IO on Postgres server. Since anything could be standby or master at any time, managing who will actually push files could be slightly complex. So, I would either go for archiving consumer or scp to non-Postgres server.
Reg not invoking pgrsync directly, We did this initially. pgrsync would then have to handle each file individually in-series (compress and push to S3). This was slowing down the ability to handle 500 WAL files (or 500*16MB WAL bytes). Copying to another location gives pgrsync the ability to push files in parallel and thus the ability to handle 500 WAL files per min. I was avoiding the need for another server (rsync / scp to), but it looks like that is a better approach now.


texts: Min no. of replicas to set up SQL Server Always ON over a Failover Cluster on a Windows Cluster

texts:
AO: Always ON
FCI: Failover Cluster Instance
WSFC: Windows Server Failover Cluster

I'm scratching the surface of AO. I currently have one SQL 2012 FCI on a WSFC. Which means one Active node at a given time. I cannot set up an AO with this, can I? Logically speaking I need “at least two” active (up & running) SQL Server instances to set up. And in Microsoft's example they used a total of 5 nodes, and the minimum would be 3 for a AO on FCI + WSFC right?

Here is what my FCI looks like.

There is no point in having AO when only 1 instance of SQL is running at a given time. Right ?

If yes what would I need to set up AO?

A new Node in the Cluster or
Any other Stand alone SQL Should(same
version) do as well?


texts: How to start my 9.2 data directory using an 8.4 server?

texts:
I have an old version of postgres (8.4.13) that doesn't run for various reasons. I plan to move the database files in there to a new install of postgres 9.3. How would I go about doing this? For one I can not start up postgres when I run:

postgres -D /var/lib/pgsql/9.2/data

As I get :

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 8.4.13.

The easiest route would be to figuring out how to run this server but if not how would I locate the db files in it?

EDIT:

How this actually happened was last week I wanted to intiliaze postgres server commands so I could easily stop and restart the server since my knowledge of postgresql is pretty limited. I ended up installing postgresql-server for RHEL and it was initially working fine. The commands weren't working so I forgot about it. The databases were working fine too at that time. I log on today and to my shock the commands were working but it also wiped the dbs. So my presumption is it installed a completely new server that could be accessed via the commands.

Somehow this tampered whatever settings I had.

Typing postgresql --version gives me version 8.4.13.
If I can only start it now by pointing postgres -D to the correct pgsql.

There are two pgsql dirs:

/var/lib/pgsql/9.2/data
/var/lib/pgsql/data

The latter seems to be the newest install of postgresql as the pg_hba.conf files are unedited.

I used to be able to start the 8.4.13 server with 9.2 it would just give me a WARNING: Some features might not be available. I don't know why installing postgresql-server would change this now. As they are separate dirs.


texts: Log Shipping: Unable to change from Standby mode to No Recovery mode

texts: I have tried to change the mode from standby to no recovery in log shipping environment. But the restore job fails automatically. And it did not change to restoring mode. Can anyone help me to resolve this?


texts: Accidental deletes at SQL Server Subscriber

texts:
Unfortunately, this happened twice in 2 weeks on our most critical db server; just before Christmas! Instead of running a delete script at the publisher, the script was run at the subscriber, which broke replication!

I'm exploring options to avoid this scenario in future. This is a massive DataWarehouse. Lot of transactions. Everyone and his brother in the organization has access. Any more such mistakes might trigger a resume-generating-event.

Here are some of my notes:

  Maybe I can use the deny_db_writer role at the subscriber. But I believe sysadmins will still be able to run the deletes.
  INSTEAD OF DELETE TRIGGERS could be an option; however, we have hundreds of tables, that needs these triggers. I don't know if the
  genuine deletes from replication will invoke this trigger or not. (we
  prefer NOT to. There's something called NOT FOR REPLICATION for
  triggers; we might have to use that)
  Can this be achieved by a DDL trigger? what could be the impact on the subscriber?

Has anyone done anything like this? Thank you so much for any suggestions.


texts: How to prevent a record from being affected by the ORDER BY RAND() function?

texts:
I am building an online exam site using php and mysql. It's just for MCQ type questions.
Here is my database structure:
1) QUESTIONS ( Exam_ID, Questions_ID, Questions_Des)

2) OPTIONS ( Question_ID, Option, Options_ID, Atype)

1) TRACK (User_ID, Exam_ID, Question_ID)

The table TRACK keeps a record of which questions have been answered already by which students.

The field Atype in the OPTIONS table indicates if the answer option is STATIC or null.

I want the questions randomized for each student without repetition and I achieved that with:

"SELECT * FROM questions WHERE question_ID NOT IN (SELECT question_ID FROM track where User_ID = '<user_id>') ORDER BY RAND() LIMIT 1"

This works perfectly.

And I also randomize the various answer options so that no two students have the same answer pattern for a question. The code i used is:

SELECT * FROM options WHERE question_ID = '<question_ID>' ORDER BY RAND()

A problem arises when a question is of this form:
Question: which of the following are even numbers?
A) 1
B) 3
C) 7
D) None of the above.

When this randomizes, option D could now be option A which makes no sense since there aren't any answers above it. That is why I added another field to the options table known as Atype which indicates if the answer is static or not.

How can I then query the options such that any answer option which is not STATIC won't be affected by the ORDER BY RAND() function so that when it randomizes option A, B and C will randomize but not Option D which has been marked as static. Hope it is clearer now.


texts: While testing our workload on SQL Server 2019 we experience much higher CPU utilization compare to the same test on SQL Server 2014

texts:
We are getting ready to upgrade our SQL Server 2014 systems to SQL Server 2019. As part of our due diligence we created a workload which we are testing against both systems. What we have observed was the following:

The query performance on average is about 50% faster on SQL Server 2019 - good!
The reads are about 40% less on SQL 2019 - good!
The CPU utilization is about 30% higher on average - not good.
This last point is what causes our concern. Does this means that we have to plan to increase our CPU capacity as part of our migration to SQL Server 2019?

To describe what we are seeing from slightly different angle: when we attempt to ramp up our workload by pushing higher number queries/sec, we are seeing lower throughput on SQL Server 2019 because we max out CPU earlier and start seeing errors as a result.
I hope this makes sense and I wonder if the others had similar experience?


texts: Normalizing data for joins, dedicated columns, a view or.. something else?

texts:
As an example, i have the following tables

USERS, USER_JOBS and USER_HOUSES

USER_JOBS keeps a list of jobs for a given user_id

USER_HOUSES keeps a list of houses for a given user_id

the common use case is to see a user, his current job and his current house.

it looks like this :

To get the result i want i would need to join USERS with USER_JOBS and USER_HOUSES using a complex query (matching the latest seq in jobs and houses by last_update column)

here's one for just joining with USER_JOBS

SELECT USERS.USER_ID,
       USERS.DESCRIPTION,
       USER_JOBS.JOB_ID
  FROM USERS, USER_JOBS
 WHERE USER_JOBS.USER_ID = USERS.USER_ID
   AND USER_JOBS.SEQ = (SELECT B.SEQ
                          FROM USER_JOBS B
                         WHERE B.USER_ID = USERS.USER_ID
                           AND B.LAST_UPDATE = (SELECT MAX(C.LAST_UPDATE)
                                                  FROM USER_JOBS C
                                                 WHERE C.USER_ID = USERS.USER_ID))

(not even sure if that works, either way.. not fun)

I'm trying to find the best alternative, since there might be a lot of these

alternative A:

I create columns that make this a straight join to the corresponding SEQ in either table
the actually work happens when those tables are written to (either via trigger, or manually) to update the main tables 'pointers' to the latest seq.

so harder write (although not that bad), much easier read.

PROS:
 * easier queries
 *
alternative B:

create a view that does the complex SQL of joining


texts: Oracle: Delete data from one table based on starttime and finishtime column of another table

texts:
I want to delete all the data from Table1 which is getting inserted between specific timeframe. And that timeframe value I am getting from Table2 columns starttime and finishtime and these two tables have no relations between them. So for doing this, I wrote the below query:

delete from table1 where
SCN_TO_TIMESTAMP(ora_rowscn)>=(select starttime from table2 where taskid=10502)  and
SCN_TO_TIMESTAMP(ora_rowscn)<=(select finishtime from table2 where taskid=10502)

The above query is working but it is very slow. So, I have tried another query which is not working

delete from table1 where
SCN_TO_TIMESTAMP(ora_rowscn) in
(select starttime, finishtime from table2 where taskid=10502);

I have searched on Stack Overflow, but I didn't get anything. Could anyone please help me in making a better query?


texts:
I made some research on MySQL performance.
After doing some tests and verifyed the improvement, (thank you guys)
I end up asking myself:
"Is there any formula or metric between groups of variables?"

I mean, given a server with X GB of RAM and X2 processor

innodb_additional_mem_pool_size + innodb_buffer_pool_size <= Y% of X

innodb_log_file_size + innodb_log_buffer_size <=  Z% of X

and so on...

Is there any specific proportion between this group of variables?