texts: What is happening with locks in my database?

texts:
I'm having some problems on my database. At any given time, I'm trying to execute an update with five records on my table, but this table is locked and I get a time out executing the query.

I realized that if I execute update in only four records, the statement run sucessfully. And when I try to update the five recods together, it doesn't work.

Example:

--ONLY LOCKS IF THE UPDATE IS IN ALL FIVE RECORDS. (any combination works fine)

UPDATE X SET STATUS = 1 WHERE ID IN (1,2,3,4,5) -- THIS LOCKS THE UPDATE!!

UPDATE X SET STATUS = 1 WHERE ID IN (2,3,4) -- THIS  DOESN'T LOCK AND RUN SUCESSFULLY!

UPDATE X SET STATUS = 1 WHERE ID IN (1,4,5) -- THIS  DOESN'T LOCK AND RUN SUCESSFULLY!

UPDATE X SET STATUS = 1 WHERE ID IN (1,2,3,5) -- THIS DOESN'T LOCK AND RUN SUCESSFULLY!

Does anyone have an idea to how I fix this situation?

EDIT 1:
My isolation level is Read Commited.


texts:
I'm new to mysql.I have a 127GB size ibd table file to restore. While running "alter table shiwangini_test2.mytable import tablespace ;" -it's just running out of memory and stopping mysql service. I know it's binary file. However, is there any alternative way to split/restore this file without hitting high memory usage.
Thanks in advance!


texts:
Iseries system using I/90

Is there a way to check changes made to database object like tables and views from any systables or system views or any logs on db2 for iSeries machine.


texts: Is my table is stored as heap or b-tree?

texts:
I have a SQL Server 2005 database. It’s .MDF & .LDF can be downloaded at https://www.dropbox.com/s/uj2mw410jm2m078/MyDB.rar?dl=0

Now I want to know whether two table are stored as heap or b-tree(linked list), so I run the following commands:

USE CR_YPLS8_NEW_QB;

select
    sys.objects.name,
    sys.objects.object_id,
    sys.partitions.index_id,
    sys.system_internals_allocation_units.first_page,
    sys.system_internals_allocation_units.first_iam_page
from
    sys.objects, sys.partitions, sys.system_internals_allocation_units
where
    (sys.partitions.object_id = sys.objects.object_id) and
    (sys.system_internals_allocation_units.container_id = sys.partitions.partition_id) and
    ((name = 'cardfa') or (name = 'pcspkc'));

Where 'cardfa' and 'pcspkc' are the two user tables I want to investigate.

The result is below:

Based on https://technet.microsoft.com/en-us/library/ms189051%28v=sql.105%29.aspx, since both table’s index_id is 1, they are all stored as b-tree(the data pages in the leaf level is in linked list).

However, in the result table, the first row has first_page value (filenum, pagenum) = (1, 0x6F2A8) which is an invalid pointer.

The second row has first_page value (filenum, pagenum) = (1, 0x5941) = (1, 22849).

After I using the following commands:

DBCC TRACEON(3604);
DBCC page(CR_YPLS8_NEW_QB, 1, 22849, 3);

I get the following result:

After checking each column in each slot, I find the data is completely different from the actual data stored in 'cardfa' table. So the first_page for the second row is also invalid.

And I then find the 'cardfa' table is actually stored in heap and can be referenced by first_iam_page instead of first_page, which is inconsistent with the search result. Why?

Thanks


texts: How to get the top 20 percent records with a single query?

texts:
SELECT start_coins
FROM   coins
ORDER BY  start_coins DESC LIMIT 10; /* till here the query works*/
CAST((COUNT(start_coins) * 0.2) AS INT);

I want the number returning from the count to be used as the LIMIT instead of 10. My database version is 10.1.32-MariaDB.


texts: How can I log the way in which a job was started?

texts:
Using SQL Server Agent, I've created a Job that runs some SQL. This SQL does some work on one database, then adds a row to a logging table on a separate database.

The Job is run on a couple of schedules and is also occasionally started by hand.

On the log row, I'd like to be able to record what it was that started the Job. In other words, I'd like to detect whether the Job was started by schedule 1, schedule 2, or ad hoc.

Is this possible? If so, how?


texts:
We have an Ubuntu dedicated server. A couple of weeks ago we started to notice that when the postgresql service restarts, the directory /var/lib/postgresql turns readonly.

We changed the permissions manually, but recently we found that the file pg_hba.conf has extra lines:

host all postgres 0.0.0.0/0 reject
host all pgdbadm 0.0.0.0/0 md5
host all odoo 0.0.0.0/0 reject

Is it possible we have some kind of malware?

Is there a solution without having to format the server?


texts:
What is the difference in terms of I/O disk usage between Simple Backup and Full Backup?

Does full backup incur more reads and writes?

How does full backup mode impact reads and writes to TempDB's transaction log?


texts:
Is there anyway to see which address connections are connecting on?

More info:

I've repeatedly found people setting up connectionstrings using IP instead of by DNS name, and with SQL2012 and above, connecting to instances by server name without including the instance name (i.e. where this is on port 1433).
I'd like to audit this so that within hours of something having connected using a non-compliant connection string, I can be alerted and so quickly find the culprit and fix the issue; as opposed to waiting for changes in infrastructure (e.g. us moving to a non-standard port, or switching to a different host on a different IP whilst maintaining the same underlying DNS entry) to cause more serious surprises as a result of non-compliant configurations.


texts: SQL Server TempDB files 1-> n and write stalls growing

texts:
I have been kinda accidentally pushed to starting to manage our DB settings, performance analyzing etc. Also as it's commonly said that TempDB file should be spread into n files based on core numbers and performance stuff.

I ran some analyzis on one of our servers and spotted that tempDB Write stalls were averaging around 600ms with single file. With this find and with some more analyzis I decided to split TempDB into 4 same sized files (as recomended). At start it looked really first. For first day new tempDB stalls were around 1.5ms (SSD). After 24h though all the new TempDB files were above original write stall. This same thing is repeating on different enviroments also. Am I looking on wrong numbers in here? Am I missing some setting that I should check? Or is single TempDB file still working better.

Here are some environments that this thing has happened:
SERVER1
SQL2008R2, SSD drive for SQL, 2 main databases with around 10gb each.

Before: 1 autogenerated TempDB file with stats:

num_of_Writes: 18909792
Avg_Write_stall_ms: 645.554ms
Avg_Read_stall_ms: 2.207ms

After: Original one (changed to 2gb), + 3*2gb tempDB files

Avg_Write_stall_ms around: 2.15ms
Avg_Read_stall_ms around: 1.3ms

After 24hours:

Avg_Write_stall_ms around: 650-700ms
Avg_Read_stall_ms around: 2.2ms

SERVER2 (virtual):

SQL2012, heavy use and lot of database
Before: 1 autogenerated TempDB file with stats:
Avg_Write_stall_ms: 1576.154ms
Avg_Read_stall_ms: 12.788ms

After: Original one (changed to 2gb), + 3*2gb tempDB files

Avg_Write_stall_ms around: 19-39ms
Avg_Read_stall_ms around: 6.8-7.6ms

After 24hours:

Avg_Write_stall_ms around: 8700-9500ms
Avg_Read_stall_ms around: 18.5 - 19.1 ms

This same situation is on multiple of our servers with different setups (virtuals, newer and older, SSD and disk drives). What are we missing?


texts:
I'm using MySQL 5.7.20. My schema, tables and columns are all supporting utf8mb4. I used the following:

ALTER SCHEMA `MyDB`  DEFAULT CHARACTER SET utf8mb4  DEFAULT COLLATE utf8mb4_general_ci ;
ALTER TABLE MyDB.Videos CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

I don't understand why I'm still getting the following error:

DatabaseError: 1366 (HY000): Incorrect string value: '\xF0\x9F\x92\x96' for column 'Content' at row 1

I can't figure out the reason, please help.


texts: What security vulnerabilities are there with IDENTITY_INSERT and DBCC CHECKIDENT

texts:
I'm writing some simple C# code through a website to take an excel file and update a table on a database. My system admin seems to not want to give me access to pretty much anything including the two things above stating that I need a Security+ certificate in order to run those commands.

I've been doing this a long time (15 years), first I've ever run into a problem like this, and so this seems really really weird and strange.

I already can update, insert, delete and do modifications to tables as I have been given some access to the database. So what if any are the security implications of these additional permissions? Can someone explain the reluctance of the SA to grant me these permission? Please enlighten me, because at this point I'm just scratching my head trying to figure out why the application cannot be granted these rights.


texts: Maximum overall size of MariaDB binary logs

texts:
I am looking for a way to prevent MariaDB binary logs from taking a huge size when they grow faster than usual.

variable max_binlog_size set the maximum size of ONE binary logfile.
variable expire_log_days set the number of days after which the binary logs are removed.

But is there a way to set a "maximum overall size" of ALL binary log files? or, maybe simpler, a maximum number of binary log files, which would overload expire_log_days retention...


texts: Where can I find database tuning exercises for learning?

texts:
As a developer, often DBAs take responsibility for resolving performance issues at the database level so we don't get that much experience diagnosing, tuning, refactoring queries etc.

I'm looking for a database with a bunch of tables, data, queries, triggers, SPs and so on, in which there are deliberate performance problems, and hopefully a series of exercises in which you attempt to identify and resolve the problems. Ideally this will be on MS SQL server.

Anyone know of this kind of thing?


texts: Check if table exists on a linked server

texts:
I've had a search, but can't find a post relating to this instance specifically.

We have a linked production database that I'm trying to reference from our 'sandbox' server. I want to check if a table exists on the LINKED production server. For any given database/table combination on the server from which I am executing the SQL I would use an

IF OBJECT_ID(etc) IS NULL etc.

call, however this does not work when I reference the LINKED server. e.g.

IF OBJECT_ID('ZPRODSERVER.DM_Database.dbo.MyTable', 'U') IS NULL PRINT 'YES' ELSE PRINT 'NO'

returns "YES", even though I know this table exists, as when I select top 1 * from it I get table results. If I use:

IF EXISTS(select top 1 * from ZPRODSERVER.DM_Database.dbo.MyTable) PRINT 'YES' ELSE PRINT 'NO'

then I get "YES" returned, HOWEVER if the table doesn't exist, I don't get NO and instead I get an error message:

Msg 7314, Level 16, State 1, Line 90
The OLE DB provider "SQLNCLI11" for linked server "ZPRODSERVER" does not contain the table ""DM_Database"."dbo"."MyTable"". The table either does not exist or the current user does not have permissions on that table.

Is there a consistent method that I can use to determine if a table on a different server exists without incorrect results or an error message?

Thanks!


texts:
I was reading the examples on MSDN for TRY-CATCH blocks.

The code listing for example B is as follows:

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

What I don't understand is why they would bother to check the @@TRANCOUNT in the CATCH block. Is it really possible for the transaction count to be < 1 in this code snippet, or is the guard clause just an example of the principle that examples should be exemplary?


texts: Temporal Validity and Primary/Foreign Key Relationships

