texts: MySQL+Web-App Performance Issues using different servers

texts:
We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage to squeeze out another 30% more TPS  thanks to those Percona articles. However, our problem is with our in-house web-app (a Tomcat/Java/Apache model). It performs poorly when connected to certain servers - the better hardware servers.

Here is the symptom:

If we point our test application server (Ubuntu, Apache, Tomcat, Java) to server MYSQL02, the applications performance is acceptable. However, if we point the application to MYSQL01 or MYSQL03 (and these two boxes are idle!) the application performance is poor. There are high latencies. Example:

Retrieving an account (via our web-app) from MYSQL02 takes
2 to 3 seconds. On MYSQL01 or MYSQL03 it takes 12 seconds or longer.

We cannot figure out why! The MySQL servers and MONyog do NOT report any problems! If we execute the statements (100's of them) manually they return instance results and their explanations show they are all using indexes. We do NOT get any slow query, deadlock, or contention notifications.

Here is some basic information about our MySQL systems. They are all DEDICATED MySQL servers:

PROD (current production, not in replication farm, standalone)

Best/newest hardware configuration (8 core/ 20GB / 15K RPM HW RAID 10)
MySQL: 5.0 (RHEL)
We are trying to migrate from this MySQL 5.0 to our MySQL 5.5 farm (see below).
This server will then be reloaded with MySQL 5.5 and return as the primary
production server.

MYSQL01

Second best hardware configuration (8 core / 12GB RAM / 15K RPM HW RAID 10)
Supermicro X8FAH; 2xXEON X5550 @ 2.66GHz
2x LSI MegaRAID SAS 9260-8i
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

MYSQL02

Worst/oldest hardware configuration(4 core / 12GB RAM / 7.2K RPM SW {mdadm} RAID 5)
Supermicro X2DBE; 2xXEON 5150 @ 2.66GHz
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

This is our EDI workhorse. It spends most of it's day importing data from
our old mainframe system. It replicates the data to 01 and 03.

MYSQL03

Third best hardware configuration (8 core / 12GB RAM / 7.2K RPM HW RAID 10)
Supermicro X7DGB8-X; XEON E5410 @ 2.33GHz
MySQL: 5.5.31 (Ubuntu)
OS: Ubuntu 12.04 LTS x64

We used sysbench to test and tweak all the above systems and here are the test results with notes.

NOTE: TPS = Transactions Per Second

Results before applying any new tweaks:

PROD: 1,179.72 TPS <- Expected
MYSQL01: 442.92 TPS <- Why?
MYSQL02: 543.22 TPS <- Expected
MYSQL03: 904.22 TPS <- Surprising

Results after my.cnf tweaks:

MYSQL01: 655.00 TPS <- 32% improvement. Not bad but still very poor compared to its siblings.
MYSQL02: 754.81 TPS <- 28% improvement. This was unexpected.
MYSQL03: 969.59 TPS <- 07% improvement.

We are unsure why MYSQL01's performance is so poor. We can only summarize that there is an OS, RAID CARD or BIOS setting(s) that may be improperly set. I am leaning towards the RAID Card/Configuration. They only way to know for sure is to shutdown this server and scrutinize the configuration. A reload may be necessary. However, since it is our ultimate plan to make the current PROD hardware the primary production MySQL server then we may leave MYSQL01 alone for now and re-purpose the hardware after migrating to the 5.5 farm. However, we can't migrate until we figure out why our application is behaving so poorly on certain hardware.

Anyone have any suggestions?


texts:
I have installed the latest stable version of PostgreSQL v9.2 and I want to install the PostGIS v2 extension.

I've been searching for it for 3 days now, however can't find it.

I did however find PostGIS v2 for PostgreSQL v9.1, thanks to this site: http://linfiniti.com/2012/05/installing-postgis-2-0-on-ubuntu/

I'm an Ubuntu novice, I'm more accustomed to Windows.

The PostgreSQL v9.1 package doesn't work.  Where can I get the correct package?


texts: Verifying password policy on existing users

texts:
I've recently come into an environment where a lot of databases logins do not have the enforce_password_policy flag enabled.
An upcoming audit is necessitating the verification of these logins' passwords.

I used the following query to obtain a list of logins and whether the flags are on or off.

select
    @@SERVERNAME as servername,
    name,
    IS_SRVROLEMEMBER('sysadmin', name) as SYSADMIN,
    type_desc,
    create_date,
    is_policy_checked,
    is_disabled,
    password_hash,
    PWDCOMPARE(name, password_hash) as UsernameAsPassword
FROM sys.sql_logins

However, this doesn't tell me if the passwords actually adhere to the password policy, as the flag is only relevant when creating a user.

Is there a known way to test existing users for password policy compliance?
I have no access to the old passwords, and I would prefer a method that doesn't require them.


texts:
I have a trigger on an entity that copies the row and saves it in a different entity.

I also have 2 MySQL servers one on Linux and the other on Windows. It seems the order of the columns in the same table are different between the servers. Is there a way to make them the same order? Some configuration?

I'm creating an audit table using this script:

CREATE TABLE xxx.AuditEntity (
  auditAction VARCHAR(45) NULL DEFAULT NULL COMMENT '',
  auditTime DATETIME NULL DEFAULT NULL COMMENT '',
  type VARCHAR(64)  NULL COMMENT '',
  id BIGINT(20) NOT NULL COMMENT '',
  modifiedBy BIGINT(20) NULL DEFAULT NULL COMMENT '',
  timeCreated DATETIME  NULL COMMENT '',
  currency VARCHAR(32)  NULL COMMENT '',
  nameVARCHAR(128) NULL DEFAULT NULL COMMENT '',
   INDEX auditTime (auditTime ASC)  COMMENT '',
  INDEX id (id ASC)  COMMENT '');

The trigger on the original entity:

CREATE DEFINER=`root`@`localhost`
TRIGGER xxx.entity_AINS AFTER INSERT ON Entity FOR EACH ROW
BEGIN INSERT INTO AuditEntity select 'AFTER_INSERT', now(), e.*
    from Entity e where id = NEW.id;
END

Because the columns in the 2 servers have a different order it doesn't work.

Our idea is not to explicitly write out the columns' names, is there a difference between how Linux and Windows save entities?


texts: Update mysql DB when data has a single quote with Dynamic values

texts:
I am writing data to mysql DB from my linux machine using sqlcmd. My script is updating the data Dynamically based on the employee number. I have a situation where the the CN attribute might have single quote and the update fails for that employee record. How can the below command modified to ignore the single quote. I know by using a double single quote can do the job for a single entry and there are many solution for this that has two or three line sql command to achieve the task, please note I am using sqlcmd to update my data and the data is Dynamic, so am looking for a one line command to update the data

Below I have an employee where the CN has value "Testing 'O User", so SQL fails to update. I know I can use a single quote in front of O and that should do the job "Testing ''O User".....But think of Dynamic Values I may not able to go to each record and do that...Need your help please...

sqlcmd -S servername1,8888 -d TESTDB -Q "update test_data SET status = 'HIRED', CN = 'Testing 'O User' where employeenumber = '12345';"

Below is the error:

  Msg 102, Level 15, State 1, Line 6
  Incorrect syntax near 'O'.
  Msg 105, Level 15, State 1, Line 6
  Unclosed quotation mark after the character string ';
  '.


texts: Could I use both UUID and Sequential ID or is it bad design

texts:
Sequential IDs have pros and cons. UUIDs have pros and cons too. Why not use both for each table? (Let's say I have -and will have- no problem about storage space)

I could use:

UUID for exposition, display, export, API, etc.
Sequential ID for join, internal queries, convenience and debugging.

Is it a bad design idea? What are the pitfalls?

The benefit of using both would be to get the "pros" of each. There is an infinite discussion about "use seq id VS use uuid" and both have good arguments, so maybe I can use both and take benefits of each (depending on use case).


texts: Moving Large Tables and All Indexes to a new File Group

texts:
I'm looking at implementing File Groups on an existing database that's roughly 600GB. The largest hurdle I have is actually setting this up in production. I need the database to still be available but moving some of the tables will take a considerable amount of time. In development it took nearly 5 days to move all the indexes to their new homes and then shrink the database.

So what is the best way to implement file groups on a large database where you need to keep the database live?


texts: Oracle 11g install on Debian Wheezy does not start

texts:
I successfully installed Oracle 11g R2 on my laptop for evaluation, by mixing various sources of documentation. I saw the Oracle daemons after installation.
But I have a few problems :

After a reboot, the instance does not start, and does not even create
a log file. (only the TNSLSNR starts).
The oracle user's ORACLE_SID gets a default value (AL32UTF8) where
the value I set in the /etc/profile.d/oracle.sh initialisation script
is INFO (created during install). Where does this come from?
Where can I start investigating?

Thanks for your help.

Note : Only one line not commented out in the /etc/oratab file :

  INFO:/u01/app/oracle/product/11.2.0/dbhome_1:N

Best regards,

Fred


texts: What's worse, too many entities or too many tables?

texts:
I just had a question about database design, I am in fairly early learning stages of this so bear with me.

Suppose I have a parent table called "Projects" each "Project" Entity can have any number of "Attachments" and each "Attachment" can have any number of "Anchors".

I can only see a few possible solutions for this.
Estimations assume 10 years use of this database:
300 Projects/year * 3 Attachments/Project * 20 Anchors/Attachment

Solution 1 (My initial design idea):

Involves 3 tables. "Projects"->"Attachments"->"Anchors"

Number of Tables: 3

Max entities in a table: 180,000 in "Anchors"

Solution 2:

Involves 2+n tables.  "Projects"->"Attachments"->"Anchors[n]"

Number of Tables: 9,002 (9,000 Anchor Tables)

Max entities in a table: 9,000 (In Attachment Tables)

Solution 3:

Involves 1+n1+n2 tables. "Projects"->"Attachments[n1]"->"Anchors[n2]"

Number of Tables: 12,001 (3,000 Attachment + 9,000 Anchor)

Max entities in a table: 3,000 (In Project Tables)

To summarize:

What's worse, more entities or more tables?
AND/OR
Is there simply a better design for this situation that I am unaware of?


texts: Is there a standard language/interface for programmatic ETL in SQL Server?

texts:
I'm currently in the process of creating ETLs for our data warehouse. We're using SSIS 2008, but we're running into issues, the biggest of which is the difficulty in re-using components. We have separate packages for each table and each package takes as input a number of variables from a parent package. As we make changes to these input variables, we're required to go into each package (we have 15 or so now, but this number is going to grow significantly) and modify the package to deal with those changes. There are also other issues, including the inability to run arbitrary SQL for our extraction, poor logging capabilities, etc.

This entire process would be much more robust if there were a way develop our ETLs in code, enabling code reuse, common libraries, better unit tests, etc. Is there a de facto standard ETL language / API for SQL Server? I'm looking to avoid GUI tools as much as possible.

Edit: I should mention my background. I'm not a DBA and have no formal (or informal) DBA training, I've basically figured this stuff out as I went along, so there is every likelihood that I'm attempting to do inappropriate things with SSIS or approaching this ETL project from the wrong angle. Also, I'm currently employed in state government, so any solutions that require the purchase of a new software package aren't within the realm of possibility.

Here's one of our tasks. We are using a single SSIS Package to load each table in our warehouse. Each Fact package and Dimension package are generally the same, they only differ in

Extractions from the source database
Manipulations in a Data Flow
Merges into the destination table

What I would like to be able to do (that I'm finding to be difficult to do in SSIS)

Load the extraction query from a text file. When developers are writing and testing their extraction queries, I should not have to manipulate their query in any way before SSIS runs it and I should not have to cut and paste the query into a DB Source object.
Test each component individually. I should be able to test the complete ETL process for an individual table in isolation, independent of other table loads.
Make modifications to the shared logic in one place, not have to edit each individual package. Every package loads data into the audit tables the same way, if I want to change the data that is loaded audited, I don't want to have to edit all 15 packages (this number is going to get much much larger over time).

The entire process feels like it would be much easier to implement and more robust if done programatically with proper use of shared code.


texts:
I am currently setting up a website that uses a MySQL database having more than 40 million rows of data. I am using one server for files and separate mysql server. Two servers have 20 cores, 64G ram.
Meanwhile I could see while executing longest SQL queries my MySQL server is using two cores maximum hence it is taking more than 38 seconds to execute my longest query. See the result below.

User@Host: dev_data @ localhost []
Query_time: 38.113460 Lock_time: 0.000514
Rows_sent: 10 Rows_examined: 48683733

How can I configure my MySQL server so that it uses all the 20 cores to handle the query? MySQL version is 5.6.
Atop result below.


texts:
I have such a table:
I want to rank the people, as who has the more V1 and the less V2, V3, V4, V5 be on top.

Please advise me SQL code for MS-ACCESS.


texts: Checking for dupes on Compact Insert Statement

texts:
I'm not sure if that is the correct term "Compact Insert Statement" It is just what I have always heard it referred to. It goes as follows:

INSERT INTO [tblUsers]
       ([username], [password])
VALUES ('user1', 'pass1'),
       ('user2', 'pass2')

Anyway We have a table of just over 5M rows, and are about to import some data, BUT the probability is high that about 75% of the data is duplicated (we buy data from multiple sources, but they share around 30-40% with each of our sources :/).

If I do a unique constraint on the column, the entire insert fails from that point forward (unless wrapped in a transaction, of course).

I am just at a loss for how to do this efficiently and with code that can be reused going forward.


texts: Implement a Parent-Level Count in Binary Search Tree

texts:
Based on this question, have any way to implement a parent level count?

Find highest level of a hierarchical field: with vs without CTEs

Example table:

+----+-----------+
| id | parent_id |
+----+-----------+
|  1 |         0 |
|  2 |         1 |
|  3 |         1 |
|  4 |         2 |
|  5 |         2 |
|  6 |         3 |
|  7 |         3 |
|  8 |         5 |
|  9 |         5 |
| 10 |         7 |
+----+-----------+

Function:

DELIMITER $$

DROP FUNCTION IF EXISTS `junk`.`GetFamilyTree` $$
CREATE FUNCTION `junk`.`GetFamilyTree` (GivenID INT, Count INT) RETURNS varchar(1024) CHARSET latin1
DETERMINISTIC
BEGIN

DECLARE rv,q,queue,queue_children VARCHAR(1024);
DECLARE queue_length,front_id,pos INT;

SET rv = '';
SET queue = GivenID;
SET queue_length = 1;
SET count_limit = Count;

WHILE queue_length > 0 && count_limit > 0 DO
    SET front_id = FORMAT(queue,0);
    IF queue_length = 1 THEN
        SET queue = '';
    ELSE
        SET pos = LOCATE(',',queue) + 1;
        SET q = SUBSTR(queue,pos);
        SET queue = q;
    END IF;
    SET queue_length = queue_length - 1;

    SELECT IFNULL(qc,'') INTO queue_children
    FROM (SELECT GROUP_CONCAT(id) qc
    FROM pctable WHERE parent_id = front_id) A;

    IF LENGTH(queue_children) = 0 THEN
        IF LENGTH(queue) = 0 THEN
            SET queue_length = 0;
        END IF;
    ELSE
        IF LENGTH(rv) = 0 THEN
            SET rv = queue_children;
        ELSE
            SET rv = CONCAT(rv,',',queue_children);
        END IF;
        IF LENGTH(queue) = 0 THEN
            SET queue = queue_children;
        ELSE
            SET queue = CONCAT(queue,',',queue_children);
        END IF;
        SET queue_length = LENGTH(queue) - LENGTH(REPLACE(queue,',','')) + 1;
    END IF;
END WHILE;

RETURN rv;

END $$

I need implement a count on that function to limit iterate based on parameter passed on query.

SELECT id,parent_id, GetFamilyTree(1, 3) FROM pctable;

Detail: first param is id of desired family tree
        secund param is a level depth of descendants

Example result of example table:

+----+-----------+
| id | parent_id |
+----+-----------+
|   1|        0  |
|   2|        1  |
|   3|        1  |
|   4|        2  |
|   5|        2  |
|   6|        3  |
|   7|        3  |
+----+-----------+

It is possible?

In this case, the count not working as well...its counting every tree item, not every tree level.


texts:
I´m a newbie in the SQL world and I code with just the simple insert, select, delete and update.

So... I have a table with a column named "market" that has a list of cryptocoins... like this...

market
-------
EOS/ETH
EOS/BTC
XMR/BTC
LTC/BTC
DASH/ETH
...

I need to select * from this table where:

the string before the '/' is equal to each other but after the '/' goes ETH and BTC.
There is a column named exchange that I want to filter too. In this example would be all this coins where exchange = 'bittrex'

In this example the result would be:

EOS/ETH
EOS/BTC

Can anyone help?


texts:
I'm trying to think about the best way to do this. If i have a table like this in sql server:
StudentPointsTable
StudentID
TestDate
Points

I need to have Averages and standard deviation calculated as well for a student. I know i can create a view, or create a new table with these computed columns, or add the computed columns to this table.

Not sure if there are any other options but in terms of performance, what would be the best way to do this?


texts: Strange Hostname/Application name when hunting for SA login errors

texts:
So I've noticed thousands of login failed for user S.A. after I've changed sa password. We all agreed with this, its a test environment and nobody was using SA and since i'm new here I would like to have all SA password. Nobody noticed any problem, but I can see this strange behavior running a trace on the server:

I've never seen such thing. hostname and appname is just random characters.  Has anyone seen this before?

The login is failing so this query above has no use:

SELECT s.session_id, s.login_name, DB_NAME(s.database_id) as database_name,
    s.host_name, s.program_name, c.client_net_address, client_tcp_port,
    c.net_transport, c.auth_scheme, s.login_time
FROM sys.dm_exec_sessions s
INNER JOIN sys.dm_exec_connections c
ON s.session_id = c.session_id
ORDER BY s.session_id


texts: Is there any difference on backup process whether it is recovery model is Full or Simple?

texts:
In past i have done server migration and i moved my databases by backup via this script template.

BACKUP DATABASE [ExampleDB] TO  DISK = N'D:\Backups\ExampleDb.bak

And i restored bak files on new server. Unexpectedly a user reported data lost after migration and i validated it by checking data in old server. It was around 8 hours data lost. This was very unexpected moment for me.

My question is having backup via that script will give me full backup whether it is in Full or Simple recovery model ?

In another way, Is there any difference between backup files when i get backup whether recover model is Simple or Full ?


texts: SQL Server connectivity and script execution using PowerShell

texts:
I am designing a PowerShell script that can execute some sql scripts in a folder. Actually this must be done remotely. I am able to connect to the particular server remotely using:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = servername; Database = dbname; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

But when I use:

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server")"SQL01\Instancename"

I am getting an error related with SMO dll. Since I am trying to execute remotely, in this machine SQL Server is not installed so SMO.dll is not available. How can I achieve my aim?


texts: Check if the trigger was fired by INSERT or DELETE

texts:
I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet.
How to check if the trigger was fired by an INSERT or DELETE?
Example
CREATE TRIGGER [dbo].[TestTrigger]
On [dbo].[Table1]
AFTER INSERT, DELETE
AS
    --if save
    --do some work

    --else if delete
    --do some work other work

I want to do some work if it's an insert and some other work if it is a delete.
Please provide a code snippet for the same.


texts: Fix transaction log file after database update

texts:
I have a databases that are regularly (monthly) updated. An update includes a lot of new or changed T-SQP procedures, functions, indexes creation, etc.

A database X has transaction log file with the following settings:

unlimited growth
initial size 128 MB
growth with 128 MB

with space used - maximum 5-10% of it (a backup of the transaction log file is made every 15 minutes).

So, the above settings are specific for each database but in common, the log file size is enough and not growing. After UPDATE is made, the size of the transaction log file is increased with 1 or 2 or 3 GB. Since I do not need such big file for regular use of the database, I am shrinking them back like this:

DBCC SHRINKFILE(2,TRUNCATEONLY)
ALTER DATABASE X MODIFY FILE (NAME = N'x_Log', SIZE = 128MB)

I want to execute the code above after each update, but sometimes I need to execute the command above many times in order to reduce the database tlog file (each time I have to wait 15 minutes for the tlog backup to complete).

The database are in FULL recovery mode with Always ON, end the tlog backups are made on the secondary databases.

Could anyone tell if the idea for regular shrink is bad and why sometimes I need to executed the shrink several times?


texts: How to execute some script when database starts up

texts:
I want to execute some T-sql code, when a database starts up.

the objective is to resume a session of mirroring in case the session is "suspended" when the database comes "back to life".

So I've done this code:

begin try
    Declare @state_desc nvarchar(60)
    SELECT @state_desc = mirroring_state_desc
    FROM SYS.database_mirroring
    WHERE database_id = DB_ID('MyDataBase')
    if @state_desc = 'SUSPENDED'
    begin
        ALTER DATABASE [MyDataBase] SET PARTNER RESUME
    end
end try
begin catch
end catch

but how can I make it when the sql server starts up?

I used this with no success:

create procedure dbm_startup_resume
                                as
                                begin
begin try
    Declare @state_desc nvarchar(60)
    WAITFOR DELAY '00:00:10'
    SELECT @state_desc = mirroring_state_desc
    FROM SYS.database_mirroring
    WHERE database_id = DB_ID('MinhaBaseDeDados')
    if @state_desc = 'SUSPENDED'
    begin
      ALTER DATABASE [MinhaBaseDeDados] SET PARTNER RESUME
    end
  end try
  begin catch
  end catch

exec sp_procoption @ProcName = dbm_startup_resume,
    @OptionName = startup, @OptionValue = 'on'

end


texts: Building and Executing a Query Execution Plan Manually in PostgreSQL

texts:
An SQL query goes through multiple stages to be executed. This is a wide topic, but in summary, multiple execution strategies are proposed for each query, they are evaluated, and one is selected for execution, and finally, it is executed.

We can see an SQL's query plan using PostgreSQL's EXPLAIN ANALYZE here https://www.postgresql.org/docs/current/using-explain.html.

I was wondering if it is possible to build the plan manually and then execute it. In other words, as a user, I won't use SQL nor the query optimizer features offered by PostgreSQL. Instead, I will manually construct the query plan and execute that. I would figure that PostgreSQL would have such features for debugging or an extension would exist, but I can't find such functionality.


texts: Efficient pagination for big amount of data

texts:
I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '57133c4aea332cb17b001a28'.

string_id CHARACTER VARYING(255)

CREATE UNIQUE INDEX index_name ON users USING BTREE (string_id);

I've tried

SELECT
   customer_id,
   string_id
FROM users
WHERE string_id > '57133c4aea332cb17b001a28'
ORDER BY string_id
LIMIT 50000

but it has a very poor execution time:

Limit  (cost=0.11..48044.67 rows=50000 width=29)
  ->  Index Scan using index_name on users  (cost=0.11..2799915.87 rows=2913874 width=29)
    Index Cond: ((string_id)::text > '57133c4aea332cb17b001a28'::text)

Limit  (cost=0.11..48237.53 rows=50000 width=29) (actual time=0.561..25740.665 rows=50000 loops=1)
  ->  Index Scan using index_name on users  (cost=0.11..2792079.10 rows=2894101 width=29) (actual time=0.560..25730.852 rows=50000 loops=1)
    Index Cond: ((string_id)::text > '57133c4aea332cb17b001a28'::text)
Planning time: 0.364 ms
Execution time: 25747.091 ms

I'm using PostgreSQL 9.5.

Why is it so hard for DB to get 50,000 records using ordered index and what can I do to improve it?


texts:
I am using MySQL dump to back up my MySQL Databases and I'm having trouble with a couple dumps on one box.

Server Specs
Windows 2008
MySQL 5.1.48-community

I've tried various parameters in the mysqldump command and not much seems to help.
mysqldump --user=%mysqluser% --password=%mysqlpassword% --max_allowed_packet=64M \n
    INSERTDBNAMEHERE INSERTTABLENAMEHERE > %basedir%\export.sql

What is odd, is if I do a dump out of phpmyadmin, it is fine.


texts: Advantage of exposing data model to customers using our product

texts:
We have a product through which we provide a standard definition to customer's asset data. The problem currently lies in importing customer's data from different types of data sources to our data model. I am looking for an option of exposing our data model to customers so that they can populate it accordingly. What are the pros and cons of this approach ?
What are the different ways through which I can achieve this ? Any inputs would be greatly appreciated.


texts: Convert UNION query to JOIN. Is it actually needed for high performance?

texts:
I have the following table structure in MySql.

To get the list of roles for a particular user, I use the following union Query,

SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID
FROM  `T_ROLE` AS r
JOIN T_ROLE_USER AS ru ON r.id = ru.ROLE_ID
WHERE ru.user_id ="100"
UNION SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID
FROM  `T_ROLE` AS r
JOIN T_ROLE_GROUP AS rg ON rg.ROLE_ID = r.id
JOIN T_USER_GROUP AS ug ON ug.GROUP_ID = rg.GROUP_ID
WHERE ug.user_id ="100"
LIMIT 0 , 30

Explain of the above query,

and to get the list of permissions for a user I wrote the following query,

SELECT p.ID,p.TENANT_DOMAIN,p.PROVIDER_ID,p.RESOURCE_NAME,p.ACTION
FROM  `T_PERMISSION` AS p
JOIN T_ROLE_PERMISSION AS rp ON rp.PERMISSION_ID = p.ID
JOIN T_ROLE AS r ON r.ID = rp.ROLE_ID
JOIN T_ROLE_USER AS ru ON ru.ROLE_ID = r.ID
WHERE ru.USER_ID ="100"
UNION
SELECT  p.ID,p.TENANT_DOMAIN,p.PROVIDER_ID,p.RESOURCE_NAME,p.ACTION
FROM  `T_PERMISSION` AS p
JOIN T_ROLE_PERMISSION AS rp ON rp.PERMISSION_ID = p.ID
JOIN T_ROLE AS r ON r.ID = rp.ROLE_ID
JOIN T_ROLE_GROUP AS rg ON rg.ROLE_ID = r.ID
JOIN T_USER_GROUP AS ug ON ug.GROUP_ID = rg.GROUP_ID
WHERE ug.USER_ID ="100"

Explain results of the above query is,

Is it possible to convert the above two queries to use JOIN instead of UNION ? If can why? Will there be any issues on huge data with this query. I see that all the data are indexed properly.


texts: Can I use Foreign Data Wrapper for SQL Server in Windows Server 2012?

texts:
I use PostgreSQL and SQL Server. I want to get SQL Server data in PostgreSQL.

I found PostgreSQL Foreign Data Wrapper. There are installation packs for CentOS and Ubuntu. However, I want to use FDW in Windows Server 2012.

How can I use FDW? Or what is another way to get SQL Server data in PostgreSQL?


texts: Call stored procedure via trigger or SQL Server Agent

texts:
At the moment, I need to synchronize data between two SQL Server databases on separate servers but on the same network. As the schema between the tables that are to be synced differ, I've been developing a solution that utilizes a custom queue to transfer data. This involves triggers for each table that inserts the new/updated fields into a transfer queue table, to which a periodically run stored procedure via SQL Server Agent checks for entries, acts accordingly, then deletes those entries.

My question is whether or not it'd be more efficient to remove the transfer queue table and instead evoke the stored procedure via the trigger and transfer the data that way. Would the trigger simply call the stored procedure and complete or would it wait for the stored procedure to complete before exiting itself?


texts: Delete and Recreate Indexes in MySQL to reduce table size for copying the db to a new server?

texts:
I have to move a given database of 100gb from server a to server b and later this year to server c with a copy for testing to server d.

So all servers are Unix (mostly Debian) with MySQL.
The databases are MyISAM.

My biggest table, which holds 99% off all data, is 47gb of data (MID) with additional 50gb of indexes (MIY).

My idea is to:

1) Tar the database files
2) copy to new server
3) un-tar it
4) copy it to new mysql folder

