texts: Postgres datetime sort slow and not using index

texts:
Using Postgres 9.4, I'm trying to order a query by an indexed datetime column.

explain
SELECT g.*, array_agg(gp.platform_id) as platform_list,
            array_agg(gm.metacritic_id) as metacritic_list,
            array_agg(gr.review_id) as review_list,
            array_agg(gn.genre_id) as genre_list,
            array_agg(gp.id) as release_list
FROM gb_game g
LEFT JOIN gb_release gp ON g.id = gp.game_id
LEFT JOIN gb_game_metacritic gm ON g.id = gm.game_id
LEFT JOIN gb_game_reviews gr ON g.id = gr.game_id
LEFT JOIN gb_game_genre gn ON g.id = gn.game_id
GROUP BY g.id
ORDER BY g.release_date
DESC limit 25 offset 0

Limit  (cost=66391.46..66391.52 rows=25 width=294)
  ->  Sort  (cost=66391.46..66511.58 rows=48051 width=294)
        Sort Key: g.release_date
        ->  GroupAggregate  (cost=1.56..65035.49 rows=48051 width=294)
              Group Key: g.id
              ->  Merge Left Join  (cost=1.56..53579.05 rows=691686 width=294)
                    Merge Cond: (g.id = gn.game_id)
                    ->  Merge Left Join  (cost=1.14..16374.78 rows=63464 width=290)
                          Merge Cond: (g.id = gp.game_id)
                          ->  Merge Left Join  (cost=0.85..10856.60 rows=48051 width=282)
                                Merge Cond: (g.id = gr.game_id)
                                ->  Merge Left Join  (cost=0.58..10676.85 rows=48051 width=278)
                                      Merge Cond: (g.id = gm.game_id)
                                      ->  Index Scan using platforms_game_pkey on gb_game g  (cost=0.29..8984.87 rows=48051 width=274)
                                      ->  Index Scan using gb_game_metacritic_6072f8b3 on gb_game_metacritic gm  (cost=0.29..1264.75 rows=24569 width=8)
                                ->  Index Scan using gb_game_reviews_6072f8b3 on gb_game_reviews gr  (cost=0.28..51.04 rows=686 width=8)
                          ->  Index Scan using platforms_release_6072f8b3 on gb_release gp  (cost=0.29..4604.75 rows=63464 width=12)
                    ->  Materialize  (cost=0.42..27979.58 rows=523702 width=8)
                          ->  Index Scan using gb_game_genre_6072f8b3 on gb_game_genre gn  (cost=0.42..26670.33 rows=523702 width=8)

The release_date column doesn't have many repeats other than null.
Everything is fast until the final sort.

Is there a special kind of index I need to use to speed up this query?


texts: Multiple copies (cp) with Oracle ASM asmcmd tool?

texts:
In order to import a transportable tablespace into an Oracle 11 database with ASM, we use the cp command of the asmcmd tool.

How can we copy more than one file at a time?

Wildcards are not supported, copying directories either, and I can't find any hint if asmcmd has a scripting mechanism.


texts: SQL Log Shipping Read Only/Standby Different users

texts:
I have a primary OLTP server on Server A and Log Shipping in Standby/Readonly mode on Server B.

Log Shipping Server is refreshed every 4 hours, and utilized for BI Reporting Analysis, to reduce load from OLTP.
I want a different set of users on LogShippingDb, which Cannot access Primary OLTP.
Everytime, I try to add user on LogShipping Db, it says "Failed to Update database because the database is read-only."

How do I resolve this issue, and have a different set of users on Log Shipping?

Can I also try orphaned users on Primary server? example Winad BI UserGroup on OLTP, without a login, but user, and then have both Login and User on Destination server?

Orphaned users may not be best way either, may cause issue down the line, trying to refrain from view synonym db also, we have lot of tables 1000+


texts:
I'm studying to be a DBA right now because I have a massive live database that is very sensitive that I manage. Here's the current system stats of my dedicated database server:

CentOS 5.9
24gb Ram
8 Core CPU
50gb FusionIO disk for /var/lib/mysql
MySQL 5.5.23

top - 18:40:27 up 14 days,  4:43,  1 user,  load average: 19.72, 22.62, 24.04

Tasks: 183 total,   3 running, 180 sleeping,   0 stopped,   0 zombie

Cpu(s): 69.9%us,  0.4%sy,  0.0%ni, 29.2%id,  0.0%wa,  0.1%hi,  0.4%si,  0.0%st

Mem:  24685224k total, 20172096k used,  4513128k free,   343420k buffers

Swap:  2007284k total,        0k used,  2007284k free,   729004k cached

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND

5446 mysql     15   0 18.4g  17g 6176 R 765.8 76.2 114437:38 mysqld

I'm currently using 43gb of that 50gb FusionIO slice. MySQL averages around 700 QPS and 75-90% CPU usage. Here's my my.cnf file:

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#innodb
#innodb_log_file_size = 256M
#innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout=50
innodb_file_per_table
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=4
#eliminating double buffering
innodb_flush_method = O_DIRECT
flush_time=86400

skip-name-resolve
query_cache_limit=4M
query_cache_size=256M
sort_buffer_size=8M
read_rnd_buffer_size=1M
max_connections=5000
interactive_timeout=60
wait_timeout=300
connect_timeout=30
thread_cache_size=32
key_buffer=124M
tmp_table_size=4096M
max_heap_table_size=256M
join_buffer=16M
max_connect_errors=2000
table_cache=2048
thread_concurrency=12
long_query_time=5
log-slow-queries=/var/log/mysql-slow.log
#table_definition_cache=384
max_allowed_packet=1024M
#server-id=20
#log-bin=mysql-bin
#expire_logs_days=10

event_scheduler=ON

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

can you guys recommend any config changes I should make to MySQL or CentOS that can greatly reduce my CPU usage? Or for that matter any other resource improvements?


texts: Syntax error while using variables in stored procedure

texts:
This might look like a silly question so pardon me. I was trying to write a trigger in mysql and am constantly getting #1064 which, I believe is a syntax error. Here is the sql for the trigger:

DELIMITER ;;

CREATE TRIGGER lh_insert
AFTER INSERT ON leaderboard_forheap
FOR EACH ROW
BEGIN
  DECLARE leaderboard_len TINYINT;
  INSERT INTO leaderboard_heap VALUES (NEW._id);
  CALL check_len(leaderboard_len);

  IF leaderboard_len > 1 THEN
    -- swim
    DECLARE k, comp TINYINT;
    SET k = leaderboard_len;

    CALL comp_row(k, FLOOR(k/2), comp);

    WHILE (k > 1 AND comp = -1) DO
      CALL exch_row(k, FLOOR(k/2));
      SET k = FLOOR(k/2);
      CALL comp_row(k, FLOOR(k/2), comp);
    END WHILE;

  END IF;
END;;
DELIMITER ;

The procedures check_len, comp_row and exch_row are working as expected independently. The syntax error is reported on the line with the declaration of k, comp. Here is the error string:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DECLARE k, comp TINYINT;
    SET k = leaderboard_len;

    SET k = leader' at line 11

I will really appreciate any help regarding this. Thanks.


texts: AWS Postgres RDS dumping data into sql format

texts: My team has hosted a django project in AWS with a Postgres RDS instance. What is the proper way to get export the database into SQL or pg_dump?


texts:
I'm building out a database with a users table, and I want to store the mutual (Facebook) friends between every set of two users in the database. Doing this in a normalized, efficient way seems tricky. I've considered two options so far:

Option 1

Create a table mutual_friend_set with columns id, user1_id, user2_id. Create another table mutual_friends with columns id, mutual_friend_set_id, name, picture, unique_mutual_friend_id.

The problem with this solution is that there's nothing to distinguish user1_id from user2_id, so I'd need to either create duplicate entries such as:

id   user1_id  user2_id
1    123       456
1    456       123

which would take up twice as many rows as necessary and throw normalization out the window, or ensure the lower of the 2 user_ids was in the user1_id column. If, however, I wanted to get all mutual friend sets for a single user, I'd need to query across both columns.

Option 2

Create a table mutual_friend_set with fields id, user_pair, such that user pair is a string of the combined user relationship, delimited by a comma. Eg: id: 1, user_pair: '123,456'. The user with the lower ID would be placed before the comma.

This would get around the normalization issues, but of course if I wanted to grab all the mutual friends pairs for a single user I'd have to run a LIKE query which isn't exactly efficient.

I assume this is a common problem, which raises the question: is there a standard way of doing this? Given my inexperience, am I missing something obvious?

Any ideas appreciated


texts: Adjusting the my.cnf file for DB servers after an instance resize

texts:
I am trying to optimize the DB server of one of our production environments (MySQL DB, centos7), and I've noticed that there is a very high memory consumption of the DB.
After further investigation I noticed that we should resize the DB from 8 GB RAM to 16 GB RAM due to increased usage. But when looking at the my.cnf file, I found the following settings for the buffer pool parameters:
#Pool size configuration
innodb_buffer_pool_size=6G
innodb_buffer_pool_chunk_size=128M
innodb_buffer_pool_instances=48

Now after checking 15.8.3.2 Configuring Multiple Buffer Pool Instances and a few other websites, it seems to point out that innodb_buffer_pool_instances should be set to 1 per CPU core.
For some reason ours is 48, and I couldn't find anyone in charge who could give me the reason for this.
I'd like to know if this setting could remain the same, which means it's probably there, because of a certain usage pattern?
This is a relatively small DB.
I will be resizing to 16 GB and adjusting the pool size to 12 GB, so it makes sense that innodb_buffer_pool_instances should be also set to 12, from my very basic understanding. Am I correct with my assumption?


texts:
I have a question for you concerning the PRIMARY filegroup.

Out goal is to have a small PRIMARY, so that we can do partial restores to other systems (test, dev)

So, we moved all tables and indexed from the PRIMARY to other filegroups.

However, the PRIMARY filegroup is still large. I have been trying to see what system object actually is in an "empty" PRIMARY, but can't seem to find anything that takes up that much space!

So, the question is, is the size of an "empty" PRIMARY dependent on the over-all size of the database? And if so, which kind of system objects lurks with in the PRIMARY, and is there anything one can do the decrease the size of these?

The total size of the database is  5300 GB.
The total space used of the "empty" PRIMARY is now 444 GB (and 134 GB free space.)


texts: Refreshing a table using CREATE TABLE, DROP and Rename

texts:
A read-only database (database1) is updated via log shipping every minute,
then a procedure queries multiple tables in it and refreshes a large table on database2.

For this process I

run an "expensive" query against database1 and generate a new Table in database2 (Table_A_TEMP)

BEGIN TRAN
DROP TABLE Table_A
RENAME Table_A_TEMP to Table_A
COMMIT

Does it look too ugly? Otherwise I would have to create a very complex verifications and updates.