texts:
I've read through several of the oracle tutorials that show temporal validity and time features. However, in the examples I'v read, there is no primary key used in the demo tables.

http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS1005
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html

Should primary keys be added to these tables?
I'm asking because I'm wondering about how one of these temporal tables should be referenced by another table. Can I add a foreign key from one temporal table to another?

If I add a pk/ foreign key realtionship, and then update the referenced in the table with the pk, the table with the fk points to a record that is no longer relevant.... does temporal data break normal primary key - foreign key relationships? And if so, how does this affect performance, do I just use a normal column as the 'foreign key' and select the right one for a referenced time period in a query?

Does anybody know of or have any examples or tutorials handy that show temporal data with normal or pseudo-normal pk / fk usage?

Thanks


texts: is there a rule for the size of the page file on dedicated sql server servers?

texts:
Hi All,

  We've been investigating further into the C: disk on my_server in
  regards to the low disk space. We can see that there aren't many files
  that can be removed in order to free up storage. However, we have
  noticed the page file on this disk is using 32 GB of storage.

  The settings are currently set to allow Windows to automatically size
  the page file, however, we would recommend turning this setting off
  and manually setting the size. By reducing the page file size manually
  to 8 GB we would free 24 GB of space on the C: disk, which would be
  the best long term solution with the issue of disk space.

should I really disable the page file?
and reduce it to 8 GB?

there is a similar question here:
Recommended page file size for SQL 2008R2 on Windows 2008R2

and this article is also very good:
Understanding the Windows Pagefile and Why You Shouldn't Disable It


texts:
I have a 200+GB mysql dump file that I need to import into the local sql server running on my development machine. I attempted a naive dump restore using the mysql < db_backup.sql approach and managed to restore around 85% of the database before hitting a

  mysql server has gone away

error. This took in the region of 5 days.

I'd like to know if there's a way of comparing the partially restored DB to the dump file to understand which tables are still pending or if there's a significantly (at least 4x) quicker method to restore from a huge dump file.

Any advice would be appreciated. Thanks.


texts: Add query executed user information to Mysql binlog

texts: We are using MYSQL as our production database and we have setup another salve for read requests. Time to time various database users can execute insert/update queries on master database. If we want identify those executed queries (auditing/troubleshooting) we are using mysql bingos for it (reading after decoding it). From mysql binlog we can identify the query but we cant identify the executed user. is their a way to get that query executed user info into binlog. Enable general log  wont be a option due to performance issues.


texts: Query in procedure occasionally does not finish

texts:
I have a question about how to debug very strange behavior (nested query getting stuck) in SQL Server 2008 R2 (Standard Edition) running a query.

Sometimes (unfortunately not always) when I run a complex procedure that does some data transformations involving:

-- Update of statistics.
EXEC sp_updatestats;

-- Run of another procedure with single complex query
-- procedure declared with recompile.
EXEC usp_query_procedure;

I run into problem that this procedure is not completing (typical time < 1s, when stuck the running time can be days, never finished yet).

If I take out the query text and run it separately it finishes in under a second, but if I run it inside the procedure it gets stuck.

When these symptoms started I didn't have either WITH RECOMPILE or sp_updatestats.
I have googled around and found that cached bad execution plan (for some input paramters) may be used and that with recompile will help (provided that statistics are updated). While these two options seem to have mitigated the problem (it occurrs less often) it sure did not go away completely. Why didnt it help in all cases? What else can cause this problem?

When the procedure is "stuck" and I attach a SQL Profiler I can see thousands of lock acuqires/releases every refresh tick. When the query runs normally there is only fraction of used locks, but thats probably obvious.

What is also very strange is that this happens only on one (Standard Edition) of four servers running the same application (mix of editions Standard / Enterprise).

How can I go about debugging this further? What events should I log in profiler to be able to see what is causing the problem? Did something similar happened to sombody else here?

Please even a hint on what to google, what to look for, what to trace can lead to a solution. I will be sure to post my findings here for future generation If I manage to solve the problem.


texts: How can one drop all default values and allow NULL for all columns of a table?

texts:
I'm working on a PHP script to modify the primary key (PK) of a table.  My current code is:

$q = "SHOW KEYS FROM $db.$table WHERE Key_name='PRIMARY';";
$keys = Query ( $q);
if ( $keys)
{
    $q = "ALTER TABLE $db.$table
            DROP PRIMARY KEY,
            ADD PRIMARY KEY ($keyQ);";
    $res = Query ( $q);
}
else
{
    $q = "ALTER TABLE $db.$table
            ADD PRIMARY KEY ($keyQ);";
    $res = Query ( $q);
}

This mostly works for what I need.  The only problem is that if I drop a field as a PK, it retains the NULL='No' and DEFAULT=0 properties from being a PK.

Since I'm going to define the PK later and I generally don't need not null and default constraints I would like to clear all not null values and default values.  Then I can proceed to define the primary key and know that the primary key is the only columns with default values and not NULL constraints.

Is there a way to do this with MySQL?  I could do it with PHP, but it would probably be more work and would be less elegant.


texts: Failing to insert into table getting error: ORA-00600: internal error code, arguments: [ktssinseg2]

texts:
I am facing problems inserting records into my oracle database 12C R2. I have no Oracle Support. All this time I was able to do inserts into my database, but all of a sudden it is now failing. Below is the query and error:

SQL> INSERT INTO MV_IN_SMS_TOTAL SELECT * FROM mv_in_sms_total@newdwhlink.com WHERE START_DATE = '20191101';
INSERT INTO MV_IN_SMS_TOTAL SELECT * FROM mv_in_sms_total@newdwhlink.com WHERE START_DATE = '20191101'
                                   *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktssinseg2], [60], [7],
[289598865], [], [], [], [], [], [], [], []


texts: MongoDB document delete and fragmentation

texts:
I've a MongoDB collection that works like a queue: new documents are insert and old documents (after 60days) are removed. I can see a rapid grown of the datafile size, too rapid. I can be reasonable because we remove old data after 60 days, but I was thinking, are my deletes effective without execute the defragmentation? (in few words, what's a good way to manage disk space in MongoDB)

What is a correct defragment / clean collection politics? It's a production database and version is 2.6.9

thanks.


texts: how to change the default date format in oracle

texts:
New to Oracle here, i am working on some assigments but i noticed when i was creating the tables using a script, oracle throws the following error:

ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
       incorrect.  The input data did not contain a number where a number was
       required by the format model.
*Action:   Fix the input data or the date format model to make sure the
       elements match in number and type.  Then retry the operation.

knowing that the date format in my queries is something like '01-JUN-2012'
i was wondering how can i change the default format in my oracle installation to meet my system format? I am using SQL Developer with the Free Oracle version 11g.

also i am wondering if this is because my computer is WIN 7 french version and teh dates in my queries are in the English US format?

UPDATE bb_product
   set salestart = '01-JUN-2012', saleend = '15-JUN-2012', SalePrice = 8.00
   WHERE idProduct = 6;

this query is from the Brewer Beans DB that my professor handed out. an which i am assuming was created in a system that uses the EN-US format...any help?


texts: Load balancing Availability groups with SQL Server Standard

texts:
So I have the current scenario, and it looks to be working fantastically, but I just want to get some input on the configuration.
Is it smart? Are there any issues I am not thinking of?

We have SQL Server Standard, and as such, you can only have 1 DB per AG (we have 20 databases), and no Read-Only secondaries.
Basically meaning, the primary server is doing ALL the lifting, with the secondary doing a lot less.
So essentially, you are paying for resources for Node 2, that are sitting at 10% workload, while node 1 is at 70/80% workload.
Both nodes are fully licensed with regards to SQL Server cores.

What I have done, to assist this, is slit the database primaries up.
So about 50% of the databases are Primary on node1, while the other 50% are primary on node2.

The results:

The applications all connect great to either node via their respective listener.
If a failover occurs, just the databases on the failing node are affected, and failover to the other node (we have tested this fairly well).

Each node, can now split the load, essentially load balancing.
It is a manual process to set it up this way and when deploying new DBs and groups they go to the lighter node. but a small price to pay for "more" hardware punch without much cost (licenses which we already have and a bit of admin).

What are your thoughts on this, guys?


texts: What is the database model of Apache HAWQ?

texts:
I read a lot of blogs about hawq and how it is adapted from Pivotal Greenplum.
Hortonworks say, it is native sql engine over hadoop.
Pivotal say, it is a SQL query database that combines powers of Greenplum + Hadoop. It also supports OLAP.

Now I know there are databases and database models.

  Which database model best describes HAWQ ?

Relational
Wide column
Document
Hybrid
Something else ?

Please help!


texts:
I'm trying to find similar values for a single column.
I have a column 'name' and I've used a query to find duplicates, however I'm fairly certain that there are similar...entries? Not duplicates per say, but rather typos?

I realise I myself am not exactly sure what I'm trying to do, an example would be:
table.name - apple and I want to be able to sort all table.names that are similar to it, like 'applle'.

To note: I dont want to compare table.name with a certain word but rather sort all instances of table.name that are similar

Is it possible to search through 'name' and compare it with all other 'name' values?

