texts: ORA-01861 - Input seems ok and FX/FM modifiers not used

texts:
Using SQL Developer 4, I am getting an ORA-01861 exception when I do the following (and BTW, I ticked 'Ignore NLS Definitions'):

alter session set nls_date_format = 'DD-MON-YY';
alter session set nls_timestamp_format = 'DD-MON-YY HH24:MI:SS';

select value from lpp_n3_qas.mes_piece_char pc
join lpp_n3_qas.mes_char_def cd on cd.mes_char_def_id = pc.mes_char_def_id
where
  cd.char_name = 'DispatchTimestamp' and
  value is not null and
  to_timestamp(value, 'DD/MM/YYYY HH24:MI:SS') >= '09-OCT-13' and
  to_timestamp(value, 'DD/MM/YYYY HH24:MI:SS') < '16-OCT-13';

----
01862. 00000 -  "the numeric value does not match the length of the format item"
*Cause:    When the FX and FM format codes are specified for an input date,
           then the number of digits must be exactly the number specified by the
           format code.  For example, 9 will not match the format specifier DD but
           09 will.
*Action:   Correct the input date or turn off the FX or FM format specifier
           in the format string.

But when I do this:

select to_timestamp(value, 'DD/MM/YYYY HH24:MI:SS') as Dispatch_Timestamp from lpp_n3_qas.mes_piece_char pc
join lpp_n3_qas.mes_char_def cd on cd.mes_char_def_id = pc.mes_char_def_id
where
  cd.char_name = 'DispatchTimestamp' and
  value is not null;

DISPATCH_TIMESTAMP
------------------
09-OCT-13 11:20:06
09-OCT-13 11:20:06
11-OCT-13 16:33:13
11-OCT-13 16:44:25
15-OCT-13 11:23:54

I get no exceptions at all and I used the exact same to_timestamp() conversion.

In addition, if I take one record from the dataset and replace 'value' with it in the first query's where clause I also get no exceptions. The problem doesn't seem to be a bad input timestamp and I don't have any idea about what is going on. Can someone please shed some light?


texts: Commonly Used Acronyms by Database Administrators

texts:
What are the most commonly used acronyms among database administrators and what are their correlated meanings?

This is for the community and those searching for meanings of commonly used terms and acronyms when working with databases, etc.


texts:
I've been stuck with this problem, so I'm looking for help now.

I'm trying to get a .cvs file from a query with PL/SQL. I'm using a cursor and UTL_FILE to achieve that. The problem is that I have to pass all the column names to UTL_FILE.PUT_LINE(), and there are some columns that are set dynamically, so I don't know how to pass those columns to UTL_FILE.

I tried to build a string with those columns, but it doesn't work.
Example:

    FOR pointer IN (SELECT column1, column2, **piece_of_query_with_dynamic_columns**, column3 FROM table1)

    LOOP
      UTL_FILE.PUT_LINE(v_file, pointer.column1|| ',' ||pointer.column2|| ',' ||**string_with_dynamic_columns**|| ',' ||pointer.column3);
    END LOOP;
 UTL_FILE.FCLOSE(v_file);

The query works well and, except for those dynamic columns, I can get the .cvs file.

Any suggestions?

I hope i explained myself well


texts: To what extent can we monitor jobs/suspended queries as DBO

texts:
Short Background:

I work for company A, at company A we have a DBA that should not be. When we are tasked with items for troubleshooting which require quick turn around, it generally takes quite a bit longer than it should.

For this reason, we asked him to grant me DBO access to certain servers (Biztalk SQL servers). I'm the one who is dropping files and monitoring this stuff, sometimes overnight, and lately we have been experiencing system slow down.

So with these issues with Biztalk, I've gone into the SQL server and tried to dig around but it seems most of them require higher priveleges then I have. I've looked for suspended queries, killed them, but as DBO, I was wondering what's the extent I can monitor and find out about the jobs currently running/suspended.

I am familiar with exec sp_who2 and kill %% but other than that I've been hitting a wall...

My question:

Are there any useful features I can utilize as DBO that will assist in identifying the cause of these suspended queries? Other than who and who2?

Any homegrown queries you folks have wrote for identifying such causes of these errors?

Any help is much appreciated!


texts: Sybase - How to get logsegments off my data devices?

texts:
I'm running Sybase ASE 15.7 and coming up to speed to clean up a database server installed by a non-DBA.

I have a database that has the following device configuration:

I want to have data1 and tlogs1 devices hosting only Data and Log respectively so that I don't encounter any anomalies in the event of a database restore due to a device failure.

How can I correct this configuration?

Note: The remove button is always grayed out.


texts: SQL Server Cumulative Updates - not visible in Windows Update

texts:
I just installed all possible Windows updates on WS2008R2/SQL2012 instance. When I run

SELECT @@VERSION

I get

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

According to

  http://sqlserverbuilds.blogspot.com/

and Microsoft websites, there are 4 Cumulative Updates available for SQL Server 2012 SP2 and the newest build is 11.00.9000.

Why didn't Windows Update get Cumulative Updates too? Should I download and install them manually?


texts: Best way to design a database and table to keep records of changes?

texts:
I need to setup a history feature on a project to keep track of prior changes.

Let's say I have two tables right now:

NOTES TABLE (id, userid, submissionid, message)

SUBMISSIONS TABLE (id, name, userid, filepath)

Example: I have a row in notes and the user wants to change the message. I want to keep track of it's state before the change and after the change.

What would be the best approach to setting up a column in each of these tables which will say if an item is "old." 0 if active OR 1 if deleted/invisible.

I also want to create a history (AUDIT TRAIL) table which holds the id of the prior state, the id of the new state, which table these id's relate to?


texts: Relation does not exist while scanning schema

texts:
My Postgres database is getting slow while executing some function. But another database of identical design and with more data is much faster than the first one. I have recently migrated data from MS SQL to PostgreSQL. So I have checked if there is any problem from following queries:

SELECT
  relname                                               AS TableName,
  to_char(seq_scan, '999,999,999,999')                  AS TotalSeqScan,
  to_char(idx_scan, '999,999,999,999')                  AS TotalIndexScan,
  to_char(n_live_tup, '999,999,999,999')                AS TableRows,
  pg_size_pretty(pg_relation_size(relname :: regclass)) AS TableSize
FROM pg_stat_all_tables
WHERE schemaname = 'loan'
      AND 50 * seq_scan > idx_scan -- more then 2%
      AND n_live_tup > 10000
      AND pg_relation_size(relname :: regclass) > 5000000
ORDER BY relname ASC;

And I got the result:

ERROR:  relation "mv_transaction_view" does not exist
SQL state: 42P01

What is the solution for this? I have a materialized view named mv_transaction_view.


texts: Selecting a nonaggregated column which is not functionally dependent on other columns

texts:
I'm using MySQL 5.7 and have run in to this dreaded error.  Changing the sql_mode is not an option.
SQL Fiddle:
http://sqlfiddle.com/#!9/18f79b/1/0
Expected output is {"This": "row"}.
I thought changing the SELECT clause to item_number, MAX(l.datetime), json would fix it but alas no.  And the demonstration in 5.6 shows why this mode was enabled.
Edit: Fiddle updated to have multiple items


texts: What does the word "SARGable" really mean?

texts:
SQL Server users use the term "sargable". I'm wondering if there is an objective implementation-agnostic timeless definition for "sargable."

For instance, WHERE foo LIKE '%bar%' is said by many to be not sargable, but some RDBMSs are able to use indexes on such queries. What then does "not sargable" mean?

Other References

What makes a SQL statement sargable?


texts: Can I drop an overlapping index if the larger index is unique

texts:
I have 2 nonclustered indexes:

IX1 = [col1]
IX2 = [col1],[col2] (unique).

I want to drop the IX1 index as the optimizer can stop use IX2 for the first column and stop using when it has satisfied the query in question, so should not hurt performance. But I had a nagging thought in my head, it might not be able to use the 2nd index if it was marked as unique?

Both columns are integers datatype, col1 is a FK


texts: SQL Server 2012 Client Statistics Incorrect

texts:
When I run my query, I get the following Client Statistics Results :-

Number of SELECT statements = 2

The query being ran is only 1 SELECT statement :-

SELECT TOP 1000000 * FROM [DATABASE].[SCHEMA].[TABLE]

My client statistics returned on the server claiming 2 SELECT statements looks like this :-

Can someone explain why it would show as 2 SELECT statements ?

I have also used SQL Profiler to trace this as its being ran, and there was no second query


texts: SQL Server 2014 only using 10 percent of its available memory?

texts:
Hoping someone can weigh in here. I have a 2014 developer edition box that doesn't seem to be performing well. I currently have 128 GBs of RAM allocated to the box and 120 GBs maximum allocated inside of SQL itself.
When I look at the server itself, Windows recognizes it and so does SQL, but it never seems to jump up above 10 percent. All of the other SQL boxes I've built have memory at least sitting at 90 percent or so.
It's running a QA environment that sees maybe 10 users on it at one given time, but I would expect our memory to be using a lot more than it is currently. Any ideas?


texts:
I have a transaction table called questionnaire and a question lookup table called question_codes.

questionnaire

qst_id -  number (primary key)
question_type -  number (foreign key)
question_category -  number (foreign key)

question_codes

code_id - number (primary key)
code_name - String
code_desc - String

Both tables are linked with qst_id and question_type,question_category are defined in question_codes lookup tables

I have a requirement to add a new column called product_category in questionnaire table and corresponding id,name and desc will be added to question_codes. product_category has a sub category column called product_type which will be displayed in questionnaire when product category is selected.