My question:

Can I delete the index-file and recreate the indexes on the new server to reduce the copy and tar-time by 50%?


texts: Why Isn't the SQL Server Agent Service Set to Automatic Startup by Default?

texts: After a default install of SQL Server 2008 R2, the SQL Server Agent process seems to be set to manual startup. What is the reason for this? Is setting to automatic a bad idea or pointless for some reason?


texts: If the SIDs for ##certificate-based## SQL Server Logins don't match across replicas in an Availability Group, will this be a problem on failover?

texts:
I am reading mixed messages about server level object syncing between replicas in an Availability Group.

As I understand it, server-level objects must be kept in sync (aside: then why isn't it part of AG by default?). For example, if a Login (identified by SID) does not exist on a Secondary replica (regardless of username and password) and the system fails over to that replica, then the corresponding principal will fail to authenticate.

All well and good - keeping these objects in sync is a task to be done. But what about certificate-based Logins? I read these are for internal system use only, so I would expect the DB engine to maintain SIDs across replicas if that's required. I have a case with three nodes; the third being read-only. I see the Primary's SIDs for logins such as ##MS_SQLAuthenticatorCertificate## do not exist on either of the other nodes. However on those other nodes, there exists a login with the same name and a different SID, but that SID is the same between those two nodes, viz:

SQL01 (Primary) SID = ABC
SQL02 (Secondary) SID = DEF
SQL03 (Read-only) SID = DEF

Is this of concern? Will a failover work despite these Logins having mismatching SIDs between some replicas?


texts: mysql custom query logging for inter-software data exchange

texts:
Some 2-3 years I had written some triggers (one per table) in SQL server allowing me to log queries in our main software.
ALTER TRIGGER [dbo].[SUIVI_REQUETE_CAT_CATBRUT] ON [dbo].[CAT_CATBRUT] AFTER INSERT, DELETE, UPDATE

AS
DECLARE
  @date as nvarchar(10),
  @heure as nvarchar(15),
  @nomUtilisateur as nvarchar(50),
  @poste as nvarchar(50),
  @requete as nvarchar(MAX)
BEGIN
  set @requete = 'DBCC INPUTBUFFER(' + str(@@SPID) + ')'

  CREATE TABLE temporaire (EventType nvarchar(30), Parameters int, EventInfo nvarchar(MAX))
  insert into temporaire exec(@requete)

  set @requete = (SELECT EventInfo FROM temporaire)
  drop table temporaire

  set @poste = (SELECT HOST_NAME() AS HostName)
  set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)
  set @date =(SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD])
  set @heure =(select CONVERT(VARCHAR,getdate(),108))

  INSERT INTO INF_LOG_REQUETE (REQUETE, POSTE, NOM_UTILISATEUR, [DATE], HEURE) VALUES (LEFT(@requete, 4000), @poste, @nomUtilisateur, @date, @heure)