Suggestions to minimize locks?


texts: Auto-commit UPDATE Transaction On Linked Server

texts:
I would like to know more about what happens behind the scenes with an auto-commit transaction when performing a cross-server query on a linked server.

I naïvely think that when executing an auto-commit transaction the compiler/SQL Server/something else just prepends all statements with a BEGIN TRANSACTION and appends all statements with a COMMIT TRANSACTION since everything is technically enclosed in a transaction (Is it a bad practice to always create a transaction?). I'm sure this is incorrect and the source of my confusion as to why performing a cross-server UPDATE without explicitly stating BEGIN TRANSACTION works but explicitly stating one does not. According to Microsoft (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017), an explicit BEGIN TRANSACTION on an UPDATE query that references a table on a linked server gets escalated to a distributed transaction; and since distributed transactions aren't configured on the linked server, I receive an error. How does the auto-commit setting avoid this? How does it not get escalated to a distributed transaction? Does the auto-commit setting send the data to the linked server but doesn't "listen" for a response from the linked server via Microsoft Distributed Transaction Coordinator (MS DTC); thus if an error occurs, it "silently" fails?

Auto-commit doesn't escalate to a distributed transaction:

UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;

Explicit transaction does escalate to a distributed transaction (and errors in my case):

BEGIN TRANSACTION
UPDATE l
SET l.RecordKey = s.RecordKey
FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l
INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s
    ON l.Value1 = s.Value1;
COMMIT TRANSACTION;

I should add that the statements were run in SQL Server Management Studio.


texts: MySQL trigger and values not supplied in update query

texts:
there is a way in mysql / mariadb triggers to know if a value is present  (set) in the update query?

For example:

    create table MyTest (
    col1 varchar(45),
    col2 varchar(45)
);

insert into MyTest values ( 'val1','val2');

So when I execute

update MyTest set col1 = 'val1.1'

in the trigger I would like to know that col2 is not set.

Thank You

UPDATE (I changed also table schema for better explain my problem)

I have setup a trigger like this

CREATE DEFINER=`root`@`localhost` TRIGGER `MyTest_BEFORE_UPDATE` BEFORE UPDATE ON `MyTest` FOR EACH ROW
BEGIN
    IF (NEW.col2 <=> OLD.col2) THEN
        SET NEW.col2 = 'triggered';
    END IF;
END

If I call

UPDATE MyTest set col1 = 'val1.3'

table will be (this is ok)

+------+---------+
|col1  | col2    |
+------+---------+
|val1.3|triggered|
+------+---------+

but if i call

UPDATE MyTest SET col1='val1.4',col2='val2.4'

first time table is ok

+------+---------+
|col1  | col2    |
+------+---------+
|val1.4|val2.4   |
+------+---------+

but a second update like this

UPDATE MyTest SET col1='val1.5',col2='2.4'

table will be:

+------+---------+
|col1  | col2    |
+------+---------+
|val1.5|triggered|
+------+---------+

instead i would like it should be

+------+---------+
|col1  | col2    |
+------+---------+
|val1.5|val2.4   |
+------+---------+

There is some way  i can check this?

Thanks


texts: mySQL trigger - prevent insert won't work for multiple inserts

texts:
This is not another "how to prevent insert with triggers" thread. I have done my research and I found a couple of ways but unfortunately, they won't work in my case.

I have a table in my database with a numeric column called "value". I want to cancel a new insert if its value is near the value of a previous insert. So here are my attempts:

first try:

IF EXISTS(SELECT * FROM test2 WHERE ABS(new.val - val) <= 1) THEN
  SET new.val = null;
END IF

Results: It works well for one insert (throws an error and cancels the insert). However, for multiple inserts at once, all records are inserted (and the inserts that should be rejected have value 0).

second try:

IF EXISTS(SELECT * FROM test2 WHERE ABS(new.val - val) <= 1) THEN
  SIGNAL SQLSTATE '45000';
END IF

Results: Again, works well for one insert. But for multiple inserts at once, it will reject all inserts even if there is only one that should be rejected.

Any suggestions?

I hope I was clear. Thank you in advance!


texts: Best Practices for Separating Content from Presentation in SQL

texts:
A common and useful paradigm of programming is to separate content and presentation. One system deals with building the result, another system deals with outputting it.
You see this with HTML vs CSS. HTML should ideally be clean and without any internal styling (color, position, etc), which should be handled by CSS.
I'm now wondering how this applies to SQL results. I often find myself formatting results in a query:
SELECT
    CONCAT(first_name, ' ', last_name) AS name,
    DATE_FORMAT(last_edit, '%d/%m/%Y') AS last_edit,
    CONCAT(
        record_type,
        IF (private = 1, ' (Private)', ' (Public)')
    ) AS record_type
FROM people
ORDER BY updated DESC, LastEdit DESC

Three different scenarios are covered here:

The first column is a simple concatenation.
The second transforms the output into a different format.
The third actually adds content to the result.

I understand that there are many useful functions for transforming data in a query. But from a team development point of view, should I be formatting these results in the presentation layer rather than the logic layer (i.e., in my PHP code instead of the SQL query)?
If so, then how far should I take it? Are columns 1 and 2 acceptable but 3 isn't?
Is there a widely-accepted best practice regarding this?


texts: Oracle materialized view refresh schedule

texts:
Since I see that it is possible to specify an expression like sysdate + 1, here are my questions:

Is it possible to specify the exact time of the refresh for materialized views in oracle?
Is it possible that it all starts at 2 a.m. while there is nobody using the db from my application?


texts: How do I add a directory, or bulk, of users to Microsoft SQL Server permissions

texts:
I have a front end program in Visual Basic that is using SQL Server as a "back end". The only way I can get permission for login to use the SQL server is by adding each name or computer individually and this will take forever to update and maintain.

My question is how do I do this at the group level? I found two similar questions that are essentially asking the same thing and they can be found at the following links:

https://stackoverflow.com/questions/5029014/how-to-add-active-directory-user-group-as-login-in-sql-server
Microsoft SQL Server v13 bulk add users from active directory

My problem is that these do not work for me. I get all the way to the end and when I click Ok nothing happens. Please reference the picture I attached to the post.

Do I need to add the directories in some other place before I can use the add button or am I missing something else?

The following picture is what I am trying to select.


texts:
I've an Oracle 11g Database, now the data is very huge, I've an approximately 858 Object in 1 schema and i have 20 Schema, and it's about approx 20 TB in size. I've tried using Navicat Premium v11 to dump the data, but it fail in the process transfer after a while it starts.

I've read this article to optimize export data in oracle, but i didn't get a point how to do that. especially how can I dump the data in parallel ?

May be somebody know technique how to do a backup without making a timeout to the server? thanks in advance before.


texts: What would cause a login to unexpectedly become a member of db_denydatareader?

texts:
After months of working without issue a stored procedure began failing this morning with the error message:

SELECT permission was denied on the object 'XXX', database 'XXX', schema 'dbo'.

So I dutifully began tracing the issue through lost permissions on tables, views etc.
I eventually discovered that the login was now in the db_denydatareader role, but none of the people responsible for the database have made that change.
Are there any processes that could automatically make this change?
Is there anything I can do toprevent the change in the future?

Incidentally another 2 logins lost permissions on certain tables.


texts: Drop the last character of string, if the last character is s

texts: Working through an odd issue that the moment and trying to find a solution. If we have a string variable (@string) how might we drop the last character of the string, ONLY if the last character is an s?


texts: Mysql used key changes between two consecutive EXPLAINs


texts:
I am facing this particular issue in one of my server (SQL 2016 std on Win 2016 std) , where SQL server does not come online post OS reboot. Event viewer has below error.
After I login to server and start the SQL service manually, it comes up. I am facing this issue in only one SQL server, rest all working fine. What is this issue and what is the permanent solution for this problem.

The MSSQLSERVER service was unable to log on as Domain\XXXXX with the
currently configured password due to the following error:  There are
currently no logon servers available to service the logon request.
To ensure that the service is configured properly, use the Services
snap-in in Microsoft Management Console (MMC).

Thanks


texts:
Everything I am talking about relate to relational database, specific MySQL.

I have a number of tables in a database and for a moderate number of them, I am going to want to store a history of the records values when it changes.  I have seen this done in a couple of different ways:

One Table/One Field - Basically there is one table that store the history of all the table that need history storage.  All change are recorded in one field as a text data type.
Table Per Table/One Field - Same as above except the each table has its own history table (ie. Projects/ProjectsHistory, Issues/IssuesHistory, etc...).
Table Per Table/Field Per Field - This is like the above in the each table has it own histroy table but also the history table has pretty much the same definition as the regular table with an additional of additional history related fields (updateDatetime, updateUserId, etc...).

What are some of the advantages and disadvantages to these different methods of storing record history?  Are there other methods that I have not thought of?


texts: SQL Server Database going into suspect mode after bulk insert and delete

texts:
I'm working on an application that does a bulk copy of data from one table to another within the same database (using an Insert... Select) and then deleting the data from the source table. In production, sometimes the database goes into suspect mode and I have noticed that it always happens after this process is run. This process does this to multiple tables (around 20 tables). First it copies the relevant data from each table to its corresponding archive table. Then it deletes the relevant data from the source tables.

I checked on common reasons for this and there is enough free space in the disk and no antivirus software installed

Edit: Auto Close is disabled for the database


texts: Is there any way to perform atomic updates of stored procedures in MySQL?