My question is do I need to create a new table to map product_category
and product_type? Or can I use question_codes` table as lookup table?

Edit 1

I have table description and a few sample records in SQLFiddle


texts:
I am trying to simulate a solution for a deadlock issue happened on our production server.

Run this to create a table to be used in 2 and 3.

IF (SELECT OBJECT_ID('dbo.MyProduct') ) IS NOT NULL
DROP TABLE dbo.MyProduct;
GO
CREATE TABLE dbo.MyProduct (ProductID INT ,Price MONEY);
INSERT INTO dbo.MyProduct VALUES (1, 15.0)

select * from MyProduct

create a proc. I set DEADLOCK_PRIORITY LOW to ensure the transaction will be rolled back in case of deadlock.

create PROCEDURE [dbo].[SP_xxx]
as
DECLARE @Price INT

SET DEADLOCK_PRIORITY LOW

BEGIN TRAN NormailizePrice

    SELECT @Price = Price
    FROM dbo.MyProduct AS mp WITH(REPEATABLEREAD)
    WHERE mp.ProductID = 1

    --Allow transaction 2 to execute*/
    WAITFOR DELAY '00:00:10'

    IF @Price > 10
    UPDATE dbo.MyProduct
    SET Price = Price - 10
    WHERE ProductID = 1
COMMIT

the other session. set deadlock priority to high to ensure the transaction will continue in case of deadlock.

--Transaction 2 from Connection 2
SET DEADLOCK_PRIORITY HIGH

BEGIN TRAN ApplyDiscount
UPDATE dbo.MyProduct
SET Price = Price * 0.6 --Discount 40%
WHERE Price > 10
COMMIT

Testing code to run the proc in 2. It is supposed to rerun the proc in case of deadlock.

DECLARE
  @retry TINYINT
  ,@retrymax TINYINT -- 3 times
  ,@retrycount TINYINT

SELECT
  @retry = 1
  ,@retrymax = 2
  ,@retrycount = 0

WHILE @retry = 1 AND @retrycount <= @retrymax BEGIN
    SET @retry = 0
    BEGIN TRY
        PRINT 'RetryCount: ' + CAST(@retrycount AS VARCHAR(5))
        exec SP_xxx
    END TRY
    BEGIN CATCH
        PRINT 'ERROR_NUMBER: ' + CAST(ERROR_NUMBER() AS VARCHAR(5))
        PRINT ERROR_MESSAGE()
        IF (ERROR_NUMBER() = 1205) BEGIN
            SET @retrycount = @retrycount + 1
            SET @retry = 1
        END
    END CATCH
END

Now first run 4, then run 3 in 10 seconds. The expected result is a deadlock will happen and 3 will finish and 4 will retry after 3 is finished. But I got this: Why?

RetryCount: 0

ERROR_NUMBER: 1205

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

RetryCount: 1

ERROR_NUMBER: 3930

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

Msg 3998, Level 16, State 1, Line 1

Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.


texts:
I'm writing an ssis package that imports a number of access databases nightly.  Each database has the potential to have a slightly different schema. Since I'm creating the tables than doing a direct table insert using execute sql tasks inside of a for each loop container the schema differences cause errors.  I've upped the max error count so that the package can take errors and continue running.  This leaves me with some databases that have incomplete data which I would prefer to drop.

I attempted to make an event handler that would drop the database; however, at the point that the event handler is called the database is still in use.  Is there another way that I could drop the database if there is an error?


texts:
Looking at Oracle's docs on the Function Index, what's the meaning of rivers in the following?

CREATE INDEX area_index ON rivers (area(geo));

SELECT id, geo, area(geo), desc
     FROM rivers
     WHERE Area(geo) >5000;

What's the importance of rivers with respect to this query?

Could dual have just been used?


texts: Storing point data for linestring in MySQL

texts:
Ok, i am have a need to store data on SOME points of a poly line but not others.

The current way I an doing it is i have 3 tables

line:
lineID INT AUTO INCREMENT
lineType INT
forkFrom INT

linePoint:
pointID INT AUTO INCREMENT
lineID INT
lat DECIMAL
lng DECIMAL

pointMarker:
pointID INT
someDataFields...

but a line, lets say it has 20 points, will take up 20 rows in a table.

I would like to have a line table with a linestring field, removing the need for the linePoints table. but then I am left with no way to reference a specific point in a line.

Example: the line is a water pipe, it has a lot of points as it go's around corners and such, on some of these points, it has a tap or a water meter, how do I record this?

do I use the lat/lng as an index and then in my code somehow check for orphaned points if the line is modified? or is there a better way to do this?

also some lines are "forks" or "branches" from a main one, for this I am currently using the "forkFrom" column to store the ID of the point the "fork" would start from. how would i do this with a lineString? is that what a multiLineString is for?

I am having some problems with my front end code that deals with lines so now is a good time to change the the way the data is stored, as I will likely have to re write a large chunk of code that deals with it anyway. may as well write a few more lines and get the database side of things sorted as well, instead of fixing whats broken now, then throwing everything out the window when I end up changing the database.


texts: Is C collation suitable for my Postgres database?

texts:
I am working with 500m rows (120GB) in Postgres 9.4, and I am trying to make SELECT statements as fast as possible. I am less concerned about the speed of INSERT and UPDATE, because my data only changes once a quarter and I'll probably use a backup database and switch over when that happens.

I'm working on a Debian Wheezy server, with 32GB of RAM and 320GB of solid-state. I have set maintenance_work_mem to 16GB while setting up the database, and shared_buffers to 12GB.

I have found that creating indexes is slow, and even with indexes, SELECT queries are still quite slow. To help this, I've been recommended to use the C collation for the database.

I've been reading through the collation documentation, but it doesn't answer all my questions. So here's what I want to do with my database:

Support fast SELECT * FROM mytable WHERE col1='020406AAA' queries.
Support fast SELECT * FROM mytable WHERE col1 LIKE '02%' queries.
Allow non-ASCII characters in some columns (e.g. Ménière's disease). This isn't absolutely critical, though - I could convert these to ASCII if it would really help for speed.
Add the PostGIS extensions to the database.

Are any of these contra-indications for using the C collation?

I'm expecting all the columns where I want to use LIKE queries to consist of digits and uppercase letters only.

The data is a UK dataset, in English, for UK-only users. So I'm not expecting to have to add internationalization.


texts: Find who will change a record in oracle DB

texts: Someone is updating a record in a custom Oracle table, is there a way to find who is updating the record and or at least the table?


texts:
I'm noob when it's come to DBMS so i wrote a query on couple of relations and i want to know what's the best way to do it because i don't feel good about my query.

Here are the relations:

CREATE TABLE "Product" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" text,
  "unit_price" float8,
  "quantity" integer,
  "produce_per_day_ratio" integer,
  "sold_till_now" integer
);

CREATE TABLE "Order" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "personal_id" bigint,
  "product_id" bigint,
  "quantity" bigint,
  "company" text
);

CREATE TABLE "SalesManager" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "fName" text,
  "lName" text,
  "age" integer,
  "gender" bigint,
  "salary" bigint,
  "profit" float8
);

CREATE TABLE "Gender" (
  "id" SERIAL PRIMARY KEY NOT NULL,
  "name" text
);

ALTER TABLE "Order" ADD FOREIGN KEY ("personal_id") REFERENCES "SalesManager" ("id");

ALTER TABLE "Order" ADD FOREIGN KEY ("product_id") REFERENCES "Product" ("id");

ALTER TABLE "SalesManager" ADD FOREIGN KEY ("gender") REFERENCES "Gender" ("id");

So the question is "show the name of people who have sold the most products?" and by most sold i meant (quantity * unit_price)

EDIT:this is the updated query:

WITH orderProduct AS(
  SELECT
    O.personal_id as pId ,
    sum(O.quantity * P.unit_price) as sold_amount,
    RANK() OVER (ORDER BY sum(O.quantity * P.unit_price) DESC) as rank
  FROM "Order" AS O
  INNER JOIN "Product" AS P ON O.product_id = P.id
  GROUP BY O.personal_id
)
SELECT concat("fName" , ' ' , "lName") as name , orderProduct.sold_amount
FROM "SalesManager"
INNER JOIN orderProduct ON id = pId
WHERE rank = 1;

So how bad is it ? :(

PS: i'm using postgres 12.2


texts: Ola Hallengren Backup - stuck at 100% complete

texts:
Full Backup started on 4/21 12 AM. While backing up one of the database, it is stuck.

sp_WhoIsActive shows following information

wait_info: (1208ms)PREEMPTIVE_OS_WAITFORSINGLEOBJEC
CPU - 188,528,7853 and Increasing

Also, I see another backup on the same database started 30mins later (Backup to VirtualDevice - I know this is AppAssure backup tool).

From SQLSkills preemptive_os_waitforsingleobject, I see This wait type is when a thread is calling the Windows WaitForSingleObject function for synchronization with an external client process that is communicating using that object.

And this is blocked for 2 days - I don't think one of the session will terminate itself until I kill the other.

However, LOG backups were happening without any issues. Only FULL backup and DIFF backup for that particular database was blocked.

I killed the AppAssure session and all the long queue went away from AppAssure.
I killed the DIFF backup as well. Now the only process left is this FULL backup with 100% complete with same wait_info and not willing to complete!!

I had no other options but to kill the FULL db backup and restart backup jobs.
And, the USER_DATABASES full refuses to start saying the job is already running but sp_WhoIsActive showed nothing. Also, there was nothing in killed/rolled back state.

CurrentJobActivity in msdb showed that the job was active. Had to right-click-stop under jobs and then start the job again!!

Any idea on how do we avoid this situation? (other than telling the IT-team to stop AppAssure?)

[EDIT]: Adding the version

Microsoft SQL Server 2012 (SP3-CU8) (KB4013104) - 11.0.6594.0 (X64)
    Mar 15 2017 10:52:31
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

[EDIT2]: Another day - 4/26

Memory Clerks at the time

Log shows this

Server Config:
RAM: 24GB - 21.5GB for SQL Server
Processors: 8
Not much activity in this server - hardly 50 people access this SQL Server via Sharepoint.


texts: Inheritance and its alternatives in PostgreSQL

texts:
I make an archaeological database in PostgreSQL 9.2. The description of archaeological context has a subdivision between different types of context. I'm not sure how to implement it:

1) to violate third normal form

It is the dirtiest and probably the worst solution. I could check potencial duplicities by triggers, but I suspect it would be the hardest way, for there are quite a lot of type specific columns.

2) inheritance

This is better and might be good enough, but my "finds" table should have a foreign key to the "context" table regardles of its type. According to the manual this would cause problems. With all data in tables "layer", "cut", "structure" etc., would the foreign key to "context" table really point to the data stored in the inherited tables? If there's any caveat, is there some (relatively) easy workaround?

3) some sort of 1:1 relation

The "finds" table would be connected to "context" table, and tables for various types of context would have a foreign key pointing to the "context" table. But how to assure that exactly one row in all the dependent tables corresponds to each row in "context"?

4) ???

There might be some other solution which I didn't know about.

Which of these is easiest to implement (including avoiding duplicity)? And which is best in the terms of performance?
The database will never be really big, so slightly suboptimal performance is no problem, but big differences in performance might be more important than easiness to write it.

I'd be glad for answers telling me which option I should use, why, and especially: how to overcome its drawbacks (or that I missed something and there's no real drawback).


texts:
We are getting ORA-01406 error while trying to build an index over Oracle DB using Sphinx:

root@bdt:~# ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe/ NLS_LANG=AMERICAN_AMERICA.UTF8 indexer bsrpIndex --rotate
Sphinx 2.2.6-id64-release (r4843)
Copyright (c) 2001-2014, Andrew Aksyonoff
Copyright (c) 2008-2014, Sphinx Technologies Inc (http://sphinxsearch.com)

using config file '/etc/sphinxsearch/sphinx.conf'...
indexing index 'bsrpIndex'...
ERROR: index 'bsrpIndex': sql_fetch_row: [Oracle][ODBC][Ora]ORA-01406: fetched column value was truncated
.
total 648 docs, 16082835 bytes
total 18.330 sec, 877377 bytes/sec, 35.35 docs/sec
total 0 reads, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
total 0 writes, 0.000 sec, 0.0 kb/call avg, 0.0 msec/call avg
root@bdt:~#

We are using Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.
We understand that this error relates to the buffer size on client side but we are not sure how to configure it. We are using the ODBC driver. Basically, two fields that prevent the Sphinx index to be created are of VARCHAR2(4000) and VARCHAR(100) types. If we remove them from the query in Sphinx config the index builds without an error.

So, the question is: what can we do to extend this client side buffer to handle big columns data properly?


texts:
So, probably spelled my question wrong, anyway.
I'm trying to run a test scenario where some values are duplicated, so they should be 6 but if duplicated they are 12 or 18 and so on,

Currently I'm finding them by
    having COUNT(*) > 6

but I also want to make sure that the return result would be only those that can be divided by 6 , like 12/6, 18/6 would be returned, but something like 15/6 would not, so I suppose its 'whole' numbers - integer.

select rr.review_id
from review_rating rr
inner join review rw on
rr.review_id = rw.id
group by review_id
having COUNT(*) > 6

So, I only want to filter the results whose count can be divided by 6 and the result would be an integer/whole number.


texts:
Is it possible to create a table from a variable name? For example I have the following stored procedure that I'm trying to write but it will not compile past with the create statement.

CREATE DEFINER = 'sabretooth'@'%' PROCEDURE `sp_archive_sabretooth`(
    IN vhost INTEGER(11),
    IN record_age DATETIME,
    IN delete_records BOOLEAN
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE i_exist BOOLEAN DEFAULT FALSE;
    DECLARE table_to_create, backup_table_name, database_name VARCHAR(255);
    DECLARE backup_tables_cursor CURSOR FOR
            SELECT DISTINCT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
                       FROM INFORMATION_SCHEMA.COLUMNS
                      WHERE (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME LIKE 'vhost%'
                             OR
                             INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'mid')
                         AND INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA LIKE 'sabretooth%';
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    INSERT INTO sabretooth.debug (message) VALUES (CONCAT('Initializing sp_archive_sabretooth; vhost = ', vhost, '; record age = ', record_age, '; delete_records = ', delete_records));

    SELECT INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME
      INTO database_name
      FROM INFORMATION_SCHEMA.SCHEMATA
     WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME LIKE 'sabretooth%'
       AND INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME <> 'sabretooth_archive';

      OPEN backup_tables_cursor;

    create_tables_loop: LOOP
        FETCH backup_tables_cursor INTO table_to_create;
        IF done THEN
            LEAVE create_tables_loop;
        END IF;
        CREATE DATABASE IF NOT EXISTS sabretooth_archive;

        SELECT CONCAT(database_name, '.', table_to_create,'_archive') INTO backup_table_name;

        INSERT INTO sabretooth.debug (message) VALUES (CONCAT('I am here 38 backup_table_name = ',backup_table_name));

        CREATE TABLE CONCAT('sabretooth_archive.',table_to_create) LIKE CONCAT(database_name,'.',table_to_create);
    END LOOP;
END


texts: Is it possible: MySQL semisync and async replication on same master-slave pair?

texts:
I want to use master-slave MySQL (or MariaDB) replication to remote server as data protection from hardware failures.
I have two goals: to keep low response time and don't lose important data.
Good solution would be an asynchronous replications for most of data to keep responsiveness of the system and semisynchronous replication for important tables (payments).

In best case, tables with sync/async replication should be in same database or on same mysql server.
But as far as I understand mysql will use semisynchronous replication for all data, if master and slave has semisynchronous replication enabled.

Not so good way is to have two mysql instances as masters (one with semisync enabled and one with disabled). Then I'll need either two slaves or multisource replication. Multisource replication is still in dev version of MySQL and doesn't support semisync in MariaDB.

The only probably working way I found for now based on https://dba.stackexchange.com/a/41491/55235
M1

DB1 (with async data)

M2

DB1 (BLACKHOLE, async slave of M1)
DB2 (with semisync data)

S1 (semisync slave of M2)

DB1
DB2

Is it possible to have semisync+async replication with one master-slave pair? If no,  is described above scheme a viable solution?


texts:
I have question about MySQL Cluster:
Is it possible to have database larger than amount of RAM? (to not have all cluster data in-memory, but some data will reside on clusters nodes hard disks)
Especially, is it possible to point to MySQL Cluster which partitions doesn't have to be in-memory and can be stored on disks. (in case of lack of memory).

I am only asking, if solution like this is possible to achieve with MySQL Cluster.


texts: Configured identity seed starting value on database level

texts:
Is there an option in sql server to configure the default starting value(seed) of an identity column on a database level .

I want that by default, every table that created with an identity column ,the first  value will start from 3000 . And not from 1 .
I know how to do this in a table level. But is it possible in a database level?

10x


texts: Is it possible to accidentally use some paid feature in Oracle XE database while in an Enterprise environment?

texts:
IT in my org are wary about approving me installing Oracle Express Edition.
https://www.oracle.com/database/technologies/xe-downloads.html

Their reasoning is that apparently some DBA once said that devs can trigger some enterprise features accidentally while using it, and rack up hundred thousands of dollars in usage fees. I'm skeptical because I can't find any information online that would suggest that. Though I know Oracle doesn't have a great reputation.

From looking at XE description, it seems to be designed so that devs can install it on their environments to get a prototype working etc. Without having to worry about fees and licenses.
Is it true, can using Oracle XE on my dev box in a large company somehow rack up fees without knowing about it?


texts: Combine Multiple primary databases to single AlwaysOn AG

texts:
Can i combine 2 different databases like db01 database from server1 and db02 database from server2 to single AG group on DR ?

Version : SQL server 2016 enterprise edition


texts: Estimated Rows Higher than Actual Despite Updating Statistics

texts:
I have a (anonymised) query plan

There is a table (Object8) which has a high variation on the number of Actual Rows vs Estimated Rows on two columns (Column2 and Column6) which can be seen in the hash match and sort operators

I have updated all the statistics on the Object8 table (they were only a few days out of date and the table doesn't change much)

What else could the problem be?


texts: How do I resolve this data source connection issue with Cognos?

texts:
We have (fairly recently) installed IBM's Cognos 10 as our business intelligence reporting tool. Most of the databases that we report over are DB2 9.7 LUW Enterprise Edition. We installed the DB2 Runtime-Time Client in order for Cognos to have access to the driver to communicate with DB2.

We also have some legacy systems and databases on our iSeries (aka AS/400) - so this means DB2 for i. We need Cognos to now recognize these databases. From what we have read up on, we need to install DB2Connect to grant access to DB2 for i from a DB2 LUW perspective.

Our Cognos was installed on a Windows Server system. We installed the RTC on the same system and can catalog DB2 LUW databases. We can get Cognos to create data sources with those databases.

To get iSeries functionality, we recently installed DB2Connect (on the same system as Cognos and RTC) and were able to catalog the iSeries database and connect and call into it. But we cannot get Cognos to see the cataloged database. It just looks like it is not there.

Some of the documentation we looked at makes it seem like you cannot access two different DB2 drivers locally within the same process in Cognos.

Our thoughts:

Uninstall RTC and just install DB2Connect with all the datasources cataloged through that and then have Cognos look them up. Our concern with this method is that we are afraid that Cognos may lose information for existing reports and projects that are already set up. We do not want to mess up/lose work already done by other teams.
We are wondering if there would be a nice clean way to migrate from RTC to DB2Connect so we could move connections off of the one to the other. Our concern here...how do we get DB2Connect to be recognized by Cognos in the first place?
Is there another way to catalog the iSeries databases and/or get them recognized by Cognos?

Thoughts? Ideas?

Edit: Our company has decided not to wait. They are bringing in a consultant to help us rebuild this from scratch so we better understand the product, how it installs, works, etc.

That being said, I would still welcome any thoughts or ideas on this one.


texts: Recover MongoDB data without WiredTiger.wt file

texts:
We have recovered a MongoDB database from filesystem, but the WiredTiger.wt file has changed since then (i.e. the collection has been dropped).

So is it possible to read data (documents) from some .wt files without having the corresponding WiredTiger.wt?

Or create a WiredTiger.wt file with our own .wt files?


texts: Best way to design / query multi-level relationship table

texts:
My company is going to develop a mobile app selling services across different regions. We are expecting users are going to hugely depend on the Search function. Thus, I need to make sure the database design is good enough to handle the searches.

The main concern I have is, not only should the user be able to search the services by name or description, but also by service location.

Let me first show you what I designed:

Solution 1
Table: Service
+------+----------+--------------+--------+----------+----------+
| id   | name     | description  | cityid | address1 | address2 |
+------+----------+--------------+--------+----------+----------+
|     1| Service1 | Description1 |       1| Address1 |          |
|     2| Service2 | Description2 |       2| Address2 |          |
|     3| Service3 | Description3 |       3| Address3 |          |
+------+----------+--------------+--------+----------+----------+

Table: City
+------+-----------+---------+
| id   | name      | stateid |
+------+-----------+---------+
|     1| KL        |       1 |
|     2| Georgetown|       2 |
|     3| JB        |       3 |
+------+-----------+---------+

Table: State
+------+---------------------+-----------+
| id   | name                | countryid |
+------+---------------------+-----------+
|     1| Wilayah Persekutuan |         1 |
|     2| Penang              |         2 |
|     3| Johor               |         3 |
+------+---------------------+-----------+

Table: Country
+------+-----------+
| id   | name      |
+------+-----------+
|     1| Malaysia  |
|     2| Singapore |
+------+-----------+

Above are my tables with some sample data. The requirement is to allow the user to enter a keyword to start searching in the Service table – if the keyword matches a service name, then return that result from Service. If not, then search for the keyword in the City table – if the keyword matches a particular City row, then get all the Service rows that match that city by the cityid. If no row is found, then go on searching in the State table, up until the Country table.

So here is a problem with my solution. If the user enters a Country name, I will get a match result from the Country table, then I use the result's countryid to get all the related states in the State table, then I use those results' stateids to get all related cities in the City table – and only then I use the last results' cityid to search for the related services in the Service table. Which I think is not a good approach as it needs to use a lot of recursive IN searches to filter the results, which is not good for performance.

My colleague then came up with another solution, which put all the search related references into the Service table – something like this:

Solution 2
Table: Service
+------+----------+--------------+--------+----------+-----------+----------+----------+
| id   | name     | description  | cityid | stateid  | countryid | address1 | address2 |
+------+----------+--------------+--------+----------+-----------+----------+----------+
|     1| Service1 | Description1 |      1 |        1 |         1 | Address1 |          |
|     2| Service2 | Description2 |      2 |        2 |         2 | Address2 |          |
|     3| Service3 | Description3 |      3 |        3 |         3 | Address3 |          |
+------+----------+--------------+--------+----------+-----------+----------+----------+

So, if Country matched the search, then use the result's countryid to perform search straight in the Service table, otherwise, if State matched the search, then use the result's stateid to perform the search in Service and so on. This method is more efficient as it doesn't have so much recursive searches, but the downside is it violates the Normalisation practices: the Service table has redundant info, which is countryid and stateid. And logically speaking, I can find those two IDs with only cityid, even though I need to perform multiple look-ups/joins.

So which solution should I go for? Or do you have a better suggestion? Please advise.


texts: Is it worth indexing a varchar(32) column of UUIDS where half the UUIDS are v1 and half are v4?

texts:
I have a MySQL InnoDB table with tens of millions of rows. Each row has uuid column. They are stored in the standard VARBINARY(16). I was recently asked to add a column to this table of VARCHAR(32) that stores the non-dashed UUID hex.

While doing this, I discovered that about half our UUIDs are v1, and the other half are v4. This means that for the first half, since they were all generated on a single machine, the randomness is a bit lacking (only the left-bytes timestamps are different). On the other hand, the newer half are basically completely random.

Is it worth putting an index on this column? If so, I'm struggling to decide how large that index should be (or maybe even what type of index).


texts: Will my database index be more performant with a smaller varchar size?

texts:
We're using Mysql 5.7

We have an index over a varchar(255) column in a table of ~ 500M rows.

select distinct varchar_column from table yields seven results, with the max length 21.

A DBA consultant recommended we drop the length of the varchar column to 21. Dropping the size of the varchar column in tests shrank the index size equivalently to simply defragmenting the table without changing the schema (e.g. alter table).

We saw marginally better results when we dropped the length to 17.

Are there any advantages we're missing besides index size?


texts: ODBC convert postgres citext column in SQL Server linked server

texts:
I am trying to connect a PostgreSQL to SQL Server server as a linked server.
On the SQL Server, I have installed the ODBC driver for PostgreSQL (psqlodbc_09_02_0100-x64). I created a system DSN to a specific database on the PostgreSQL. This DSN works correctly.
Using SSMS I created a linked server that created successfully. When I test the connection it says successful and when I expand I can see all the tables from postgres.
The select I am running is: select * from [LinkedServer].[Database].[Schema].[TableName]
I have 2 issues now:
1 - When selecting from a table that has a column with citext this select throws an error:
The OLE DB provider "MSDASQL" for linked server "linkedserver" supplied inconsistent metadata for a column. The column "columnName" (compile-time ordinal 5) of object ""Database"."schema"."tablename"" was reported to have a "DBCOLUMNFLAGS_ISLONG" of 128 at compile time and 0 at run time.
2 - I would like to put a limit on the select but when trying select top 10 it gives me all 34,00 rows in the postgres table.
I do not have access to make any changes on the postgres side but I can change configurations in the ODBC driver and SQL Server which I have tried to play around with but no success.


texts: Can no longer connect to localdb after updating from Sql Server 2016 to 2019

texts:
I need to restore a backup for an assignment, but SMSS said I was running v13 while the backup was made in v15. So I download and installed SQL Server Express 2019 and uninstalled the older version.
However, SMSS can't connect anymore to (localdb)\MSSQLLocalDB.
I read somewhere to add a firewall rule for sqlservr.exe. When I try to run that file manually, it says the SQL Server installation is corrupt.
So I ran the Repair from SQL Server Installation Center, everything was okay in the output log but still no luck.
I'm pretty clueless, any help is appreciated.
Edit - the available services:

Status
Name
DisplayName

Running
MSSQL$SQLEXPRESS
SQL Server (SQLEXPRESS)

Stopped
SQLAgent$SQLEXP...
SQL Server Agent (SQLEXPRESS)

Stopped
SQLBrowser
SQL Server Browser

Running
SQLTELEMETRY$SQ...
SQL Server CEIP service (SQLEXPRESS)

Running
SQLWriter
SQL Server VSS Writer

The list of LocalDb instances:
MSSQLLocalDB

PS C:\Program Files\Microsoft SQL Server\150\Tools\Binn> SqlLocalDb.exe s "MSSQLLocalDB"
Start of LocalDB instance "MSSQLLocalDB" failed because of the following error:
Cannot create an automatic instance. See the Windows Application event log for error details.

Event viewer details:
LocalDB parent instance version is invalid: MSSQL13E.LOCALDB


texts: What privileges are needed to run EXPLAIN PLAN FOR CREATE INDEX?

texts:
My user can EXPLAIN PLAN FOR any query I like, but not for CREATE INDEX. I need to do that in order to estimate a suggested index's size, and using sysdba is out of question. What privileges should I ask for? This is Oracle 11.2.0.3.

SQL> explain plan for create index INDEXNAME on SOMEONE.SOMETABLE("FLD1","FLD2");
explain plan for create index INDEXNAME on SOMEONE.SOMETABLE("FLD1","FLD2")
                                                   *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> explain plan for select * from SOMEONE.SOMETABLE order by fld1 desc;

Explained.

SQL>


texts:
I am working with sql server 2005 and I had a situation where many values can be passed in a parameter.

Based on this: Passing multiple values for one SQL parameter this procedure  uses XML as a parameter.

here is the code of the stored procedure:

CREATE PROCEDURE [DENORMV2].[udpProductBulletPointSelectByTier1NoteTypeCode] (
    @Tier1 VARCHAR(10),
    @LanguageID INT,
    @SeasonItemID VARCHAR(5) = NULL,
    @ListNoteTypeCode XML,
    @CacheDuration INT OUTPUT )
    WITH EXECUTE AS 'webUserWithRW'
AS

        SELECT  pbp.Tier1, pbp.LanguageId, pbp.NoteText, pbp.NoteTypeCode,
                pbp.NoteGroup, pbp.SortOrder
        FROM    dbo.ProductBulletPoint pbp

        WHERE   Tier1 = @Tier1
          AND   LanguageId = @LanguageID
          AND   (      SeasonItemId = @SeasonItemID
                  OR
                       @SeasonItemID is null
                )

          AND pbp.NoteTypeCode IN (

                 SELECT  NoteTypeCode=BulletPoint.NoteTypeCode.value('./text()[1]', 'varchar(50)')
                   FROM  @ListNoteTypeCode.nodes('/BulletPoint/NoteTypeCode') AS BulletPoint ( NoteTypeCode )

          )

SELECT  @CacheDuration = Duration
FROM    dbo.CacheDuration
WHERE   [Key] = 'Product'
GO

more info about this procedure here

this is an example of how it can be called:

declare @p5 int  set @p5=86400
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>FootwearAccessoryComposition</NoteTypeCode></BulletPoint>',
@CacheDuration=@p5 output  select @p5

Question:

what is the best way to find out whether or not the parameter @ListNoteTypeCode XML is empty?

what if they call this procedure like this:

declare @p5 int  set @p5=86400
exec DenormV2.udpProductBulletPointSelectByTier1NoteTypeCode
@Tier1=N'WW099',
@LanguageID=3,
@SeasonItemID=N'16AUT',
@ListNoteTypeCode=N'',
@CacheDuration=@p5 output  select @p5

as it was suggested here I could avoid the select altogether by testing the parameter @ListNoteTypeCode.

My main goal in this scenario is to retrieve the data in the best possible performance, since this procedure is not cached in the web-servers and is called over a million times a day.


texts: Controlling SQL Backups From Two Machines

texts:
I'm far from a DBA, and don't really have a great understanding of backup chains, but I was asked the following question by a network administrator, and wanted to be sure that my answer was correct.

We have Symantec Backup Exec 2012, which currently takes care of full backups for databases either every day or every week, depending on the usage of that database.

As well as the fulls, BE is also doing incremental backups of the transaction logs either every 30 mins or every 2 hours, again depending on the usage of that database.

Our BE server is also responsible for other storage, cluster and virtual machine backups, and only has so many slots available to run jobs simultaneously.

I have been asked if it is possible for BE to be responsible for the full backups, but have SQL natively handle log file backups (with BE probably then backing up the actual .bak file every couple of hours).

I know that if I try to do an incremental backup in BE, and the last backup was not done by BE, it will fail because the LSN is off (rather, I think this is the reason). However I am not sure how the chain is impacted if all the transaction log backups are done in one device, and the full is done in another. Do transaction log backups work from the last log backup and then also from the last full backup if that occurred later?

If this question needs any clarification (because I've used terms incorrectly, for example) then please just let me know and I will modify as appropriate.

Thanks in advance for your time and advice.


texts: Using an index for both asc and desc on a string column

texts:
I know a trick to make order by queries faster when we want to order by asc and desc.
For integers store a negated value.
So if I store -7,-11,-8,-5,-1,-2 the order by asc will give -11, -8, -7, 5, -2, -1 and so the actual row will be sorted desc by that column (if e.g I show the non-negated version).
Question: How do I use this trick with strings?
How is a string "negated" or reversed so that the trick works for string columns as well?

Update
MySQL can not use an index for a query of the type:
ORDER BY col1 ASC col2 DESC
If the column I want to descent is an integer I can store the negated value and do the query as:
ORDER BY col1 ASC col2 ASC
In this case the index can be used and I will in fact get the value in descending order due to negation -11, -8, -7, 5, -2, -1 is ascending but if you remove the negation is it descending.
My problem is that I am not clear how I could apply this approach to a string column. Can I somehow get the arithmetical value of the string?


texts: Backend Server gives Mongo Error not authorized for query on mongo_db.tb_trx when primary is unavailable and Secondary Replica gets elected to Primary

texts:
Using mongodb with replica sets, when the primary node fails, one of the secondary gets elected to the primary but the node servers start to throw error
Mongo Error not authorized for query on mongo_db.tb_trx
To fix this, I restart node-servers and everything returns to normal but running 20+ backend-servers serving other requests takes time (otherwise suffer downtime, other services get affected as well).
I suppose the connections are made again after breaking the old ones. Should I manually ping mongo every time and update the connection in my code? (But if mongo goes down, all servers will reload at once, once again giving downtime)
Is there a mongo setting I am missing?
The problem is: How do I not get this error and avoid reloading backend-servers?
I am planning to update the mongo version but meanwhile need to fix this.
connection uri: mongodb://user:pass@mongo.server.io:27017,mongo-replica1.server.io:27017,mongo-replica2.server.io:27017/mongo_db?replicaSet=rs0&authSource=mongo_db
mongodb connection options

        {
          poolSize            : 20,
          replicaSet          : 'rs0',
          connectWithNoPrimary: true,
          readPreference      : 'primaryPreferred'
        }

Using node version v10.9.0
npm package mongodb version  ["mongodb": "2.2.19"]
[root@mongo mongodb]# mongo --version
MongoDB shell version: 3.0.15


texts: How to upgrade a MySQL database using numbered scripts based on a version field

texts:
I have a database that contains a version table with one value stored in it which is the database version number.

I would like to be able to update the database version automatically using numbered scripts.

If the database is version "6" and I have a dir of scrips numbered inconsistently as per the below:

001.sql, 02.sql, 3.sql, 4script.sql, 5.sql, 6.upgrade.sql, 7.sql, 8.data.sql, 009.updated.sql

What is the best way to only run scripts which are numbered greater than the version number and to do this in sequence?

Should I use an SQL script or create a bash script?

I am a little stuck on how to approach this so any pointers or advice will be much appreciated...


texts: How to automatically shrink a SQL Server transaction log only if it exceeds a certain limit (e.g. 20 GB)

texts:
Our database is set to FULL recovery mode and we take a database backup and a log backup every night. On several occasions we have noticed that the transaction log file is not truncated after the log backup. The log_reuse_wait_desc shows LOG_BACKUP. So we have to run couple of iterations for log backup and sometimes even dbcc shrinkfile command.

Is there any way we can run the dbcc shrinkfile command to shrink the log file every time it reaches 20 GB?

The predefined 'Shrink' maintenance plan is only for the .mdf file which I don't want to do. I believe we'll have to do it through t-sql only and use a trigger but I am unable to make it work. We have a relatively small database and we are running SQL 2012 standard edition. Any pointers you can provide would be immensely helpful.


texts: Import multiple CSV fields into one MySQL field


texts: Global Temp Table Error when Stored Proc run by SQL Job on a Schedule

texts:
I have a puzzling situation I am trying to confirm/solve.
A stored procedure I have written creates a series of Global Temp Tables and fires off an email if any records show up in the final one. This all works well. It has been tested over and over, so I am convinced that there is nothing amiss in the way the tables are being instantiated or accessed.
The stored procedure is then kicked off by a Job using the SQL Job Agent. This also works fine as long as I start the SQL Job manually.
But strangely, if the SQL Job is put on a schedule (say it runs every hour on the hour), the last Global Temp Table set to be created sometimes can't be found and the job sometimes fails while sending back an error:
Server Error Message = Invalid object name '##GlobalTempTableName'
But it doesn't fail every time. It might fail twice and the succeed the next time only to begin failing again the next hour. In addition, I have created a workaround to force the scheduled Job to succeed by setting it to make two more attempts before giving up. This solution has satisfied the stakeholders, because all they care about is that the SQL Job runs, they don't care HOW it runs.
But I am a very curious person and I would really like to know why this might be occurring.
Thanks for the geeky answers in advance.
Lars


texts:
I have a table in a database with three columns: ID, Key and Value.  Everytime I update the record, I have a trigger that sends these updates to another table and creates a new record in it, who's schema is this:

RowID (new ID field for this table, identity(1,1) column)
ID (from source table)
Key (from source table)
Value (from source table)
Version (integer)

So anytime the first table record's changed, it creates a new record in the second with the table everytime.

For every record with a matching ID, I want to add a unique incremental version number.  If a record with ID of 1 is entered into this table 5 times, those records should have version numbers of 1, 2, 3, 4, and 5.  Is the way to accomplish this through a subquery for existing version numbers, or setting up a partition, or something else?

Thanks.


texts: Something is totally wrong with my query. .. Slow. .. Why?

texts:
i have query with multiple JOINS and SUBQUERYS and it seems i have messed something up here. ..
It did worked when there was low data (data is not even that much now too) but now it is not even finishing query (even in MySQL workbench)
I know how to write simple join query but when it has to load data i mess up :(
SELECT l.id               AS lid,
       l.docNr            AS receive_docNr,
       l.thisTransport    AS receive_transport,
       l.productNr        AS receive_productNr,
       l.activityDate     AS receive_date,
       h.ladingNr         AS receive_ladingNr,
       l.place_count      AS receive_place_count,
       h.cargo_status     AS receive_cargo_status,
       h.clientCode       AS clientCode,
       e.id               AS eid,
       e.docNr            AS docNr,
       e.deliverydate     AS deliveryDate,
       e.activitydate     AS activityDate,
       e.assistant_amount AS assistant_amount,
       e.amount           AS amount,
       e.productnr        AS productNr,
       e.productumo       AS productUmo,
       e.status           AS status,
       e.orgline,
       ni.name1,
       ni.name2,
       h.agreements       AS agreements,
       nc.Name,
       a.uom              AS uom,
       au.uom             AS auuom,
       (select z.status from        item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as istatus,
       (select z.place_count from   item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iplace_count,
       (select z.activityDate from  item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iactivityDate,
       (select z.place_count from   item_ledger_entry as z
            where  ( z.orgLine = l.id OR z.cargoLine = l.id )
              AND e.docNr = l.docNr ORDER  BY z.id DESC LIMIT  1) as iassistant_amount

FROM cargo_line_receive AS l

    LEFT JOIN item_ledger_entry AS e
    ON e.cargoline = l.id
    OR e.orgline = l.id

    LEFT JOIN cargo_header_receive AS h
    ON l.docNr = h.docNr

    LEFT JOIN agreements_lines AS a
    ON h.agreements = a.contractnr
    AND e.productnr = a.item
    AND e.resource = a.service

    LEFT JOIN additional_uom AS au
    ON e.productnr = au.productnr
    AND au.status = 1
    AND au.convert_from = 1

    LEFT JOIN n_customers AS nc
    ON h.clientCode = nc.Code

    LEFT JOIN n_items AS ni
    ON l.productNr = ni.code

WHERE  h.clientCode = '999999999999'
AND h.agreements = 'LIG00067'

GROUP  BY l.id
ORDER  BY l.activityDate ASC, l.docNr

EXPLAIN QUERY:

I did not know what i can remove from query so i left all.
If i removed subquerys from select nothing changed (so for me it seems to be some join)
Can anyone tell me what indexses should have here for each table?
EDIT: I have added indexes but nothing changed :(
ALTER TABLE cargo_header_receive ADD INDEX aaa (docNr, agreements, clientCode);
ALTER TABLE item_ledger_entry ADD INDEX bbb (cargoline, orgline, productNr, resource);
ALTER TABLE cargo_line_receive ADD INDEX ccc (docNr, productNr, activityDate);
ALTER TABLE agreements_lines ADD INDEX ddd (contractnr, item, service);
ALTER TABLE additional_uom ADD INDEX eee (productnr, status, convert_from);
ALTER TABLE n_customers ADD INDEX fff (Code);
ALTER TABLE n_items ADD INDEX ggg (code);


texts: How to enable remote debugging of Stored Procs with MSSQL Linux Docker container?

texts:
I'm running the latest SQL Server docker container from DockerHub in a Linux environment.  Port 1433 is forwarded from my container, and everything is working fine.

However, when I have tried to connect via SSMS to do remote debugging of a Stored Procedure, I see that the Debug->Start Debugging greyed out in SSMS.  From what I understand, Debugging needs port 135 to be open/enabled in the container mapping as well.

So I changed my container startup script to be the following:

create --name mssql2 -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@$$12345'  -p 135:135  -p 1433:1433  -v /home/eric/docker/mssql:/var/opt/mssql -v /usr/share/zoneinfo:/usr/share/zoneinfo:ro -e TZ=America/Toronto microsoft\
/mssql-server-linux

docker start mssql2

However, when I try to telnet to port 135, however, the connection is immediately closed:

eric@localhost ~/docker $ telnet localhost 135
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

If I login into the mssql2 container and running netstat (after installing the net-tools package) I see:

root@2a378b0479d9:/etc# netstat -ano
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       Timer
tcp        0      0 0.0.0.0:1433            0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 127.0.0.1:1434          0.0.0.0:*               LISTEN      off (0.00/0/0)
tcp        0      0 172.17.0.3:57064        91.189.88.162:80        TIME_WAIT   timewait (7.52/0/0)
tcp        0      0 172.17.0.3:36154        91.189.88.149:80        TIME_WAIT   timewait (54.62/0/0)
tcp        0      0 127.0.0.1:37135         127.0.0.1:1433          ESTABLISHED off (0.00/0/0)
tcp        0      0 127.0.0.1:40978         127.0.0.1:1433          TIME_WAIT   timewait (18.04/0/0)
tcp        0      0 127.0.0.1:1433          127.0.0.1:45665         ESTABLISHED keepalive (7051.49/0/0)
tcp        0      0 127.0.0.1:1433          127.0.0.1:37135         ESTABLISHED keepalive (7051.46/0/0)
tcp        0      0 127.0.0.1:45665         127.0.0.1:1433          ESTABLISHED off (0.00/0/0)
Active UNIX domain sockets (servers and established)
Proto RefCnt Flags       Type       State         I-Node   Path

So clearly MSSQL isn't even listening on port 135 within the container. What do I need to do to enable the remote debug port on the docker container? I do not even see any reference to port 135 in the logfile either.  Is there a special switch I need to use in the MSSQL configuration to tell it to enable remote debugging?


texts:
Running PostgreSQL server on Void Linux. After installing ran initdb as OS user 'postgres':

[user@host]$ sudo -u postgres -i

$ initdb -D '/var/lib/postgresql/data'

Received output:

  creating directory /var/lib/postgresql/data ... ok creating
  subdirectories ... ok selecting default max_connections ... 100
  selecting default shared_buffers ... 128MB selecting dynamic shared
  memory implementation ... posix creating configuration files ... ok
  running bootstrap script ... ok performing post-bootstrap
  initialization ... locale: Cannot set LC_MESSAGES to default locale:
  No such file or directory ok syncing data to disk ... ok

  WARNING: enabling "trust" authentication for local connections You can
  change this by editing pg_hba.conf or using the option -A, or
  --auth-local and --auth-host, the next time you run initdb.

  Success. You can now start the database server using:
  pg_ctl -D /var/lib/postgresql/data -l logfile start

I've then proceeded to create the service, grant the ownership to 'postgres' and start it:

[user@host]$ ln -s /etc/sv/postgresql /var/service
[user@host]$ sudo chown postgres: /var/service/postgresql
[user@host]$ sudo sv start postgresql
[user@host]$ ls -l /var/service/postgresql
lrwxrwxrwx 1 postgres postgres 18 nov   2 17:05 /var/service/postgresql -> /etc/sv/postgresql

Running:

[user@host]$ sudo -u postgres psql ...

will give me the following error:

psql: FATAL:  role "postgres" does not exist

I've tried granting ownership of /etc/sv/postgresql to 'postgres', but no luck.

These were my sources:

https://wiki.voidlinux.eu/PostgreSQL

PostgreSQL error: Fatal: role “username” does not exist

I do not know what to do next.

EDIT: Stumbled upon psql: FATAL: role “postgres” does not exist. Cannot run psql -l because I get the same error, no matter which user I run psql as.


texts: Can I rely on reading SQL Server Identity values in order?

texts:
TL;DR: The question below boils down to: When inserting a row, is there a window of opportunity between the generation of a new Identity value and the locking of the corresponding row key in the clustered index, where an external observer could see a newer Identity value inserted by a concurrent transaction? (In SQL Server.)

Detailed version

I have a SQL Server table with an Identity column called CheckpointSequence, which is the key of the table's clustered index (which also has a number of additional nonclustered indexes). Rows are inserted into the table by several concurrent processes and threads (at isolation level READ COMMITTED, and without IDENTITY_INSERT). At the same time, there are processes periodically reading rows from the clustered index, ordered by that CheckpointSequence column (also at isolation level READ COMMITTED, with the READ COMMITTED SNAPSHOT option being turned off).

I currently rely on the fact that the reading processes can never "skip" a checkpoint. My question is: Can I rely on this property? And if not, what could I do to make it true?

Example: When rows with identity values 1, 2, 3, 4, and 5 are inserted, the reader must not see the row with value 5 prior to seeing the one with value 4. Tests show that the query, which contains an ORDER BY CheckpointSequence clause (and a WHERE CheckpointSequence > -1 clause), reliably blocks whenever row 4 is to be read, but not yet committed, even if row 5 has already been committed.

I believe that at least in theory, there may be a race condition here that might cause this assumption to break. Unfortunately, documentation on Identity doesn't say a lot about how Identity works in the context of multiple concurrent transactions, it only says "Each new value is generated based on the current seed & increment." and "Each new value for a particular transaction is different from other concurrent transactions on the table." (MSDN)

My reasoning is, it must work somehow like this:

A transaction is started (either explicitly or implicitly).
An identity value (X) is generated.
The corresponding row lock is taken on the clustered index based on the identity value (unless lock escalation kicks in, in which case the whole table is locked).
The row is inserted.
The transaction is committed (possibly quite a lot of time later), so the lock is removed again.

I think that between step 2 and 3, there is a very tiny window where

a concurrent session could generate the next identity value (X+1) and execute all the remaining steps,
thus allowing a reader coming exactly at that point of time to read the value X+1, missing the value of X.

Of course, the probability of this seems extremely low; but still - it could happen. Or could it?

(If you're interested in the context: This is the implementation of NEventStore's SQL Persistence Engine. NEventStore implements an append-only event store where every event gets a new, ascending checkpoint sequence number. Clients read events from the event store ordered by checkpoint in order to perform computations of all sorts. Once an event with checkpoint X has been processed, clients only consider "newer" events, i.e., events with checkpoint X+1 and above. Therefore, it is vital that events can never be skipped, as they'd never be considered again. I'm currently trying to determine if the Identity-based checkpoint implementation meets this requirement. These are the exact SQL statements used: Schema, Writer's query, Reader's Query.)

If I'm right and the situation described above could arise, I can see only two options of dealing with them, both of which are unsatisfactory:

When seeing a checkpoint sequence value X+1 before having seen X, dismiss X+1 and try again later. However, because Identity can of course produce gaps (e.g., when the transaction is rolled back), X might never come.
So, same approach, but accept the gap after n milliseconds. However, what value of n should I assume?

Any better ideas?


texts: How to have MySQL Slave Replicate From a Master's Master

texts:
We are using the Following:

MySQL 5.1.63-community-log MySQL Community Server (GPL)
OS : Red Hat Enterprise Linux Server release 5.4

We have three servers and all servers are enabled with binary logging option

Server A : Running master on it.
Server B : it is slave of Server A but also enable binary logs
Server C : it is slave of Server B.

Our goal to change master server of server c from server B to server A.

Is that possible in run time without any data corruption?
What are prerequisites for this?
How can we achieve this?


texts: Using CROSS APPLY OPENJSON causes Azure to hang

texts:
I have a table with around 8 million rows with a schema of:

CREATE TABLE [dbo].[Documents](
    [Id] [uniqueidentifier] NOT NULL,
    [RemoteId] [int] NOT NULL,
    [Json] [nvarchar](max) NULL,
    [WasSuccessful] [bit] NOT NULL,
    [StatusCode] [int] NULL,
    [Created] [datetime2](7) NULL,
 CONSTRAINT [PK_Documents] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Id]  DEFAULT (newsequentialid()) FOR [Id]
GO

ALTER TABLE [dbo].[Documents] ADD  CONSTRAINT [DF_Documents_Created]  DEFAULT (getdate()) FOR [Created]
GO

The json documents are of a structure of

{
    "Id": 1,
    "Data": [
        {
            "Id": 99,
            "Name": "Person 1"
        },
        {
            "Id": 100,
            "Name": "Person 2"
        }
    ]
}

I am attempting to extract the JSON array from within the document and insert it into a new table using the following query:

;WITH CTE (Json) AS
(
    SELECT TOP 10
        JSON_QUERY([Json], '$.Data')
    FROM
        Documents
    WHERE
        ISJSON([Json]) > 0
)
INSERT INTO [dbo].[ParsedDocuments] (Id, Name)
SELECT
    JSON_VALUE([Value], '$.Id') AS [Id],
    JSON_VALUE([Value], '$.Name') AS [Name],
FROM
    CTE
CROSS APPLY
    OPENJSON([Json]) as X

What I'm finding is that if I extract and query a sample of the data, say 1000 rows.  Everything works as expected and the data is inserted into the destination table.  However, when I query the main table the server appears to just hang and become unresponsive.

I suspect that it is attempting to run a cross apply across all the rows before inserting data.  Is there any way to improve performance?  Or allow the job to begin "streaming" results into the destination table rather than attempting to batch them up?

Finally, as you can see I am employing the use of "TOP 10" results.  I am however still experiencing the performance hanging.  And I am unsure as to why.

Query plans for large table are never generated how when running against a sample of the data the query plan can be found here.


texts:
First thing, I am totally new to SQL. I saw some tutorials, videos, tried searching but didn't find what I needed. I know it has asked before, but I couldn't find it.

What I need to find is basically like:

Select
    Company.Name, Programmer.Name, Manager.Name, CEO.Name
where
    Company.City = Programmer.City = Manager.City = CEO.City

like select all of them who are from London.

EDIT: Tried doing this with 2 tables only, code as below, but I'm getting an error:

SELECT Company.Name
FROM Company Cm
INNER JOIN Programmer Pg ON Cm.City = Pg.City
WHERE Cm.City = 'London'

  The multi-part identifier "Company.Name" could not be bound.


texts: Can I change the wal_level from logical to replica? If so are any impact on the replication process?

texts:
Is there a chance to change the wal_level from logical to replica without any impact. I know this involves a restart of services but is this possible?
For example if there is a already a logical replication running with master and slave setup. The question here is can I change the wal_level now from logical to replica(streaming) with the same master and slave.


texts:
I have installed and created a database in oracle 12c in ubuntu 14.04 . Now I can login to the CLI using sqlplus \ as sysdba and run sqlcommands , but I can't login to the EM from the web. The name given to me while database was loading was tamim-Inspiron-5421:5500/em that is just my machine name, but when I type this on the browser , webpage not found error happens.
when I run the command lsnrctl status | grep HTTP it returns empty i.e blank.
the output of the command in SQL is select dbms_xdb_config.getHttpsPort() from dual; is

DBMS_XDB_CONFIG.GETHTTPSPORT()
------------------------------
                         5500

I am logged in as oracle user like this oracle@tamim-Inspiron-5421 . I have checked the netstat --listen and saw that no program is listening to port 5500. What could be the problem ?

Here is the error in lsnrctl

LSNRCTL> status
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 111: Connection refused

I think I need to configure my firewall , or edit my /etc/hosts file somehow , but I don't know how !!
here is my /etc/hosts file

127.0.0.1   localhost
127.0.1.1   tamim-Inspiron-5421

after I added localhost like this 127.0.1.1    tamim-Inspiron-5421 localhost
Here is the status

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date                21-SEP-2014 18:47:22
Uptime                    0 days 0 hr. 9 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /oracle/app/oracle/diag/tnslsnr/tamim-Inspiron-5421/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=tamim-Inspiron-5421)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=tamim-Inspiron-5421)(PORT=5500))(Security=(my_wallet_directory=/oracle/app/oracle/admin/tamimdb/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "tamimdb" has 1 instance(s).
  Instance "tamimdb", status READY, has 1 handler(s) for this service...
Service "tamimdbXDB" has 1 instance(s).
  Instance "tamimdb", status READY, has 1 handler(s) for this service...
The command completed successfully


texts: Why is Postgres on 64-bit CentOS 6 significantly slower than Postgres on 32-bit CentOS 6

texts:
We have some Postgres + PostGIS applications that run well on CentOS 6 32-bit machines.

We've recently been testing them on CentOS 6 64-bit machines, with similar configuration (all our machines are managed by Puppet), and the applications run significantly slower.

Even loading the database schemas take several times as long. (On the 32-bit machines, this takes 7 seconds to load PostGIS, the schema, and fixtures; on the 64-bit machines, this takes 50-110 seconds.)

We initially had the problems with virtual servers, so ran tests on a physical machine and found the same problems. Note that the physical 64-bit machine is slower than virtual 32-bit machines.

The databases are not large at all.

We've experimented with various parameters on postgresql.conf and not gotten any improvement.

Is this a known issue with Postgres or PostGIS on 64-bit CentOS?

If not, how do we diagnose this?


texts: Will pgAdmin III let me run an SQL command that works after detaching from my SSH tunnel?

texts: I just discovered pgAdmin III and I am absolutely overwhelmed by the number of options it offers. I'd like to try to replace my traditional workflow (psql ran over an SSH session) with this tool. The problem is that I usually work on a hundred-million-row database and some of the queries I run hours or even days to complete. During that time, I'd like to be able to turn my client PC off. Does pgAdmin III support this workflow?


texts: Is staggering reindexing jobs a good strategy?

texts:
I have inherited a server with 35 databases and a job that reindexes all tables on all databases (using sp_MSForEachTable). It's taking over 14 hours to complete and is now blocking other processes.

My question is this:

Would it be an acceptable strategy to stagger the reindexing over the week? Say, 5 DBs per night?

Thanks.


texts: How to get hierarchical data from two tables in oracle?

texts:
I have two tables table1 and table2, I need hierarchical output by joining of both of the tables.

Table1 contains three categories CAT1, CAT2 and CAT3 with one Foreign key i.e. F_ID which is a primary key of another table table2 which contains one more column i.e.VAL.

TABLE 1
-----------------
CAT1        CAT2        CAT3        F_ID
A           a           aa          1
A           a           ab          2
A           b           ba          3
A           b           bb          4
B           c           ca          5
B           c           cb          6
B           d           da          7

TABLE 2
-------------------
F_ID    VAL
1       4
2       6
3       4
4       1
5       6
6       6
7       9

Now i need the data in the below format, where every category and sub-category contains the total sum of VAL.

Need Data in below Format from the above table
----------------------------------------------
A       -       -       15
A       a       -       10
A       a       aa      4
A       a       ab      6
A       b       -       5
A       b       ba      4
A       b       bb      1
B       -       -       21
B       c       -       12
B       c       ca      6
B       c       cb      6
B       d       -       9
B       da      da      9

Please help me.


texts: Retrieving data from SQLServer mutitenant database

texts:
I have a multitenant database with something like the following naming structure:

Sch1.Logins   => 2 records
Sch2.Logins   => 4 Records

Now I want to be able to "Execute as" different users , say when user1 tries to run the query "select * from Logins", the results should be returned from the Sch1.Logins (2 records) and when user2 runs the same query, it would return records from Sch2.Logins.

How can I do this?
What settings are required for each user?


texts:
If I  have 12345678, 1234567, 123456 in a table as a column and using sql I want to create a new column that will have new numbers that removed the last two digits of each number. The new column will have 123456, 12345,1234.

Would someone help in writing this code in Microsoft sql environment


texts:
PostgreSQL 9.4

Consider the following pretty simlpe query:

EXPLAIN ANALYZE SELECT *
FROM (
    SELECT *,
    ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY date DESC NULLS LAST) rn
    FROM tbl) sub WHERE rn = 2123231

The analyzed plan is

                HERE
------------------------------------------------->
                                                 |
                                                 |
Subquery Scan on sub  (cost=0.42..23380.71 rows=1216 width=40) (actual time=269.947..269.947 rows=0 loops=1)
  Filter: (sub.rn = 2123231)
  Rows Removed by Filter: 243147
  ->  WindowAgg  (cost=0.42..20340.47 rows=243219 width=32) (actual time=7.361..248.437 rows=243147 loops=1)
        ->  Index Scan using idx on tbl  (cost=0.42..14260.00 rows=243219 width=32) (actual time=0.014..66.079 rows=243147 loops=1)

My question is how the total rows  1216 is computed. Is there any strictly defined rule for that?


texts: stats_column_id and index_column_id do not update with physical order of clustered index is changed

texts:
Unless I'm misunderstanding the purpose of the column, the following code indicates that a change of the structure of the clustered index does not change the ordinal position (stats_column_id)of the column in the sys.stats_columns DMV. (Tested in AdventureWorks2014, AdventureWorks2008R2)

select i.name, c.name, ic.column_id, ic.index_column_id
from sys.indexes i
join sys.index_columns ic
    on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c
    on i.object_id = c.object_id
    and ic.column_id = c.column_id
where i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by ic.key_ordinal;

select sh.name,s.name, c.name, c.column_id, sc.column_id, sc.stats_column_id
from sys.stats s
join sys.stats_columns sc
    on s.object_id = sc.object_id
    and s.stats_id = sc.stats_id
join sys.columns c
    on s.object_id = c.object_id
    and sc.column_id = c.column_id
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where s.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by sc.stats_column_id;

dbcc show_statistics('[Person].[BusinessEntityAddress]','PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID') with density_vector;

ALTER TABLE [Person].[BusinessEntityAddress] DROP CONSTRAINT [PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID]
GO

ALTER TABLE [Person].[BusinessEntityAddress] ADD  CONSTRAINT [PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID] PRIMARY KEY CLUSTERED
(
    AddressID ASC,
    [BusinessEntityID] ASC,
    [AddressTypeID] ASC
)
GO

select i.name, c.name, ic.column_id, ic.index_column_id
from sys.indexes i
join sys.index_columns ic
    on i.object_id = ic.object_id
    and i.index_id = ic.index_id
join sys.columns c
    on i.object_id = c.object_id
    and ic.column_id = c.column_id
where i.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by ic.key_ordinal;

select sh.name,s.name, c.name, c.column_id, sc.column_id, sc.stats_column_id
from sys.stats s
join sys.stats_columns sc
    on s.object_id = sc.object_id
    and s.stats_id = sc.stats_id
join sys.columns c
    on s.object_id = c.object_id
    and sc.column_id = c.column_id
join sys.tables t
    on s.object_id = t.object_id
join sys.schemas sh
    on t.schema_id = sh.schema_id
where s.name = 'PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID'
order by sc.stats_column_id;

dbcc show_statistics('[Person].[BusinessEntityAddress]','PK_BusinessEntityAddress_BusinessEntityID_AddressID_AddressTypeID') with density_vector;

However, the density vectors indicate a change in the leading column of the index/statistics object. Is this a fundamental misunderstanding on my part? If so, how would I find the leading column of a statistics object using DMVs?

Tested SQL Server versions: 2008R2, 2014


texts: How to make dataguard switchover seamlessly

texts:
In my Dataguard configuration on a 12c environment, when I perform a switchover from primary (foo) to standby (bar) and then try to sqlplus in with user@foo, I receive the "ORA-011033: ORACLE initialization or shutdown in progress" error instead of being able to connect to the new primary(bar).

Now, I know that this is because the connection is still pointing to the foo address instead of the bar address, and according to many articles, you need to create a new "virtual" service to handle the different connections, as well as a trigger to stop/start this service after a rolechange.

I did this and with the service_names parameter set to this new "virtual" service, a switchover will work and can also switch back but when trying to sqlplus with user@foo it throws ORA-011033. The trigger is useless and the service continues to run on both foo and bar because it seems that having that parameter set overrides whatever the trigger tries to do.

Now if I don't define that parameter, my switchover works fine, and the triggers do their job, and when I sqlplus user@foo, it will correctly redirect me to the bar address. However, when I try to switchover back to foo, I receive the following in the DG alert log:

Redo transport problem detected: redo transport to database foo has the following error:
ORA-16047: DGID mismatch between destination setting and target database
08/05/2016 16:01:02
Initialization of connection failed.
Expected destination db_unique_name is foo.
Database actually reached is bar
Failed to send message to site foo. Error code is ORA-16642.
Data Guard Broker Status Summary:
Type                        Name                             Severity  Status
  Configuration               foo_dg                         Warning  ORA-16607
  Primary Database            bar                            Error  ORA-16778
  Physical Standby Database   foo                              Error  ORA-16642

ON FOO:

  log_archive_dest_2='service="bar"','ASYNC NOAFFIRM delay=0 optional
  compression=disable max_failure=0 max_connections=1 reopen=300
  db_unique_name="bar"
  net_timeout=30','valid_for=(online_logfile,all_roles)'
  fal_client='foo' fal_server='bar'

ON BAR:

  log_archive_dest_2='service="foo", ASYNC NOAFFIRM delay=0 optional
  compression=disable max_failure=0 max_connections=1 reopen=300
  db_unique_name="foo" valid_for=(online_logfile,all_roles)'
  fal_client='bar' fal_server='foo'

Entries in LDAP:
foo:

  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=foo_home)(PORT=15940))(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=virtual_foo)(FAILOVER_MODE=(TYPE=SESSION)(METHOD=basic))))

bar:

  (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=bar_home)(PORT=15940)))(CONNECT_DATA=(SERVICE_NAME=bar)))

I'm not sure what I'm missing or misunderstanding. Can anyone shed any light as to what I might be doing wrong? Can provide more info if needed.


texts:
The row (with type MEDIUMTEXT) has been accidentally updated with empty value. I can see previous data in MYD file of a table, but I can't get them neither from mysqldump or any mysql client. If I execute mysqlcheck -u root -p --auto-repair --check --optimize the MYD-file shrinks from 1688484 bytes to 449008 bytes.

Is there any way to export data from MYD or mark data as undeleted?

Version from dump is: MySQL dump 10.13  Distrib 5.1.37, for pc-solaris2.11 (i386)


texts: Trigger having issues on partioned table execution - Postgres - 11

texts:
Postgres - Trigger having issues on partitioned table execution
I am using postgres 11 version
I am trying to execute a trigger on partitioned table with customized function and trigger function,
that return partitionid. Based on the partitionid table have to be updated  The trigger is getting executed but values are not updating, always it updates with null.
The below one I have tried
PARTITIONED Table
CREATE TABLE "DATA_RECORDS" (
    ID                     VARCHAR(255) NOT NULL,
    RECORD                 VARCHAR,
    STARTTIME               TIMESTAMP(9) DEFAULT CURRENT_TIMESTAMP,
    ENDTIME                 TIMESTAMP(9),
    PARTITIONID               NUMERIC
)PARTITION BY RANGE (PARTITIONID);

CREATE TABLE P0 PARTITION OF "DATA_RECORDS" FOR VALUES FROM (1) TO (2);

CREATE TABLE P1 PARTITION OF "DATA_RECORDS" FOR VALUES FROM (2) TO (3);

CREATE TABLE P2 PARTITION OF "DATA_RECORDS" FOR VALUES FROM (3) TO (4);

CREATE TABLE P3 PARTITION OF "DATA_RECORDS" FOR VALUES FROM (4) TO (5);

CREATE TABLE data_default PARTITION OF "DATA_RECORDS" DEFAULT;

CREATE OR REPLACE FUNCTION GETPARTITIONS(pConfigurationId VARCHAR)
    RETURNS INTEGER
    AS $$
        DECLARE
                PARTITIONID integer;
        BEGIN
                SELECT PROPERTY_VALUE::text::int INTO PARTITIONID
                      FROM "DATA_CONFIGURATION"
                        WHERE CONFIGURATION_ID = pConfigurationId
                        AND PROPERTY_KEY = 'data.partition.size';
                        RAISE NOTICE '%', 'CONFIGURATION_ID ' || pConfigurationId;
                        RAISE NOTICE '%', 'partitions ' || PARTITIONID;
                RETURN PARTITIONID;
        END;
    $$ LANGUAGE plpgsql IMMUTABLE;

TRIGGER Function
CREATE OR REPLACE FUNCTION GETPARTITIONID()
        RETURNS TRIGGER
        AS $$
        DECLARE
                PARTITIONID integer;
                partitions integer;
                sequence_num integer;
                arg_config_id varchar;
        BEGIN
                arg_config_id := TG_ARGV[0];
                partitions := GETPARTITIONS(arg_config_id);
                RAISE NOTICE '%', 'partitions ' || partitions;
                BEGIN
                        SELECT nextval('partition_seq') INTO sequence_num;
                        RAISE NOTICE '%', 'sequence number ' || sequence_num;
                        SELECT mod(sequence_num, partitions)+1 INTO PARTITIONID;
                        NEW.PARTITIONID := PARTITIONID;
                       RETURN NEW;
                END;
        END;
$$ LANGUAGE plpgsql IMMUTABLE;

TRIGGER ON PARTITIONED TABLE
CREATE TRIGGER DATA_PARTITIONID1 AFTER INSERT ON P0 FOR EACH ROW EXECUTE PROCEDURE GETPARTITIONID('configId');

CREATE TRIGGER DATA_PARTITIONID2 AFTER INSERT ON P1 FOR EACH ROW EXECUTE PROCEDURE GETPARTITIONID('configId');

CREATE TRIGGER DATA_PARTITIONID3 AFTER INSERT ON P2 FOR EACH ROW EXECUTE PROCEDURE GETPARTITIONID('configId');

CREATE TRIGGER DATA_PARTITIONID4 AFTER INSERT ON P3 FOR EACH ROW EXECUTE PROCEDURE GETPARTITIONID('configId');

CREATE TRIGGER DATA_PARTITIONID_DEFAULT AFTER INSERT ON data_default FOR EACH ROW EXECUTE PROCEDURE GETPARTITIONID('configId');

Any wrong in the above execution of triggers with partitioned table?


texts: SSAS Cube in Excel - Show only applicable rows with KPI?

texts:
I have a cube built in SSAS 2008r2.  I'm exposing it for the moment through an excel PivotTable.

I recently added a KPI to the cube, and when I show the KPI in my PivotTable interface it causes non-applicable dimension values to be shown at the current level.

For instance, suppose I have customers:

CustomerA
CustomerB
CustomerC

And States:

NY
VA
TX

When I look at the PivotTable without the KPI, CustomerB has no rows under NY because they have no locations there.

When I add my KPI in and display just the value of the KPI, all is well.

When I add in the status indicator (i.e. traffic light or whatever),  I see every customer under every state.

Is this expected behavior? Is there a way around it?  I like the KPI but it defeats the purpose if I get every value of my dimension at each level.

Some more details:

The Value Expression for the KPI is just a calculated measure, which works fine.

The Status Expression is:

CASE
    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") > .90
        THEN 1
    WHEN KpiValue("MyKPI") / KpiGoal("MyKPI") <= .90
        AND KpiValue("MyKPI") / KpiGoal("MyKPI") > .75
    THEN 0
ELSE -1
END


texts: SQL Server - db_owner user can't see sys schemas

texts:
these is a user in database XXX_service (it's technical account) and it has set "Server role" - public and and user mapping as dbo_owner for one database.

Programmer sent logs and there are errors like this one:

Msg 229, Level 14, State 5, Procedure sp_OACreate, Line 1
The EXECUTE permission was denied on the object 'sp_OACreate', database 'mssqlsystemresource', schema 'sys'.

I think it is necessary to rise rights but i thought db_owner for concrete database >= sysadmin. Am i wrong? Sorry for my bad english, thank you:)


texts: How to retreive deleted records in a trigger on a table with composite primary key

texts:
I am trying to optimize a trigger on a table in a database from a 3rd party. I am not allowed to change or add any columns to the schema.

The table has a composite primary key, one column is an integer, the other is nvarchar.

How can I retreive the deleted rows without using a temp table?

I am currently concatinating the keys in a string in the where clause like this:

SELECT FROM deleted WHERE (deleted.a + CAST(deleted.b as nvarchar)
       NOT IN (SELECT deleted.a + CAST(deleted.b as nvarchar)
       FROM inserted, deleted
       WHERE inserted.a = deleted.a
         and inserted.b = deleted.b))

I have also tried several variants of deleted.%%physloc%% kind of attempts, but with no luck. Is there any more elegant way to filter out the deleted records?


texts: Converting MongoDB instance from Standalone to Replica Set and Backing Up

texts:
I want to convert my MongoDB 3.6 standalone server to a replica set so I can watch a collection using change streams, which requires an oplog. I only want to use change streams so will keep this as a single member replica set.

The problem is that my standalone instance is kind of big (~400 GB) and I do not have a backup of it.

I have a few questions:

Is such conversion considered a safe operation? Are there any caveats considering the time of "conversion", the additional memory needed etc. or does that only apply if I connect more nodes?
What are the ways of backing up such big instances? I have heard of and read about Filesystem Snapshots but I'm not sure what are they and how to handle them.


texts: SSMS Extremely Slow connecting to local dev db

texts:
So I found this question and believe this is related.
SQL Server Management studio slow connection or timeout when using Windows Authentication

If I extend my timeout to 30 seconds I get SSMS connected but everything is terribly slow!  The application is extremely slow/timing out when debugging.

They mention adding an entry to the hosts file to bypass DNS lookup.  This is a local dev db running on my machine.  What would I add to the hosts file to eliminate these DNS issues???  Servername = "ROG-DREAM-MACHI\SQLDEV".

I'm a db noob.  Plz help. :(


texts: Innodb buffer pool indexes versus data usage

texts:
The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB.

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+--------------+
| Variable_name           | Value        |
+-------------------------+--------------+
| innodb_buffer_pool_size | 113816633344 |
+-------------------------+--------------+

When looking at the information_schema tables, it looks like I'm utilizing ~85GB for indexes.

mysql> SELECT ist.SCHEMA db, ((SUM(data_size) / 1024) / 1024) / 1024 size_in_gb FROM INNODB_BUFFER_POOL_PAGES_INDEX ibppi JOIN INNODB_SYS_INDEXES isi ON(isi.INDEX_ID=ibppi.INDEX_ID) JOIN INNODB_SYS_TABLES ist ON(ist.TABLE_ID=isi.TABLE_ID) GROUP BY ist.SCHEMA ORDER BY size_in_gb DESC;
+-----------+-----------------+
| db        | size_in_gb      |
+-----------+-----------------+
| databaseB | 78.940829364583 |
| databaseA |  5.648017468862 |
+-----------+-----------------+

Is it safe to assume the remaining 20GB's is being used for data?

semi-related-note: information_schema tables are really slow. I've waited on some queries for many hours, only to come back the next day with the query still running. I've even went so far as creating another db, copying the accompanying information_schema tables, changing the table engine to innodb/myisam, and insert into <table> select * but this doesn't give me live results. If anyone has insight into making these information_schema queries faster, that would be appreciated as well.


texts: Fetching records using some unknown join

texts:
I have two tables:

1.  Users
2.  Ratings

Users table have columns i.e. id, name etc.

Ratings table have columns i.e. - id, rate_by_id, rate_to_id etc.

I want to display all the ratings in a table. I can do this very easily with a join but the challenge here is that 2 columns of Ratings table point to Users table and according to me join with only one is possible.

By using which JOIN I can join Ratings table to Users table in a single join query with both the columns (rate_by_id and rate_to_id) have the join with Users table.

Please add comment if you need any clarification.

Thanks & Regards


texts: Index with ops for `LIKE` and `=` queries

texts:
In my Rails 5 with PostgreSQL 9.5 app I have 2 frequent queries:

SELECT * FROM table WHERE lower(varchar_col) = 'some_string' AND int1_col = some_int AND int2_col = some_other_int

-- and

SELECT * FROM table WHERE lower(varchar_col) LIKE 'some_str%' AND int1_col = some_int

table is about 1.5m rows and quickly growing.

So, I've created 2 indices for both of these queries:

CREATE INDEX index_1 ON table USING btree (lower((varchar_col)::text), int1_col, int2_col)

-- and

CREATE INDEX index_2 ON table USING btree (lower((varchar_col)::text) varchar_pattern_ops, int1_col)

I have 2 questions, maybe they are a little noob, I'm not good in DB performance:

1.Can I use only one index for both of these queries? Something like:

CREATE INDEX index ON table USING btree (lower((varchar_col)::text) varchar_pattern_ops, int1_col, int2_col)

Does varchar_pattern_ops shows the same performance for LIKE and = queries? Will PostgreSQL planner use multicolumn index for 3 columns in query for 2 columns?

2.I noticed that Rails generate index with expression lower((varchar_col)::text, but my varchar_col has varchar type, not text. Does this matter and Rails made some mistake and I need to write CREATE INDEX query to specifically include column type in query?

Thanks for any help!

P.S. Of course, table's, columns' and indices' name are changed for better reading.


texts:
This is a question about data warehouse design. We are setting up a healthcare datawarehouse and starting with 2 major source systems that combine for about 20,000 tables and 2 TB of data. 1) It is highly dimensional data 2) We don't wan't to heavily effect OLTP systems

We have chosen an incremental Kimball design. My question is, should all of the data be staged, then sorted into inserts/updates and put into the data warehouse. Then the staging data would be cleared for the next incremental load.

This leaves you with 1 copy of the data.

The other method would be to incrementally load it into staging, sort it into inserts/updates and store it in the same format as the source systems. Then we would combine data from the source systems into the datawarehouse from the full copy.

This would essentially leave you 2 copies of the data, one in the form of the source systems and 1 loaded into the actual datawarehouse.

What is the best practice for this? I originally thought it would be best to only store the copy in the data warehouse and clear the source tables each load.

However, in that case if you ever have to go back to an existing dimension and add a column you would have to re-load all the dependent source tables. Plus you would lose history?

It just seems really inefficient to store it twice though....just wanted some thoughts on the design, your experiences and best practice.


texts: 'skip rows' in a flat file source does not work as advertised

texts:
In SSIS(2012 specifically) there is an option in the Flat File Datasource to skip a number of lines at the beginning of a file. The preview appears correctly. However, when trying to execute the data flow, it appears that those rows are still being validated; so that if they contain invalid data(blank rows), the datasource fails. This prevents using a conditional switch to remove rows, since the data flow never starts.

Is this accurate description of the normal functioning and is there any way to work around it?

Sample data looks like -

COL1|COL2|COL3

abc|123|10
def|456|20
ghi|789|30


texts: Constraint column value based on another column? SQL Server

texts:
i am trying to set my defaults column Y or N based on membershipType column.
There can be only one Y in defaults column based on the 3 different value in membershipType column. Other than that, others will be N. How do i do it?

ID  titlePromo   membershipType         defaults
--  ----------   --------------         ------
1    Promo 1     Membership Promotion      Y
2    Promo 2     Membership Renewal        Y
3    Promo 3     Membership Grad           Y
4    Promo 4     Membership Promotion      N
5    Promo 5     Membership Promotion      N
6    Promo 6     Membership Grad           N


texts: How to figure out the latest version of a table in the 'recyclebin' section of PL-SQL developer

texts:
There are three tables with the same name Pantea in my recyclebin  section of pl-sql developer and I don't know how to find the one that had been dropped the last (I mean the last version of the table).

I can not restore all of them cause there can not be tables with the same name in tables section and I can not see the properties of the tables in recyclebin either . What is the best way to find the latest version of a table in recyclebin?
Important thing is that think about a situation in which the table has nearly more than 300 columns and it's not a good solution to restore the tables one by one to check whether it's the latest one . I want to know if there is a solution that for one hundred percent returns the latest version of the table from the recyclebin.
Thanks in advance


texts:
I have below tables:
ProductsTable (has product details),
UsersTable (has all application users),
RatingCategry (category for which user can rate a products like Quality, Cost etc.) and
UserRatingsforProductsTable (Users rating for a product for any RatingCategory) for a Mobile Application.

Products (ProductId, ProductName… other details like cost etc.)

UsersTable (UserId, UserName… other details like DateOfJoin etc.)

RatingCategory (RatingCategoryId, CategoryName)
**Data:**
1         Quality
2         Cost

UserRatingsforProductsTable (RatingId, UserId, ProductId, RatingCategoryId, Score)
**Data:**
1         5006  20  1   4.5

2         5006  20  2   5.0

3         5600  23  1   3.5

4         7800   20  1   2.5

5         8900   34  1   4.0

6         8900   34  2    4.5

What will be the best and faster query for a Mobile Application to show “Rating ScoreTotal” in the Products list (ProductId, ProductName, Cost, RatingScore)? Shall I do calculation in the Query or Add a new column in Products table that will be auto updated when each Rating added into UserRatingsforProductsTable?

The expected output should be:
ProductId, ProductName, Cost, OverallRatingForThisProd
---------  -----------  ----- ------------------------
*** OverAllRating = Average of Ratings from All Categories for the Specific Product from UserRatingsforProductsTable.

If the answer will be Query instead of adding a Column, can you please advise an optimized query?

Thank you.