End

I'm now at the head of a new web division and one of our goal is allow my web-software to share data with our main software. I would like to save some key rows in the tables (customer, vehicle, employee, calendar, etc.) we want to share between both softwares.
I have three questions regarding my problem:

I would like to log the query only for a specific database user (we do not want to get stuck in an infinite loop of sending back and forth the same modification). In SQL server I used that to detect the user:
set @nomUtilisateur = (SELECT SUSER_NAME() LoggedInUser)

Is it possible to detect the user that executed the query in MySQL?

In SQL server, I was able to use the same trigger on insert, update and delete actions.

Is it possible to do the same in MySQL or do I have to write three different trigger from every table?

If the same trigger can be used for multiple action (insert, update, delete), is it possible to detect which action (insert, update, delete) launched the trigger?

Additional note: I want to record only specific fields (containing the key data to share) in the table. I really want to write a trigger for every table so a PHP function can automatically parse the field with data and build an XML file. By example, I want a field in my log table containing:
{table}:[vehicle], {action}:[insert], {field}:[make], {value}:[acura], {field}:[model], {value}:[S7], ...

And produce XML like the following:
<LIST>
    <QUERY>

vehicle

        <ACTION>insert<ACTION>
        <FIELD>make</FIELD>
        <VALUE>acura</VALUE>
        <FIELD>model</FIELD>
        <VALUE>S7</VALUE>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
    <QUERY>
        ...
    </QUERY>
</LIST>

Thank you


texts: Why is log file divided into virtual log files in SQL Server?

texts:
In Books Online it is mentioned that the transaction log file is divided into virtual log files internally with no fixed number of count. It says that virtual files are useful while file growth operation but I didn't get it completely. I am curious to know why exactly it needs virtual structure?