Second thing, which is, how to phrase it - complementary values.
(e.g. "3d audio" vs "3d audio, entertainment and music"
So, if 'name' has '3d audio' in one instance and in another '3d audio, blah blah' to have them sorted in some way ?


texts:
I'm experiencing high Signal Wait percent while CPU utilisation is very low at one of our servers.
I have gone through numerous articles online about Signal Waits and understand that it is the time spent on the runnable queue however I'm still having difficult time to understand why CPU usage is staying so low (25%) while Signal Waits' percent is high (band of 75-90%.)
If a session is waiting for an available CPU to run it, why is CPU utilised so low?


texts: Why does DELETE not work after an UPDATE in a CTE?

texts:
CREATE table test (
    id integer,
    x integer
);

INSERT INTO test VALUES (1, 1);

SELECT works:

WITH res AS (
    UPDATE test SET x = 10 WHERE id = 1
    RETURNING id
)
SELECT id FROM test WHERE id IN (SELECT id FROM res);

id
---
 1
(1 row)

But DELETE does not work:

WITH res AS (
    UPDATE test SET x = 10 WHERE id = 1
    RETURNING id
)
DELETE FROM test WHERE id IN (SELECT id FROM res);

DELETE 0


texts: Merging tables with a 1 to 1 relationship in a database

texts:
Is it a good idea to merge two tables that have a 1..1 relationship together?

I have inherited a database with too many tables linked together in a 1..1 relationship. I'd like to evaluate the benefits of merging these tables where possible to have more tidy model with higher query performance.

For example table t1 has 150 records and table t2 has 150 records, they each contain 5 columns and these two tables have a 1..1 relationship. Would it be a good idea to merge them into one table, say t3 which contains all the columns from t1 and t2?

Also does it make any difference if these tables are reference tables (less than 200 records) or an operational tables?

Any idea is appreciated.


texts: Are triggers and their results visible in same order as original operations?

texts:
I've created simple trigger

CREATE TABLE data (
    id int,
    v1 text,
    v2 text
);

CREATE TABLE events (
    id serial primary key,
    v1 text
);

CREATE OR REPLACE FUNCTION emit_event_for_v1()
RETURNS trigger AS '
    BEGIN
        IF OLD.v1 != NEW.v1 THEN
            INSERT INTO events (v1)
            VALUES (NEW.v1);
        END IF;
        RETURN NULL;
    END
' LANGUAGE 'plpgsql';

CREATE TRIGGER event_v1 AFTER INSERT OR UPDATE ON data
FOR EACH ROW EXECUTE PROCEDURE emit_event_for_v1();

INSERT INTO data VALUES (1, '', '');

After that, I've launched many clients (25) doing many updates (500 each) on the
data.

UPDATE data SET v1 = $1 WHERE id = 1

with $1 being $WORKER_ID : $ITER_ID. In the end I've verified that last
inserted event (SELECT id, v1 FROM events ORDER BY id DESC) matches current
state of data (SELECT v1 FROM data WHERE id = 1).

I've also checked that id of last event is equal to number of updates
(25 * 500). And it is.

My question is, is this guaranteed to work? Are results of inserts in triggers
always visible in same order as the original update that triggered the trigger?
Or did I just get lucky during my testing (like 50 times in a row)?


texts: Oracle 10XE: Stop schemas seeing the all_users table?

texts:
I've got an Oracle 10g XE Database that I want clients to be able to view remotely via SQL*PLUS or SQL Developer. Each schema has a name which includes the name of the client, so anyone looking at the all_users table would be able to see the names of all my other clients! Is there any way to restrict permissions on certain tables? And are there any other tables like this which would be a good idea to restrict access to? I'm not particularly concerned about our clients trying to hack things, I just don't want them to see a list of all our other clients!

Thanks!


texts: Incompatibility between unixODBC and Firebird ODBC driver

texts:
I'm currently developing a application based in Rcode, ODBC and Firebird.

For that, first I've installed firebird server and unixODBC (both 64 bits since my machine OS is 64-bits). I tried with 32-bits software aswell since Ubuntu multiarch allow it, but that caused another problem (doesnt matter in this subject).

Created database, using firebird isql function (/opt/firebird/bin/isql), and made some fake tables with integer, float, text columns.

I can connect to it using unixODBC isql function (after properly programming DSN and Driver in odbc.ini / odbcinst.ini), but theres a problem:

I can't select tables containing text columns, it gives error std::bad_alloc

After some research, I've discovered slencheck function from unixODBC, using it on my driver returns

slencheck: sizeof(SQLLEN) == 4
slencheck: driver manager and driver differ!!!

How can I modify unixODBC SQLLEN so it became compatible with 64-bits Firebird ODBC driver?

I can't select text columns because of SQLLEN or there's more to this?
I think this odbc's github issue is related to mine


texts: Oracle UPDATE statements lock race condition

texts:
Say I have a table MY_TABLE without optimistic lock column. I have the following update statement that will be executed with org.hibernate.SQLQuery:

UPDATE MY_TABLE SET NAME = ? WHERE SOME_NUMBER = ? AND NAME = ?

In some place in my Java code, I'll create a new async thread to execute this update. Say that at a given time after some seconds this thread is executed:

Thread 1:

UPDATE MY_TABLE SET NAME = 'TEST2' WHERE SOME_NUMBER = 3 AND NAME = 'TEST'

This operation can take some minutes. Few milliseconds later the following thread is executed:

Thread 2:

UPDATE MY_TABLE SET NAME = 'TEST3' WHERE SOME_NUMBER = 3 AND NAME = 'TEST'

I need to make sure that, since the rows affected are the same, the UPDATE from the first thread is executed before the one from the second thread.

Does the default lock strategies from Oracle already guarantee that? Or do I need anything else?

Thanks!


texts:
I have dataset:

timestamp               event   user
2020-04-28 20:07:55.503 log_in  john
2020-04-28 20:08:01.996 log_out john
2020-04-28 20:08:02.470 log_in  john
2020-04-28 20:08:03.996 log_out john
2020-04-28 20:08:05.729 log_failed  john
2020-04-29 10:06:45.683 log_in  mark
2020-04-29 10:08:58.299 password_change mark
2020-04-30 14:19:24.921 log_in  jeff
2020-04-30 14:20:31.266 log_out jeff
2020-04-30 14:21:44.438 create_new_user jeff
2020-04-30 14:22:44.455 create_new_user jeff

How to write a sql query to count all unique events per day. the unclear part for me is the presence of hours in timestamp. The desired result looks like this:

timestamp  count
2020-04-28 3
2020-04-29 2
2020-04-30 3


texts: How to find if my innodb row uses overflow/off pages?

texts:
I use INNODB engine.

My row format is Dynamic.

For variable-length columns like TEXT, BLOB and varchar etc. , data is stored in off page and not in the same page itself.

Do I have a way to determine if my INNODB row uses additional overflow pages?

If yes, can I also get the total number of off pages used?


texts: "on duplicate key update" all the non-key fields mentioned in the insert

texts:
How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion?

insert into tablename set keyname = 'keyvalue',
    fieldname = 'fieldvalue'
    . . .
    on duplicate key update
        updatecounter = updatecounter + 1
        fieldname = 'fieldvalue'
        . . .;


texts: Error in AG Dashboard when looking at a Distributed AG

texts:
I have a Distributed AG, but whenever I go to look at the AG Dashboard it says "An error occurred on the Always On Dashboard".  When I click for details I get "Object reference not set to an instance of an object."

Is this a known bug?  Or is there possibly something about the setup of my DAG that's causing this?  I get this error in both

SSMS 2016 16.5.3 (build 13.0.16106.4)
SSMS 2017 17.4 (build 14.0.17213.0)


texts: Divide data in single column to multiple columns

texts:
I have source data like

Name Is_Trget
a     Yes
s     No
d     Yes
f     Yes
a     Yes
S     No

from this source data expected output is

Name Is_Yes Is_No
a    2      0
s    0      2
d    1      0
f    1      0

I need count of Yes and No for a specific Name.

Can someone help on this.


texts:
I recently heard a fellow co-worker mention that InnoDB rollbacks are costly and negatively impact performance. How true is this statement?

To give a context, we use spring+hibernate and set @Transactional(readonly=true) for transactions where we need to only retrieve data. We also do see large number of rollbacks in sys.statements_analysis table.


texts: Get matching part from conditional pattern

texts:
I'm having a table like this:

|id|key    |value      |
------------------------
| 0|name   |Homer      |
| 1|surname|Simpson    |
| 2|town   |Springfield|

I'm running this query:

SELECT *
FROM Search
WHERE [value] LIKE '%m%'
OR [value] like '%p%'

Is there any chance to know, which of both conditions did match?

Within one statement (so not running each of condition separately).

So for example a result like this:

|id|key    |value      |match  |
--------------------------------
| 0|name   |Homer      |%m%    |
| 1|surname|Simpson    |%m%,%p%|
| 2|town   |Springfield|%p%    |

http://sqlfiddle.com/#!18/bc0b1/3/0

EDIT:
The result may also be like this:

|id|key    |value      |match  |
--------------------------------
| 0|name   |Homer      |%m%    |
| 1|surname|Simpson    |%m%    |
| 1|surname|Simpson    |%p%    |
| 2|town   |Springfield|%p%    |

EDIT 2:
Conditions might be more than two so a generic approach (if possible) would be great


texts: Reporting services 2008 reports connection to azure sql database

texts: Due to the end of support of sql server 2008 R2 we are planning to move the database to azure as a paas service (azure sql database). Right now the application server has reporting services 2008 R2, for now it won't be moved to the cloud or upgrade his version. The users create the reports, then they upload them to reporting services and generate the desired information, My question is this: since azure sql database uses the latest version of sql server do the users or myself have to redo the reports in a newer version of report builder or visual studio data tools, or the reports will work the same way and the only thing I have to modify is the data sources.


texts: How to compare two last updated entities from two different table Postgresql?

texts:
I have 2 tables named as price and price_history. price_history table is automatically filled by triggers from price table just before updating the price table. I would like see the change at the new table named as change and fill it by triggers again just before the price table will be updated.
Function:
create or Replace function test()
returns trigger
as
$$
begin

update change set change_= SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 - SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;
return new;

end;
$$
language plpgsql;

Trigger:
CREATE TRIGGER testtrig
BEFORE UPDATE
ON price
FOR EACH ROW
EXECUTE PROCEDURE test();

I get error while using the querry --> SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 - SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;

Can you help me please?
Thanks


texts:
I have a table that has been sharded between two machines, and the same table on each machine holds different data (not replicated).

With ProxySQL, how can I create a mysql_query_rule that allows a client to perform a SELECT on the appropriate shard?

Or if I perform the query as:

  select * from table_name where id=5

Then how would ProxySQL know which shard (machine) has the record with id=5?


texts: Creating an Index on a varchar(1024) column succeeded. Does it work?

texts:
When I created an Index on a varchar(1024) column using SSMS GUI, it failed with the message:

"Warning! The maximum key length is 900 bytes. The index 'XXX' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail."

However, the same operation succeeded when I used SQL (not GUI). I'm not sure why this happened, but does this mean the index will work until the data over 900 bytes really is inserted into the column?

(The column currently doesn't have value over 900 bytes.)

environment:
- SQL Sever 2012
- Windows Server 2008 R2 SP1


texts: Can you use a SELECT ... FOR UPDATE cursor in a stored procedure to enforce row-level locking?

texts:
From https://www.oreilly.com/library/view/mysql-stored-procedure/0596100892/re37.html:
    CREATE PROCEDURE needy_bonus(  )
    BEGIN
      DECLARE  v_employee_id INT;
      DECLARE  v_salary      NUMERIC(8,2);
      DECLARE  v_last_emp    INT DEFAULT 0;

      DECLARE emp_csr CURSOR FOR
       SELECT employee_id,salary
         FROM employees
        WHERE salary <45000
          FOR UPDATE;

      DECLARE CONTINUE HANDLER FOR NOT FOUND SET  v_last_emp=1;

      START TRANSACTION;
        OPEN emp_csr;
        emp_loop:LOOP
          FETCH emp_csr INTO  v_employee_id, v_salary;
          IF  v_last_emp THEN
            LEAVE emp_loop;
          END IF;
          CALL grant_raise( v_employee_id, v_salary);
        END LOOP emp_loop;
        CLOSE emp_csr;
        SET  v_last_emp=0;

      COMMIT;

    END;

Does this method really work for row-level update locking in a stored procedure? I ask because I'm doing this myself and I'm getting some consistency issues. Is it possible I need to set the transaction isolation mode to REPEATABLE READ? I'm currently using READ COMMITTED.


texts: Can I copy a collection in mongoDB from one database to another by copying the db files directly?

texts:
I tried using cloneCollection http://docs.mongodb.org/manual/reference/command/cloneCollection/ , but clonecollection takes a lot of time in case of big collection because it starts indexing the data.

My use case :

Machine 1 : only one collection with huge data (lets say collection x) Machine 2 : more than one collection. (lets say collection y,z)

I need to copy collection x from machine 1 to machine 2 , so that machine 2 has all the collections (x,y,z) . It should also have all the indexes intact.


texts: Why does pg_hba.conf sometimes have random rules added to it? (postgresql)

texts:
I have postgres 10.4 set up on a Linux machine, and sometimes there will randomly be rules at the top of pg_hba.conf that only allows an MD5 connection by a user "pgdbadm". I'm not sure if the database got hacked but after changing the password and checking that a pgdbadm doesn't exist, I still see the new rules from time to time. Is it possible that the database is compromised?

The three rules, if I recall correctly are as follows:

local all all 0.0.0.0/0 reject
host all postgres 0.0.0.0/0 reject
host all pgdbadm 0.0.0.0/0 md5

Any explanation as to what is going on would be appreciated, as well as potential solutions.

EDIT: I have discovered that when setting up pg_hba.conf I used a rule where all connections didn't require a password. This probably allowed a bot to log in and create unwanted databases/other things, so I will do a clean install of Ubuntu and backup the databases.


texts:
I am familiar with PIVOTS but have done only simple ones of one column into a row.

I have this query:

DECLARE @EndDate DATE
SET @EndDate ='20160728' --{?EndDate}

SELECT T1.ItemCode,SUM(T0.IssuedQty) [Litres],SUM(T1.CmpltQty) [Tons]
FROM    WOR1 T0 INNER JOIN
        OWOR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
        OITM T2 ON T1.ItemCode = T2.ItemCode
WHERE   T0.ItemCode ='FURNACE OIL/0001' AND T1.Type ='S' AND
        (T1.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, @EndDate), 0)) AND @EndDate)