texts:
I know that MySQL does not support atomic updates of stored procedures yet and it is bug which was reported on 2005 but not yet resolved.(http://bugs.mysql.com/bug.php?id=9588).

In our project multiple store procedure call within a second, we provide high availability service to client. but due to this bug are not updating any stored procedure during production hours. If we update any thing in stored procedure MySQL simply drops the entire procedure and recreates it again which creates downtime issue for our client.

It's also possible that a client calls a stored routine in exactly the moment between a DROP and a CREATE which create major issue for us.

Anyone facing this kind of issue? If yes if you have any way to resolve this kind of issue then please share.

Thanks


texts:
we are having starnge deadlock issue. We have a deadlock in mysql and it appears conitously when we run engine innodb command. the webserver it is comming from do not exist as we have manually killed it. But it still shows that same deadlock.

Is there is a possibility that mysql caches that somehow or need to manually kill or flush something ?


texts: What permission should I give to a user or role so that it can Create Login or Create User in database.


texts: Impossible to log errors after dooming a transaction within an INSERT-EXEC statement

texts:
We have a stored procedure that does some work within a transaction, wrapped within a stored procedure that returns some result data using the INSERT-EXEC method of sharing data.

After observing the generic error message "the transaction has been rolled back" floating back up to the UI, we went to check the log tables but found nothing. We ended up having to track down the error through the SQL Server Profiler (which is made excessively difficult by omitting things like line number in the Exception event class). I tracked down our logging failure as the reason in the first paragraph.

How can a failure in such a circumstance possibly be logged?

Here is some sample code demonstrating how it is impossible to write to a table after dooming a transaction while within an INSERT-EXEC statement:

IF OBJECT_ID('TempLog') IS NULL
    CREATE TABLE TempLog
    (
         RowNumber INT NOT NULL IDENTITY(1, 1)
        ,LogMessage VARCHAR(MAX) NOT NULL
        ,TimestampUtc DATETIME NOT NULL DEFAULT(GETUTCDATE())
    )
GO
TRUNCATE TABLE TempLog
GO
IF OBJECT_ID('Failer') IS NOT NULL
    DROP PROCEDURE Failer
GO
CREATE PROCEDURE Failer
AS BEGIN
    BEGIN TRANSACTION;
    SELECT 1 / 0
    --unmatched COMMIT for fun
END
GO
IF OBJECT_ID('Wrapper') IS NOT NULL
    DROP PROCEDURE Wrapper
GO
CREATE PROCEDURE Wrapper
AS BEGIN
    BEGIN TRY
        EXEC Failer
        SELECT 'success'
    END TRY
    BEGIN CATCH
        BEGIN TRY
            INSERT INTO TempLog(LogMessage) VALUES ('test 1')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 1: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 2')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 2: ' + ERROR_MESSAGE()
        END CATCH
        BEGIN TRY
            IF XACT_STATE() = -1
                ROLLBACK TRANSACTION;
            IF XACT_STATE() = 1
                COMMIT TRANSACTION;
            INSERT INTO TempLog(LogMessage) VALUES ('test 3')
        END TRY
        BEGIN CATCH
            PRINT 'failed test 3: ' + ERROR_MESSAGE()
        END CATCH
        ;THROW
    END CATCH
END
GO
DECLARE @Result TABLE(RowNumber INT IDENTITY(1, 1), OtherStuff VARCHAR(MAX) NULL)
INSERT INTO @Result(OtherStuff)
EXEC Wrapper

SELECT [@Result]='', *
FROM @Result

IF XACT_STATE() = -1
    ROLLBACK TRANSACTION;
IF XACT_STATE() = 1
    COMMIT TRANSACTION;
GO
SELECT [Trancount] = @@TRANCOUNT
SELECT * FROM TempLog

The final selection statements return @@TRANCOUNT as 0 and nothing in TempLog with these messages:

failed test 2: Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
failed test 3: Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
Msg 8134, Level 16, State 1, Procedure Failer, Line 60
Divide by zero error encountered.

(but somehow the first INSERT statement to TempLog is permitted even though it is within a doomed transaction)

How is it possible to write any sort of log data in such a case? Ideally it goes to the log table as we have an admin GUI which lets us inspect it without bugging anybody to get us direct backend access.

P.S. The management log doesn't include anything from my testing.

P.P.S. I dislike the INSERT-EXEC method, but we have to deal with it sometimes.


texts: User defined stored procedure - where's the execution plan?

texts:
SQL Server 2016, Enterprise Edition in Windows

Is there anyway to display the execution plan of a stored procedure created in C# with Visual Studio Data Tools?  This is a question that someone with Visual Studio experience probably knows the answer to. My searches on Google haven't turned up anything useful.

Here is the stored procedure in c#

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    static public void Addition (SqlInt32 a1, SqlInt32 a2, out SqlInt32 sum)
    {
        sum = a1 + a2;
    }

}

Here is the execution in SSMS 2017 v17.3

USE CLR1;

declare @sum int
exec dbo.Addition 3, 4, @sum output
select @sum

I have also been able to find the execution plan in cache. Even though there is a plan_handle available in dm_exec_procedure_stats, I can't seem to use a cross apply to [dm_exec_query_plan] to view it.


texts: List full text indexes in Oracle database

texts:
Using SQL Server, it's possible to execute a query that returns all the columns that contain a full text index.

How can I do something similar using Oracle? Is it possible?


texts: Copy mysql_native_password to new server

texts:
I did a mysqldump of my entire database mysqldump --defaults-file="/home/ubuntu/.my.conf" -u api --all-databases > full-dump.sql . I setup a new mysql and phpmyadmin and loaded the mysqldump in mysql -u root -p < full-dump.sql
Then my root password stopped working (it's the same on both servers). I was able to reset the password and regain access. I am now comparing authentication_strings on my new server to my old server. I noticed the strings changed on the new server when I reset the passwords. My presumption is that the new and old server use different hashes for the same password and hence aren't compatible with each other.
Is there a way to copy the mysql users over and have the existing passwords work? Or will I always have to manually reset them like I did in this case?


texts: Using SQL Agent Job to execute a script from a UNC path

texts:
I need to execute a script located on an UNC share.  I've setup my SQL job to use CMDExec and to execute the following command:
PowerShell -NOPROFILE -ExecutionPolicy Bypass -File \\Server\SharedFolder\Script.ps1

When executed, I get the following error:
The argument '\\Server\SharedFolder\Script.ps1' to the -File parameter does not exist. Provide the path to an existing '.ps1' file as an argument to the -File parameter

On that same server, I've tried executing this command in CMD.exe and it does works flawlessly.
Other things I tried with my SQL job:

Using the command parameter
Setting up a proxy account

It always end up with the same error.  Any idea on what I am missing?


texts: How do I recover deleted rows from one table while having Recovery SIMPLE Model? I am using Microsoft SQL Server 2014

texts:
How do I recover deleted rows from a table while having Recovery SIMPLE Model? Database is in Microsoft SQL Server 2014. I have managed to find them in fn_dblog table before data is overwritten with a new one. Can I do anything with it?

SELECT * FROM fn_dblog(NULL, NULL) WHERE Operation = 'LOP_DELETE_ROWS'

I also find out the specific time at which rows were deleted.

USE Databasename
GO
SELECT
[Current LSN],  Operation, [Transaction ID], [Begin Time], [Transaction Name], [Transaction SID]
FROM
fn_dblog(NULL, NULL)
WHERE
[Transaction ID] = ‘000:000001f3'
AND
[Operation] = 'LOP_BEGIN_XACT'

I've read SIMPLE Recovery doesn’t support transaction log backup. But is there really no way to restore that data?


texts: How to create new column with value from other table

texts:
I have a table called 'menus':

 menu_id | sort | page_id | parent_page_id
---------|------|---------|----------------
 1       | 0    | 1       | NULL
 1       | 1    | 2       | 1
 1       | 2    | 3       | 1
 1       | 3    | 4       | 1

And a table called 'pages':

 id | url
----|-----------
 1  | vehicles
 2  | car
 3  | bicycle
 4  | bus

I chose to store the data into separate tables because one page can be included in multiple menus.

Now I want to do a query from which the result is:

 menu_id | page_id | sort | url      | parent_url
---------|---------|------|----------|------------
 1       | 1       | 0    | vehicles | NULL
 1       | 2       | 1    | car      | vehicles
 1       | 3       | 2    | bicycle  | vehicles
 1       | 4       | 3    | bus      | vehicles

The columns menu_id, page_id, sort and url can easily be retrieved using a LEFT JOIN:

SELECT
    menus.menu_id,
    pages.id AS page_id,
    menus.sort,
    pages.url
FROM menus
LEFT JOIN pages
ON menus.page_id = pages.id
ORDER BY sort

But how do I get the column parent_url in the query result?


texts: Which views can be queried while instance is up but database not mounted in Oracle 11G?

texts:
If I understood it correctly, when the instance is started, some views are loaded into memory and we can query against it like v$instance. And it doesn't need db to be mounted.

If so, are there any other views which can be queried, apart from v$instance?

Also, when I query v$database, it says database is not mounted, but a different error when I try to give a random name. Is it just different error handling for the view name v$database (as it seems to be considered something important or known database) or any other reason for this?

SQL> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-01507: database not mounted

SQL> select * from v$data;
select * from v$data
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SQL> select * from testing;
select * from testing
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

Update:

It seems the following views also work: v$session, v$sql.

The reason for asking is I just want to understand the scope and purpose of each view or database table practically.


texts:
The other day I noticed something strange that perhaps someone have an explanation for. The problem I was facing was a query that due to a skewed distribution of data generated a poor plan when a parameter marker was used. The execution time was prox 10 times higher compared to a hardcoded value concatenated into the query.

I have never tried optimization profiles before, so I thought it would give it a shot. I installed a profile like:

<?xml version="1.0" encoding="UTF-8"?>
<OPTPROFILE VERSION="10.5.8.0">
<STMTPROFILE ID="Guideline for update of process_state_id">
    <STMTKEY>
       <![CDATA[UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90 WHERE EXISTS ( SELECT     1 FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID AND SUBSTR(P.PNR, 9, 1) <> 'F' ) AND NOT EXISTS ( SELECT    1 FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID ) AND PCO.ADMISSIONROUND_ID = ? AND PCO.RESULT = 20]]>
    </STMTKEY>
    <OPTGUIDELINES>
      <REOPT VALUE='ALWAYS'/>
    </OPTGUIDELINES>
</STMTPROFILE>

This gave me what I wished for, i.e. the query where recompiled every time it got a new parameter and benefitted from distribution statistics. So far so good.

I wanted to take a closer look at how the plan changed due to the optimization profile, so I turned on explain mode and fired of the query:

]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql
SQL0217W  The statement was not executed as only Explain information requests are being processed.  SQLSTATE=01604
]$ db2exfmt -d ...

Much to my surprise, I get an error doing the same thing with the optimization profile enabled:

]$ db2 "set current optimization profile test.optprof"
DB20000I  The SQL command completed successfully.
]$ db2 set current explain mode explain
DB20000I  The SQL command completed successfully.
]$ db2 -tf update_orig.sql
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0313N  The number of variables in the EXECUTE statement, the number of
variables in the OPEN statement, or the number of arguments in an OPEN
statement for a parameterized cursor is not equal to the number of values required.  SQLSTATE=07004

If I run the query from python or java everything works well, so it is more a question of the convenience to be able to explain statements from command line.

In addition, it also works to explain the plan like:

]$ db2 "explain plan for UPDATE NYA.PICKED_COURSE_OFFERING PCO SET PCO.PROCESS_STATE_ID = 90
> WHERE EXISTS (
>      SELECT     1
>         FROM NYA.PERSON P     WHERE P.PERSON_ID = PCO.PERSON_ID
>         AND SUBSTR(P.PNR, 9, 1) <> 'F'
> ) AND NOT EXISTS (
>     SELECT    1
>         FROM NYA.COURSE_OFFERING_PREVENT_LADOK_PUBLICATION CPL
>         WHERE CPL.EDUCATIONORG_ID = PCO.EDUCATIONORG_ID
>         AND CPL.ADMISSIONROUND_ID = PCO.ADMISSIONROUND_ID
>         AND CPL.COURSEOFFERING_ID = PCO.COURSEOFFERING_ID
> ) AND PCO.ADMISSIONROUND_ID = ?
> AND PCO.RESULT = 20"
DB20000I  The SQL command completed successfully.