Thanks for your time


texts: MySQL Error 1062 after reboot node in multi master replication

texts:
I setup 2 MySQL Community Server (8.0.20) with a replication master to master. Today one of the nodes went down, and when it went up, the replication failed. I tried to just ignore a registry with SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;, but the errors keep coming in.
This is the status log of the particular node that failed:
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: database_1
                  Master_User: replicador
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000122
          Read_Master_Log_Pos: 217195469
               Relay_Log_File: 33afd376b907-relay-bin.000105
                Relay_Log_Pos: 2829
        Relay_Master_Log_File: mysql-bin.000122
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1062
                   Last_Error: Could not execute Write_rows event on table gc57125800.ate_logs; Duplicate entry '49328847' for key 'ate_logs.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000122, end_log_pos 188792316
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 188791943
              Relay_Log_Space: 28407134
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1062
               Last_SQL_Error: Could not execute Write_rows event on table gc57125800.ate_logs; Duplicate entry '49328847' for key 'ate_logs.PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000122, end_log_pos 188792316
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: bef45e1b-99d6-11ea-a355-3e2547e4f083
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 201029 17:59:51
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set (0.00 sec)

Not sure what I should do. Should I dump the another master and do it all over again? Do I need to do this every time a server fails?


texts:
I have added and removed a few indexes from my user table and, while testing, didn't see the performance gains I had hoped. As a result, I removed the indexes and tried a simple query to see what my baseline speed is.

With so few records in the table, it seems like this "baseline speed" should be way faster, but I'm not experienced in MySQL performance tuning, so I'm not sure.

My user table (InnoDb) has 4344 records.

If I run select firstName from user; the query takes roughly 0.5 seconds to complete.

Thanks in advance for any insight:

+-----------------------+------------------+------------+---------------+
|         Field         |       Type       | Allow Null | Default Value |
+-----------------------+------------------+------------+---------------+
| userId                | int(10) UNSIGNED | No         |               |
| email                 | varchar(150)     | No         |               |
| password              | varchar(50)      | Yes        |               |
| passwordSalt          | varchar(50)      | Yes        |               |
| firstName             | varchar(50)      | Yes        |               |
| lastName              | varchar(50)      | Yes        |               |
| country               | varchar(50)      | Yes        |               |
| phone1                | varchar(10)      | Yes        |               |
| phone2                | varchar(10)      | Yes        |               |
+-----------------------+------------------+------------+---------------+


texts: Why does optimizer estimate 1 row for this plan?

texts:
Here's my plan I'm having trouble with: https://www.brentozar.com/pastetheplan/?id=r168tltbe

I'm betting this is something easy I'm not seeing. I have a query that is running very slowly because instead of the optimizer pulling in the 16 rows from the 'child' table, it says "pull all the rows from the child table between 26550557 and 28078855".  I rebuilt the statistics on the index, which worked in one of my environments, but not another.

RANGE_HI_KEY    RANGE_ROWS  EQ_ROWS DISTINCT_RANGE_ROWS AVG_RANGE_ROWS
26461290        32767       1       16043               2.042449
26652512        65544       1       31523               2.079244
26868906        65535       1       31715               2.066372
26950626        32765       4       15484               2.116055
27097668        32762       6       16202               2.022096
27220249        32810       4       15978               2.053448
27301782        32770       1       15161               2.161467
27572159        32782       4       15406               2.127872
27758980        32793       4       16564               1.979775
27864880        32781       4       16356               2.004219
27922320        32771       4       16075               2.038631
28092976        32789       5       16462               1.991799

You can see from my DBCC SHOW_STATISTICS partial results above, there is more than 1 row in that range.

EDIT: Posted correct range of stats (per Martin) and a non-commented query.  Thanks everyone for your responses!

EDIT2: Here's the stats_stream.  Also, even though is says sorting is 44% of the effort, removing that sort speeds it up by a negligible amount.  Because stats_stream is so large, here's a link to it:
https://www.dropbox.com/s/fq6gu1jvb1q7tg8/Stats_Stream.csv?dl=0

EDIT3: DDL Script - https://www.dropbox.com/s/fq6gu1jvb1q7tg8/Stats_Stream.csv?dl=0


texts: MySQL Handler_read_rnd_next == tablescans without indexes?

texts:
I am in the process of tuning our Percona 5.5 server, and have been using percona's tools to plot our database's performance.
In the MySQL Handlers Plot, i have been noticing alot of "Handler_read_rnd_next" calls.

After doing some digging in the mysql dev docu, this handler supposedly indicates: "this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have"

I then started analyzing our queries for non-indexed calls with the help of tcpdump and mk-query-digest (awesome tool btw) with the options:

--filter '($event->{No_index_used} eq "Yes" || $event->{No_good_index_used} eq "Yes")'

but am drawing a blank:
all the queries which are being listed as not using an index are ones which have a "derived" select type in them somewhere and all finish within a few milliseconds.

Hence my question: if the derived select types are the only ones without indexes, where are my Handler_read_rnd_next instances coming from? I'd appreciate any help i can get on trying to find out what the issue is.

Thanks!


texts: Execution plan of on-premises sql server and Azure sql server is not the same

texts:
I've been having some problems with moving databases from on-premises (2014 sql server) to Azure sql server. It's using the exact same database structure and data, but for some reason the query uses a different execution plan and takes much longer.
On the on-premises server it returns almost immediately and on the Azure server it takes about 6 seconds when using standard with 50 DTU's and when using e-DTU's it takes about 3 seconds, which is better, but still much longer.
Any ideas why the execution plan is different (even the rows are returned in another order), or how I can make it use the same query plan?
The query (generated by entity framework) is:
SELECT  DISTINCT
    [Extent4].[Id] AS [Id],
    [Extent4].[Name] AS [Name],
    [Extent4].[Background] AS [Background],
    [Extent4].[Code] AS [Code],
    [Extent4].[DeactivatedOn] AS [DeactivatedOn]
FROM    [dbo].[Orders] AS [Extent1]
INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[User_Id] = [Extent2].[Id]
INNER JOIN [dbo].[UserRegistrationGroups] AS [Extent3] ON [Extent2].[UserRegistration_Id] = [Extent3].[UserRegistration_Id]
INNER JOIN [dbo].[UserGroups] AS [Extent4] ON [Extent3].[Group_Id] = [Extent4].[Id]

execution plan of the on-premises server:

https://www.brentozar.com/pastetheplan/?id=S1cfDgpzD
Execution plan of the azure sql server:

https://www.brentozar.com/pastetheplan/?id=HyyshlTMw
The waits from the Azure plan are:
<Wait WaitType="SOS_SCHEDULER_YIELD" WaitTimeMs="597" WaitCount="686" />
<Wait WaitType="RESOURCE_GOVERNOR_IDLE" WaitTimeMs="570" WaitCount="77" />

I've already tried to set the compatibility level the same (100).
My customer pays 10 euro per month for his current service provider, which provides a web and sql server in the cloud as well.
I'm trying to convince him to go to azure, but the azure sql, which costs 60 euro per month for an s2 is a lot slower in most queries, which makes it difficult for me to convince him. I would have expected about the same performance.
This query was just an example.


texts: How can I group the null results in this FULL OUTER JOIN with non-null responses?

texts:
I am looking at the overlap and non-overlap (unique values) of users-ids from two different select statements using a full join. The main differentiation being that one table will have a deal_id = 0 and the other will have any deal_id greater than or equal to one.
I am joining the select statements on exchange_id, pub_id, and user_id but not on deal_id.

The field incremental value is trying to calculate users who are present in the pool deal_id >= 1 and not present in the pool of deal_id = 0 (a main reason for the full join).

Here is a simplification of the query I've typed up, it's in two select statements :

SET
hive.auto.convert.join = TRUE
;

SELECT
    First.deal_id
    ,COALESCE( First.exchange_id, Second.exchange_id ) as exchange_id
    ,COALESCE( First.pub_id, Second.pub_id ) as pub_id
    ,COUNT (DISTINCT(case when Second.user_id is null then First.user_id else null END)) AS Incremental
    ,SUM (First.imps) AS First_imps
    ,SUM (Second.imps) AS Second_imps
    FROM
        (
            SELECT
                a.deal_id
                ,a.exchange_id
                ,a.pub_id
                ,a.user_id
                ,1 AS imps
            FROM
                logs a
            WHERE
                a.deal_id >= 1
            AND a.event_type = 'TRUE'
        ) First
        FULL JOIN (
            SELECT
                a.exchange_id
                ,a.pub_id
                ,a.user_id
                ,1 AS imps
            FROM
                logs a
            WHERE
            a.deal_id = 0
            AND a.event_type = 'TRUE'
        ) Second
        ON (
            First.exchange_id = Second.exchange_id
            AND First.pub_id = Second.pub_id
            AND First.user_id = Second.user_id
        )
        GROUP BY
        First.deal_id
        ,COALESCE( First.exchange_id, Second.exchange_id )
        ,COALESCE( First.pub_id, Second.pub_id )
;

Here are the results I am seeing:

DEAL_ID    EXCHANGE_ID    PUB_ID    INCREMENTAL    FIRST_IMPS    SECOND_IMPS
/N         4              1780      0              0             15
/N         4              1560      0              0             32
3389       4              1780      2              7             6
1534       4              1560      4              9             8

And here is what I would like to see:

DEAL_ID    EXCHANGE_ID    PUB_ID    INCREMENTAL    FIRST_IMPS    SECOND_IMPS
3389       4              1780      2              7             21
1534       4              1560      4              9             40

Where the results with a null deal id match up to the results with a non-null deal id based on exchange_id and pub_id.

What can I do?

Similar to this problem but this solution isn't working for this problem.

Note: I've posted this question on stackoverflow here but thought I might try dba instead

Edit: Here is a sqlfiddle that replicates the problem, note that it's using PostgreSQL while I'm using hql


texts: Display line number where error occurred in DB2 stored procedure

texts:
I have added an EXIT handler in my procedure that captures the SQLSTATE and SQLCODE, and even found a way to get the procedure name, but I also need to know where the error occurred.  Suggestions greatly appreciated.
declare EXIT handler for SQLEXCEPTION
begin
    select sysibm.routine_specific_name, SQLSTATE, SQLCODE
    into v_sp_name, v_sqlstate, v_sqlcode
    from sysibm.sysdummy1;

    call dbms_output.put_line('Error in '||v_sp_name ' ['||v_sqlstate, v_sqlcode||']');
end;


texts: "Use Database" command inside a stored procedure

texts:
I would like to dynamically use a database inside a stored procedure but the use database_name  command is not supported inside procedures.

What alternatives are there?


texts: How to handle "or" possibilities in MongoDB