GROUP BY T1.ItemCode

This returns the results:

What do I need to do is to get the following results but I'm unsure how.

How do I achieve this?

EDIT: The one highlighted as duplicate is different. It is joining 2 queries for additional columns. I just need 1 query transposed to take 2 columns into rows.


texts:
I created a SQL Server Agent Job with two different schedules. One is at 6 AM, the other is at 11 AM. When it runs at 11AM, it works fine; but at 6 AM it fails and gives this error message;

  The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 12 ("@SubscriptionID"): Data type 0x4F is unknown.

What can cause this error?


texts: Improve Performance by Left Join and Group By Count - Postgresql

texts:
I am trying to write a query that does COUNT(*) on GROUP BY and a LEFT JOIN.

How do I improve the left join?

I am using the below index

create index session_opportunity_index on public.session using btree(opportunity_id,session_id)

Below is my query

  EXPLAIN ANALYZE

  SELECT session.session_id AS session_id ,session.opportunity_id AS opportunity_id,
  session.last_modified_at AS last_modified_at,count(distinct layout_feature.layout_id) AS no_of_layouts ,
  count(distinct layout_pad_data.pad_id) AS n,
  session.tags AS tags  FROM public.session
  LEFT JOIN public.layout_feature ON session.session_id = layout_feature.session_id
  LEFT JOIN public.layout_pad_data ON layout_feature.layout_id = layout_pad_data.layout_id
  WHERE session.opportunity_id=263945 GROUP BY session.session_id

  LIMIT 600 OFFSET 3

Below is my Query Plan