This, however, becomes a bit unpractical for large queries since it requires either to change the query in the file, keep two files, one for explain and one without, or as above executing the query directly.

Does the optimization profile somehow invalidate the explain mode? It does not matter in what order I set explain mode and optimization profile, I cannot execute a query with a parameter marker either way. Any thoughts anyone?


texts: PostgreSQL is using more memory than I'd expect

texts:
I have PostgreSQL configured mostly how pgtune recommended, and the settings are supposedly conservative. PostgreSQL is using more memory for a single connection than the settings would suggest is allowed, causing my Linux system to run out of RAM. Here are my settings for a system with 64GB of RAM:

# All set by pgtune...
default_statistics_target = 50
maintenance_work_mem = 5GB # ... except I increased this one...
constraint_exclusion = on
checkpoint_completion_target = 0.9
effective_cache_size = 32GB # ... and decreased this one
work_mem = 384MB
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 15GB
max_connections = 80

Postgres is running a huge UPDATE query right now using the process on top:

PID   USER      PR   NI VIRT    RES    SHR   S  %CPU %MEM     TIME+ COMMAND
21857 postgres  20   0 43.381g 0.042t 0.015t S   1.0 68.9 605:40.48 postgres
20623 postgres  20   0 15.475g 0.015t 0.015t S   0.0 24.3  64:25.95 postgres
20624 postgres  20   0 15.457g 7.021g 7.013g S   0.0 11.2   0:41.90 postgres

RES is ~42GB for that one. Combined with those other two processes, that makes ~64GB of RAM used, so something is probably page faulting. Shouldn't it be less than or equal to 32GB per process? I thought maybe the work_mem was not included in effective_cache_size (documentation doesn't explicitly state it), but that couldn't take 10GB of RAM by itself unless I had 26 joins at once, and I have at most 4. So I'd expect at the very most 34GB RES for one process. What am I missing here?


texts: Postgres don't start after an unexpected shutdown

texts:
After an unexpected shutdown in postgres 8.4, I'm getting this error.

couldn't open the file 'pg_xlog/....'. No such file or directory.
invalid primary checkpoint record.

Complete message in portuguese:

  2014-05-13 16:46:04 BRT [5980]: [1-1] LOG:  sistema de banco de dados
  foi interrompido; última execução em 2014-05-13 15:46:07 BRT
  2014-05-13 16:46:04 BRT [5980]: [2-1] LOG:  não pôde abrir arquivo
  "pg_xlog/00000001000001A1000000ED" (arquivo de log 417, segmento 237):
  No such file or directory 2014-05-13 16:46:04 BRT [5980]: [3-1] LOG:
  registro do ponto de controle é inválido 2014-05-13 16:46:04 BRT
  [5980]: [4-1] PÂNICO:  não pôde localizar registro do ponto de
  controle requerido 2014-05-13 16:46:04 BRT [5980]: [5-1] DICA:  Se
  você não está restaurando uma cópia de segurança, tente remover o
  arquivo "C:/Arquivos de programas/PostgreSQL/8.4/data/backup_label".

  This application has requested the Runtime to terminate it in an
  unusual way. Please contact the application's support team for more
  information.

  2014-05-13 16:46:04 BRT [5440]: [1-1] LOG:  processo de inicialização
  (PID 5980) terminou com código de retorno 3 2014-05-13 16:46:04 BRT
  [5440]: [2-1] LOG:  interrompendo inicialização porque o processo de
  inicialização falhou

I read about it and the only option it seems to execute pg_resetxlog.exe.

  pg_resetxlog -- reset the write-ahead log and other control information of a PostgreSQL database cluster.

  You should immediately dump your data, run initdb, and reload. After
  reload, check for inconsistencies and repair as needed.

But according to documentation this should be the last resource.

Any other ideas to solve this problem?


texts: Mongodb: how to move a member to a backup site?

texts:
It is stated in the docs, that any replica set should have an odd number of members. Let's say I have a primary site with 3 members. I need to move one member to a backup site. In order to do this, do I need to first add a new member on the backup site, and them remove a member on the primary site? What is the official procedure? I'm very confused, because by the time the member is added, there will have been an even number of members. Where will I go from there? How dangerous it is to add a single member to a replica set without doing any hanky-panky?

Should I do some magic tricks (e.g. set priorites of particular members to zero) before I add a new member?

Is this documented somewhere?


texts: replication monitor: why is current average performance poor?

texts:
I am a replication rookie.  We have a very simple transactional replication set up in SQL Server.  I am looking at the Replication Monitor on the subscriber and one of my subscriptions show Poor Current Average Performance.  My understanding is this is derived from the latency threshold but when I look at the detail of the subscription, Performance is Excellent and Latency is 00:00:00.  For what it's worth, Synchronization  Status is fine.

Why is this current average performance poor?  Over what time period is this "average"?  This subscription has had no noticeable issues and has been active for some time.

(I am asking for my own knowledge as well as ensuring there is not something broken/sub-optimal that should be modified)


texts: Can't connect to SSAS server -- "Element 'return' was not found. Line 5, position 2."

texts:
I'm trying to access an SSAS server from SSMS v17.4 (2017).

I'm using Windows Authentication.
I know the credentials are correct and can actually connect successfully from Visual Studio 2015.

However in SSIS, with the connection string (note my credentials are not my actual Windows login but an assumed identity/ different username I think).

Server type: Analysis Services
Server name: myServerName.com\myCatalog
Authentication: Windows Authentication

and then

Data source = myServerName.com;
User ID = myUserID;
Password = myPassword;
Initial Catalog = myCatalog;
Provider=MSOLAP.7;
Format=Tabular;

I know the connection string is registering as correct because when I put in a wrong/ dupe password, a different error results. The system knows the password is correct.

I'm getting the error

Element 'return' not found. Line 5, position 2. (System.Xml)

So I'm not sure what's going wrong here. What is the best client to connect to an SSAS environment to view Cube definitions and MDX queries etc? What could be going wrong here where the connection otherwise works fine in Visual Studio 2015 from the same computer?

I heard it could be .dll files of some sort. No idea where to begin here.


texts: SQLLite query datatime divided into week of the year, and month of the year

texts:
In my database I have two tables:

type_of_activity

CREATE TABLE IF NOT EXISTS type_of_activity(id_type INTEGER PRIMARY KEY, name VARCHAR(20))

activity

CREATE TABLE IF NOT EXISTS activity(
 id_activity INTEGER PRIMARY KEY AUTOINCREMENT,
 id_type INTEGER,
 start_time TEXT,
 distance REAL,
 duration INTEGER,
 elevation INTEGER,
 FOREIGN KEY(id_type) REFERENCES type_of_activity(id_type))

Some details: start_time format: "YYYY-MM-DD HH:MM",
duration in seconds.
I want to make query that summary:

Every week mileage, duration, maybe from this data calculate pace, and elevation.
The same as above, but for months

I think that is good to convert the start_time to:
in 1)strftime('%Y-%W',start_time); 2)strftime('%Y-%m',start_time)
But I don't know how to aggregate results.

Can someone help me?


texts: Is the SQLTRACE_FILE_BUFFER wait type documented? What does it indicate?

texts:
We run a background task on one of our servers which polls sys.dm_exec_requests for long-running queries, gathering diagnostic information when it finds them, including wait type.

I'm seeing a number of queries with the SQLTRACE_FILE_BUFFER wait. This isn't documented anywhere I've looked - I wondered if anyone has any further information about this wait type?

The name suggests that there's a delay writing trace data to disk; we have a server-side trace running on the instance which writes to a file for audit and performance monitoring purposes.

The SAN admin tells me we have IOPS to spare, so I don't think this is a simple I/O issue. What else should I check?

UPDATE

We disabled the server-side trace and continued to see the same issue; the latest thinking is that the SQLTRACE_FILE_BUFFER wait is a symptom of memory pressure (there's a different documented wait type used when the trace buffer is being written to disk - SQLTRACE_BUFFER_FLUSH - which doesn't appear).

UPDATE 2

Disabling the default trace removes the wait. We are still investigating the underlying cause of the performance problem.


texts: Getting insufficient memory available in the buffer pool

texts:
I'm trying to trim out most data across a few dozen tables in a copy of a database.  This is an ad hoc need I'm performing on a local machine running SQL Server 2008R2 Standard SP1.  Tables are notable but not tremendously large, with the larger ones at tens of millions of records.

When running large DELETEs, I'm getting an "insufficient memory available in the buffer pool" error.  I can see physical memory continue growing without any indication of releasing memory.

I have set recovery to simple, and have tried chunking the deletes using rowcount to throttle.  At the end of each chunk I see no evidence of the memory being released.  Next attempt was to wrap in an explicit transaction, with no difference in the outcome.  I've also had no luck with

DBCC FREESYSTEMCACHE ('ALL')

Does anyone have suggestions around where else I might free up the memory mid process?  Because this is a rare, ad hoc need being done on a local machine, I'm okay with some choices that might otherwise be not a best practice for ongoing production scenarios.  I'd like to steer clear of rewriting all sorts of involved rewriting to a "perfect" solution, e.g. dropping constraints and indexes, then rebuilding after truncating and adding data.


texts: Database Design question, calculate or save financial positions

texts:
As the question states I'm currently internally strugling with a database design question. I work as a software developer (and I tend to do everything database related as well since we're rather small) for a financial company and we're doing a revamp of our current system.

Our system is starting to become outdated and the biggest issue we have with the current system is the database. My predecessors have made some "challenges" for me in the current design and I'd like to not fall into the same pits in the new design.

Basically we've got things like invoices, adminstration costs and all this fancy stuff.

Added on this we have Creditinvoice, administrational adjustments on payments.

The current system is WAY too redundant having financial values all over the place and it's sometimes a nightmare to actually figure out what is the current position of a debtor/invoice.

In order to not have the same problem I'm currently struggling with the question, do I want to store the position of an invoice somewhere, or do I want to calculate this through a view (indexed maybe; unsure since I've never indexed my views)?