texts:
I'm new to MongoDB and by no means an expert in DB design in general. This feels like a really basic question that's probably been answered a million times, but I'm having a surprisingly hard time finding an answer for it: is there a good way to easily handle either/or choices in the DB, or structure it in a way that makes it easy to deal with in code?

Let's say I'm building a cocktail database in MongoDB. I'm adding the entry for a Manhattan.

2 oz. Bourbon or Rye Whiskey (this is the issue)
1 oz. Sweet Vermouth
Dash Aromatic Bitters
Garnish Maraschino Cherry

So I might do a cocktails collection with an entry like:

{
    "_id" : "1234",
    "name" : "Manhattan",
    "ingredients" : [
        {
            "measure" : "2 oz.",
            "ingredient" : "Bourbon Whiskey"
        },
        {
            "measure" : "1 oz.",
            "ingredient" : "Sweet Vermouth"
        },
        {
            "measure" : "Dash",
            "ingredient" : "Aromatic Bitters"
        },
        {
            "measure" : "Garnish",
            "ingredient" : "Maraschino Cherry"
        }
    ]
}

Which is fine, but how do I get the rye in there? I don't think I'd want to do "ingredient" : "Bourbon or Rye Whiskey", would I? Is it better for the purpose of searching, later, to have them separated out? What's the best practice, here?

Also, an even more tricky drink would be something like a Martini, where I would actually use a different garnish for vodka than I would for gin, so I'd want to give the user the option to choose the base liquor they're working with and then give the proper garnish accordingly.

Any suggestions? Thanks!


texts: How do I truncate performance_schema tables on a read replica?

texts:
On occasion, you have to truncate events_statements_summary_by_digest, when it's hit the row limit. On a write master, this is easy, but on a read replica, there doesn't seem to be a way to do it. The read-only enforcement extends to the performance_schema tables. Since the data are created locally, it seems to me that there'd be a way that I could exempt those tables (or even the entire performance_schema DB) from the read-only enforcement?

I have already set the performance_schema_events_statements_history_long_size to 10000, which is getting pretty large. I could set it larger (well, if it weren't read-only), but the size of that table affects overall system performance, and I'd rather not just crank the limit to something obscene - especially when so many of the entries in the history haven't been used in a mont or two. It's better to toss out the garbage.


texts:
I'm trying to execute ndb_desc from API client. The config and parameters are as below:

my.cnf in mysqld:

[mysql_cluster]
ndb-connectstring=192.168.1.1,192.168.1.2

server-id=5
log-bin=mysql-bin

config.ini in mgmd: (part of it)

-- ...
[mysqld]
hostname=192.168.1.5
--- ...

Situation: there are 2x mgmd, 2x data-node, and 2x mysqld.

Parameters tried to call ndb_desc: # ndb_desc -c 192.168.1.1 --ndb-nodeid=7 -d db_name -t tb_name

Above response with error: ndb_desc: [ERROR] unknown variable 'server-id=5

On changing my.cnf, removing these two lines of server-id and log-bin, then the error response after calling ndb_desc will be as follow:

Unable to connect to management server.
NDBT_ProgramExit: 1 - Failed

There is no firewall, ping to the management is running fine.

So the question: how to exec ndb_desc (with correct parameters)? what is the correct my.cnf/config.ini for setting up the node-id?

Some links i have tried to check before posting this:
mysql-cluster-work-well-but-ndb-desc-doesnt-work, mysql:ndb_desc, mysql:connection-string, grokbase, and several hours of googling around.

update1: changed the config.ini on mgmd for emtpy mysqld section, ndb_mgm -e show output:

[mysqld(API)]   3 node(s)
id=5    @192.168.1.5  (mysql-5.7.23 ndb-7.6.7)
id=6    @192.168.1.6  (mysql-5.7.23 ndb-7.6.7)
id=7 (not connected, accepting connect from any host)

ndb_desc still has the same response: Unable to connect to management server.

update2: from mgmd node, saw this log: WARNING  -- Failed to allocate nodeid for API at 192.168.1.5. Returned error: 'Id 7 already allocated by another node.

update3: changed config.ini, added [api] nodeid=7 host=192.168.1.5. log still the same: WARNING  -- Failed to allocate nodeid for API at 192.168.11.51. Returned error: 'No free node id found for mysqld(API).


texts: postgresql pg_dumpall restore one database

texts:
My environment:

# cat /etc/redhat-release
Red Hat Enterprise Linux Server release 7.3 (Maipo)
# rpm -q postgresql
postgresql-9.2.18-1.el7.x86_64
#

I used following PostgreSQL: Documentation: 9.6: pg_dumpall to take dump all databases

  $ pg_dumpall > db.out

from what I understand, following will restore all databases:

  $ psql -f db.out postgres

my goal is, I need to restore just a one (single) database from that entire dump, how does one accomplish this task?


texts: PostgreSQL exits the shell when importing large data

texts:
I'm having this totally weird scenario: We have an archiving server based on PostgreSQL inside a Scientific Linux virtual server. I want to migrate the data (20+ GB) to a physical Scientific Linux machine, so I used

\copy (select * from table) to '/path/to/file

This ran successfully, and the data is in the output file.

On the physical machine I try to import the data using

\copy table from '/path/to/file'

When I try this command on a terminal emulator, it exits after a few minutes.  Also, tried it on the terminal itself after login but after a few minutes it exits, too.  In both cases there is no data imported.

Could it be that the server is somehow overloaded?  Can we overcome this and import the entire file?

Edit: I increased the RAM to 3 GB, the crash took a bit longer to come.

Edit 2: There is a log file under /var/lib/pgsql/data/pg_log, this log file has these lines as the last few lines of it:

Context: COPY sample, line 77324116
Statement: COPY sample from STDIN
ERROR: Unexpected EOF on client connection
Context: COPY sample, line 77324116
Statement: COPY sample from STDIN
Log: Could not send data to client: Broken pipe
Statement: COPY sample from STDIN
ERROR: Unexpected EOF on client connection

Any help is really appreciated!


texts: How to install cstore_fdw inside RDS postgres 9.6

texts: cstore_fdw  is foreign data wrapper for columnar storage for postgresql. I want to install it inside RDS postgre 9.6 instance. Is it possible to install inside RDS instance? Thanks


texts: Why isn't "DROP OWNED BY" freeing up my ability to drop a role?

texts:
I'm using Postgres 9.6 on Mac.  I would like to drop a user/role.  I logged in as my super user (postgres) and tried this
postgres=# DROP ROLE chicommons;
ERROR:  role "chicommons" cannot be dropped because some objects depend on it
DETAIL:  41 objects in database directory_data

so following some docs I then tried this
postgres=# DROP OWNED BY chicommons;
DROP OWNED

but the above doesn't seem to do anything since dropping the role again results in the same error ...
postgres=# DROP ROLE chicommons;
ERROR:  role "chicommons" cannot be dropped because some objects depend on it
DETAIL:  41 objects in database directory_data

How can I drop my role from the system when logged in as a super user?


texts: Unable to connect Oracle 11g server remotely after recovering Database on a new host?

texts:
I have recovered my Oracle database using RMAN after following this tutorial

http://www.isaca.org/Groups/Professional-English/oracle-database/GroupDocuments/CKE-Database_Disaster_Recovery_using_only_RMAN_Backups.pdf

after I have recovered the database I have created listener ORCL using NETCA ....when I run SQL developer it can connect to the database on port 1521 when I'm on the server ...

  but I'm not able to connect to the database remotely !!!

my windows firewall is OFF !!!it's too bad the one who write this tutorial didn't talk about the listener recovery .

I can ping my listener using the command tnsping orcl...the lister is already is started not OFF ...I really have no clue what to do . when I try to connect remotely using SQL developer I get the error "network adapter can't establish connection" ...while my SQL developer can connect to other database normally .

C:\Windows\System32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 16-SEP-2017
20:40
:42

 Copyright (c) 1991, 2010, Oracle.  All rights reserved.

 Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 -
Produ
ction
Start Date                16-SEP-2017 20:35:56
Uptime                    0 days 0 hr. 4 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\MOD\product\11.2.0\dbhome_1\network\admin\liste
ner.ora
Listener Log File         c:\app\mod\diag\tnslsnr\osama-PC\listener\alert\log.xm
l
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

 C:\Windows\System32>

tnsping orcl result

C:\Windows\system32>tnsping orcl

TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-
SEP-2
017 17:43:32

Copyright (c) 1997, 2010, Oracle.  All rights reserved.

Used parameter files:
C:\app\MOD\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)
(HOST = osama-PC)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL)))
OK (10 msec)

C:\Windows\system32>

lsnrctl services  result

  C:\Windows\system32>lsnrctl services

 LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 17-SEP-2017
 17:46
 :10

  Copyright (c) 1991, 2010, Oracle.  All rights reserved.

  Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
  Services Summary...
  Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
   Handler(s):
  "DEDICATED" established:0 refused:0 state:ready
     LOCAL SERVER
   Service "orclXDB" has 1 instance(s).
   Instance "orcl", status READY, has 1 handler(s) for this service...
    Handler(s):
     "D000" established:0 refused:0 current:0 max:1022 state:ready
     DISPATCHER <machine: OSAMA-PC, pid: 2396>
     (ADDRESS=(PROTOCOL=tcp)(HOST=osama-PC)(PORT=49415))
The command completed successfully

C:\Windows\system32>


texts:
The binlog position isn't continuous, so how mysql binlog position generate?

There no document about this.


texts: postgres: list role grants for all users

texts:
I have an audit requirement to query all roles granted to users; listed by user.
For example:

Username   Roles
user1      role1_rw, role2_ro, rol3_rw
user2      role2_rw

I cannnot use psql meta-commands as this result set will be merged with some other queries to do analysis of the grants.
The roles have been created to manage ro/rw access to various application schemas.
In oracle, this is quite straight forward; I dont see a single view to return this in Postgres. The querying user is a superuser.

Does anyone have a query to return this?
Postgres 9.4 on ubuntu


texts:
We use Log Shipping to allow Business Analysis users to query data. I would like to email the Log Shipping Status report to users to see last time data is refreshed.

When I Right Click on Server--> Standard reports --> It says members of the SysAdmin Report can only view this.

How can I give access, or email Report Status to Users of the report? I prefer to keep the same color html format if possible.


texts:
Up to now we used check_postgres.pl to monitor our database.

We used this to check the count of the locks:

https://bucardo.org/check_postgres/check_postgres.pl.html#locks

We often see more than 150 locks.

I was told this in a different question: How to Debug check_postgresql

  A single DML statement can easily obtain millions of locks. There is no
  relation to the number of statements and the number of locks. But why are
  150 locks a bad thing? Unless they are held for a long time (minutes,
  hours) I don't see any reason to check this.

How can I check for the lock count and ignore all locks which are only N seconds old?


texts: How can one create a directory dynamically in PL/pgSQL?

texts:
I want to create a directory in PL/pgsql:
CREATE OR REPLACE FUNCTION getNextId() RETURNS VARCHAR(200) AS
$BODY$