QUERY PLAN
Limit  (cost=910.74..921.45 rows=298 width=64) (actual time=8.219..9.243 rows=298 loops=1)
  ->  GroupAggregate  (cost=910.63..921.45 rows=301 width=64) (actual time=8.206..9.226 rows=301 loops=1)
        Group Key: session.session_id
        ->  Sort  (cost=910.63..912.58 rows=781 width=64) (actual time=8.184..8.214 rows=301 loops=1)
              Sort Key: session.session_id
              Sort Method: quicksort  Memory: 39kB
              ->  Hash Right Join  (cost=831.86..873.11 rows=781 width=64) (actual time=8.070..8.099 rows=301 loops=1)
                    Hash Cond: (layout_pad_data.layout_id = layout_feature.layout_id)
                    ->  Seq Scan on layout_pad_data  (cost=0.00..38.60 rows=660 width=8) (actual time=0.002..0.058 rows=660 loops=1)
                    ->  Hash  (cost=822.10..822.10 rows=781 width=60) (actual time=7.689..7.689 rows=301 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 23kB
                          ->  Hash Right Join  (cost=160.14..822.10 rows=781 width=60) (actual time=7.602..7.641 rows=301 loops=1)
                                Hash Cond: (layout_feature.session_id = session.session_id)
                                ->  Seq Scan on layout_feature  (cost=0.00..543.38 rows=29538 width=8) (actual time=0.002..1.716 rows=29538 loops=1)
                                ->  Hash  (cost=156.38..156.38 rows=301 width=56) (actual time=0.112..0.112 rows=301 loops=1)
                                      Buckets: 1024  Batches: 1  Memory Usage: 23kB
                                      ->  Bitmap Heap Scan on session  (cost=10.62..156.38 rows=301 width=56) (actual time=0.028..0.070 rows=301 loops=1)
                                            Recheck Cond: (opportunity_id = 123)
                                            Heap Blocks: exact=4
                                            ->  Bitmap Index Scan on session_opportunity_index  (cost=0.00..10.54 rows=301 width=0) (actual time=0.024..0.024 rows=301 loops=1)
                                                  Index Cond: (opportunity_id = 123)
Planning time: 0.318 ms
Execution time: 9.351 ms


texts: AWS RDS Postgres pg_hba.conf auth-method

texts:
I'm starting to use Amazon Web Service's Relational Database Server product (AWS RDS) and want to confirm I have not made a security blunder.

My configuration:

Postgres
Publicly accessible
Inbound rule on security group added for "PostgreSQL / My IP"

These options enable me to use pgAdmin to connect and populate data.  I've tried to determine the auth-method used in AWS RDS Postgres without success.

Am I transmitting my password in clear text when I connect with pgAdmin?

Edit:

I'm starting to think my fears are warranted.  This is what I've learned:

Directions for Using SSL with PostgreSQL DB Instance suggests it's not the default
The 'SSL' option in my pgAdmin connection properties has no options (prob'ly because I haven't setup certificates)


texts: DB2 select from final table update gives SQL0199 error

texts:
Goal: I am trying to retrieve records that were affected by UPDATE query.

Based on IBMs article I can use the following query:

SELECT empno, salary FROM FINAL TABLE
(UPDATE employee SET salary = salary * 1.10 WHERE job = 'CLERK')

However when I try to run similar query in database I get the following error:

[42601][-199] [SQL0199] Keyword UPDATE not expected. Valid tokens: INSERT.


texts: What's a good client tool to access Oracle RDBMS?

texts:
I'm looking for a good client tool to replace "Oracle SQL Developer" (the tool I currently use) which supports:

Oracle DB 10g, 10gR2, 11g
Running SQL code
Debugging SQL code (with walk-through... click error, jumps to error line)

Do you know of one, preferably free?


texts: Optimizing a group by/where in/min() query in MySQL

texts:
Assume I have a table that looks like such (The actual column names of my real table would only make things harder to explain, so I have created a mimic that behaves similarly).

id  |  cond1  | cond2  | cond3  | cond4  | cond5  |  start  |  end  | val1  | val2  |

cond1 - cond5 are condition values, and each one can take on about 2-6 values.
For example, cond1 belongs to the subset ('hold', 'setup').

start - end are varchar strings (potentially quite long) and represent the start point and end point of a path.

val1 - val2 are values. They represent the time required to complete the path, etc.

After data has been uploaded to this table it is static. There are no future updates/inserts.

The basic goal of a query sent to this table is such:

Grab all rows that meet certain condition requirements. (For example, cond1 must equal 'setup', cond2 must belong to some subset of the set of values it can take on, cond3 must belong to some subset of the set of values it can take on, cond4 must belong to some subset of the set of values it can take on, and we will say cond5 is ignored for now).
Uniquify this resultset on end where val1 is at its minimum.

Essentially, I am trying to find the number of failing ends and what their worst failure was under the condition requirements.

The query I have been using up til this point is below.

select cond1, cond2, cond3, cond4, cond5, start, end, **min(val1)**, val2
from table
where cond1='setup'
  and cond2 in ('...')
  and cond3 in ('...')
  and cond4 in ('...')
**group by end**

When these tables start to grow really big, (right now one of the tables I am using has 2.5 million rows), queries of this type take forever (aka 30 seconds).

I've played around with some indexes, but often times MySQL chooses not to use them. For example, a likely query is one where cond1='hold' and every other condition can be anything. The number of hold rows GREATLY outnumbers the number of setup rows (its safe to assume 98% of the rows are hold), so a full table scan in that scenario makes sense.

I guess my question is where am I screwing up? I am positive many of you can do a lot better than 30 seconds, and any help would be appreciated.


texts:
As per books online Differential backups are not supported on secondary replicas. Also as per this link COPY_ONLY has no effect when specified with the DIFFERENTIAL option.
I am using SQL2014 SP1. I can take differential backup with adding 'copy_only' option and I understand that does not have any effect but I am able to take differential in secondary replica. Which contradicts books online. I can use that differential backup with the full backup taken at primary replica.  Why it is allowing me to do this? Full code for reproducing the scenario I am explaining.

--Connect to primary replica
USE [AGTest]
GO
IF OBJECT_ID('dbo.t1', 'U') IS NOT NULL
    DROP TABLE dbo.t1;
GO
CREATE TABLE t1 (
    c1 INT
    ,c2 VARCHAR(10)
    );
GO
DECLARE @counter INT = 1

WHILE (@counter < 21)
BEGIN
    INSERT INTO t1
    VALUES (
        @counter
        ,REPLicate('a', 10)
        );

    SELECT @counter = @counter + 1;
END
GO
--20 rows are inserted
SELECT count(0)
FROM dbo.t1;

--Take a full backup on primary
BACKUP DATABASE [AGTest] TO DISK = N'J:\AG_Test_Full.bak'
WITH NOFORMAT
    ,NOINIT
    ,NAME = N'AGTest-Full Database Backup on primary'
    ,SKIP
    ,NOREWIND
    ,NOUNLOAD
    ,STATS = 10

--adding another 20 rows
USE [AGTest]
GO

DECLARE @counter INT = 1

WHILE (@counter < 21)
BEGIN
    INSERT INTO t1
    VALUES (
        @counter
        ,REPLicate('a', 10)
        );

    SELECT @counter = @counter + 1;
END
GO

--Taking a diff backup with 'copy_only'
--Connected to preffered secondary
IF sys.fn_hadr_backup_is_preferred_replica('AGTest') = 1
BEGIN
    PRINT 'taking backup here'

    BACKUP DATABASE [AGTest] TO DISK = N'J:\AG_Test_diff.bak'
    WITH differential
        ,copy_only
        ,NOFORMAT
        ,NOINIT
        ,NAME = N'AGTest-diff Backup on Secondary'
        ,SKIP
        ,NOREWIND
        ,NOUNLOAD
        ,STATS = 10
END
ELSE
BEGIN
    PRINT 'I am not prefered backup node'
END
GO

--Restore with different name AGTest2 using using full backup I took on primary
--You can use any replica node for this.
USE [master]
GO

RESTORE DATABASE [AGTest2]
FROM DISK = N'J:\AG_Test_Full.bak'
WITH FILE = 1
    ,MOVE N'AGTest' TO N'L:\AGTest2.mdf'
    ,MOVE N'AGTest_log' TO N'F:\AGTest2_log.ldf'
    ,STANDBY = N'J:\AGTest_RollbackUndo_2016-10-18_07-53-01.bak'
    ,NOUNLOAD
    ,STATS = 5
GO

USE [agtest2]
GO
--should give a count of 20
SELECT count(0)
FROM dbo.t1;

--Restoring the diff I took on secondary with copy_only option
USE [master]
GO

RESTORE DATABASE [AGTest2]
FROM DISK = N'J:\AG_Test_diff.bak'
WITH FILE = 1
    ,MOVE N'AGTest' TO N'L:\AGTest2.mdf'
    ,MOVE N'AGTest_log' TO N'F:\AGTest2_log.ldf'
    ,STANDBY = N'J:\AGTest_RollbackUndo_2016-10-18_07-53-01.bak'
    ,NOUNLOAD
    ,STATS = 5
GO

USE [agtest2]
GO
--should give a count of 40
SELECT count(0)
FROM dbo.t1;


texts: How can I backup my large SQL Server table?

texts:
Environment: SQL Server 2008 R2 Standard Edition on Windows Server 2008 R2 (yeah - I know).  VMWare, Commvault.

We have a 1.4 TB DB with one table.  Here is the schema:

CREATE TABLE [dbo].[FileManager_FilesContent](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [GUID] [uniqueidentifier] NOT NULL,
    [FileContent] [varbinary](max) NOT NULL,
    [Extension] [varchar](20) NULL,
    [IsDeleted] [bit] NULL,
 CONSTRAINT [PK_FileManager_FilesContent] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_FileManager_FilesContent] ON [dbo].[FileManager_FilesContent]
(
    [GUID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

We have a network share where we backup the other DBs.  This one won't backup there because it would take more than 40 hours.  My thought is a local drive (or drives) plus backup compression, striping, and dialing in maxtransfersize and buffercount might help.

Any other advice on how to get this DB backed up?

Thanks in advance,
-Peter


texts: What is the difference between public_dependency and dba_dependencies?

texts:
I know that PUBLIC_DEPENDENCY has only the objects and references Ids, and the DBA_DEPENDENCIES does not have the Ids, but the Type, Owner, Name, etc of them.

But beyond that, what is the difference between both? When I do a count on my database, they return different values. And PUBLIC_DEPENDENCY has more elements.


texts: Reporting Services buttons show up one under the other instead of side-by-side

texts:
This seems a browser problem to me.  When I render a reporting services report in IE11, the "Export", "Refresh" and "Export to Data Feed" buttons show up under each other taking up a lot of extra screen space.  Does anybody know what could be causing this?

I am running Reporting Services 2012:

My IE version:

and here is how the buttons appear:


texts: Which technology to synchronise SQL Server and MySQL data?

texts:
Which technology should be used if I want to move my data from SQL Server to MySQL server when data is entered to SQL Server row by row and getting feedback from MySQL server?

Should it be  1. Linked-Server or 2. SSIS?

Also,

Each time I insert a row in SQL Server it should also insert into MySQL and when it is done successfully I should update a flag column in SQL Server to 1 for acknowledgement of data insertion.
What if I update MySQL table. Would it be reflected in SQL Server also?


texts: Consistency errors in database table after upgrade to SQL Server 2012

texts:
I keep getting a weird problem with some databases after upgrade to SQL Server 2012 (either from SQL Server 2005 or from SQL Server 2008 R2).

The backup (.bak) is fine on older version. I do DBCC CHECKDB on SQL Server 2008 R2 and everything is fine. I create a .bak file, restored it on SQL Server 2012 and run DBCC CHECKDB -> 2 consistency errors in table.

Using suggestions from the article "Upgrading To SQL 2012: Ten Things You Don’t Want To Miss" I tried DBCC CHECKDB WITH DATA_PURITY; before and after upgrade.

Is this a know/common issue?

The error details are:

  Msg 8970, Level 16, State 1, Server NUX2012\S11, Line 1
  Row error: Object ID 277576027, index ID 2, partition ID 581141175926784, alloc unit ID 581141175926784 (type In-row data), page ID (1:258), row ID 1. Column 'ID' was created NOT NULL, but is NULL in the row.

  Msg 8970, Level 16, State 1, Server NUX2012\S11, Line 1
  Row error: Object ID 277576027, index ID 2, partition ID 581141175926784, alloc unit ID 581141175926784 (type In-row data), page ID (1:258), row ID 2. Column 'ID' was created NOT NULL, but is NULL in the row.

Obviously I checked the table in question and the ID column doesn't have any rows with NULL values.


texts: Increase Oracle buffer cache gets slower

texts:
So we're contemplating increasing our Oracle 11gR2 buffer cache.  They said they tried it in the past and things appeared to get worse - so they undid the change.  They, however, have no idea why things could have gotten worse.

Its possible they were preemptive and just didn't give it time considering it was full server reboot, but what are the situations where increasing the cache would yield worse results?  I've seen this page (situation B) but I can't find anybody else claiming that.  We're definitely doing more scattered reads than 'normal' probably given the kinds of queries we have to run.


texts: MySQL How to Script to create indexes on all date columns in database

texts:
I have a lot of tables in the database that are using a datetime stamp - I need to create a script that will create indexes on all columns in each table that does not already have an index .

So far I can get all the columns of type date, time etc..
I can get all of the existing indexes .. but I am having trouble combining them , as I intend to

Select (CONCAT('ALTER TABLE ' , table_name , ' ADD INDEX (' , column_name , ');') FROM
MyIdentifiedColumns ..

The goal here is to create indexes on all of these columns of type date, timestamp where no index currently exists with a script - NOT manually in workbench .. So how can I do this ?

Get my Columns in the database that are of the proper date time

select col.table_schema as database_name,
       col.table_name,
       col.ordinal_position as column_id,
       col.column_name,
       col.data_type,
       col.datetime_precision
from information_schema.columns col
join information_schema.tables tab on tab.table_schema = col.table_schema
                                   and tab.table_name = col.table_name
                                   and tab.table_type = 'BASE TABLE'

where col.data_type in ('date', 'time', 'datetime', 'timestamp')
      and col.table_schema not in ('information_schema', 'sys',
                                   'performance_schema', 'mysql')
     and col.table_schema = 'mydatabase'

And to get all of the Indexes currently in the database

select index_schema,
       index_name,
       group_concat(column_name order by seq_in_index) as index_columns,
       index_type,
       case non_unique
            when 1 then 'Not Unique'
            else 'Unique'
            end as is_unique,
        table_name
from information_schema.statistics
where table_schema not in ('information_schema', 'mysql',
                           'performance_schema', 'sys') AND index_schema = 'mydatabase'
group by index_schema,
         index_name,
         index_type,
         non_unique,
         table_name
order by index_schema,
         index_name


texts:
I am trying to setup repmgr using the autofailover quick setup tutorial provided on Github Link

But I am kind of stuck on the witness section, when trying to create the witness with repmgr -d repmgr -U repmgr -h [MASTERIP] -D /var/lib/postgresql/9.1/witness -f /var/lib/postgresql/9.1/repmgr/repmgr.conf witness create

I get the following error:

Connection to database failed: fe_sendauth: no password supplied

I am not sure which database the error is talking about? Is it the master's DB or the witness'?

Also, I haven't been able to find a password parameter to pass to repmgr yet, is there even one?

Below are the repmgr.conf files for each server:

Witness:

cluster=main
node=3
node_name=witness
conninfo='host=[WITNESS IP] dbname=witness user=witness password=witness  port=5499'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

Standby:

cluster=main
node=2
node_name=node2
conninfo='host=[STANDBY IP] dbname=[DB NAME] user=[DB USER NAME] password=[PASSWORD]'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'

Master:

cluster=main
node=1
node_name=node1
conninfo='host=[MASTER IP] dbname=[MASTER DB] user=[MASTER DB USER] password=[PASSWORD]'
master_response_timeout=60
reconnect_attempts=6
reconnect_interval=10
failover=automatic
promote_command='promote_command.sh'
follow_command='/usr/lib/postgresql/9.1/bin/repmgr standby follow -f /etc/repmgr/repmgr.conf'


texts: mysqlslap number in sql statement to change with iteration

texts:
I am trying to figure out how to increment a number in a mysqlslap query with every iteratiom.

Let me explain what I exactly mean:

I got a query:

SELECT age FROM member WHERE id = [some number]

is there a way to write mysqlslap parameter, so it iterates the [some number] while executing the query multiple time ? I do not want to keep doing the same query, as it caches.

I went through the documentation on http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
but did not really find the straight answer to my question.

If there is not mysqlslap solution, maybe there is a way to achieve this with some other tool.

Cheers for help !


texts: How to find maximum of average grouped by in sql

texts:
I have three tables Faculty {id, name}, Student {id, faculty_id, name}, Course_student {id, student_id, mark}

And i would select top student in each faculty

I'm tired to perform this in multiple ways but i can't

SELECT Faculty.name, Student.name, MAX(AVG(mark))
FROM (Faculty inner join Student On Faculty.id = Student.faculty_id) inner join Course_student On Student.id = Course_student.student_id
group by Faculty.name


texts:
I have archive_mode = on and archive_command = 'cp %p /var/lib/postgresql/11/main/archive/%f'

What is retention policy of files under archive/? Will pg clean up it periodically or let it pile up until administrator panic and get involved?


texts: How to make a function return a dynamically named column?

texts:
Simple pseudocode example:

create function myfunc (someparam text, desired_return_column_name text)
returns table (somekeyname int, MAGIC_DYNAMIC_NAME text)
language SQL
as $funcdef$
  select
    somekeyname,
    fieldofinterest as <MAGIC HERE?> desired_return_column_name
  from the_base_table
  where some_other_field = someparam
$funcdef$;

In English, how can I make an SQL function in Postgres (9.6) return a table with statically defined types but where one of the returned column names is named according to a parameter passed in?

If this is not possible with an SQL function, is it possible with a PL/pgSQL function?


texts:
I'm running SQL Server 2016 and I'm getting this error:

  There is insufficient system memory in resource pool 'default'

...when running batch queries with millions of rows.

I decreased the batch size from 1000 to 100 and I still get this error. I have shown what my current settings are at for my SQL Server.

I didn't change any of these settings. I have the SQL Server installed on my 2TB hard drive.

I can share the code but basically I'm posting inserts for around 3 million rows into a table in my database. I can't even make one of my tables memory optimized because it gives me the same error when I try.

Everything I have checked makes it look like it has plenty of memory so I'm not sure what to do next. This only seems to happen when I'm doing batch queries on my memory optimized tables.

I tried the command ALTER RESOURCE GOVERNOR RESET STATISTICS as suggested in SQL2014EE 'Insufficient System Memory' message by Henk VanderValk. I thought it worked at first, but it hasn't worked on memory optimized tables, though it seemed to work on regular tables. I'm almost positive this has to do with the fact that I'm using mostly memory optimized tables but I don't know what to try.

UPDATE: I followed the steps at https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/bind-a-database-with-memory-optimized-tables-to-a-resource-pool and now I get the same error message but with the name of my pool instead. I also have the full details for my pool below but I'm not sure how to change the max memory that the pool is pulling from


texts: Timeout error after SQL Server upgrade from 2008R2 to SQL Server 2014

texts:
We had a SQL Server 2008R2 Enterprise edition for our database to support a front-end application. We never had any timeout issues before. Recently the company decided to upgrade the database in to SQL Server 2014 Enterprise edition with 2 node always on cluster setup. The new server has better CPU, Memory than the old server.

After the upgrade I made all the necessary modifications, Checking Database consistency, run DBCC UPDATEUSAGE, update Statistics, index rebuilding, recompiling the stored procedures and so on. The database switching and migration all went well. However, our users start complaining about timeout issues.

I have been reviewing different articles,blog posts and made some modification like changing the connection string and add MultiSubnetFailover = 'True', which seems helped a lot and minimize the frequency of timeout but still the issue is there.  Does anyone know what can cause this issue and how to resolve it?  I would highly appreciate your suggestions and recommendation to resolve this problem.


texts:
I have a database setup with the following schema:

CREATE TABLE siteInfo (
    siteID             SERIAL  NOT NULL,
    siteName           TEXT    NOT NULL,
    siteHost           TEXT    NOT NULL,
    notes              TEXT    NOT NULL,
    allowableCountries TEXT    NOT NULL,
    displayChance      INT     NOT NULL,
    imagePath          TEXT    NOT NULL,
    PRIMARY KEY (siteID)
);

CREATE TABLE siteLinks (
    siteID             SERIAL  NOT NULL,
    URL                TEXT    NOT NULL,
    FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);

CREATE TABLE siteDemographicLinks (
    siteID             SERIAL  NOT NULL,
    URL                TEXT    NOT NULL,
    country            TEXT    NOT NULL,
    chance             INT     NOT NULL,
    FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);

CREATE TABLE linkVisits (
    siteID             SERIAL  NOT NULL,
    rawVisit           BIGINT  NOT NULL,
    redirects          BIGINT  NOT NULL,
    FOREIGN KEY (siteID) REFERENCES siteInfo(siteID) ON DELETE CASCADE
);

Data from my form is in the following format:

array(7) {
["siteName"]=>string(9) "Site Name"
["siteHost"]=>string(9) "SIte Host"
["notes"]=>string(5) "Notes"
["links"]=>array(2) {[0]=>string(6) "Link 1" [1]=>string(6) "Link 2"}
["countries"]=>string(4) "None"
["displayChance"]=>string(2) "15"
["extraLink"]=>array(2) {
   [0]=>array(3) {
      ["url"]=>string(7) "dSite 1"
      ["countries"]=>string(4) "NONE"
      ["chance"]=>string(2) "11"
      }
   [1]=>array(3) {
      ["url"]=>string(6) "dSite2"
      ["countries"]=>string(4) "None"
      ["chance"]=>string(2) "13"
      }
   }
}

How would I go abound inserting data into the many type tables so they reference tuples inserted into the one type table?

I have this query constructed:

INSERT INTO siteInfo (siteName, siteHost, notes, allowableCountries, displayChance, imagePath)
VALUES ('Sample', 'sample.com', 'note', 'NONE', '50', '../var');

I would then need to insert data into the siteLinks table, which is related to the entry already in the siteInfo table.

INSERT INTO siteLinks (siteID, URL)
VALUES ((SELECT siteid FROM siteinfo WHERE sitename='Sample'), 'test');

Is there a better way of doing this?


texts: SQL Server 2014 compile Stored Procedure response

texts:
I have several SQL Servers and databases and when I compile a Stored Procedure I receive one of two responses...

ALTER_PROCEDURE - dbo.usp_MyProcedure

or

Command(s) completed successfully.

My question is why did I receive different responses. The setting has to be at the server level somewhere, because every database on that server where I compile the SP I get the same message.  I received the alter message on 17 databases on 3 servers, and the command completed message on 19 databases on 7 servers.

I use Redgate's Multi Script to push the stored procedure at the same time, I know it is not in Multi Script due to compiling the SP using SSMS yields the sames responses.
Can anybody point me to the setting that controls this response?


texts:
How do I grant execute permissions to select client_addr from pg_stat_replication; for a regular PostgreSQL user?

Always after creating a function, give execute rights

grant execute on function client_addr_pg_stat_replication() to user;

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS text AS $$
BEGIN
  PERFORM client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;

Result:

select * from client_addr_pg_stat_replication();
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication()

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS SETOF RECORD AS $$
BEGIN
  select client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;

Result:

select * from client_addr_pg_stat_replication();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from client_addr_pg_stat_replication();

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication(TEXT)
    RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
    RETURN QUERY SELECT client_addr FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Result:

select * from client_addr_pg_stat_replication(client_addr);
ERROR: column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);

Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS TABLE(client_addr varchar) AS
$$
BEGIN
  SELECT client_addr FROM pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;

Result:

select * from client_addr_pg_stat_replication();
ERROR:  column reference "client_addr" is ambiguous
LINE 1: SELECT client_addr FROM pg_stat_replication
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT client_addr FROM pg_stat_replication
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication() line 3 at SQL statement

Make the function

CREATE OR REPLACE FUNCTION public.client_addr_pg_stat_replication(varchar)
    RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
    RETURN QUERY SELECT * FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

Result:

select * from client_addr_pg_stat_replication();
ERROR:  function client_addr_pg_stat_replication() does not exist
LINE 1: select * from client_addr_pg_stat_replication();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
redcheckpg=> select * from client_addr_pg_stat_replication(client_addr);
ERROR:  column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);

Thank you


texts: Log all user error messages for retrospective troubleshooting

texts: Suppose I want to log all severity 16 (and higher) user error messages to a database table in order to allow some retrospective application troubleshooting (since the answer to "Can you send me the error message?" is quite often "No, I closed it.") Is a server-side trace going to be the most effective option, or would there be an even better approach?


texts: Getting Index Scan instead of a possible Index Seek?

texts:
Currently learning a few things on query optimization, and I've been trying out different queries and stumbled upon this "issue".

I am using the AdventureWorks2014 database, I ran this simple query:

table structure (taken from https://www.sqldatadictionary.com/AdventureWorks2014.pdf):

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 100

returns 19,720 rows

total number of rows in Sales.Customer = 19,820

And after checking to make sure CustomerID is in fact not just the PK of the table but also has a clustered index on it (yet it uses a non clustered index), that indeed is the case:

EXEC SP_HELPINDEX 'Sales.Customer'

Here is the execution plan ↓

https://www.brentozar.com/pastetheplan/?id=B1g1SihGr

I have read that when faced with huge amounts of data and/or when it returns more than 50% of the data set, the query optimizer will favor an index scan. But that table as a whole barely has 20,000 rows (19,820 to be exact), it's not a big table by any means.

When I run this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 30000

returns 118 rows

https://www.brentozar.com/pastetheplan/?id=Byyux32MS

I get an index seek instead, so I thought it was due to that "more than 50% case" however, I also ran this query:

SELECT C.CustomerID
FROM Sales.Customer AS C
WHERE C.CustomerID > 20000

returns 10,118 rows

https://www.brentozar.com/pastetheplan/?id=HJ9oV33zr

And it also used an index seek, even though it was returning more than 50% of the data set.

So what's going on here?

EDIT:

With IO Statistics turned on, the >100 query returns:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

While the >20,000 one returned:

Table 'Customer'. Scan count 1, logical reads 65, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So added WITH(FORCESCAN) option to the >20,000 one to see what would happen:

Table 'Customer'. Scan count 1, logical reads 37, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So it ends up running better with an Index Scan (less logical reads), even though the query optimizer chose to run an Index Seek for this particular query.


texts: Function returning table : access individual fields

texts:
I have a plpgsql function declared like this :

CREATE OR REPLACE FUNCTION dynamiccondition_byid(idcond INTEGER, OtherFilters TEXT)
RETURNS TABLE (id INTEGER, weight INTEGER)

I can get the individual values like this :

SELECT * FROM dynamiccondition_byid(1,'');

Now I have a table with several conditions and I want to get all the values for all conditions.

SELECT iddyncondition,dynamiccondition_byid(iddyncondition,'')
FROM dynconditions

Now I'm getting two columns : one with the iddyncondition and one with the result of the function in which the 2 values are combined with parens :

iddyncondition   dynamiccondition_byid
--------------   ---------------------
1                (1234,5)
2                (5678,10)

I want to use the individual values (these are not arrays, I don't know what they are really).

The only way I have found is the following but I'm pretty sure the function is called twice for each line and performance is an issue :

SELECT iddyncondition
      ,(dynamiccondition_byid(iddyncondition,'')).id
      ,(dynamiccondition_byid(iddyncondition,'')).weight
FROM dynconditions

I would like to retrieve the data in a CTE like this (I'm using an array syntax so you get the idea but I know it is wrong) :

WITH dc AS (
  SELECT iddyncondition,dynamiccondition_byid(iddyncondition,'') AS dcrec
  FROM dynconditions
)
SELECT r.amountpaid,dc.weight
FROM rights r
JOIN royaltiesad ra USING(idoeu)
JOIN dc ON dc.iddyncondition=ra.iddyncondition and dc.dcrec[0]=r.idright;


texts: How to speed up a query with a lot of subqueries

texts:
I have a query which uses a series of functions to return the status of each document for  a  loan.

SELECT loan_number,
  borrower_name,
  get_application_status(loan_number, date_application_ordered, date_application_verified),
  get_appraisal_status(loan_number, date_appraisal_ordered, date_appraisal_verified),
  get_endorsement_status(loan_number, date_endorsement_ordered, date_endorsement_verified),
  get_waiver_status(loan_number, date_waiver_ordered, date_waiver_verified),
  get_insurance_status(loan_number, date_insurance_ordered, date_insurance_verified)
FROM loans_table
WHERE loan_type IN ('A', 'B', 'C')
AND filing_date > TO_DATE('30-OCT-12')
AND filing_date <= TO_DATE('13-NOV-12');

It returns several hundred rows and takes around 30 seconds. I suspect that it is taking so long because each function uses a subquery with a MAX function, like this:

FUNCTION get_waiver_status (loan_number IN NUMBER, date_ordered IN DATE,
                            date_verified IN DATE) RETURN varchar2
AS
last_upload_date DATE := NULL;
BEGIN
  IF date_ordered IS NULL THEN
    RETURN 'Not Ordered';
  END IF;
  SELECT MAX(upload_date) INTO last_upload_date
  FROM documents_table
  WHERE documents_table.loan_number = loan_number
  AND document_type = 'waiver';
  IF last_upload_date >= date_verified THEN
    RETURN 'Not Verified Since Last Upload';
  ELSE IF last_upload_date < date_verified THEN
    RETURN 'Verified';
  END IF;
END get_waiver_status;

How do I get the query to return faster? I have made indexes on loan_number and filing_date. I tried creating a trigger on documents_table to store the last upload_date of a document into a new table for the function to retrieve, but if a document is deleted, I need to be able to select the next latest upload_date from documents_table, which I can't do from within the trigger because I get a mutating table error. I am using PL/SQL on Oracle 10g R2.


texts: How to see what was executed in the database via binlogs for specific date?

texts: I am trying figure out how to "query" binlogs to see what was exectued in the database for a specific date. For example, if I execute mysqlbinlog binlog then the thing just runs forever and it is had to see what exactly was executed there, especially if binlog is huge. Thank you!


texts: Reset MongoDB root Password on Amazon Linux

texts:
I have inherited a MongoDB and no one knows the root password. Is there a way to change it? I am running MongoDB shell version: 3.2.17.

I would like to back up the DB's but need access to see what's there first


texts: Issues with uninstalling and re-installing SQL Server 2008 R2

texts:
I have installed SQL Server 2008 R2, and was able to login and also create a database and everything.

When I was facing some problems with connecting my .NET project with SQL Server, I created some new accounts in SQL Server.

For some reason I uninstalled it. But later when I re-installed, I was not able to login with the "SA" account. I still see a few of the other user accounts that I created earlier.

Is there any way to completely uninstall SQL Server 2008 R2 and successfully install new copy and login?


texts: Good learning source for learning normalization

texts:
Can anyone recommend a good learning source to learn about database normalization from scratch please?

I referred to many youtube videos but they weren't fit to me.


texts:
I made a MariaDB Tuning course, and it's very important to test the system with real traffic and real data, simulate a Production server basically.

The data is easy to replicate, just a dump, or even replication for have the database updated. But how do you test your traffic for make statistics? You can't replicate your users and their consumption of the server's resources, can you?

Do you know any tool that I could use for simulating this traffic?


texts:
while i'm executing this command  mysqld --initialize --user=mysql --datadir=/var/lib/mysql  , i'm getting this error :

2019-07-11T10:07:04.599756Z 0 [ERROR] Unknown suffix 'l' used for variable 'server_id' (value 'logBinIndex')

2019-07-11T10:07:04.599796Z 0 [ERROR] mysqld: Error while setting value 'logBinIndex' to 'server_id'

2019-07-11T10:07:04.599815Z 0 [ERROR] Aborting

I'm not using systemctl or service command for executing this, i'm using custom script to executing this command.

Using Environments;

Ubuntu 18.04
mysql Distrib 5.7.26


texts: Is a three-way associative relationship correct in this case

texts:
So I have started modelling some entity relationships.

It goes as follows:

A Mandate represent a client. A row for a client (Mandate) will only appear once in the Mandate table.
Each Mandate can have one or more Asset, and the same Asset can be had by different Mandates. This is relationship is modelled through the Mandate Asset Bridge, in which a Mandate ID will be linked to one or more Asset ID.
The goal of the Asset table is for an asset to be on a single row of the table (thus no two same assets will appear).
The issue comes when considering Security. The security are parameters for Asset and are dependent on the Mandate, so Mandate1 could have Asset1 with Security1, and Mandate2 could have Asset1 with Security2. There cannot exist an entry for Security without it being linked to an Asset.
At the moment, given the model, the same Asset will need to be duplicated for all different Security parameters. So while in Asset, the Bloomberg ID, Name, Underlying, etc... will always be the same, in the example above Asset1 will need to be appear twice, once with the Security1 FK and once with the Security2 FK.
This to me breaks some normalisation i.e., if the Bloomberg ticker changes, I have to go through the Asset table, and change that all occurrences of the same Asset

Solution

One possible way of solving this I guess is to create a three-way bridge table, which in my case would contain MandateID, AssetID, and Security ID.

I've never encountered a situation like this. Is this a good way of doing it? Intuitively I would say no, it does not seem very elegant, but I'm not sure.

See below for what a three-way associative relation would look like.

Let me know if anything is not clear.


texts: SQL Server 2012 - Can't use AlwaysOn HA. What other options do I have?

texts:
I have 2 SQL Server 2012 machines. One is production and the other will be for backup. I was looking at using the AoHA but it requires

SQL Server 2012 Enterprise for server OS
an AD domain

I have neither Enterprise or a domain so that throws out the AoHA option.

I was investigating mirroring as that looks to be my next best option but all the documentation I'm reading on MSDN, etc says that this feature will be removed.

For my situation will mirroring work? I have about 50GB worth of DB's and would like to have them duplicated on a 2nd server in case the primary goes down or something.

Quick update - based on comments I looked into Log Shipping and that solved my issues. My DB's are now backing up on a secondary server via the Log Shipping method (I also offsite backup the DB's as well nightly). Thanks for the help.


texts:
I have a create table ext_file (....) type oracle_loader query that opens a text file to pull data out of it. The access parameters are:

records delimited by newline
fields terminated by '\t'
optionally enclosed by '\"'
ltrim
missing fields are null

The problem I just noticed is that one of the fields is height and it is stored in the text file as:

"5'6.35""

The use of a " inside a "-enclosed field without an escape is causing every record with height to be kicked out.

How can I tell oracle_loader to accept these values (and I really could care less if the " makes it in or if the field is trimmed to 5'6.35).

I have two solutions, neither of which are optimal:
1. Use sed to escape the " inside the " as \"
2. Omit the optionally enclosed by '\"' line, which will break lines in which the field terminator is inside a field


texts: Duplicate database and keep it up to date - Replication or SSIS?

texts:
I would like to duplicate a database, on the same server, and keep it up to date either by running a scheduled service once a day or having SQL Server 2008 take care of this internally.

We don't need to transform data, just copy from DatabaseA to DatabaseB (but never from DatabaseB to DatabaseA).

Would you set this up under the Replication services or an SSIS job? What are the advantages and disadvantages of each?

The reason I am doing is so I can have a staging application which read and writes to a staging database, but then every night any new data from the live database is pulled in to the staging database.

The live database is updated daily with new events and we want to ensure that the staging environment resembles the live environment as much as possible.

Thanks,
Greg.


texts:
I have a case where i need to have a dimension based on measure value.
example :

the fact table is linked to date dimension through DateID,
and what i want to add is another dimension for Tier, but the problem is that there's no specific range for date selection and it can be whatever the user select on the date dimension.

I can't do it in data warehouse as the date range to calculate and group the amount into each tier is not predefined. So is it possible to create something like this?

the only way i can achieve close to this is using analysis cube but it'll only come out as row
example :

Thanks in advance.


texts: Refactoring subquery to JOIN and CROSS APPLY, get only one row for each record in parent table

texts:
Given the following query:

SELECT
  p.ProductName,
  CASE
    WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND HasImage = 1)
    THEN
      1
    ELSE
      0
    END
  AS HasImage,
  (SELECT Sum(StockBalance) FROM Product WHERE ProductSuperID = p.ProductSuperID) AS StockBalance,
  CASE
    WHEN EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND Price IS NULL)
    AND EXISTS(SELECT 1 FROM Product WHERE ProductSuperID = p.ProductSuperID AND DiscountPrice IS NULL)
    THEN
      0
    ELSE
      1
    END
    AS HasPrice
FROM ProductSuper p

-- SCHEMA
CREATE TABLE ProductSuper
(
  ProductSuperID int,
  ProductName varchar(255)
)
CREATE TABLE Product
(
  ProdID int,
  ProductSuperID int,
  HasImage bit,
  StockBalance int,
  Price decimal(10,2),
  DiscountPrice decimal(10,2)
)
INSERT INTO ProductSuper
  (ProductSuperID, ProductName)
VALUES
  (1, 'Product 1'),
  (2, 'Product 2')
INSERT INTO Product
  (ProductSuperID, HasImage, StockBalance, Price, DiscountPrice)
VALUES
  (1, 0, 10, 10.00, 9.00),
  (1, 0, 0, 10.00, 9.00),
  (2, 0, 10, 10.00, 9.00),
  (2, 0, 2, 10.00, 9.00),
  (2, 1, 5, 10.00, 9.00)

I want to learn how could I best rewrite it to use either JOIN or CROSS APPLY, if anything to avoid some code duplication. I tried writing a JOIN-based version (and one with APPLY) but I'm getting more than one result for each row in the ProductSuper table, whereas I only want one row.

i.e. expected result:

+-------------+-----+-----+-----+
|  Product 1  |  0  |  10 |  1  |
+-------------+-----+-----+-----+
|  Product 2  |  1  |  17 |  1  |
+-------------+-----+-----+-----+

(I'm aware that for this particular piece of code there is little benefit from rewriting, since the subqueries are fast. But still, this is only an example.)

Thanks.


texts: How to backup and restore in oracle with RMAN

texts:
I want to complete a task of backup and restore using oracle in the simplest way possible because I never did it before and I don't really know how to do it. What I want to do is backup the database, and inside the database I'll have a user with one table. I'll back up in this state, and I want to delete that table, and restore the database in a previous state where it had the table. All I tried is here, but still I can't solve it. Oracle isn't restoring correctly a backup

I'm asking again so maybe now somebody knows how to do it.


texts:
We have following tables - Action, Employee and ActionAssignedTo

I am trying to write a query to fetch an Action with id : 10 that has been assigned to emp_id : 1000.
 the reply of query should also contain all the other assignees for the same action i.e. same action can be assigned to multiple people

select     Action.name,
           completed_At,
           group_concat(AssigneeNameTable.name) as assignedTo,
           group_concat(AssigneeNameTable.id) as assignedToId,
           ActionAssignedTo.action_Id as actionId
from       Action

inner join Employee
on         Action.emp_Id = Employee.id and Employee.vendor_Id = 1

inner join ActionAssignedTo
on         Action.id = ActionAssignedTo.action_Id
and        ActionAssignedTo.action_Id = 10

inner join Employee as AssigneeNameTable
on         ActionAssignedTo.emp_Id = AssigneeNameTable.Id

where     ActionAssignedTo.emp_Id = 1000
and       group by Action.id
limit 2

CREATE TABLE `Action` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `emp_Id` int(10) unsigned NOT NULL,
      `name` varchar(60) NOT NULL,
      `updated_action_at` datetime(3) DEFAULT NULL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `notes` varchar(400) DEFAULT NULL,
       PRIMARY KEY (`id`),
       KEY `action_empId_fk` (`emp_Id`),
      CONSTRAINT `action_empId_fk` FOREIGN KEY (`emp_Id`) REFERENCES `Employee` (`id`) ON DELETE CASCADE,
    ) ENGINE=InnoDB AUTO_INCREMENT=502004 DEFAULT CHARSET=latin1

CREATE TABLE `ActionAssignedTo` (
  `action_Id` int(10) unsigned DEFAULT NULL,
  `emp_Id` int(10) unsigned DEFAULT NULL,
  KEY `actionassignedto_emp_id_foreign` (`emp_Id`),
  KEY `actionassignedto_action_id_foreign` (`action_Id`),
  CONSTRAINT `ActionAssignedTo_ibfk_1` FOREIGN KEY (`emp_Id`) REFERENCES
  `Employee` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ActionAssignedTo_ibfk_2` FOREIGN KEY (`action_Id`) REFERENCES `Action` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned DEFAULT NULL,
  `name` varchar(40) NOT NULL,
  `mobile_Number` varchar(15) NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `employee_vendor_id_foreign` (`vendor_Id`),
  CONSTRAINT `employee_vendor_id_foreign` FOREIGN KEY (`vendor_Id`) REFERENCES `Vendor` (`vendor_Id`)
) ENGINE=InnoDB AUTO_INCREMENT=511 DEFAULT CHARSET=latin1

Now, lets say we have a Action with id = 10 and its Assigned to 2 Employees with id: 1000 and id: 1001

ActionAssignedTo Table :

action_Id |  emp_Id
-------------------
10        |  1000
10        |  1001

My current query only returns a single record with emp_Id id : 1000. How can I modify the query so that the query returns both the assigness but it also checks that the

ActionAssignedTo.emp_Id = 1000 (as only Employee who either created or are assigned that action can view the action).


texts: Azure Data warehouse - User defined function issues

texts:
Anyone here had luck with creating and using UDFs on Azure data warehouse database? I am in the middle of migrating an on-prem warehouse from SQL Server 2014 to Azure datawarehouse and I ran into an issue with UDFs.

CREATE FUNCTION dbo.fn_GetImpliedRate (@Multiple float, @term int)
RETURNS float
AS
BEGIN
    DECLARE @ImpInt float
    IF(@Term = 1)
        SET @ImpInt = (select [1] from  dbo.ImpliedRate where Multiple = @Multiple);
    IF(@Term = 2)
        SET @ImpInt = (select [2] from  dbo.ImpliedRate where Multiple = @Multiple);
    IF(@Term = 3)
        SET @ImpInt = (select [3] from  dbo.ImpliedRate where Multiple = @Multiple);
    IF(@Term = 4)
        SET @ImpInt = (select [4] from  dbo.ImpliedRate where Multiple = @Multiple);

RETURN @ImpInt

END;
GO

This UDF works perfectly on SQL Server 2014. When I create this on Azure data warehouse, it gets created but it doesn't work when I query it. It returns a NULL. I have verified obvious things like whether the target table exists etc. All check. I looked at CREATE FUNCTION documentation for Azure data warehouse and it has an example UDF that converts int to a decimal. This works flawlessly on Azure DW. The moment I write a simple function that has a select, it fails.  Unfortunately Azure's documentation here is not really helpful and I was wondering if any of you ran into this issue. If yes, how did you resolve?

I just tested another use case and it also doesn't work:

CREATE function [dbo].[fn_GetNumberBusinessDays]
(
    @StartDate datetime,
    @EndDate Datetime
)
returns int
as
begin

DECLARE @NDAYS INT = 0
SELECT @NDAYS =
  ISNULL( (DATEDIFF(dd, @StartDate, @EndDate) + 1)
  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) - 1 ,0) + 1

 SELECT @NDAYS = @NDAYS - COUNT(*)
 FROM dbo.FedHolidays
 WHERE DateOfHoliday BETWEEN @StartDate AND @EndDate

 RETURN @NDAYS
end
GO


texts:
I have table (plots) with data witch read only access.
In QGIS there is tool which transforms "normal" table into one which logs changes.
Tool generates SQL like this:

ALTER TABLE public.plots
ADD "id_hist" serial, ADD "created_at" timestamp, ADD "deleted_at" timestamp, ADD "user_role" varchar;

ALTER TABLE public.plots DROP CONSTRAINT "plots_pkey", ADD PRIMARY KEY ("id_hist");

CREATE VIEW public.plots_current AS
SELECT "id_hist", "id", "geom", "number", "precinct"
FROM public.plots
WHERE "deleted_at" IS NULL;

CREATE OR REPLACE FUNCTION public."plots_at_time"(timestamp)
RETURNS SETOF public.plots_current AS $$
SELECT "id_hist", "id", "geom", "number", "precinct"
FROM public.plots
WHERE (
    SELECT CASE
        WHEN "deleted_at" IS NULL THEN ("created_at" <= $1)
        ELSE ("created_at" <= $1 AND "deleted_at" > $1)
      END
  );
$$ LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION public."plots_update"()
RETURNS TRIGGER AS $$ BEGIN IF OLD."deleted_at" IS NOT NULL THEN RETURN NULL;
END IF;
IF NEW."deleted_at" IS NULL THEN
INSERT INTO public.plots ("id", "geom", "number", "precinct", "created_at", "deleted_at")
VALUES (OLD."id", OLD."geom", OLD."number", OLD."precinct", OLD."created_at", current_timestamp);
NEW."created_at" = current_timestamp;
NEW."user_role" = current_user;
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION public."plots_insert"()
RETURNS trigger AS $$ BEGIN if NEW."created_at" IS NULL then NEW."created_at" = now();
NEW."deleted_at" = null;
NEW."user_role" = current_user;
end if;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

CREATE RULE "plots_del" AS ON DELETE TO public.plots DO INSTEAD
UPDATE public.plots
SET "deleted_at" = current_timestamp
WHERE "id_hist" = OLD."id_hist"
  AND "deleted_at" IS NULL;

CREATE TRIGGER "plots_update" BEFORE UPDATE ON public.plots FOR EACH ROW EXECUTE PROCEDURE public."plots_update"();

CREATE TRIGGER "plots_insert" BEFORE INSERT ON public.plots FOR EACH ROW EXECUTE PROCEDURE public."plots_insert"();

CREATE OR REPLACE RULE "_DELETE" AS ON DELETE TO public.plots_current DO INSTEAD
DELETE FROM public.plots
WHERE "id" = old."id";

CREATE OR REPLACE RULE "_INSERT" AS ON INSERT TO public.plots_current DO INSTEAD
INSERT INTO public.plots ("id", "geom", "number", "precinct")
VALUES (NEW."id", NEW."geom", NEW."number", NEW."precinct")
RETURNING "id_hist", "id", "geom", "number", "precinct";

CREATE OR REPLACE RULE "_UPDATE" AS ON UPDATE TO public.plots_current DO INSTEAD
UPDATE public.plots
SET "id" = NEW."id",
  "geom" = NEW."geom",
  "number" = NEW."number",
  "precinct" = NEW."precinct"
WHERE "id" = NEW."id";

My question is how to achieve similar functionality but with changes stored in other table.
Now I'm making all this things manually with view like this:
CREATE VIEW plots_current AS
SELECT
    geom, number, precinct
FROM public.plots
EXCEPT
SELECT
    geom, number, precinct
FROM history.public_plots
WHERE "deleted_at" IS NOT NULL
UNION
SELECT
    geom, number, precinct
FROM history.public_plots
WHERE created_at IS NOT NULL AND deleted_at IS NULL

Only unique data except geom column is concatenation of precinct and number (eg. like this precinct||number )


texts: Change code editor variable highlighting colour

texts:
Within the SSMS Code Editor (for SQL Server 2012) I have this code written within the editor, that has a Dark theme applied.  When you click on a variable, SSMS highlights all the other occurrences of that variable in scope.  Notice that the variable highlighting colour is quite dark against the background.

Within the Tools > Options > Fonts and Colors menu, can anyone tell me which is the correct Display items choice to edit this colour?  I've been through all of the ones that I can see that roughly match the same colour, but none actually effect the code.


texts: Reporting Services service restart fixes slow reports

texts:
SQL 2012, the reports are displayed on a WEB page or rendered to PDF

I have an ongoing problem with reports suddenly slowing down. The only way to "fix" this problem is to restart the service. This occurs after an unknown number of days passing, seemingly at random one of my reports will start timing out in the application (must be under 5 minutes). The report that starts failing may normally run in two to three seconds or one to two minutes but now Reporting Service does not reply with in the available window. When profiling the procedure call being executed by the report is returning the data in a timely fashion but then nothing is returned to the application.

The last time this happened I started a profile and found this, I see the procedure call for the report return in 400 milliseconds. The report is then rendered about five minutes later. In this interval Report server queries for the parameters of the requested report 4 to 6 times, I see sessions for the report created and deleted, checks for snapshots (snapshots turned off for this report).

As soon as the Reporting Services service is restarted the report returns to its normal behavior Request to Render 3 seconds. I looked at the execution logs and found that from the service side reports it spent all of its time getting the data from the database but the profiler is telling me a different story. If I run the same report from Reporting Server Management I get the same slow response so I do not think it has anything to do with the app that is requesting the report.

Things I have tried; uploading the report again, rewrite the procedure to improve performance, for the currently miss performing report. Nothing helps but restarting the service.

Has anyone seen this, if so how did you address this issue? This issue happened in SQL 2008R2 as well but not as often about ever 6 months and to make matters worst it is happening more frequently. Over the last month it occurred 3 times.

The stats are up to date. The procedure ran at normal speed in SSMS, about one second for the procedure for the report. I did not look at waits. It may be that the reports database was experiencing waits but I am not sure why restarting the service would resolve the problem. Other reports were not experiencing any slow down so it seems unlikely there were issues with waits.

The report server does not slow down just one report does. Other report requests are completed normally. Performance monitor shows no increase in CPU usage, no uptick in IO, network activity stays normal.


texts: MySQL SSL error: Unable to get private key

texts:
Getting an error while trying to enable encryption:

[ERROR] SSL error: Unable to get private key from '/etc/mysql/ssl/nginx.key'
[Warning] Failed to set up SSL because of the following SSL library error: Unable to get private key

/etc/mysql/conf.d/encrypt.cnf:

[mysqld]
ssl-ca=/etc/mysql/ssl/nginx.ca
ssl-cert=/etc/mysql/ssl/nginx.crt
ssl-key=/etc/mysql/ssl/nginx.key

The key is generated like that:

openssl req -newkey rsa:4096 -nodes -keyout nginx.key -subj "/C=US/ST=California/L=Sacramento/O=MyOrg/OU=MyDev/CN=MyApp"

The openssl checks it fine.

I've tried to change permissions, owners, file names, moving to different directories, disabling SELinux (it's already disabled), looking at AppArmor logs (it doesn't block it), changing 'BEGIN/END PRIVATE KEY' to 'BEGIN/END RSA PRIVATE KEY'.

What's wrong with it?


texts: Trying to query why all records are being deleted from a table, nothing comes up with eventclass = 47 (delete) - perhaps its a truncate so I want to query it in the trace file thanks


texts: Error while inserting into array of timestamps

texts:
Im using postgres 10.1 and the Datestyle config is the following

 DateStyle
 -----------
 ISO, MDY

Im having this error

  ERROR: date/time field value out of range: "1535673858" Hint: Perhaps
  you need a different "datestyle" setting.

While executing the following insert statement:

INSERT INTO ex (taken_ats) VALUES('{ 1535673858 , 1535678856}')

Schema:

CREATE TABLE ex (
    taken_ats TIMESTAMP WITHOUT TIME ZONE[]
)