Simply put I have the following tables (it's a slightly simplified example but I think it paints the picture);

Invoices
Invoicelines
TaxPercentages
BankstatementLines
Entries (a table that shows what value of a statement is "booked" on the invoice)

Now, InvoiceLines, BankstatementLines & Entries are currently the only tables with a financial value in them, being NettAmount (InvoiceLines has this column, which is the amount before taxes). Bankstatmentlines holds the actual amount recieved on the bank account. Entries hold which amount (could be partial) from the bankstatmentline has been booked on the invoice.

In order to calculate the position I'd have to do the following;

Calculate the Tax values for the invoice (Adding this to the Nettamount gives me the grossamount)
Subtract the amount from the entries from the GrossAmount so I end up with an "OwedAmount"

This is somewhat complex, however I'm 100% sure that I will never have a wrong value in my database, since I'm not storing any "GrossAmount" or "OpenAmount".

Personally I'm thinking I should add a "TaxAmounts" Table which simply holds the calculated tax amounts (these numbers never change so calculating them over and over seems redundant). This also goes for the GrossAmount. If I have the TaxAmounts & the NettAmount I know the GrossAmount, which should also NEVER change.

I think doing those two things is the right way to go as those values should NEVER change. If they do something went horribly wrong anyway so recalculating on the fly to fix an error shouldn't be an issue.

My current plan is to calculate the owed amount in a view (most likely indexed on the invoice PK). I wonder if this is the best way to go at this "problem" though? Maybe there is some way of doing this that is more efficient but has the same end result (not having an "OwedAmount" column somewhere that needs to be kept up to date and has a risk of becoming erroneous).

Other information; We'll be hosting this on SQL Server 2012 (currently designing in 2005 but the licenses for 2012 have been ordered and are on their way!)


texts:
I'm using Informix IDS 11.50 Innovator-C edition running on Slackware Linux 12.0. I've been running a very small Database on it(less than 10000 records on the biggest table).

I'm trying to select a view from a web service, and every time there's a high load user load queries are being dropped with messages like:

  ERROR [HY000] [Informix .NET provider][Informix]Could not write to a temporary file

Or

  ERROR [HY000] [Informix .NET provider][Informix]Could not open or create a temporary file

The Web Service is using the .NET Informix Driver which relies on the Informix ODBC API.

Searching for those errors on the web or in the online documentation I found that it's something related to DBTEMP environment variable and the DBSPACETEMP configuration parameter.

Both are set. DBTEMP pointing to a directory with all permissions(rw), three dbspaces are listed on DBSPACETEMP. One regular dbspace and two flagged as temporary dbspace.

Storage is not a problem, the directory and the dbspaces have a lot of free space left.
The tables that the view Targets use R-tree indexes, and those are stored on a dedicated dbspace. One more thing I'm using a Geodetic Datablade on the database.


texts:
My application is running in Azure SQL which doesn't support DTC. We only use Azure SQL in upper environments. In the local development environments, we're still using SQL Server 2016.

I want to avoid a situation, which has happened a couple of times in past where a developer has ended up writing queries with a transaction that ended up escalating to DTC, which of course was only caught in upper environments.

I'm looking for a command to run in SQL Server to completely remove its ability to participate in DTC, basically make it like its Azure SQL counterpart.

Edit: I don't want to remove MSDTC from the system itself as I've other services, that doesn't involve Database, that requires MSDTC.


texts:
I have been using pandas (python library) to modify some data that I take from one database (MS SQL) and load it to another one for reporting (Postgres).

One of the steps sums up a pretty large list of columns (40+) and if the result is > 0, that row is retained, otherwise it is dropped. This all takes place in memory in a pandas dataframe. This reduces the row count by 80%.

dimension1, ..., dimension20, count1, count2, ..., count89, count90

What is the best way to accomplish this using pure SQL? Can I do it directly from the SELECT query, or do I need to load everything into a temporary table (perhaps) and then only insert the rows which sum up to > 0?


texts: Spike in Disk I/O due to external process causing PostgreSQL commits to pile up in the slow log

texts:
Background:

I have a live Django app that utilizes 4 Redis instances alongwith PostgreSQL 9.6.5.

Note that the first two Redis instances are big in size: back ups amount to ~2GB and ~4.4GB respectively.

The problem:

PG data and Redis backups are both saved in the secondary drive xvdb.

I've noticed that whenever my big Redis instances start backing up, disk I/O naturally spikes and PostgreSQL commit statements start piling up in the slow log. Behold:

21:49:26.171 UTC [44861] ubuntu@myapp LOG:  duration: 3063.262 ms  statement: COMMIT
21:49:26.171 UTC [44890] ubuntu@myapp LOG:  duration: 748.307 ms  statement: COMMIT
21:49:26.171 UTC [44882] ubuntu@myapp LOG:  duration: 1497.461 ms  statement: COMMIT
21:49:26.171 UTC [44893] ubuntu@myapp LOG:  duration: 655.063 ms  statement: COMMIT
21:49:26.171 UTC [44894] ubuntu@myapp LOG:  duration: 559.743 ms  statement: COMMIT
21:49:26.172 UTC [44883] ubuntu@myapp LOG:  duration: 1415.733 ms  statement: COMMIT

As a consequence, this is how my PostgreSQL commits look like every day:

The question:

Is there anything I can do on the PostgreSQL side to help smoothe out this spikey situation?

I'd like Redis and PostgreSQL to live in as much harmony as they possibly can on a single machine. If you feel this situation can only be helped from the Redis side, write that as an answer and I'll accept that too.

More information:

Ask for more information if you need it.

Machine specs:

AWS EC2 m4.4xlarge (16 cores, 64GB RAM)
Elastic Block Store gp2 volumes (105 IOPS, burst upto 3000 IOPS)

redis-server --version yields Redis server v=4.0.2 sha=00000000:0 malloc=jemalloc-4.0.3 bits=64 build=401ce53d7b0383ca.

Typical iostat -xmt 3 values are:

10/15/2017 08:28:35 PM
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          10.44    0.00    0.93    0.15    0.06   88.43

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.00     0.00    0.00    2.00     0.00     0.04    38.67     0.00    0.00    0.00    0.00   0.00   0.00
xvdb              0.00     2.67    0.00   44.67     0.00     0.41    18.99     0.13    2.81    0.00    2.81   1.07   4.80

Compare that to the same around the time slow commits are logged:

10/15/2017 10:18:11 PM

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8.16    0.00    0.65   11.90    0.04   79.24

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              0.00     4.00    0.00    1.00     0.00     0.02    48.00     0.00    1.33    0.00    1.33   1.33   0.13
xvdb              0.00     0.00    1.67 1312.00     0.01   163.50   254.90   142.56  107.64   25.60  107.75   0.76 100.00

If needed, here are some relevant lines from postgresql.conf:

shared_buffers = 2GB
checkpoint_timeout = 30min              # range 30s-1d
max_wal_size = 2GB
min_wal_size = 700MB
checkpoint_completion_target = 0.9      # checkpoint target duration, 0.0 - 1.0
#checkpoint_flush_after = 256kB         # measured in pages, 0 disables
#checkpoint_warning = 30s               # 0 disables

Note: I've asked a similar question - but focused on Redis - separately on SO as well.


texts: How to edit publication's generation_leveling_threshold configuration value

texts:
I dont know where I put this code:

update sysmergepublications set [generation_leveling_threshold] = 0

I only need do it. But I need edited any funcion of agent.


texts:
Here is my requirement :-

I have a table called "users" and field called "Admin".
Field "id" is primary key.
Wants to update "Admin" field to "1" if id=1000;

Tried this ,but not working.I tried "New" instead of "old".Also I tried "After" instead of "Before". But it is not working..

Any suggestions please...

DELIMITER $$
CREATE TRIGGER update_user_admin
Before UPDATE ON users
FOR EACH ROW
BEGIN

  UPDATE users
    SET admin = 0
  WHERE old.id = 672;

END;
$$
DELIMITER ;


texts: Why database has char data type when varchar is more efficient?

texts:
I understand the basic difference between CHAR and VARCHAR datatype that CHAR takes up fixed length whereas VARCHAR takes up space based on the content being stored.
But if VARCHAR is so efficient in handling the space management dynamically based on the content being stored, why databases has the support for CHAR datatype, all CHAR(n) fields can be VARCHAR(n) fields, right ?


texts: Get list of databases accessed by a particular SQL Server login

texts:
I'm a DBA who recently joined a new company. One of the first tasks I am given is to ensure that any SQL accounts (both logins and users) do not have elevated permissions in our production environment. They should only have access that is required by the app/jobs they support. Management is fine with filtering access at the database level (granting db_datareader, db_datawriter, ddladmin, etc.) I've reached out to the people in charge of the apps/etl but they themselves are unsure of what access is required by these service accounts.

The problem here is, we have dozens of logins on each SQL instance, and over 50 SQL instances. Whoever created these logins had just granted sysadmin access rather than ensuring only the required access was given. Now we have many custom jobs and 3rd party apps running under these logins.

It's hard to make any changes to the permissions for these accounts without the possibility of breaking production apps/jobs. We don't have a test environment where I can test the changes.

I am thinking that if I can get a list of databases accessed by these service accounts over, say, a 2 month period, it would give me a starting point. I will start by granting db_owner to the logins on the appropriate databases and narrow it down from there.

I know that you can trace the actual SQL code being run by sessions, but is there a way to filter out the "noise" and just get the names of the databases accessed?

Also, I am welcome to any suggestions on an alternative approach to dealing with this issue, or if any other DBA's have tackled on a similar project in the past to share how they did it. This environment is primarily SQL Server 2008 R2 / 2012


texts: How can I get the minimum sum value of fields without repeating where clause?

texts:
How can I get the minimum value after adding value of 2 fields without repeating the where clause?

Example: Get records with minimum value of num1 + num2 and deleted_at is null

id          num1        num2       deleted_at
----------  ---------   ---------  ---------
1           1           1          2015-01-01
2           2           2          null
3           2           2          null
4           4           4          null

Expected result is

id          num1        num2       deleted_at
----------  ---------   ---------  ---------
2           2           2          null
3           2           2          null

I tried subquery, but I have to repeat the where clause

SELECT *
FROM   foos t1
WHERE  t1.num1 + t1.num2 = (SELECT Min(t2.num1 + t2.num2)
                            FROM   foos t2
                            WHERE  t2.deleted_at IS NULL) //repeat
       AND t1.deleted_at IS NULL //repeat


texts: A question regarding the performance of normalization

texts:
I am sorry this is such a newb question, but it is one thing I am having a difficult time wrapping my mind around, and after an evening spent reading I finally just decided to ask for some clarification.

Say I have a table that contains information about servers. As new servers come online, this table is populated with information about the servers. Now, assume that the server name is unique, and server serial number is unique.

Scenario #1:

Server Table
server_name text,
server_serial text,
server_os integer (FK points to os table),
os_version integer (FK points to os_version table),
cpu_type integer (FK points to cpu_type table),

Now, let's assume there are 20 things that will not be unique about this server (e.g., server_os is not unique), thus in Scenario #1 we have 20 columns that contain FKs pointing to PKs of other tables.

Scenario #2:

No normalization. No other tables, just a server table with all the columns related to the server. No FK/PK relationships.

Normally I would go for Scenario #1. However, in scenario #1 I end up with a lot more queries. For scenario #2, I would simply do an insert for each server. In scenario #1, I would need to do a select to grab the PKs for all 20 columns, and then do my insert with the returned values (21 queries assuming all values exist in the 20 other columns). It gets even worse if there's a new server_os, cpu_type, etc.

Any help understanding why scenario #1 (normalized) is better in this situation would be greatly appreciated, or what exactly I am missing here. Thank you!


texts: How to enforce an "exactly-one-per-group" constraint?

texts:
I have a table x that I've defined like this:

CREATE TABLE x (
    xid INTEGER NOT NULL PRIMARY KEY,
    yid INTEGER NOT NULL REFERENCES y(yid),
    is_principal BOOLEAN NOT NULL
);

This definition misses one constraint that x must satisfy.  In English, this constraint could be described like this:

  there may be one or more rows with a given value in the yid field, but among them there must always be exactly one whose is_principal field is TRUE1.

I'm looking for a way to enforce this constraint.

(In case it matters, I'm particularly interested in solutions applicable to SQLite3 and PostgreSQL.)

EDIT: Just to be clear, the description above does not preclude the existence of rows in table y whose value of yid is not mentioned at all in table x.  For such values of yid there is no value of xid at all, principal or otherwise.  It is only for those values of yid that appear in table x that there must be one and only one row in table x having is_principal = TRUE.

1 Another way to express the same constraint would be to say that the following two queries should always produce identical outputs:

SELECT DISTINCT yid FROM x                    ORDER BY yid;
SELECT          yid FROM x WHERE is_principal ORDER BY yid;


texts: SSIS package error, can't find pre-compiled script

texts:
I'm working with an SSIS package (Visual Studio and BIDS/SQL Server 2005 SP3) and out of the blue my script tasks have started failing to compile.

The actual error message: Validation error.  Generate Inbound Stats Report : The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE in Script Task Editor by clicking Design Script button to cause binary code to be generated.

To produce the error, I do the following steps:

Edit the existing package in VS
Open a script task
On the script tab and click Design Script
At this point I can just click on the X to close the script, doesn't matter if I save or not
Click OK on the script task editor window
Now I have a red X on the task and the error message above

Other facts:

I'm certain this is a local issue and not a problem with the SSIS package because if I break a package, save it, then have another member of my team perform the same steps, the error is fixed.
..but I can edit any other package's script tasks without issue (at least as many as I've tried)

This particular package has 4 script tasks, and I can produce the issue by repeating the same steps on 3 of the 4

Steps I've tried without success

Ran a Repair Installation on both SQL Server client tools and Visual Studio
Completely reinstalled both SQL server client tools (and updated to SP3) and Visual Studio 2005
A bunch of random workarounds, such as deleting the last line of the script, saving, then replacing it, creating a new script task and deleting the old one.

I haven't yet tried rebuilding the entire package - that would take me all day. Hopefully someone can help me find a better solution than that. The best I've found so far is reinstall and obviously that didn't work for me. Thanks in advance.


texts: How to load data to a table from SQL Server Audit File?

texts:
I have enabled Auditing in SQL Server 2008 R2 to monitor DML and DDL operations.

Is there any way to load SQLAudit files to the database? Or any other method to automatically load audit details directly to a database?


texts:
I use the following command to backup some normal databases:

mysqldump --host=host --user=user --password=passwd --result-file=result.sql -R --single-transaction database_name

My question is : do I need to also dump the "mysql" database ?
Note that I will create new users for each database after "restoring" them. So I don't need the "mysql" database for user information.

Thanks


texts: Copy SQL Server DB into another instance without SA access

texts:
I've two SQL Server databases and both of them are on cloud.

First server is source
Second server is destination

I only have a non SA access. And I'm using DBForge Studio for managing the database. I'm trying to copy the database from the first server to the second server through DBForge→Database Sync→Copy Database and then select copy (I'm following this article).

Note:
The database already exists in destination with same name, but no tables yet.

I got an error message when trying to copy:

  The EXECUTE permission was denied on the object 'xp_fileexist'.....

Then I try to copy & overide but I got the same error message.

So I try to use another way like export & import. I export the database from source server into .sql, but there are only queries for insert, it didn't serve the query for create table schema, and I tried to import into another file like .csv and .xsl but I got the same, it only contain data without schema.

Is there any better way to copy the database without SA access?


texts:
I have a PostgreSQL setup with one master and three slaves. It's configured as streaming replication.

Now, I want to replicate only a few tables from the master server to an analytic server using pglogical. For this, we need to enable the logical wal_level. So how can I enable it without breaking the existing replication?


texts: What is the relationship between the SSIS Package Store and Catalog?

texts: I have a server with a working installation of the Sql Server 2012 SSIS Catalog. I need to set up an additional instance including the SSIS Package Store service as an interim step while the packages are being re-written. The Package Store is a per-server feature, not a per-instance feature. Can these two features operate side-by-side?


texts: Is it against normal form to include columns with date parts if there is a datetime column in the same table?

texts:
I am working on a table that will hold batch data.

The table will have a BatchDate column as a DateTime type. I'm considering adding a column for BatchYear as an integer and BatchPeriod (which could be a week number, a month number or a quarter number) as an integer, even though both of these values can be derived from the BatchDate and using another column in the table.

I'm considering doing this because I'm thinking it would be easier to work with queries where someone doesn't have to pull the date from the table first, compute the period they want and then query the table again. Also, if someone is looking for a batch from the 3rd quarter of 2012, the server would have to convert each BatchDate to it's quarter equivalent and do the same for the year and then test for a match, but if the values are there in separate columns, one could simply query the BatchPeriod and the BatchYear column for a match.

Is this a bad idea to break normal form this way?


texts: Changing foreign key to ON DELETE CASCADE with least amount of impact

texts:
I have an existing foreign key that has ON DELETE NO ACTION defined. I need to change this foreign key to ON DELETE CASCADE. I can do this within a transaction:

begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade;
commit;

The problem is that the posts table is large (4 million rows) which means that validating the foreign key can take a non-trivial amount of time (I've tested this with a copy of the database). Dropping/adding the foreign key acquires an ACCESS EXCLUSIVE lock on posts. So, adding the foreign key blocks all access to the posts table for a decent amount of time because the lock is held while constraint validation occurs. I need to perform an online migration (I don't have a dedicated downtime window).

I know that I can perform 2 transactions to help with the check taking a long time:

begin;
alter table posts drop constraint posts_blog_id_fkey;
alter table posts add constraint posts_blog_id_fkey foreign key (blog_id) references blogs (id) on update no action on delete cascade not valid;
commit;

begin;
alter table posts validate constraint posts;
commit;

The advantage of this approach is that the ACCESS EXCLUSIVE lock is held for a very short time for dropping/adding the constraint and then for validating the constraint only a SHARE UPDATE EXCLUSIVE on posts and ROW SHARE lock on blogs since I'm on Postgres 9.5.

Are there downsides to this? I know that adding NOT VALID to the constraints means that existing data is not validated, but any rows inserted/updated before the VALIDATE CONSTRAINT will be checked. Because the foreign key is dropped/added in the same transaction, is there any possibility of creating inconsistent data?


texts: Does openquery use distributed transactions?

texts: Does openquery in SQL Server 2005 use distributed transactions? If so, in which cases does it?


texts: Can listen_addresses really be set to a list?

texts:
I have a VM with IP address 192.168.0.192 running postgreSQL.

If I specify

listen_addresses = '*'

then I can connect from another VM at 192.168.0.191 and from localhost.

But I can't seem to use a list to tell postgreSQL to use those two addresses.  If I change listen_addresses to a list:

listen_addresses = '192.168.0.191, localhost'

then I can no longer connect from 192.168.0.191.

I notice that almost all examples on stackexchange set listen_addresses to '*'.  Is this because the list form does not work?


texts: Set up a MySQL slave using mysqldump to get the initial data

texts:
I have a master database which already has some data in it. The tables in the master database include both MyISAM and InnoDB engines. Now I want use mysqldump to dump the initial data of my slave MySQL server. What parameter I should pass to mysqldump? Currently what I use is listed below.

mysqldump --host=localhost --user=root --password=pa4word --single-transaction --lock-all-tables --master-data=1 mydb > result.sql

Is this command OK? I don't know whether this is suit to my environment(both MyISAM and InnoDB engine tables in one database).

Thanks,


texts: Live migrate a database from one instance to another with zero/minimal downtime

texts:
There are 2 MySQL instances: MA and MB. They are completely separate from each other (no replication between them). Each instance contains multiple databases:

MA serves db1, db2
MB serves db3, db4

I want to migrate db1 to MB without any downtime, so the result will be:

MA serves db2
MB serves db1, db3, db4

I can instruct the application layer to pause write transactions to MA for a while ( < 10 seconds). Assume that it is possible to have replication lag (if set up) between MA and MB to be < 10 seconds.

Of course the most obvious way to do it is:

pause writes to db1 ("downtime" starts)
mysqldump db1 and apply to MB
move app layer settings to use MB for db1
unpause writes to db1 ("downtime" ends)

However, the above method is not acceptable when the mysqldump/apply takes a long time (> 10 seconds) for bigger databases. I have seen the method in https://exotel.com/blog/engineering/mysql-database-migration-with-zero-downtime/ but I think it only works when moving all databases in an instance, but for my case there is db2 in MA that still needs to be online at all times.

I have tried to fiddle around with database-specific replication, but I don't think it works well (I can guess that it might work with MySQL 8.0 filtered replication feature, but we are at 5.7 and not looking forward to an update).

Do you know of any way to do this?


texts: How Does SSIS Manage Connection Traffic?

texts:
I recently started using SSIS in my internship and I am still learning. We have a dedicated SSIS server that executes all of the ETL jobs and does not hold any other databases. We have many SQL servers as source and destination.

How does SSIS manage data transfer and transformations from source to destination when it is just executing the job and package? Does it establish a direct connection between the two or is the data channeled through the SSIS server?

Do you recommend adding SSIS to sources and/or destination servers and running jobs locally?


texts: ON CONFLICT ON CONSTRAINT fails saying constraint doesn't exist

texts:
I'm trying to use new Postgresql 9.5 upsert feature. But for some reason my query saying constraint doesn't exist (when it does).

My query is this

INSERT INTO journals (ext_ids, title) VALUES ('{"nlmid": "000"}', 'blah')
ON CONFLICT ON CONSTRAINT idx_nlmid_journal DO NOTHING;

where idx_nlmid_journal is unique index on jsonb field created like this

CREATE UNIQUE INDEX idx_nlmid_journal ON public.journals ((ext_ids ->> 'nlmid'::text));

I get error

ERROR: constraint "idx_nlmid_journal" for table "journals" does not exist

what am I missing ?


texts: #Deleted as values when using MS Access to query a db2 database through ODBC

texts:
When trying to query certain db2 tables I get #Deleted for all values in the table. But for other tables it works fine.
The row count of this query is equal to the row count when using the data studio client so it is actually querying the same table, just the fields don't display correctly.
This data studio result looks like this

and the ms access result looks like this

Update
After checking other tables I have found a similarity. Tables with a lot of records give me the results (but the values are '#Deleted'). The other tables display the results correctly.


texts: Deleting rows from tables without FK relationships

texts:
I have three tables, one of them is the "master" table and each of the other two has a field that contains the PK of the corresponding row in the master table, something like this:

TblA         TblB           TblC
----         ----           ----
ID_A (PK)    ID_B (PK)      ID_C (PK)
FieldA       ID_A (FK)      ID_A (FK)
...          FieldB         FieldC
             ...            ...

The DBA refused to create FK relationships between these tables ("performance hit" was his explanation). So the FK fields are actually just "normal" fields.

When deleting rows from the master table (TblA), the corresponding rows in the other tables have to be deleted as well. As there are no FK relationships (and therefore no cascading deletion), what is the safest way to delete these rows?

What about this transaction? (@RowID is the ID of the row to be deleted in TblA.)

BEGIN TRANSACTION
BEGIN TRY
   DELETE FROM TblA
   WHERE ID_A = @RowID

   DELETE FROM TblB
   WHERE ID_A = @RowID

   DELETE FROM TblC
   WHERE ID_A = @RowID

   COMMIT TRANSACTION
END TRY
BEGIN CATCH
  ROLLBACK TRANSACTION
END CATCH


texts: T-SQL - Script all tables creation, contraints, indexes, keys and triggers

texts: I'm using SSMS and I was wondering if there is a script that can generate tables creation, constraints, indexes, keys and triggers for a given table names as a single result and another script that will get the INSERT statement for all the data in the tables with the names in a list? I know that I could use right-Click on the database in object explorer, choose Tasks -> Generate Scripts... but I want to do this for multiple tables in the same time, as a script. Is it possible to achieve this?


texts:
I'm trying to build a procedure as follows:

CREATE PROCEDURE [test].[ZTEST1]
@Messages NVARCHAR(4000)

AS
SET NOCOUNT ON;

BEGIN
  Declare @SQL nvarchar(1000);
  SET @SQL = 'SELECT parsename(replace(substring(@ConcatenatedErrorMessages,charindex('+quotename(':','''')+',@ConcatenatedErrorMessages) + 1,len(@ConcatenatedErrorMessages)),'+quotename(':','''')+','+quotename('.','''')+'),4)';

EXECUTE SP_EXECUTESQL @SQL;

END
GO

I call the procedure like this:

test.ZTEST2 'test1:test2:test3:test4:test5';

but I get this error:

  Must declare the scalar variable "@Messages".

I'm not sure what the problem is.


texts: pg_get_viewdef error while running pg_dump in PostgreSQL

texts:
Occasionally, I get the following error while doing a simple pg_dump (
pg_dump -U postgres -Fc -f mybackup.dmp mydb):

pg_dump: [archiver (db)] query failed: ERROR:  could not open relation with OID 11072443
    pg_dump: [archiver (db)] query was: SELECT pg_catalog.pg_get_viewdef('11072443'::pg_catalog.oid) AS viewdef

The dump is then aborted.

I'm using PostgreSQL and pg_dump 9.2.2 for Windows.

Solutions welcome.


texts: Equivalent of Oracle RR date format in SQL Server

texts:
Having a bunch of SQL insert scripts generated in Oracle, I have the requirement to adjust them to be usable with T-SQL in Microsoft SQL Server.

The Oracle export has date formats like this:

to_date('01.02.28','DD.MM.RR')

The RR means that values between 0 and 49 assume the current century, whereas 50 to 99 asume the previous century.

I currently see no SQL Server equivalent to that notation.

The only solution I can think about is to write a quick script that does some Regex and/or string operations to detect the numeric values and add the century by myself so that I could use YYYY for SQL Server.

My question:

Is there a way to express RR in T-SQL?


texts: Memory usage on select for large number of rows

texts:
I am trying to dump the entire contents of a large table, from the command line using pqsl, but am running into a problem where memory usage goes up, to the point where the process is killed, before any data is even dumped.

What I don't understand is: why isn't the query returning results immediately, and completing without running out of memory?

Here is an explanation of exactly what I'm attempting:

I have a table, say:

CREATE TABLE big
(
  id integer,
  rand double precision
)

A large number of rows is inserted (50 million):

insert into big
  select generate_series(1, 50000000) AS id, random();

The query plan to select every row looks like (not surprisingly):

$ psql -d big -c "explain select * from big;"

                       QUERY PLAN
----------------------------------------------------------------
 Seq Scan on big  (cost=0.00..924326.24 rows=50000124 width=12)
(1 row)

I then attempt to dump the contents to file:

$ psql -d big -c "select * from big;" > big.dump

As I said above, this command is failing before any data is written, seemingly by taking up an ever increasing amount of memory before being killed by the OS (by "OOM killer").

Note: I understand I could use pg_dump to accomplish something similar, but in reality, my query is more complex than this - specifically, I would like to encode each row as JSON when dumping.

Some configuration details:

postgresql version = 9.3.4
work_mem = 1MB
shared_buffers = 128MB
effective_cache_size = 128MB


texts: How to alter primary key constraint for 2 columns in same table in SQL Server?

texts:
In SQL Server, I already created a table called tblDatabase with some columns. I want to add a primary key on the two columns DatabaseName and Servername.

I tried to add:

ALTER TABLE tblDatabase ADD CONSTRAINT pk_DatabaseServer
                            PRIMARY KEY (DatabaseName, ServerName)

in check constraints but I get pop up error that say

  error validating constraint 'ck_tblDatabase'.

Did I miss something?

I also have ServerName as PK in tblServer that already relationship with tblDatabase. Is that the reason why?


texts:
We are in the process of putting together a whole system for a new startup company. This project is meant to store thousands of requests and each one needs to be stored and displayed in real-time.

This data will then be analysed so as to gather insight on traffic patterns etc.

After conducting a good amount of research we thought that HBase would be a perfect fit due to its HDFS replication, MapReduce via Hadoop and fast read/write mechanism. However the issue is that it is recommended to have a couple of million rows when implementing Hbase for the first time.

Obviously being a startup we would not have such traffic in the beginning however the number of requests is expected to increase quite quickly over a short period of time.

Therefore what would you suggest, still going for HBase or starting with e.g. MySQL and change once we have the right amount of traffic? The issue with using MySQL or any other DBMS is the aspect of real-time whilst also having to shift the whole system over to HBase should we start running into issues of sharding etc.


texts:
I want to add a job to my DB to make a back up weekly but i want to do it from an script, i've serched and with what i found i made this:

CREATE SCHEMA job
go
CREATE PROC BACKUPS @dir varchar(max)
AS BEGIN
    DECLARE @comando nvarchar(max) = N'BACKUP DATABASE STAZIONE TO DISK = '''+@dir+'-'+convert(char(8),getdate(),104)+'.bak'' WITH  COMPRESSION,  ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert), STATS=1'
    EXEC job.sp_add_job @job_name = @job ;--Add a job
    EXEC job.sp_add_jobstep @job_name = @job, @step_name = N'Backing',  @subsystem = N'TSQL', @command = @comando   --Schedule the job at a specified date and time
    EXEC job.sp_add_jobschedule @job_name = @job, @name = N'DB_BACKUP', @freq_type=8, @freq_interval =1, @active_start_date = @startdate,   @active_start_time = @starttime -- Add the job to the SQL Server Server will run weekly
    EXEC job.sp_add_jobserver @job_name =  @job, @server_name = @servername
END
GO

But when executin i get this:

  The 'BACKUPS' module depends on the missing object 'job.sp_add_job'. Even so, the module will be created; however, you can not run successfully until the object exists.

How can i create a job for my DB ?


texts: SQL Server 2005 Cluster Service pack 4 upgrade failure at remote user account

texts:
I built SQL Server 2005 ENT Cluster on Windows Server 2008 R2. I need to upgrade it to SQL Server 2005 SP4. However, I get this error at the Remote User Account screen

  Enter the remote user account name and password below.
  User name, Password and domain fields.
  Your account information could not be verified.
  Press Ok to return to Authentication mode screen to determine the reason for failure.
  For setup to verify your credentials the services must be startable, connectable, and you must be a SQL Server Administrator

I am using  an domain account that has access and local admin rights to both servers. The SQL Server and agent run as this domain account and sysadmin .

I got connected using the active node name and also using the virtual name and I get the same error. I also made sure no one is connected to the passive node.

This is my exact error message on the link. But none helped.
http://answers.flyppdevportal.com/categories/sqlserver/sqlsetupandupgrade.aspx?ID=89159030-1f3d-4f13-a693-8122b6bb6aae

The link below from discusses "To work around this problem, use the NetBIOS name of the domain instead of the FQDN" but I do not know where I would be using .
http://support.microsoft.com/default.aspx?kbid=932870

Thanks


texts:
We're trying to create an exact copy of our prod database to later use it for reporting.

I was told that some of my colleges tried to use the out of the box replication tools that SQL Server provides without any success.

I believe the reason it failed was because of the version of the servers (Runtime Edition if I'm not mistaken).

Are there any good third party tools to replicate data from a SQL Server 2005 to another server?


texts: How to most effectively get the first preceding/subsequent entry outside a given time interval?

texts:
We have a SQL Server DB that contains a large table (>100M rows) with archive values, basically in the form of [Id], [StationId], [DatapointId], [Timestamp], [Value]. As the stations do not get polled, there aren't any regular intervals at which a station sends a datapoint, so the timestamps are set to arbitrary times.

For our web visualization, I need to extract entries from this table for a pannable chart that I'm building with D3.js. I need to query by StationId, DatapointId, StartDate and EndDate. This is simple and works as expected, but for the line chart that it feeds, I do need additional data, namely the first value before the chosen time interval, and (if there is one) the first value after the interval. This is neccessary to draw the line from the left resp. right boundary of the chart to the first/last value within the interval (otherwise the data within the chart would look somewhat "isolated" because the line stops before the charts boundaries, like there's no data before or after the values in the chosen interval).

At the moment I need to run 3 queries: one to get the data within the interval, one to retrieve the first matching row before StartDate, and anotherone to get the first matching row after EndDate.

It works, and after creating a covering index the speed is ok, but I think there might be a way to reduce the number of roundtrips to the DB. Do you have any idea?


texts: Connecting two different 'user' tables into one single table

texts:
How to connect three tables so when the admin inserts one row in a table, that row will be copied and pasted in another table? Or how to define a relationship between them?

I have three tables in a database, they are tb_student, tb_teacher, tb_user. Their DDL design is shown below:

-- Teacher Table: Primary Key is `id_teacher`

CREATE TABLE `tb_teacher` (
  `id_teacher` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Sample Data

INSERT INTO `tb_teacher` (`id_teacher`, `name`, `status`) VALUES
('14320', 'Lenny Belardo', 'active'),
('14323', 'Shoffin Nahwa Utama', 'nonactive');

-- Student Table: Primary Key is `id_student`

CREATE TABLE `tb_student` (
  `id_student` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entry_year` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Sample Data

INSERT INTO `tb_student` (`id_student`, `name`, `entry_year`, `status`) VALUES
('352014110577', 'Buddha Gautama', '2014', 'active'),
('352014121153', 'Harie Tanoesoedibjo', '2015', 'active');

tb_user is kind of like the combination of in both tb_student and tb_teacher

-- tb_user: Primary key is ID

CREATE TABLE `tb_user` (
  `id_user` varchar(13) COLLATE utf8_unicode_ci NOT NULL,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `entry_year` varchar(4) COLLATE utf8_unicode_ci NOT NULL,
  `level` enum('admin','teacher','student') COLLATE utf8_unicode_ci NOT NULL,
  `status` enum('active','nonactive') COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I intend to create accounts for both students and teachers; therefore, a user table is necessary to create. The data type from tb_student.id_student and tb_teacher.id_teacher is matching with tb_user.id_user which is VARCHAR(13). The sample data is as the following:

INSERT INTO `tb_user` (`id_user`, `username`, `name`, `password`, `entry_year`, `level`, `status`) VALUES
('14231', 'spectrum@unida.gontor.ac.id', 'Nunik Siti Nurzanah', '123456', '2014', 'teacher', 'active'),
('352014121188', 'tukiyem@ngebel.udik.gov.id', 'Tukiyem', '123456', '2012', 'student', 'active');

Where the id 14231 is a teacher and the id 352014121188 is a student.

How to define a relationship between these three tables so that when the admin inserts a new row of student/teacher, the data is copied automatically to the user table?

Right now I still need to insert the data for these three tables manually or one-by-one.


texts: Slave DB promoted as Master DB when Master DB is down. New data added on the new master while old master is down

texts:
I am currently setting up a call center system (Vicidial) having a Master DB and a Slave DB.
When the Master would go down, I have made a script on the SlaveDB that would execute "stop slave" on the slave mysql service and make it as a new master by pointing the other servers (Web, Dialer, Archive) to the slave as their new Master database server.

When the master is up again, I would run another script on the SlaveDB that would point the servers back to the original MasterDB and would let the slave DB become slave again to the original MasterDB by executing "start slave".

My question is ~
If there are new data added on the New Master while the original master is down, how do I replicate this data to the original master when it goes back up before I start demoting the new master to slave again?

Answers and advice/constructive criticisms will be much appreciated.

P.S. I am a newbie in Databases and scripting.

Thanks in advance!


texts: Do SQL Server stored procedures cache data results?

texts:
I have heard this from friends, but I never investigated whether this is true.

Is it true that the data results of an executed query are stored in cache?

I mean, if I have a stored procedure like :

SELECT * FROM USERLIST

...is it true that the result (list of users, in this case) is stored in cache.

Also, if I have these :

SELECT * FROM USERLIST WHERE user="user"
SELECT * FROM USERLIST WHERE user="userzzz"

(in which user/userzzz are passed as parameters), is it correct that it stores 2 different results on the database cache.

I don't think so, but I want the confirmation from you, experts!


texts:
I'm just starting to code and I try to host a Wordpress website on a server of my school.
However, as soon as I upload the website on the Wordpress interface with the plugin "all-in-one migration", the database crashed. Wordpress gives me the error :

Warning: mysqli_real_connect(): (HY000/2002): Connection refused in /var/www/html/wordpress/wp-includes/wp-db.php on line 1626

When I check my database, I find it :

● mariadb.service - MariaDB 10.3.22 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: deactivating (final-sigterm) (Result: timeout) since Thu 2020-06-18 21:23:34 UTC; 28min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 10644 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
  Process: 10645 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 10647 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1
 Main PID: 10695 (mysqld)
   Status: "Waiting for page cleaner"
    Tasks: 3 (limit: 563)
   Memory: 65.1M
   CGroup: /system.slice/mariadb.service
           └─10695 /usr/sbin/mysqld

Jun 18 21:23:40 genius systemd[1]: Starting MariaDB 10.3.22 database server...
Jun 18 21:23:41 genius mysqld[10695]: 2020-06-18 21:23:41 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-0+deb10u1) starting as process 10695 ...
Jun 18 21:39:50 genius systemd[1]: mariadb.service: State 'stop-sigterm' timed out. Skipping SIGKILL.

And, of course, when I try to enter my database, I get the code :

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

I tried to restart the database, but it doesn't return any response.

By searching on StackOverflow, a few answers suggest changing localhost  to a local IP like 127.0.0.1 in the file "wp-config.php", but it didn't change anything.

Can you help me please ?


texts: Getting email notification when jobs are created under user_schema rather than sys?

texts:
I log in as sys and set the smtp for scheduler:

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.1.1.15');
  DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'user@compny.com');
END;
/

Then create a job under user schema:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'user.daksh_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN NULL; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=minutely; bysecond=0',
    end_date        => SYSTIMESTAMP + 1/24,
    enabled         => TRUE);
END;
/

and configure the email notification:

BEGIN
 DBMS_SCHEDULER.add_job_email_notification (
  job_name   =>   'user.daksh_job',
  recipients =>  'dasharath.dixit@suddath.com',
  subject => 'Scheduler Job Notification : %daksh%.%daksh_job%-%event_type%',
  events     =>  'job_started, job_succeeded');
END;
/

I am not getting any email. If I create the job under sys schema I am getting an email.

I did not give any permission to user to send an email. It is Oracle 12.1. I just set the scheduler attribute under that user schema. Job is running successfully every time, but email is not coming.

I think we don't have to configure the utl_mail for during schedule a job only

BEGIN
  DBMS_SCHEDULER.set_scheduler_attribute('email_server', '10.1.1.15');
  DBMS_SCHEDULER.set_scheduler_attribute('email_sender', 'user@compny.com');
END;
/


texts:
I have several log shipping jobs that I have configured to run every 15 minutes.  I am clicking on the job history to find that they have not run.  I can manually run them fine.  There are no errors.  Why would these jobs not be running on their own?

(SQL 2014 shipping to SQL 2016)

Edit: Additional Confusing Information

To add to my confusion, I right clicked on the primary server and clicked on Reports > Transaction Log Shipping Reports.  The image below shows an issue in the very first row with the AppMng_Service_DB... database.  I checked the folder where this database is supposed to back up it's transactions to and the files are there (as recent as 15 minutes ago).  Why would this report show Time Since Last Backup 1230min?  Is this an alert created from a potentially old deleted log shipping job?


texts:
I have existing .bat file where i call FDSLoader64.exe on daily basis to update following tables.

FF_ADVANCED_AF
FF_ADVANCED_SAF
FF_BASIC_SAF
FF_BASIC_AF

FDSLoader64.exe is supposed to download latest txt files related to resepctive tables from FTP and upload it.

After update is successfull FDSLoader64.exe updates metadata tables where we can know whether update was successfull or not.

To do this i have created a PROGRAM in Oracle 11g as follows

BEGIN
   DBMS_SCHEDULER.DISABLE(name=>'FDS_DEMO.FF_V3', force=>TRUE);
   DBMS_SCHEDULER.ENABLE(name=>'FDS_DEMO.FF_V3');
END;

Created a schedule for it as daily it should run at 11:30 AM.

BEGIN
    DBMS_SCHEDULER.set_attribute( name => '"FDS_DEMO"."DAILY_9AM"', attribute => 'repeat_interval', value => 'FREQ=DAILY;BYDAY=MON,TUE,WED,THU,FRI;BYHOUR=11;BYMINUTE=30;BYSECOND=0');
END;

At last i have created a job which runs the program using the schedule.

BEGIN
DBMS_SCHEDULER.set_attribute( name => '"FDS_DEMO"."FF_V3_GENESIS"', attribute => 'job_priority', value => 1);
END;
/

Schedule had successfully ran at 11:30 on 24th June 2019. As per metadata tables we can confirm that data was uploaded to the tables successfully but when i see content of user_scheduler_job_run_details it shows me results as follows.

**Complete error code is as follows.**
"ORA-27369: job of type EXECUTABLE failed with exit code: There are no
child processes to wait for.
STANDARD_ERROR="2019/06/24 11:30:16  INFO>: config.xml is at the latest
version 2.10, continuing.
2019/06/24 11:30:21  INFO>: FDSLoader version 2.13.1.0
2019/06/24 11:30:21  INFO>: Emptying directory D:\FDSLoader\d""


texts:
While migrating data from Oracle 11.2.0.1 RAC to Oracle 11.2.0.3 RAC using Data Pump, the import failed with ORA-01460:

ORA-39097: Data Pump job encountered unexpected error -12801
ORA-39065: unexpected master process exception in MAIN
ORA-12801: error signaled in parallel query server PZ99, instance oda2:PROD2 (2)
ORA-01460: unimplemented or unreasonable conversion requested
Job "USER_SCHEMA"."SYS_IMPORT_FULL_01" stopped due to fatal error at 02:29:54

As far as I know the ORA-01460 is triggered in relation to a function like TO_DATE, TO_CHAR or TO_NUMBER. Why would any of these be used during a plain export/import operation?

Any idea as to what could cause this error, or how to get more information?


texts: Restarting a Publisher involved in Merge replication

texts:
Our hosting provider would like restart the SQL Server that a publisher in a merge replication topology.

I'm not able to clearly understand best practice for doing this from the Microsoft docs.

Our topology consists of ~40 Pull subscribers. The synchronisation is executed on a schedule ever 5 minutes via a Windows Scheduled task that runs an App that calls RMO.

Some of my concerns include: Will transactions be safely cued on the Subscribers - I imagine this will potentially cause transaction log growth? What is a reasonable time period for the Publisher to be offline - do I need to worry about expiry?

If someone could outline what steps would be involved in safely shutting down the Publisher that would be a great help.

Many thanks.


texts: How do I fix and prevent corrupted MySQL tables?

texts:
We have developed software that uses MySQL. After some months of usage, some of the database tables had been corrupted and need to be repaired. Because of the corruption,we are not able to create a backup using Navicat, and we can not generate a dump too.

The log file has been indicating the following error for more than 2 days:

YYMMDD HH:MM:SS [ERROR] /usr/sbin/mysqld: Can't open file: 'ini.MYI' (errno:145)

What is the cause of this corruption? How can we fix them? How can we prevent the database tables from corruption in the future?