DECLARE
    mfId integer;
    rootDir varchar(50) := 'mfdata/';
    createDir text := 'mkdir --mode=777 -p mfdata';
    dir varchar(100);
BEGIN
    INSERT INTO copy.history(time) values(now()) RETURNING id INTO mfId;
    EXECUTE('COPY (SELECT 1) TO PROGRAM ' || quote_literal('mkdir --mode=777 -p mfdata '));
    RETURN 'mfdata' || mfId;
END;
$BODY$
LANGUAGE plpgsql;

but occur an error as follow:

Where is the problem? How can I create a directory dynamically in PL/pgSQL?


texts: How to connect to another db using stored procedures?

texts:
I need to get some data from an external db and make some calculations with it in another db, is it possible to connect to an external db from a stored procedure? Thanks guys.

PS.

Im using Oracle and both databases are in the same server.


texts:
I posted this on StackOverflow and it was suggested this query was better suited here.

I'm trying to encourage the use and monitoring of autovacuum in some PostgreSQL 8.3 databases.

One objection I hit often is that people don't "trust" autovacuum or there are bugs in autovacuum in 8.3 which mean that it's ignored in preference to scheduling vacuuming. Mostly our tables are small and this approach appears to work. However, with our larger (& also heavily updated tables) this really doesn't work (dead tuple counts increase, exceed max_fsm_pages, and the tables don't get cleaned up etc etc).

I'm just wondering if anyone has a reference for autovacuum in 8.3 being buggy or not working. My own experience has shown that autovac works fine and, where necessary, adding entries to the pg_autovacuum table does the trick.

I'd like to understand the problem with autovacuum (if one exists).


texts: Adding hstore entries to an uninitialized (NULL) column

texts:
I got bitten by this "feature" recently.

If your hstore column is uninitialized and you start adding entries to it, they are all silently swallowed without error.

Is this expected?

 create table test_hstore(id int, map hstore);
 insert into test_hstore(id,map) values(0, '');
 INSERT 0 1

 select * from test_hstore ;
  id | map
 ----+-----
   0 |

 update test_hstore set map = map || hstore('key1', 'value1') where id = 0;
 UPDATE 1

 select * from test_hstore;
  id |       map
 ----+------------------
   0 | "key1"=>"value1"

 update test_hstore set map = null where id = 0;
 UPDATE 1

 select * from test_hstore;
  id |  map
 ----+--------
   0 | (null)

 update test_hstore set map = map || hstore('key1', 'value1') where id = 0;
 UPDATE 1

 select * from test_hstore;
  id |  map
 ----+--------
   0 | (null)

If I cannot have a not null constraint on the column, can I safeguard myself by doing something like that(this doesn't actually work):

UPDATE test_hstore SET map = (IF map IS NULL
                                THEN  '' || hstore('key1', 'value1')
                                ELSE map || hstore('key1', 'value1'))
WHERE id = 0;


texts:
I'm doing some basic statistical calculations on the results of a survey. A survey consists of several sections and each section has some questions in it. The answers to each question in each section are stored.

The ResultsTable table has Result column which contains computed result for the entire survey as well as overall results for each section, separated by double pipe. For example,

Moderate||Low||High||Low||Low||Low||NA||NA

Above, Moderate is overall survey result, Low is result for section 1, High is for section 2 and so on.

(Then I would be computing some statistics, such as % of people who have High as their result for a specific section. Or, of those who have High for a specific section the % who answered Yes or No to a specific question in that section, etc.)

The problem I'm facing is that I need to split string, which I think could cause performance problems. What I have right now is as follows.

SELECT COUNT(*) [Personal_Health_High_Risk] FROM
    (
        SELECT rt.*
        FROM ResultsTable rt
        WHERE
            rt.CampaignID IN (SELECT Value FROM ufn_ParseDelimitedString(@campaignIDs, ',')) AND
            rt.SectionID = @sectionID
    ) sq
WHERE
    -- where 3rd section result is High
    (SELECT Value FROM ufn_ParseDelimitedString(sq.Result, '||') WHERE RowNumber = 4 ) LIKE '%High%'

The ufn_ParseDelimitedString function splits a string using given delimiter and returns resulting tokens in a table. For example,

-- ufn_ParseDelimitedString output:
--  [RowNumber] [Value]
--  1           Moderate
--  2           Low
--  3           High
--  ...

So, is it better to continue with what I'm doing - basically split the string on each select? OR I was thinking taking that inner SELECT in code above and saving output into temporary table and actually expanding the concatenated result string into separate columns. This way I could reuse temporary table for various other calculations. What are your thoughts on this?


texts: Simplest way to constrain an SSIS Execute Process Task

texts:
We have some complex SSIS packages, currently in SSIS 2005, that use Execute Process Task in order to call out to do some business logic.

Some of these called programs are not as stable as one could wish for.

What is the simplest way, therefore, to constrain the execution in time, or other metric?

For example, nine times out of ten, the program executes (correctly) in ten seconds.  If it runs more than five minutes, kill it and presume it returned an error.

Please presume that changing the programs is not an option.


texts: Database Reader Role with the ability to Create, View and execute stored procedure's and view's in sql server

texts: I will need to allow report writers  on the Subscription database to be able to view the table, create alter and delete stored procedure and views. Since it is a transactional replication they cannot alter or delete table structure. Is this possible if so how to achieve any ideas ?


texts: Computed Column Bit based on other Bit Columns

texts:
What would be the formula to compute a bit column from other bit columns?

For example:

CREATE TABLE #Employee
(
[empNumb]       [INT] identity(1,1) PRIMARY KEY,
[IsOk]          [BIT] NOT NULL, -- This is computed from other three columns.
[HasValidHours] [BIT] NOT NULL,
[NoDemerits]    [BIT] NOT NULL,
[NoAccidents]   [BIT] NOT NULL
);

ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_HasValidHours]
  DEFAULT ((0)) FOR [HasValidHours];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoDemerits]
  DEFAULT ((0)) FOR [NoDemerits];
ALTER TABLE #Employee ADD CONSTRAINT [DF_Employee_NoAccidents]
  DEFAULT ((0)) FOR [NoAccidents];

IsOk would be computed from the and-ing of HasValidHours, NoDemerits and NoAccidents.


texts: Does DB2 LUW v10.5 Support Windows Authentication and How is it Set?

texts: We have a DB2 LUW v10.5 FP1 Express Edition. Can someone please confirm that this database supports Windows Authentication over the network (and if so, kindly provide some steps to do it) or does it only support Windows Authentication on the Server host?


texts: Loop through all tables and detect changed rows using checksum or hashbytes

texts:
All the tables have an extra row called checksum which is stored by doing INSERT INTO example_table VALUES ('a','b', BINARY_CHECKSUM('a','b')).

Suppose someone change a row value, obviously the checksum will change, and when I run Select CHECKSUM_AGG(*) FROM example_table, that value will differ from the stored checksum.
How can I detect, looping through all tables, what row or table was changed?
Can I do this with a Stored Procedure? This is an SQL SERVER database.


texts: Does Postgres have a function for converting text into ASCII escaped or HTML escaped text?

texts:
Is there a builtin Postgres function that converts multi-line text into ASCII escaped or HTML escaped form and vice versa?

I need to store multi-line text as name=value pairs, basically line .ini files, where Value is the escaped text which should fit on a single line, but I prefer a format that doesn't use numeric codes to express the non-printing characters if such a format is available.

The multi-line text is not that short and could be upto 65K in length.

Postgres does something similar when it converts multi-line text as single lines in plain format backups without using SQL inserts.
It is the type of text passed to the COPY tablename (column1, column2, columnxx)from stdin when the plain text backup is viewed.

I have tried quote_literal but it didn't come out the way I want.

Is the function that does the conversion standalone?


texts: Cannot run my template in .dbt extension in Oracle 12c DBCA

texts:
The following is my template file testing.dbt. I kept it under C:\app\ORCLUSER\product\12.1.0\dbhome_1\assistants\dbca\templates. I'm trying to create it from Oracle 12c (Enterprise) DBCA. When I select create database and choose it. It says, the template file is in invalid template file and it is not in the expected mode. Please help me with this.

This is my testing.dbt file


texts:
I'm fairly new to SQL. I just wanted to create a database from scratch to learn.

So I have a table called Addresses and I want to create two tables dependent on it that are Country and State, Im not sure what script to use for that.

Thanks in advance


texts: What is the disadvantage of maintaining both row and column table storage?

texts: I just watched Oracle's In Memory database release. I can understand that removing a lot OLAP indexes accelerated the database a bit. But does that really mean maintaining 2 table storages doesn't have extra cost? Like why would insert still be faster when 2 storages need to be maintained simultaneously?


texts: Issues on defining a PK on a nvarchar column and indexing the FK referring this PK

texts:
Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255).

The value exists in this columns are quite short strings, such as 'A', 'B' and 'ABCDEF'.

These reference tables has been referenced by a operational table that have got many records, e.g: People.

To enhance the performance issue on the operational table (e.g.: People), two indexed have been defined on these FKs which each is a nvarchar(255) column.

CREATE TABLE RefCode1(
    [Code] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_RefCode1] PRIMARY KEY CLUSTERED
(
    [Code] ASC
)
)

CREATE TABLE RefCode2(
    [Code] [nvarchar](255) NOT NULL,
    [Description] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_RefCode1] PRIMARY KEY CLUSTERED
(
    [Code] ASC
)
)

CREATE TABLE People(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    ...
    [RefCode1] [nvarchar](255) NOT NULL,
    [RefCode2] [nvarchar](255) NOT NULL,
    ...
)

My question would be:

Does defining an index on a nvarchar column with this length causes any issues?
If the developer does not join the main table to the reference tables in the select queries written in the application, the performance issues will be avoided? (That's the justification he made when I raised a question about this design).

We use SQL Server 2008 r2.

Your thought will be appreciated.


texts: Select Type UNION RESULT doesn't appear in the output of Explain statement

texts:
EXPLAIN
SELECT actor_id,
(SELECT 1 FROM sakila.film_actor WHERE film_actor.actor_id =
der_1.actor_id LIMIT 1)
FROM (
SELECT actor_id
FROM sakila.actor LIMIT 5
) AS der_1
UNION ALL
SELECT film_id,
(SELECT @var1 FROM sakila.rental LIMIT 1)
FROM (
SELECT film_id,
(SELECT 1 FROM sakila.store LIMIT 1)
FROM sakila.film LIMIT 5
) AS der_2;

I was reading about using EXPLAIN, and the explain result for this query had a row with select_type as UNION_RESULT.
However, I don't get this row. Can someone help me understand this behavior?

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY <derived3>  NULL    ALL NULL    NULL    NULL    NULL    5   100.00  NULL
3   DERIVED actor   NULL    index   NULL    idx_actor_last_name 182 NULL    200 100.00  Using index
2   DEPENDENT SUBQUERY  film_actor  NULL    ref PRIMARY PRIMARY 2   der_1.actor_id  1   100.00  Using index
4   UNION   <derived6>  NULL    ALL NULL    NULL    NULL    NULL    5   100.00  NULL
6   DERIVED film    NULL    index   NULL    idx_fk_language_id  1   NULL    881 100.00  Using index
7   SUBQUERY    store   NULL    index   NULL    idx_unique_manager  1   NULL    2   100.00  Using index
5   UNCACHEABLE SUBQUERY    rental  NULL    index   NULL    idx_fk_staff_id 1   NULL    15005   100.00  Using index


texts: Identify columns that allow NULL but don't contain NULL

texts:
I'm working with a legacy database that never had any FOREIGN KEYs defined and not very many NOT NULL constraints, even where they are obviously applicable. I've been manually adding these in where the migrations are cheap and the applicability is obvious.

However, I'd really like a systematic way to identify some appropriate targets instead of stumbling around.

I'm wondering if it's possible to write a query (as slow as it might be) to run on a snapshot of the database that would identify every table.column which is not NOT NULL but which doesn't have any (or perhaps has very few NULLs.

I saw https://stackoverflow.com/a/17688833/56690 which is kind of the inverse problem but I am not really familiar enough with this kind of meta-SQL to know how reasonable it is to accomplish what I'm hoping.


texts: Warning about memory "Excessive Grant" in the query plan - how to find out what is causing it?

texts:
I am running a query that is giving the warning about a memory Excessive Grant.

There are too many tables and indexes used, including a complex view, and therefore it is difficult to add all of the definitions here.

I am trying to find what might me causing the Excessive Grant. Can it be conversions?

Looking at the execution plan I can see the following:

<ScalarOperator
  ScalarString="CONVERT(date,[apia_repl_sub].[dbo].[repl_Aupair].[ArrivalDate] as [repl].[ArrivalDate],0)">
  <Convert DataType="date" Style="0" Implicit="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Database="[apia_repl_sub]" Schema="[dbo]" Table="[repl_Aupair]" Alias="[repl]" Column="ArrivalDate" />
      </Identifier>
    </ScalarOperator>
  </Convert>
</ScalarOperator>

And this one:

<ScalarOperator ScalarString="CONVERT(date,[JUNOCORE].[dbo].[applicationPlacementInfo].[arrivalDate] as [pi].[arrivalDate],0)">
  <Convert DataType="date" Style="0" Implicit="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Database="[JUNOCORE]" Schema="[dbo]" Table="[applicationPlacementInfo]" Alias="[pi]" Column="arrivalDate" />
      </Identifier>
    </ScalarOperator>
  </Convert>
</ScalarOperator>

Here is the query, although you can see the query with the execution plan here too:

DECLARE @arrivalDate DATEtime = '2018-08-20'

SELECT      app.applicantID,
            app.applicationID,
            a.preferredName,
            u.firstname,
            u.lastname,
            u.loginId                       AS emailAddress,
            s.status                        AS statusDescription,
            CAST(repl.arrivalDate AS DATE)  AS arrivalDate,
            app.moodleCourseComplete,
            app.moodleCourseCompleteUpdated,
            u.loginId,
            c.countryName
FROM        app.application                  AS app
JOIN        app.applicant                    AS a    ON a.applicantId = app.applicantId
JOIN        usr.[user]                       AS u    ON u.userId = a.userId
JOIN        app.ref_applicationStatus        AS s    ON s.statusCode = app.status
JOIN        APIA_Repl_Sub.dbo.repl_Aupair    AS repl ON repl.JunoCore_applicationID = app.applicationID

JOIN        app.Country                      AS c    ON c.countryCode = a.nationalityCode
WHERE       repl.arrivalDate = @arrivalDate

UNION ALL

(
    SELECT      app.applicantID,
                app.applicationID,
                app.preferredName,
                app.firstname,
                app.lastname,
                app.emailAddress,
                ap.status,
                CAST(app.arrivalDate AS DATE)    AS arrivalDate,
                app.moodleCourseComplete,
                app.moodleCourseCompleteUpdated,
                app.emailAddress                 AS loginId,
                c.countryName
    FROM        JUNOCore.dbo.vw_SelectApplication    AS app
                INNER JOIN JUNOCore.dbo.country c ON c.countryCode = app.nationalityCode
                INNER JOIN JUNOCore.dbo.application as ap ON ap.applicationID = app.applicationID
    WHERE        arrivalDate    = @arrivalDate AND
                app.applicationID NOT IN (SELECT p4.applicationId FROM APCore.app.application p4)
)

This is how the warning looks like:

The execution plan is here.

How do I tackle this warning?

As I said before I was looking at conversions. Is there anything I can look for in the execution plan that would indicate the possible causes of this excessive grant?

Obs. I said there are too many objects involved, however, I can add here whatever is required upon request if it might help to tackle this issue. No problem.


texts: Insert Timestamp on value change from other table

texts:
I have an EvidenceTimeStamp table that is triggered with data when the case table has data inserted into it.

EvidenceTimeStamp:

CaseID
EvidenceNum
EvidenceType
Evidence_Initiated_Timestamp
Evidence_Returned_Timestamp

I created a trigger to fill the first four columns of data whenever a new case is inserted in the case table and it creates a timestamp on Evidence_Initiated_Timestamp when this happens.

However, I need to create a closing timestamp when the process is complete. I wanted to do this by my Outbound Ship Table- everytime the Attribute (bit value) Outbound_Item_Recevied is changed from false to true (or 0 to 1) I want it to initate a timestamp on the Evidence_Returned_Timestamp column in my EvidenceTimeStamp table that matches the corresponding CaseID and EvidenceNum.

Note: the EvidenceTimeStamp table does not have a primary key or foreign key technically... the CaseID is primary key in the Case table and EvidenceNum is an attribute in the Evidence Table. This is a "trigger" table so it really doesn't have its own specific data, it's just filling data from other tables.

Any idea of how to do this?

Also, other quick question, how do I get bit values to show "True" or "False" instead of 1 or 0? Thank you!!


texts: Find Most active databases that can be offloaded from a busy server

texts:
I am in the process of identifying the most active databases on the server and will be moving the databases off the current server to alleviate the load.
The reason to do it is that our servers are hosting databases for out clients (per client 3 databases - 1 Main, 1 Archive and 1 for Login authentication).
I need to find out what databases are used most interms of CPU, Memory and Disk I/O.
What is the best efficient way of doing it ?
What I have tried :
Look at the buffer pool usage :

sys.dm_os_buffer_descriptors
Find the highest CPU consuming databases on the server instance.
Find databases experiencing disk latency.

My questions are :

Am I in the right direction ? What other things I can consider, so that moving the databases off from this server will alleviate the load on the server.
How do I tie up menory usage, CPU and disk latency in figuring out the right database to move off the server ?

Any thoughts/suggestions are highly appreciated.
Below are the specs:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1  (Build 7600: )
3 sql server instances running
98 GB total physical memory. Max memory is 29GB per instance
Logshipping running every min for Main database and every 15 mins for Archive and Authentication databases.


texts:
I'm trying to restore my production .bak file to a new local instance using the below script but I still get this error:

The backup set holds a backup of a database other than the existing MyDatabase database.

I'm using SQL Server 2019.
USE [master]
RESTORE DATABASE [MyDatabase]
  FROM DISK = 'C:\MyDatabase.bak'
  WITH
  MOVE 'MyDatabase' TO 'C:\MyDatabase.mdf',
  MOVE 'MyDatabase_log' TO 'C:\MyDatabase_log.ldf',
  REPLACE
GO


texts: I recently had a governor installed in our LVE Manager and noticed accounts have 2 new limits MySQL Speed and MySQL IO Limit. Currently MySQL speed is set at 400% and MySQL IO Limit at 1.8G/s. What are the recommended limits for these for a high end hosting account?


texts: How to perform a running count on null values that resets on non-null values

texts:
I am trying to calculate a column that performs a running count of consecutive null values, but the running count will reset upon non-null values. Currently I am trying to do it with a window function

ROW_NUMBER() OVER (PARTITION BY ID, VAL ORDER BY VAL ROWS UNBOUNDED PRECEDING)

But this is just giving me an incrementing number that does not reset or break upon seeing non-null values.

For example if I had a dataset like this:

id  | date  | val
----+-------+-------
  1 |   1/1 | NULL
  1 |   1/2 | NULL
  1 |   1/3 | NULL
  1 |   1/4 |  1
  1 |   1/5 | NULL
  1 |   1/6 | NULL
  1 |   1/7 |  1
  2 |   1/8 |  2
  2 |   1/9 | NULL
  2 |   1/1 | NULL
  2 |   1/2 |  1
  2 |   1/3 | NULL
  2 |   1/4 |  0
  2 |   1/5 | NULL
  2 |   1/6 | NULL

I would like the output to look like this:

id  | date  | val   | foo
----+-------+-------+-------
  1 |   1/1 | NULL  |  1
  1 |   1/2 | NULL  |  2
  1 |   1/3 | NULL  |  3
  1 |   1/4 |  1    |
  1 |   1/5 | NULL  |  1
  1 |   1/6 | NULL  |  2
  1 |   1/7 |  1    |
  2 |   1/8 |  2    |
  2 |   1/9 | NULL  |  1
  2 |   1/1 | NULL  |  2
  2 |   1/2 |  1    |
  2 |   1/3 | NULL  |  1
  2 |   1/4 |  0    |
  2 |   1/5 | NULL  |  1
  2 |   1/6 | NULL  |  2

But right now the foo column would just keep incrementing the nulls for each user.


texts: Is there a good "rule of thumb" for translating EXPLAIN cost to (wall-clock) runtime?

texts:
From time to time, consumers of my database processes will ask for an estimate of when a given task will be done. While I feel like I know how to read an EXPLAIN in most database engines, I have trouble trying to translate this to "ask me again in 15 minutes". Does anyone know a good "rule of thumb" to use for any particular database?

I realize this isn't going to be a hard and fast rule, but even being able to give a ballpark figure could be useful in some instances.


texts:
I need some help in choosing the correct isolation level on my transactions.

My situation is as follows. I have a table with a unique constraint over two columns. For the sake of the argument, lets say the table is called Animal and the columns are Name and Owner. So every owner can only have one pet with a certain name.

My problem is that I'm receiving errors during inserts because they are coming in at such a phase that I'm getting exceptions triggered by the unique constraint. Inserts are happening by stored proc. At the start of the SP I do a select from Animal where Owner=x and Name=y to determine if the animal exists, if so, select the Id and move on, else insert the new animal. The rest of the proc uses the Id to insert/update Animal details in other tables.

I know I can set the isolation level to Serializable, but I'm hesitant to do so because I know that it can cause deadlocks and since I'm having to do loads of inserts rapidly, this is a very real threat.

Any advise will be welcomed, thanks.


texts: How to do a bulk load of a database when application are relying on it?

texts:
In a daily routine, I need to drop and do a bulk load of a database.

The problem is that I have a WebApp relying on that database. So I can't drop the database just like this.

What would be a good way to tackle this problem ?


texts: Computed column with less size than the "referring column"

texts:
I have a column with data type nvarchar(max) and I wold like to index this. However, since it's too big it's not possible. So I figured I could create a persisted computed column based on that column with the formula:

left(isnull([fieldValue],''),500)

However this column also gets a data type of nvarchar(max) so I can't create an index for it. Is it possible to index it somehow without using a full-text index?


texts: Special characters in Oracle utl_http request

texts:
When calling a web service using utl_http, the calls fail when the SOAP envelope contains characters like ¿, ± or º.

IS
     http_req  utl_http.req;
     http_resp utl_http.resp;
     request_env varchar2(32767);
     response_env varchar2(32767);
BEGIN

    request_env:='
<soap:Envelope
     ...
</soap:Envelope>';

    http_req := utl_http.begin_request('http://ws.domain.com/Project.asmx', 'POST', utl_http.HTTP_VERSION_1_1);
    utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
    utl_http.set_header(http_req, 'Content-Length', length(request_env));
    utl_http.set_header(http_req, 'SOAPAction', '"http://domain.com/WS/ProjectAction"');
    utl_http.write_text(http_req, request_env);

I thought that charset=utf-8 in Content-Type should take care of that, but it still bombs and the characters above have to be replaced to their ASCII multi-characters equivalents beforehand.

This is an NLS report for this database:

NLS_CALENDAR    GREGORIAN
NLS_CHARACTERSET    WE8ISO8859P1
NLS_COMP    BINARY
NLS_CURRENCY    $
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE   AMERICAN
NLS_DUAL_CURRENCY   $
NLS_ISO_CURRENCY    AMERICA
NLS_LANGUAGE    AMERICAN
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_NUMERIC_CHARACTERS  .,
NLS_SORT    BINARY
NLS_TERRITORY   AMERICA
NLS_TIMESTAMP_FORMAT    DD-MON-RR HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT  HH.MI.SSXFF AM TZR

I am kind of consulting the in-house developers on this issue and before I will try to convince them to try redeclare request_env as NVARCHAR2, wanted to get the community opinion on whether that should be the fix they should attempt.


texts: DB2 - Is there a way to get the list of tables a particular transaction has affected?

texts:
We have a scenario where we would like to see if we could get hold of a list of tables affected by a given transaction. We use attunity to listen on the transaction log, but what I get from attunity is the transaction ID associated to a given event (a row change for a single table), not how many other tables that transaction has affected.

Consider the below update events -

UPDATE DEPARTMENTS SET (DEPT_NAME) = ('ChangedName3') WHERE DEPT_NO = 'd010';

UPDATE EMPLOYEES SET (FIRST_NME) = ('Georgi2') WHERE EMP_NO = '10001';

UPDATE DEPT_EMP SET (FROM_DTE) = ('1986-06-26') WHERE EMP_NO = '10001' AND DEPT_NO = 'd005';

UPDATE SALARIES SET (SALARY) = ('60111') WHERE EMP_NO = '10001' AND FROM_DTE = '1986-06-26';

UPDATE TITLES SET (TITLE) = ('Sr. Engineer') WHERE EMP_NO = '10001' AND FROM_DTE = '1986-06-26';

COMMIT;

This generates 5 events each having the transaction id - 00000000C420BC4E86DD010000000100 (I used a sample from attunity output).

Can I get something like -

00000000C420BC4E86DD010000000100, {EMPLOYEES, DEPARTMENTS, DEPT_EMP, SALARIES, TITLES} on each of the 5 events? Or query DB2 to find this information?


texts:
What would be an appropriate / suitable database sans Oracle that would be suitable for the following conditions?

data mining
 includes but does not limit to the following:
 a. clicking on links
 b. capture geolocation data, platform details
 c. user behaviours
there will be a foreseen exponential surge of data being captured as more apps / sites being deployed
for now, there are on average 60,000 ~ 80,000 data being captured every 15 minutes for 2 main sites / apps
In the near future, we are also planning to release all these data to the various parties involved where they can use it for their own apps / sites.

As of now, we are using mySQL but we found that it becomes very costly as we started to scale up. As such, any advice would be good as a starting point.


texts: Does unique email in user table violate normal forms?

texts:
USER(userID, email, name, address, ...)
userID

primary key
incrementing bigint

email

unique identifier
can be modified by user

To me this seemed like a good idea, but then I started to wonder: does the uniqueness of 'email' violate the third normal form (3NF)?


texts: How to do an incremental refresh of a PostgreSQL Materialized View?

texts:
I'm looking at the PostgreSQL docs where:

  REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded.

However, since 9.5 I believe, there is a way to update a MV without having to regenerate all the data.


texts:
I have been using on my SQL Server 2008R2 server Ola Hallengren solutions for almost two years.  Last night backups failed, both System databases and user databases with error below.

Example of system databases error:

  Date and time: 2016-05-31 20:00:01 Command: DECLARE @ReturnCode int
  EXECUTE @ReturnCode = [master].dbo.xp_create_subdir
  N'E:\SQLBackups\Server1\master\FULL' IF @ReturnCode <> 0
  RAISERROR('Error creating directory.', 16, 1) HResult 0x5620, Level
  16, State 1 xp_create_subdir() returned error 183, 'Cannot create a
  file when that file already exists.' Msg 50000, Level 16, State 1,
  Server Server1, Line 1 Error creating directory. Outcome: Failed
  Duration: 00:00:00 Date and time: 2016-05-31 20:00:01

User Databases error Example:

  Date and time: 2016-05-31 20:00:12 Database: [Dummy_1] Status: ONLINE
  Standby: No Updateability: READ_WRITE User access: MULTI_USER Is
  accessible: Yes Recovery model: SIMPLE Differential base LSN:
  423000000014100036 Last log backup LSN: 342000000037500001   Date and
  time: 2016-05-31 20:00:12 Command: DECLARE @ReturnCode int EXECUTE
  @ReturnCode = [master].dbo.xp_create_subdir
  N'E:\SQLBackups\Server1\Dummy_1\FULL' IF @ReturnCode <> 0
  RAISERROR('Error creating directory.', 16, 1) HResult 0x5620, Level
  16, State 1 xp_create_subdir() returned error 183, 'Cannot create a
  file when that file already exists.' Msg 50000, Level 16, State 1,
  Server Server1, Line 1 Error creating directory. Outcome: Failed
  Duration: 00:00:00 Date and time: 2016-05-31 20:00:12

It failed when I run the job on right click too

Does anyone know how to solve the problem?


texts:
How do I execute SQL Agent job 3 times in a row?

We are creating Data Warehouse Project. It ETLs data from Flatfile Legacy Folder, and creates Dim and Fact tables.

SSIS Project Environment Variable (FlatfileFolder) set: to March 2018

Import ETL Flatfiles into Staging Database for March 2018
Create Customer Dimension Tables
Create Product Dimension Tables
Create SalesTransaction Fact Tables

Want to execute this 3 times in a row, changing Project Environment Variable (FlatfileFolder) to April 2018.
Run 4 jobs again,
then change Project Environment Variable (FlatfileFolder) to May 2018, and run 4 job steps again. (Theoretically, should have been designed to extract all history files and load at once, however did not design this, just inheriting it.)

This stack link below talks about endless loop, only want to run it 3 times, and change environment variable.

Running job in endless loop

Options:

(a) write 12 Step SQL Agent Job,

(b) Or somehow after Step 4, on success go to back to Step 1? How to make this only run 3 times?

Eventually may want to execute job 5-10 times in row for later months.
Trying to conduct with SQL environment, prefer to refrain from C# or Powershell unless required.

We are processing data.

Thanks,


texts:
select a,b from table1
where a not in (select x as a from table2)
AND b not in (select y as b from table3)

The above query gives an empty result. Why? And how should I construct this combined query?

whereas:

-- gives a=5,b=6 & a=7,b=8
select a,b from table1
where a not in (select x as a from table2)

-- gives a=1,b=2 & a=5,b=6
select a,b from table1
where b not in (select y as b from table3)

Individually, the queries work; but together they give no result although I think they match row2 from table1

table1     table2     table3
a   b      x    y      x   y
1   2      1    0      3   4
5   6      0    0      0   0
7   8      9    5      0   8

I want result of query as:

   a  b
   5  6

where a is not x in table2 and simultaneously b is not y in table3.

SORRY yes aasim.abdullah
the query does work and so does this one

use the following query to get desired result.

select a,b from (select a,b from table1 where a not in (select x as a from table2))temptable where b not in (select y as b from table3)

So my new question is which query first or second has speed performance advantage & whysince my actual table is seriously huge.I am using MYSQL


texts:
SQL server 2008 R2.

I have set up transactional replication on 40 tables in a database (other tables don't replicate).

There was an issue in the morning and it is fixed now. I resynchronised the database. It has been running for last 7 hours.

I checked the Publisher's replication monitor. It says performance tab "fair" and no errors. Checked "Publister to distributer history" and "distributer to Subscriber history". Both say "no replicated transactions are available.

i checked the subscriber's "view synchronization status" and it says "No replicated transactions are available."

but when I use red gate data compare tool between these database in those replicated table, around 10 tables have different data.

How could this be happening? how do I get replication synch the data between the 2 databases.

Thanks for your help


texts: SQLite: Leave only n rows for each field

texts:
I have a table

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Window        | 5         |
|  Zinaida Serebriakova       | Florence      | 1         |
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Bath          | 4         |
|  Thomas And William Daniell | Turbine Makers| 2         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Galleons      | 1         |
|  Robert Motherwell          | Ulysses       | 2         |
+-----------------------------+---------------+-----------+

I need to get 2 paintings for each painter with the highest rate.

+-----------------------------+---------------+-----------+
|         Painter             | Painting      | Rate      |
+-----------------------------+---------------+-----------+
|  Zinaida Serebriakova       | Nude          | 8         |
|  Zinaida Serebriakova       | Window        | 5         |
|  Thomas And William Daniell | George Iain   | 7         |
|  Thomas And William Daniell | Flax Pullers  | 3         |
|  Robert Motherwell          | Ulysses       | 2         |
|  Robert Motherwell          | Galleons      | 1         |
+-----------------------------+---------------+-----------+

And delete everything else!


texts: What is the default value of ANSI Padding Enabled, and what should value should I set it to?

texts:
When creating a new database, ANSI Padding Enabled is set to False by default.

According to this however, the default is ON (which I assume is the same as True). They also recommend to set it to ON and mention that in the future this setting will be deprecated and always set to ON.

I'm quite confused by all of this, actually my question comes from a ticket I have opened that is related to this setting.

So, what is the actual default value, and do I need to change any settings?