texts:
This is the error I have been getting on my RMAN notifcations on my backup jobs for the last day or so. The title is exactly how the message is formatted.

~~~End Step Output/Error Log~~~
Error Log:BackupOp
error writing input to commandERROR: Invalid username and/or password

~~~End Step Output/Error Log~~~

I only have 2 backup jobs and both are affected by this. Not sure how much information here is useful but what I have been looking at is the SYS account the jobs are using is not locked out.

select account_status from dba_users where username = 'SYS';

ACCOUNT_STATUS
--------------------------------
OPEN

The emagent.trc log is showing this pair of lines for every backup job since the first one failed.

2019-02-06 23:44:26,791 Thread-14472 ERROR command: failed to write to stdin of process 6228: (error=232, no message available)
2019-02-06 23:44:26,791 Thread-14472 ERROR command: nmejcc_feedInput: received -11 from process write

Restarting the DBConsole service has no affect and the errors persist.

I can manually run full and archivelog backups just fine as sysdba. I see no reason to blame RMAN since I can run the backup rman scripts just fine. I think this is just an EM issue.


texts: Connection refused when trying to set up replication

texts:
I set up replication.  I've done this on two servers so far, and it's worked.  Third time unlucky, as it were.

The production server is CentOS 6.4, and the replication server is CentOS 6.5.  The prod box will join its little brother soon as a 6.5 box, on 3 January - date of the change.  But anyway, the MySQL server is 5.1.71 on both servers.

On the server, I edit the /etc/my.cnf file:

log-bin=mysql-bin
server-id=1

... I create the user :

CREATE USER 'repli'@'%.example.com' IDENTIFIED BY 'blagblah';

.. and then ...

GRANT REPLICATION SLAVE ON *.* TO 'repli'@'%example.com' IDENTIFIED BY 'blagblah';

A mysqldump is performed every morning, so I grab the dump and rsync it over to the slave.  I get the master status, the filename and the position in the log.

On the slave, I edit the /etc/my.cnf file:

server-id=2

I then restart the server, but (is this where I have gone wrong?), I just start it from /etc/init.d/.

I login to the mysql instance and execute:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='server1.example.com', MASTER_USER='repli', MASTER_PASSWORD='blagblah', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=24112816;
START SLAVE;

When I check slave status, however, it tells me that there is an error connecting to the master.

So I start to wonder whether the fact that I didn't start the server with 'skip-slave-start' might be responsible.  So I shut down the server, and execute:

sudo /usr/bin/mysqld_safe --skip-slave-start

... and this is where it starts to get weird.

mysql -u root -pXXXXXXXX

And the server tells me ..

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Excuse me?  This is the slave server, and the box on which five minutes earlier, I had being typing commands.

Where have I gone wrong?


texts:
I was just in the process of 'yum updating' a server. One of the updates its suggesting to carry out is an upgrade from mysql 5.1 to 5.5.

The mysql server operates in a master-master replication setup with another 5.1 mysql instance. I would also update the other master to 5.5, but want to keep one online at all times. This master also has a number of 5.1 mysql slaves replicating from it.

Does anyone know if:

yum update is safe from 5.1 to 5.5 without huge data rebuilds
if the above replication setup will be a problem.

Edit:
This is a centos 5.4 install. However the updates are coming in from the non-standard remi repo.


texts: Downsides of switching Master to Simple recovery?

texts:
I've read a lot about this over the last couple of days, but none specifically on the Master db.

If I switch from full to simple, will it flush out any plans, statistics, etc?
Any performance hits?

I know how to proceed, just never seen Master on full recovery before.

TIA


texts: Update column based on sum of rows from another table by join

texts:
The command I'm trying to find should do this:

UPDATE table1
SET table1.a = x
WHERE table1.b = "something" AND table1.a IS NULL

The x is:

SELECT SUM(c) from table2
INNER JOIN table1
ON table2.id=table1.id
WHERE table1.b = "something" AND table1.a IS NULL
GROUP BY table2.id;

I've tried some variations of the command but with no success.
Would appreciate any help in write the command properly.
Thanks.


texts: Strange query plan when using OR in JOIN clause - Constant scan for every row in table

texts:
I'm trying to produce a sample query plan to show why UNIONing two result-sets can be better than using OR in a JOIN clause. A query plan I've written has me stumped. I'm using the StackOverflow database with a nonclustered index on Users.Reputation.

The query is

CREATE NONCLUSTERED INDEX IX_NC_REPUTATION ON dbo.USERS(Reputation)
SELECT DISTINCT Users.Id
FROM dbo.Users
INNER JOIN dbo.Posts
    ON Users.Id = Posts.OwnerUserId
    OR Users.Id = Posts.LastEditorUserId
WHERE Users.Reputation = 5

The query plan is at https://www.brentozar.com/pastetheplan/?id=BkpZU1MZE, query duration for me is 4:37 min, 26612 rows returned.

I haven't seen this style of constant-scan being created from an existing table before - I'm unfamiliar with why there's a constant scan ran for every single row, when a constant scan is usually used for a single row inputted by the user for example SELECT GETDATE(). Why is it used here? I would really appreciate some guidance in reading this query plan.

If I split that OR out into a UNION, it produces a standard plan running in 12 seconds with the same 26612 rows returned.

SELECT Users.Id
FROM dbo.Users
    INNER JOIN dbo.Posts
       ON Users.Id = Posts.OwnerUserId
WHERE Users.Reputation = 5
UNION
SELECT Users.Id
FROM dbo.Users
    INNER JOIN dbo.Posts
       ON  Users.Id = Posts.LastEditorUserId
WHERE Users.Reputation = 5

I interpret this plan as doing this:

Get all 41782500 rows from Posts (the actual number of rows matches the CI scan on Posts)
For each 41782500 rows in Posts:

Produce scalars:
Expr1005: OwnerUserId
Expr1006: OwnerUserId
Expr1004: The static value 62
Expr1008: LastEditorUserId
Expr1009: LastEditorUserId
Expr1007: The static value 62

In the concatenate:

Exp1010: If Expr1005 (OwnerUserId) is not null, use that else use Expr1008 (LastEditorUserID)
Expr1011: If Expr1006 (OwnerUserId) is not null, use that, else use Expr1009 (LastEditorUserId)
Expr1012: If Expr1004 (62) is null use that, else use Expr1007 (62)

In the Compute scalar: I don't know what an ampersand does.

Expr1013: 4 [and?] 62 (Expr1012) = 4 and OwnerUserId IS NULL (NULL = Expr1010)
Expr1014: 4 [and?] 62 (Expr1012)
Expr1015: 16 and 62 (Expr1012)

In the Order By sort by:

Expr1013 Desc
Expr1014 Asc
Expr1010 Asc
Expr1015 Desc

In the Merge Interval it removed Expr1013 and Expr1015 (these are inputs but not outputs)
In the Index seek below the nested loops join it is using Expr1010 and Expr1011 as seek predicates, but I don't understand how it has access to these when it hasn't done the nested loop join from IX_NC_REPUTATION to the subtree containing Expr1010 and Expr1011.
The Nested Loops join returns only the Users.IDs that have a match in the earlier subtree. Because of predicate pushdown, all rows returned from the index seek on IX_NC_REPUTATION are returned.
The last Nested Loops join: For each Posts record, output Users.Id where a match is found in the below dataset.


texts: is there a way to dump schema tables that don't exist and ignore the ones that exist?

texts:
I am using this command to dump schema directly to a different server because the existing server don't have space left to dump the tables/schemas and then transfer to the other server:
mysqldump -h localhost -u username --password='supersecretpassword' --set-gtid-purged=OFF --compress --skip-lock-tables --verbose schema_name  | mysql -u username --password='supersecretpassword' -h 192.168.10.10 schema_name

ideally, it should ignore the tables that already exist on the server 192.168.10.10 server.
thanks


texts: FULL OUTER JOIN With MySQL, MySQL don't support ist what is the solution for more than 2 Tables?

texts:
select * from

(select col1,col2,col3 from tableA
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

left join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

left join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3

UNION

(select col1,col2,col3 from tableA
where col1 like 'somthing1'
and col2 like 'somthing2' ) as t1

right join

(select col2,col2, col3 from tableB
where col2 like 'somthing1'
and col2 like 'somthing2') as t2

on t1.col3= t2.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t3

on t2.col3= t3.col3

right join

(select col2,col2, col3 from tableC
where col2 like 'somthing1'
and col2 like 'somthing2') as t4

on t3.col3= t4.col3


texts:
I have an asp.net 4.0 application with a button that opens a SSRS report.  This works fine on my development machine and I can open the report directly with report manager but when I try to access the report on the production machine I get this error

Webpage error details
User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; GTB7.4; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; CMDTDF; BRI/1; .NET4.0C; InfoPath.3; .NET4.0E)
Timestamp: Sat, 1 Dec 2012 21:35:46 UTC

Message: Sys.WebForms.PageRequestManagerServerErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500
Line: 2
Char: 74741
Code: 0
URI: http://intranet/webapps/TestRecordedOnlyIncidents/ScriptResource.axd?d=ZBsMSL9jIyW8I9p1LwFOBagb0X-bFxrU28WowdY5TbC9ZankzHyooPpti5W0JT6DLsOOmhf_2t5kU1g4r1F40RiMcHtiuYqff50r1VaQMwtZ9CW6zRVLnDMJYHiVvrNZ0&t=ffffffffbad362a4

I've searched the web for a solution and have found a half a dozen but none have worked for me.  Here is my code:

    Dim r = New ServerReport
    r.ReportServerUrl = New Uri("http://vm-intranet/ReportServer")
    r.ReportPath = "/RecordedOnlyIncidents/Reports/AllIncidentsWithParameters"
    Dim EmployeeNumber As String = IIf(txtSearchEmployeeNumber.Text = "", Nothing, txtSearchEmployeeNumber.Text)
    Dim IncidentType As String = IIf(ddlSearchIncidentType.SelectedItem.Text = "", Nothing, ddlSearchIncidentType.SelectedItem.Text)
    Dim BeginningDate As String = IIf(txtBeginningDate.Text = "", Nothing, txtBeginningDate.Text)
    Dim EndingDate As String = IIf(txtEndingDate.Text = "", Nothing, txtEndingDate.Text)
    Dim parmEmpNumEmployeeNumber As New ReportParameter("EmployeeNumber", EmployeeNumber)
    Dim parmIncidentType As New ReportParameter("IncidentType", IncidentType)
    Dim parmBeginningDate As New ReportParameter("BeginningDate", BeginningDate)
    Dim parmEndingDate As New ReportParameter("EndingDate", EndingDate)
    Dim parameters(3) As ReportParameter
    parameters(0) = parmEmpNumEmployeeNumber
    parameters(1) = parmIncidentType
    parameters(2) = parmBeginningDate
    parameters(3) = parmEndingDate
    r.SetParameters(parameters)
    Dim warnings As Warning() = Nothing
    Dim streamids As String() = Nothing
    Dim mimeType As String = Nothing
    Dim encoding As String = Nothing
    Dim extension As String = Nothing

    Dim reportOutput As Byte() = r.Render("PDF", Nothing, mimeType, encoding, extension, streamids, warnings)
    Dim path As String = System.AppDomain.CurrentDomain.BaseDirectory
    Dim stream As New IO.FileStream(path & "/MyReport.pdf", IO.FileMode.Create)
    stream.Write(reportOutput, 0, reportOutput.Length)
    stream.Close()
    Dim Script As String = "<script type='text/javascript'> win=window.open("""",""_blank"",""resizable=Yes,height=600"");win=win.document;win.write(""<style>body{margin:0px;}</style>"");win.write(""<title> Incidents Report</title>"");win.write(""<iframe src='MyReport.pdf' style='width:100%;height:100%;'></iframe>"");</script>"
    ScriptManager.RegisterStartupScript(Me, Me.GetType(), "OpenReport", Script, False)

Any suggestions would be greatly appreciated.


texts: Optimising MariaDB for large AWS machines

texts:
Been reading up on DB optimisation (I am an analyst, not really a Sysadmin), and it would seem the biggest adjustment one can make is change the innodb_buffer_pool_size,  innodb_buffer_pool_instances and innodb_file_per_table.

Is there some point that an extremely large buffer pool just doesn't make sense anymore. For example, when using AWS, I can get a machine with 64 cores and 500GB RAM if I need to do analysis on data that is around 200 mil rows (indexed). Is it worth setting innodb_buffer_pool_size = 400G and innodb_buffer_pool_instances = 60:

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld]
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
user                           = mysql
# applies only when running as root
#memlock                        = 1

table_open_cache               = 3072
table_definition_cache         = 4096
max_heap_table_size            = 64M
tmp_table_size                 = 64M
max_connections                = 505
max_user_connections           = 500
max_allowed_packet             = 16M
thread_cache_size              = 32
query_cache_size               = 64M

# InnoDB
default_table_type             = InnoDB

# 80% of ram that is dedicated for the database
innodb_buffer_pool_size        = 400G
# number of CPU cores dedicated to the MySQL InnoDB backend
innodb_buffer_pool_instances = 60

innodb_data_file_path          = ibdata1:128M:autoextend
innodb_file_per_table          = 1
innodb_log_file_size           = 1G
innodb_log_files_in_group      = 2


texts: MySQL/InnoDB - Clustering Index with UUID

texts:
In MS SQL Server it is possible to cluster an index on a non-primary, non-unique key. For example, if I wanted to create a clustered index on the insert date to prevent page splits and fragmentation upon insert in the middle of a UUID primary key, SQL Server would perform the background work for this.

It appears in MySQL/InnoDB that the clustered index would be created upon the first primary or unique key added to the table. Other than adding a non-UUID primary key, or not building any unique keys, how would the major fragmentation be avoided? Is there another engine that would work better with this?

If unavoidable, are there any steps that can be taken to mitigate the issue outside of periodic rebuilds of the clustered index?


texts: Creating sequence for longer numeric values

texts:
I need to create a database model that includes a field to store 20 digit unique values. Which will be used by many clients over an API.

The value must be 20 digit long, numeric and unique. Best approach seemed to create an auto increment big-int field, but big-int supports values up to 9223372036854775807 (19 digits). Using numeric(20,0) is useless since although numeric supports larger values, Postgres 8.4 do not let me create a sequence and raises error

CREATE SEQUENCE my_sequence_name INCREMENT BY 1 NO MINVALUE NO MAXVALUE START WITH 10000000000000000101;

  ERROR:  value "10000000000000000101" is out of range for type bigint

For data integrity, I must handle this within POstgresql. What is the efficient way to handle this?

I am using Postgresql 8.4 and Django 1.3

UPDATE: I guess it is better to make a clarification...
I am trying to find a way to solve this within DBMS. So any non-database solutions will be avoided unless I had no other choice. Rest is the reason of my question:

I am making an API which communicates with a Bank for 3D secure operations. The bank API required a 20 digit unique numeric identifier for the transaction, so I decided to create an ID value that I can both use for the bank API and my API that will use that table etc.

Stating from 10000000000000000101 is because, bank API want it to be a valid 20 digit integer value, so It must be at least 10000000000000000000.

Solving this within the DBMS is best for performance and data integrity. An idea I thought  was storing the value as an auto-increment big-integer in the database field, and manually parse it to string and make some make-up like:

bid = str(23434545) # value in the DB field
final_value = '1' + '0'* (19-len(bid)) + bid

which make final_value a 20 digit integer that starts with 1 and end with my auto-incremented value. and middle is filled with zeros.

But in case of dealing with both SELECT and INSERT queries, Keeping the logic out of the DBMS is bas, so so bad.


texts:
I have a stored procedure that is looping through a result set from a select statement. Inside of the loop there is a if statement that has two insert statements. When one of those insert statements runs for the first time i am kicked out of my while loop and the procedure ends. I have tried storing the inserts in another procedure and call that procedure from the inside the if statement and i have tried adding commit after the insert statements. Neither have works.

EDIT: OK i have found the issue. On the select statement at the beginning of the loop i have a select statement that is there to see if we run the insert statements. This statement will some times set a null value to the variable. this seems to be the reason that we are leaving the loop early. I have added this statement into the initial result set pull and it is working well now.

WHILE(curse = TRUE) do
        fetch theTable into ciid,crid;
        SELECT ClientInformation_CIID INTO classAttOrAb FROM clientmonitoringdata LEFT JOIN clientattendancemonitoring
          ON FK_clientmonitoringdata_ClientMonitoringDataID = ClientMonitoringDataID
          WHERE clientmonitoringdata.FK_clientrequirements_ClientRequirementsID  = crid AND
          EntryDate BETWEEN startDate and endDate  AND VoidEntry = FALSE AND clientattendancemonitoring.AttendedClass IS NOT NULL LIMIT 1;
        IF(classAttOrAb = 0) THEN
          INSERT INTO clientmonitoringdata(ClientInformation_CIID,EntryDate,FK_clientrequirements_ClientRequirementsID)
            VALUES( ciid, classDate, crid);

          SET cmid = LAST_INSERT_ID();

          INSERT INTO clientattendancemonitoring( FK_clientInformation_CIID, Absence, AttendedClass, ClassAttended, ExcusedAbsenceReason,
            FK_clientmonitoringdata_ClientMonitoringDataID, FK_clientrequirements_ClientRequirementsID, AbsenceNotes, FK_fxuseraccounts_FXUserAccountsID)
            VALUES(ciid,abType,FALSE,class,exAbReason,cmid,crid,abNotes,empId);
          COMMIT;
        end IF;

        SET classAttOrAb = 0;
      end while;

END


texts: XAMPP - Error: MySQL shutdown unexpectedly

texts:
Last night MySQL Service on my XAMPP local server was running fine. Today... it starts for 10 seconds and then give me following error:

10:14:28 AM  [main]     Initializing Modules
10:14:28 AM  [main]     The FileZilla module is disabled
10:14:28 AM  [main]     The Mercury module is disabled
10:14:28 AM  [main]     Starting Check-Timer
10:14:28 AM  [main]     Control Panel Ready
10:14:31 AM  [Apache]   Attempting to start Apache app...
10:14:31 AM  [Apache]   Status change detected: running
10:14:38 AM  [mysql]    Attempting to start MySQL app...
10:14:38 AM  [mysql]    Status change detected: running
10:14:49 AM  [mysql]    Status change detected: stopped
10:14:49 AM  [mysql]    Error: MySQL shutdown unexpectedly.
10:14:49 AM  [mysql]    This may be due to a blocked port, missing dependencies,
10:14:49 AM  [mysql]    improper privileges, a crash, or a shutdown by another method.
10:14:49 AM  [mysql]    Press the Logs button to view error logs and check
10:14:49 AM  [mysql]    the Windows Event Viewer for more clues
10:14:49 AM  [mysql]    If you need more help, copy and post this
10:14:49 AM  [mysql]    entire log window on the forums

The Log file follows:

2019-11-21 10:14:38 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2019-11-21 10:14:38 0 [Note] InnoDB: Uses event mutexes
2019-11-21 10:14:38 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2019-11-21 10:14:38 0 [Note] InnoDB: Number of pools: 1
2019-11-21 10:14:38 0 [Note] InnoDB: Using SSE2 crc32 instructions
2019-11-21 10:14:38 0 [Note] InnoDB: Initializing buffer pool, total size = 16M, instances = 1, chunk size = 16M
2019-11-21 10:14:38 0 [Note] InnoDB: Completed initialization of buffer pool
2019-11-21 10:14:38 0 [Note] InnoDB: 128 out of 128 rollback segments are active.
2019-11-21 10:14:38 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-11-21 10:14:38 0 [Note] InnoDB: Setting file '\CoreApps\Mayfield\xampp-rtweek\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-11-21 10:14:38 0 [Note] InnoDB: File '\CoreApps\Mayfield\xampp-rtweek\mysql\data\ibtmp1' size is now 12 MB.
2019-11-21 10:14:38 0 [Note] InnoDB: Waiting for purge to start
2019-11-21 10:14:38 0 [Note] InnoDB: 10.3.15 started; log sequence number 1604056; transaction id 9
2019-11-21 10:14:38 0 [Note] InnoDB: Loading buffer pool(s) from E:\CoreApps\Mayfield\xampp-rtweek\mysql\data\ib_buffer_pool
2019-11-21 10:14:38 0 [Note] InnoDB: Buffer pool(s) load completed at 191121 10:14:38
2019-11-21 10:14:38 0 [Note] Plugin 'FEEDBACK' is disabled.
2019-11-21 10:14:38 0 [Note] Server socket created on IP: '::'.

Apparently, there doesn't seem to be any error. I have an older version of XAMPP that is workin' fine, which means there is no Port 3306 issue.

I have tried couple of suggested solutions on other platforms, e.g. deleting ib_logfile0, ib_logfile1, and ibdata1 files and adding

innodb_force_recovery = 4

to configuration file, that didn't work for me.

What could be the issue here?


texts: Rows to EAV structure with row identifier

texts:
Would you help me with this, please?

Imagine any SQL table or query results like…

+-----------+----------+-----+
| FirstName | LastName | Age |
+-----------+----------+-----+
| Peter     | Whatever |  31 |
| George    | McDonald |  55 |
| ...                        |
+-----------+----------+-----+

And I want to transform each row to EAV structure (column name, column value) and add a unique row identifier, like…

+--------------------------------------+-----------+----------+
|                 GUID                 |  ColName  | ColValue |
+--------------------------------------+-----------+----------+
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | FirstName | Peter    |
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | LastName  | Whatever |
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | Age       | 31       |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | FirstName | George   |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | LastName  | McDonald |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | Age       | 55       |
| ...                                                         |
+--------------------------------------+-----------+----------+

Preferably using FOR XML PATH clause (more tables with a different structure to transform like this).

Script for creating test table goes here:

DECLARE @MyTable TABLE (
    FirstName NVARCHAR(100)
    ,LastName NVARCHAR(100)
    ,Age INT)
INSERT INTO @MyTable (FirstName,LastName,Age)
VALUES(N'Peter',N'Whatever',31),(N'George',N'McDonald',55);

I know the solution without GUID so the row-identifier is the point of my question:

SELECT T2.N.value('local-name(.)', 'nvarchar(128)') as ColName,
       T2.N.value('text()[1]', 'nvarchar(max)') as ColValue
FROM (SELECT *
      FROM @MyTable
      FOR XML PATH(''), TYPE) as T1(X)
CROSS APPLY T1.X.nodes('/*') as T2(N)


texts: Oracle - Any way to view uncommited changes to a particular table?

texts:
I'm debugging through a batch process currently that does a lot of DML statements, but doesn't do a commit right away.  It would be nice to be able to view the "pending" changes from another session while the transaction is not committed.  Is this possible?

Example:

Insert into table myTable (col1, col2) values ("col1", "col2");

--Somehow view the pending transaction maybe by system view?....

...other DML statements....

commit;


texts: Changes to main Database during Transactional Replication

texts:
Can you please advise how can I keep slave database up to date while it's in transactional replication with master.

I mean what If I change indexes, columns, tables or sthm else in master dabase? What should I do in this case?

Should I make this changes to both databases simultaneously or transactional article is enough to handle it?


texts: Can a query whose execution plan uses parallelization use the cores of a processor on a another server that's part of the same Availability Group?

texts: If I have two servers (server A and server B) that are part of the same Availability Group failover cluster, and a query running on server A has an execution plan that uses parallelization, is it possible for that query to use cores from a processor on server B?


texts:
Is there any particular reason why adding an additional join to an ordered table would reorder the result set?

Using a left join on the existing result set

table-valued function is similar to below (function returns @table):

conditional
    BEGIN
        INSERT @table
               (col1)
        select col1 from table1
        order by col1, x, y
    END
  ELSE
    BEGIN
        INSERT @table
               (col1)
        select col1 from table1
        order by col1, x, y, z
    END


texts: Difference between EXEC() and sp_executesql with no parameters?

texts:
I just learned about the new sp_executesql stored procedure.  I like the fact that there's a way to execute parameterized code from within SQL Server.

But, I'm wondering what the difference is between using the sp_executesql stored procedure when you don't have any parameters versus just calling EXEC directly.  Also, is there a performance implication?

exec('select * from line_segment')
exec sp_executesql N'select * from line_segment'

Also, is there a difference between 2005 and 2008 or do they handle these the same?


texts: Selecting data from one table via categoryID field and displaying in different areas whilst excluding categoryID for one area

texts:
I have made a very simple CMS for the company I work for. We need to display articles on our index page as well as headlines above in a carousel.

All articles are setup with a table for articles and art_categories table to link articles to their respective categories. I have been able to create a query that selects all articles and that exclude the headlines like below.

SELECT SQL_CALC_FOUND_ROWS articles.*, art_categories.*
FROM articles, art_categories
WHERE articles.categoryID=art_categories.categoryID AND articles.categoryID NOT IN(8) AND art_categories.categoryID NOT IN (8)
ORDER BY publishdate DESC LIMIT {$start},5

{$start} is used for pagination. The above query functions 100% and excludes the headlines category from the general article display area.

Now the problem I have is not being able to display the headlines on the same page by selecting them from the same table via their respective categoryID

SELECT articles.*, art_categories.*
FROM articles, art_categories
WHERE articles.categoryID=art_categories.categoryID AND articles.categoryID=8 AND art_categories.categoryID=8
ORDER BY publishdate DESC LIMIT 5

Any advice would be greatly appreciated.

Thanks the issue has been resolved and question is closed


texts: Sales by month cross tab query using pivot

texts:
I am trying to show sales by month using PIVOT in Sql Server 2005.  I know it's a common question, but I just can't figure out what I am missing here:

select ShippedYear, 1 as Jan, 2 as Feb
from
(
    select  datepart(year, ss.SHIPPED_DATE) as ShippedYear
          , ss.SHIPPED_AMOUNT as Amount
          , datepart(month, ss.SHIPPED_DATE)) as MonthCode
    from    dbo.SALES_SHIPPER_LINE ssl join dbo.SALES_SHIPPER ss on ss.ID = ssl.SHIPPER_ID
) ps
pivot(
    sum(amt) for MonthCode in (1,2,3,4,5,6,7,8,9,10,11,12) --this shows error??
) piv;

I am getting an error at the "in" clause in my pivot.  Can anyone tell me what I am doing wrong?


texts: MySQL 5.5.57 No longer supports myisamck *.MYI

texts:
I use
    myisamchk *.MYI
to optimise all my tables.

However this no longer works with the MYI extension

# myisamchk mytable
Checking MyISAM file: mytable
Data records:   17952   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1

# myisamchk mytable.MYI
myisamchk: error: 140 when opening MyISAM-table 'mytable.MYI'


texts: Scripting the setting up of a MySQL database - best practices?

texts:
I am a relative newbie to database administration. I am setting up my first database on my localhost in MySQL and want to understand best practices. This is my current workflow:

# do_everything.sql

SELECT 'CREATING DATABASE STRUCTURE' info;

DROP DATABASE IF EXISTS my_first_db;
CREATE DATABASE IF NOT EXISTS my_first_db;
USE my_first_db;

SET default_storage_engine = innodb;
SELECT CONCAT('storage engine: ', @@default_storage_engine) info;

SELECT 'CREATING TABLE A' info;
-- Code to create table from source_data_A.csv

SELECT 'CREATING LOGGER FOR TABLE A' info;
-- Code to create log table for table A
-- Code to create insert, update, & delete triggers for table A

SELECT 'LOADING SOURCE DATA A' info;
-- Code to load source_data_A.csv into table A

SELECT 'CREATING TABLE B' info;
-- Code to create table from source_data_B.csv

SELECT 'CREATING LOGGER FOR TABLE B' info;
-- Code to create log table for table B
-- Code to create insert, update, & delete triggers for table B

SELECT 'LOADING SOURCE DATA B' info;
-- Code to load source_data_B.csv into table B

SELECT 'DATABASE CREATION COMPLETE' info;

From my Terminal, I then run:

mysql --local-infile=1 -uroot -p < /absolute/path/to/do_everything.sql

Should I be splitting up do_everything.sql into different .sql files according to logical chunks? If so, would I then have a single master shell script that executes all of them?
If I keep the same workflow, am I following best practices?


texts: Can PostgreSQL slave server act as master for a new database?

texts:
My question is somewhat related to:
https://stackoverflow.com/questions/19331577/does-postgres-replication-native-support-per-database-level-replication

We have master-slave replication set up on our Postgres server (1 master and 1 slave). We have all our app tables in database DB1.

Our reporting group reads from DB1 and creates tables in a separate database DB2. The queries look like this:

INSERT INTO DB2.<some_table>
(SELECT ... FROM ... DB1 tables ...)

The READ queries that run against DB1 take a long time to run, so ideally we want to run these queries on the slave. But the WRITEs have to go to the master, so we end up running our entire SQL on master now.

Is there a way to make DB2 available only on the slave server and let the slave server be the master for DB2?

The alternative would be to write a script that reads from slave and writes to master, but I would like to avoid that since most people know only SQL.


texts:
For years I have dinked with and now considering making it into a real project.

I am thinking about developing a 'driver' of sorts that sits on top of RDMS( oracle, MSSQL, MySQL - others to follow). That given a table description the driver creates stored procedures and client code example(s) (front-end, back-end, etc).

The end result would be that the geek would create a table in any of the above mentioned RDMS's and my code would produce selected client interfacing.

For example Enter table deffinition. Check some buttons and click some buttons and out comes a zip file that has a web form .NET project to add/update/delete/select records from the given database(or specific tables). The developer can then take that code and with minor changes, (cosmetic security) and use it as there own.

The beauty would be that if the developer wants to define table for mysql they can change to to MSSQL with click button, and whatever code produced before will remain unbroken.

I realize this is 'code review' and I have no code. But any good durable code goes through this design phase.

Any thoughts on the concept?

I appreciate the thoughts thus far.  I agree with much of what is said.  The deliverable after selecting option boxes and clicking buttons would not be 'finished product'.  Largerly the developer will still be required to make the screen pretty.  If a there is two tables tblOrders and tblOrderItems - the end  result would be two 'forms' for each.  This is not ideal for the 'user' but helpful for the developer.  Each part of the deliverable will require some developer/DBA 'tweaking'.


texts: Perform admin tasks (e.g. create database, add diagram support) in SQL Server when not connected to domain

texts:
My situation: I have installed SQL Express on my machine, while logged in as a domain user. I work remotely so I need to connect to a VPN to be on the domain, but I often prefer not to work while attached to the domain so my packets don't have to travel across the country. My user account has local admin rights (win 7).

My problem is very similar to this one. I receive the same error message when I try to do "admin-y" things, like creating new databases or adding database diagram support to existing databases. The catch is that the error only happens when I am NOT attached to the VPN (and therefore my machine cannot reach the domain controller).

Is there a way to allow SQL Server to trust my login credentials when I'm not connected to the VPN?


texts: How to exclude query results based on a blacklist

texts:
I have defined two tables:

CREATE TABLE `devices` (
    `uuid` varchar(256) NOT NULL,
    `token` varchar(256) NOT NULL,
    `last_connected` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY(uuid, token)
);

and

CREATE TABLE `blacklist` (
    `uuid` varchar(256) NOT NULL,
    `cat_id` int(10) NOT NULL
);

The first table uses uuid and token as key, so the token of a device (identified by uuid) may change over time and it will keep track of it changing. The second table may contain 0 to many category_id values for a givenuuid, and if a token is needed it is selected from the devices table, but if the category is listed in blacklist table it should not be in the result.

The tables are filled as follows:

devices:

+----+-----+-------------------+
|uuid|token|last_connected     |
+----+-----+-------------------+
|a   |t1   |2019-02-19T08:10:00|
+----+-----+-------------------+
|a   |t2   |2019-02-19T08:12:00|
+----+-----+-------------------+
|b   |ta   |2019-02-19T08:11:00|
+----+-----+-------------------+

blacklist:

+----+------+
|uuid|cat_id|
+----+------+
|a   |1     |
+----+------+
|a   |3     |
+----+------+
|a   |7     |
+----+------+
|a   |5     |
+----+------+

If I run the following query:

SELECT token
  FROM devices AS ad1
  LEFT JOIN blacklist AS abl
         ON ad1.uuid = abl.uuid
 WHERE last_connected = (SELECT MAX(last_connected)
                           FROM devices AS ad2
                          WHERE ad1.uuid = ad2.uuid)

I get this result:

+-----+
|token|
+-----+
| t2  |
+-----+
| ta  |
+-----+

Which is almost what I want, what I want to add to the query is the possibility to exclude result t2 if I add a restriction that the uuid may not be listed in blacklist for the given cat_id (the is will be inserted by php).

I have tried this query:

SELECT token
  FROM devices AS ad1
  LEFT JOIN blacklist AS abl
         ON ad1.uuid = abl.uuid
        AND abl.cat_id NOT IN (1) WHERE last_connected = (SELECT MAX(last_connected)
                                                            FROM devices AS ad2
                                                           WHERE ad1.uuid = ad2.uuid)

I added the AND abl.cat_id NOT IN (1) part where the 1 is inserted by php via a prepared statement.

The result however is this:

+-----+
|token|
+-----+
| t2  |
+-----+
| t2  |
+-----+
| t2  |
+-----+
| ta  |
+-----+

t2 is in there thrice as opposed to not.

I have tried a few variants of this, but none of them work the way I want it. Any help would be appreciated.


texts: Fetch all rows and leave column value empty if no equality match

texts:
I have a query where I want to fetch all rows where the value in column x is between 400-600.
Including in this I want a matching column value from antoher table.
But I want all 200 rows between 400 and 600 regardless of if the value in the second table column exists for the corresponding column value in the first table.
Oracle SQL:
SELECT h.*, k.customer_id
FROM EVENTS h, CUSTOMERS k
WHERE k.id = h.ident
AND h.eventid BETWEEN 400 AND 600 ORDER BY h.eventid;

This limits the results to where k.id = h.ident is true. But on false I just want an empty value aswell so that I get all 200 rows.
TL;DR - How can I default to a value if Equal to is false (a.b=c.d), instead of filtering out that row?


texts: What happens to replication if rows have been deleted on a target already?

texts:
In a normal replicated world, the following sequence happens:

MAIN ASE: delete N rows issued
RS:       delete N rows sent to replication target
TARGET:   delete same N rows

However, what happens if the following sequence of events occurs?

TARGET:   delete N rows (before main server has them deleted)
MAIN ASE: delete N rows issued
RS:       delete N rows sent to replication target
TARGET:   No such rows to delete?!?!?!

What happens then?

Does the rep server fail? Does the main ASE transaction abort? Does everyone act like this never happened and replication worked (since end result is as if it did)?

P.S. Yes, I know, typically you shouldn't ever pull such a stunt and data should never be changed on replication target. And if not you should use "set replication off" on main server session. The question is "what happens if this does occur, in violation of 'should'".


texts: How to send secure emails using sp_send_dbmail

texts:
When using sp_send_dbmail to send an email on SQL Server, how do you specify that the email be sent in an encrypted format?

I found a thread discussing including the string "[SECURE]" in the subject line, but these comments are 8 years old and not backed up by any source.


texts:
I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, the total rows examined is 10. It is taking over a second to run which is relatively very long. Here is the related information (I've edited certain names for confidentiality):

All related keys are either integers or tinyint.

Query :

SELECT SQL_NO_CACHE COUNT(DISTINCT `applicants`.`id`) FROM `applicants` LEFT OUTER JOIN `groups` ON `groups`.`id` = `applicants`.`group_id` WHERE `applicants`.`completed` = 0 AND `groups`.`c_blk_id` IS NULL AND (groups.c_a_d_id = 71883);

Explain Output:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: groups
         type: ref
possible_keys: PRIMARY,index_groups_on_c_a_d_id,index_groups_on_c_a_blk_id
          key: index_groups_on_c_a_blk_id
      key_len: 5
          ref: const
         rows: 10
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: applicants
         type: ref
possible_keys: index_applicants_on_group_id
          key: index_applicants_on_group_id
      key_len: 5
          ref: groups.id
         rows: 1
        Extra: Using where
2 rows in set (0.00 sec)

Query Profile (Only relevant line. All others are minuscule in relation to time:

QUERY_ID : 3
SEQ : 12
STATE : Sending Data
DURATION : 0.912625
CPU_USER : 0.920057
CPU_SYSTEM : 0.00000
CONTEXT_VOLUNTARY : 54
CONTEXT_INVOLUNTARY : 1
BLOCK_OPS_IN : 136
BLOCK_OPS_OUT : 24
MESSAGES_SENT : 0
MESSAGES_RECEIVED : 0
PAGE_FAULTS_MAJOR : 0
PAGE_FAULTS_MINOR : 0
SWAPS : 0
SOURCE_FUNCTION : exec
SOURCE_FILE : sql_select.cc
SOURCE_LINE : 2376

Any thoughts on this?

Thanks in advance.


texts: Does the order of fields in SELECT query matter when using composite indexing?

texts:
I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?

For example, if I have a multi-column index on [:col_1, :col_2, :col_3], does my SELECT statement need to look like "SELECT * FROM my_table WHERE col_1 = (?), col_2 = (?), col_3 = (?)" in order for the query to be optimized? Or can I specify the params in any order and the query optimizer will handle it?

I've read a few similar answers, but there didn't seem to be a definitive answer that pointed to documentation, and answered relative to indexes. Some answers say "it shouldn't matter" or "effect is negligible".

I'm working with RoR/ActiveRecord on PostgreSQL, but the question is really for any SQL/relational DB.


texts: Unexpected Second Instance of SQL Started

texts:
We had an occurrence of our main production server C:\ filling unexpectedly, and our support guy fixed it by fixing the size of the page file and rebooting.  I'm trying to diagnose why this grew from almost no usage to 100gb over the space of a couple of days.

I'm currently going back through logs, and a Spotlight server we run over it - and I noticed that the server had a second instance of sqlservr.exe started.

Our main instance runs "c:...\sqlservr.exe" -sINSTANCE as a service MSSQL$INSTANCE.

The second instance showing in the task listing shows "c:...\sqlservr.exe" -sINSTANCE, but not as a service.

I can't imagine why a second instance would have started - could it be an agent job, such as our Ola Hallengren scripts working externally, but reporting in our task manager as a second instance?

The thing here is that this is one artifact I'm seeing to diagnose the problem, but in itself it isn't reporting major memory usage - I am seeing regular pagefaults, pagefile growth, etc, starting when this appears however.

This is a curveball that I wasn't expecting and I'd appreciate any help, ideas, thoughts, or other diagnosis points you think I should be looking for.

SQL Server 2014, working on a Windows Cluster.


texts: String comparison without CASE statements?

texts:
Is there a shorter more terse way to do a string comparison in SQL Server without wrapping it in a CASE WHEN ... THEN 1 END? In PostgreSQL, I can do

SELECT c, c='foo'
FROM ( VALUES ('foo'),('bar') ) AS t(c);

In SQL Server, I have to do

SELECT c, CASE WHEN c='foo' THEN 1 END
FROM ( VALUES ('foo'),('bar') ) AS t(c);

In the abstract, is there an easier more SQL-Servery way to throw an expression into boolean context? Or, is there a better way to write strcmp without rolling your own case statement?


texts: Insert escaped double quotes into MySQL JSON field

texts:
I am trying to insert a stringified JSON which looks like this:

'{"test": "string with \"escaped quotes\" does not work"}'

into a MySQL JSON field, but I always get the error:

  SQL Error (3140): Invalid JSON text: "Missing a comma or '}' after an object member."
  at position 24 in value (or column) '{"test": "string with "escaped quotes" does not work"}'.

Any idea what might be causing the issue?

The full INSERT statement is:

INSERT INTO test(json_test)
VALUES ('{"test": "string with \"escaped quotes\" does not work"}');


texts: Manipulating duplicates by merging similar (Is it possiple)


texts: IF NOT NULL then UPDATE else keep the value of the field

texts:
I think I am somehow close to get it work, but for some reason I still get errors.

I have the following UPDATE query:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories' THEN @Accessories
          WHEN 'Description' THEN @Description
          WHEN 'Specification' THEN @Specification
          ELSE Details
END
WHERE DeviceID = 10

The parameters are filled in by different users. The problem I face now is that even when you want to update a single field you still have to fill out with the old data the other parameters. Therefore I would like to set the option IF @parameter IS NULL THEN keep the value that is already stored in the DB. I tried to find some solution and something like the following query seems to be the solution but I can't get it to work:

UPDATE DeviceAttribute
SET Details = CASE Name
          WHEN 'Accessories' IS NOT NULL THEN @Accessories
          WHEN 'Description' IS NOT NULL THEN @Description
          WHEN 'Specification' IS NOT NULL THEN @Specification
          ELSE Details
END
WHERE DeviceID = 10

THE DB is stored in SQL Server 2008

Thanks in advance for the help.

EDIT for clarifying:

The original table looks like this

DeviceID|Name         |Details   |
     10 |Accessories  |earplugs  |
     10 |Description  |protectors|
     10 |Specification|BeatsByDre|

For publishing reasons I made a query to turn the tables. So when the Select query is ran the return table looks like the following

DeviceID|Accessories|Description|Specification|
     10 |earplugs   |protectors |BeatsByDre   |

I created a UI to let system users update the different fields like Accessories, Description,Specification.
The Update works if I update all fields with the query shown in the top. However when I Leave a textbox empty, then I get an error that the @parameter is missing a value. So trying to find a solution to update only the field where is something written. So if @parameter IS NULL then keep the original value in the DB.
I found another solution which is really close to what I want but can't get it to work.
Here is the other solution:
https://stackoverflow.com/questions/9387839/mysql-if-not-null-then-display-1-else-display-0


texts:
We'll be having external auditors coming over to perform a review on our Oracle database. They will be running a tool to perform the review and for that, they require a user ID that can connect to the database and grab information from it.

We have an existing user in the database for this. However, it is a production ID and we cannot afford to have it locked out. We would like to clone / duplicate this ID including its roles and rights.

Is there a way we can do so in Oracle?


texts: Dropped an index and recreated it in PostgreSQL 9.2, not used anymore

texts:
I have a table with 15M rows, with two indexes; one of them is a very simple BTREE on a string column.

While trying to optimize some queries, I created a new partial index on the same column. Using EXPLAIN I noticed that the index wasn't used, so I tried to drop the original full index.

The partial index was still not used (a sequential scan was used instead). After some other tests, I dropped the new partial index and restored the old one. So the situation should be exactly the same as at the start. But the re-created index is not used, not even with the simplest of queries:

SELECT * FROM table WHERE status = 'status'

It still insists to do a full scan. I tried analyze, then vacuum analyze, nothing.
Before recreating it, the same identical index was used. Why did this happen? How can I make PostgreSQL use the index again?

This is the explain:

"Seq Scan on table  (cost=0.00..350340.22 rows=246 width=579)"
"  Filter: ((status)::text = 'partial'::text)"

This is the index:

CREATE INDEX index_table_on_status
  ON table
  USING btree
  (status COLLATE pg_catalog."default");


texts:
I'm trying to follow these steps: Space estimation

to estimate the size of my table that I'm creating. I specified an INITIAL as 1M and using the steps above I have the following estimations:

Rowsize = rowheader  + sum of columns = 3 + 99 = 102
Rowspace = Rowsize + SB2 = 102 + 2 = 104
Num rows per block = FLOOR(7674/104) = 73
Number of blocks = 1000000/8192 = 122

However from here I wasn't sure what to do to finish estimating the table size as I had number of rows per block, row size and row space. I tried the following but I wasn't sure if it was correct:

table size = number of blocks * (number of rows per block * rowspace) = 122 * (73 * 104) = 926224 which is close to the INITIAL I set. I also wasn't sure if I should be even setting an initial and if the estimated table size should be the initial, but then I don't know where I would get the number of blocks from..

I have not created the table and I am trying to estimate it's size before creating it.


texts: Oracle Restore and recover Database on the same host?

texts:
I have DB1 database on Server1 .
I have taken backup.
After that I have created table TEST.
Inserted some values in the TEST table.
Truncated table.
And want to return my TEST table to its previous state.

For this I took backup(datafiles, archive logs, controlfile, spfile)

I don't want to recover whole database, just this table is needed.
It can be done by restoring and recovering database to another host, export table from the new DB and import to the existing DB(DB1).

Let's assume I have just one server... Can I restore and recover database on the same machine with the new database name?

Waiting your suggestions, I will also try to search something...


texts: Asynchronous commit on Postgres over iSCSI with BBU storage

texts:
It is my understanding that it's alright to turn synchronous_commit off if your server has a local, battery backed up storage controller.

In my environment I am on a VM whose block storage is connected via iSCSI, where the iSCSI node is using DRBD and both the primary and secondary nodes have battery backed up controllers.

FWIW, here's the specifics:

PostgreSQL 9.3
CentOS 6.3 (VM)
VMware 5.1
Infiniband QDR transport
SLES 11 iSCSI nodes
DRBD
LSI BBU SCSI controllers with CacheCade Pro 2.0 (in-use).

Can I safely turn off synchronous_commit?


texts: Small sample database for practicing database administration?

texts:
I'm looking for a portable sample database that

can be created from a script
has a simple ER model
has little data for database administration practicing purposes.

I know that there is e.g. Adventureworks, Northwind and pubs sample databases out there, but I'm looking for something smaller.

Any idea where I can find this kind of database?


texts:
How to update a field in a nested JSON document in Mongo?

{
  _id : '123'
    friends: [
      {name: 'allen', emails: [
        {
          video: {
          name:'abc',
          subType:'video',
          status:'active'
              }
        },
        {
          video: {
          name:'abc',
          subType:'video',
          status:'active'
              }
        }
        ]}
    ]
 }

I have tried the following query but I am not able to update the document.

db.users.update ({_id: '123'}, {$set: {"friends.0.emails.0.video.status" : 'Inactive'} })


texts: Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?

texts:
I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the point of insertion on.

I'm looking at using a modified uuid that will be semi-sequential--ie, a scope-related number will be prepended to the uuid in order to provide some gap optimization for data in a common scope.

What would be ideal is to force InnoDB to write rows a certain distance apart based on A) the sequence of known keys, and B) table growth estimates.

A simplified example is this:

Disk w/o this tuning:

|< start of disk
| row 2592
| row 33093
| row 34928
| row 50983
| row 390853
| row 391985
|
| free space ...
|
|< end of disk

Disk with this tuning:

|< start of disk
| row 2592
|
|
|
| row 33093
|
| row 34928
|
|
| row 50983
|
|
|
| row 390853
|
| row 391985
|
|< configurable end of allocated disk
|
| free space ...
|
|< end of disk

In the second case, most writes would not require a re-write. Even if you run out of space between, we could then do a full re-alocation and cut the number of row re-writes down by a considerable factor.

Does something like this exist?


texts: Galera Cluster Setup - Primary and Secondary Site Scenario

texts:
I'm very new to Galera Cluster and is exploring a potential setup with reasonable resiliency to node failure and network failure. Looking at the very bottom part of this documentation, the Weighted Quorum for a Primary and Secondary Site Scenario is quite promising. For ease of reading, I've extracted the setup from the document as follows:

  When configuring quorum weights for primary and secondary sites, use
  the following pattern:

Primary Site:
  node1: pc.weight = 2
  node2: pc.weight = 2

Secondary Site:
  node3: pc.weight = 1
  node4: pc.weight = 1

  Under this pattern, some nodes are located at the primary site while
  others are at the secondary site. In the event that the secondary site
  goes down or if network connectivity is lost between the sites, the
  nodes at the primary site remain the Primary Component. Additionally,
  either node1 or node2 can crash without the rest of the nodes becoming
  non-primary components.

But there seems to be two drawbacks:

If there are two failed nodes and one of them happened to be on the primary site, the quorum will be <= 50% and the remaining two nodes will become non-primary components.
Despite pc.weight is a dynamic option that can be changed while the server is running, flipping between primary site and secondary site requires modification on all nodes, which is a bit troublesome.

So I've come up with another idea in mind - leave the weight as 1 for all nodes, and in the primary site add a Galera Arbitrator. In this case:

The primary site will remain the Primary Component on network issue,
just like the original setup.
The cluster still functions even if two nodes failed.
Flipping between primary and secondary site just require a move of the Galera Arbitrator.

May I know if there's anything wrong with my idea, or if there will be any practical difficulties? Appreciate if you can share your thoughts with me.


texts: Cannot get the new table's FK-constraints in an AFTER ALTER trigger

texts:
I'm trying to create a trigger for AFTER ALTER event on schema's tables which would work with the new FK-constraints, here's my test code:

CREATE OR REPLACE TRIGGER Test_Trigger
AFTER ALTER ON SCHEMA
WHEN (ORA_DICT_OBJ_TYPE = 'TABLE')
DECLARE
  TYPE queryRows IS TABLE OF USER_CONSTRAINTS%ROWTYPE;
  resultRows queryRows := queryRows();
BEGIN
  -- Get all the FK-constraints for the ALTERed table
  SELECT *
  BULK COLLECT INTO resultRows
  FROM USER_CONSTRAINTS
  WHERE Constraint_Type = 'R'
    AND Table_Name = ORA_DICT_OBJ_NAME;

  -- Output all the found constraints
  DBMS_OUTPUT.PUT_LINE('Here are '|| resultRows.COUNT ||' constraints:');
  FOR i IN 1..resultRows.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
        'FK-costraint: '|| resultRows(i).Constraint_Name
      ||' on table '|| resultRows(i).Table_Name
    );
  END LOOP;
END;
/

-- Creating some table
CREATE TABLE People (
   ID        NUMBER(6)    PRIMARY KEY
 , Name      VARCHAR2(31)
 , Parent_ID NUMBER(6)
);

-- Altering the table and adding an FK constraint,
-- the trigger fires here but can't find any new constraints
ALTER TABLE People ADD CONSTRAINT FK_People_People_Parent_ID_ID
                   FOREIGN KEY (Parent_ID) REFERENCES People (ID);

And the output is:

Here are 0 constraints:

In other words, the trigger can't find any new constraints, but there is a new constraint:

SELECT Table_Name, Constraint_Name
FROM USER_CONSTRAINTS
WHERE Constraint_Type = 'R'
  AND Table_Name = 'PEOPLE';

gives this row:

.--------------------------------------------.
| TABLE_NAME | CONSTRAINT_NAME               |
|------------|-------------------------------|
| PEOPLE     | FK_PEOPLE_PEOPLE_PARENT_ID_ID |
'--------------------------------------------'

Although when I tried the same code on AFTER CREATE instead of AFTER ALTER it worked with creating tables for some reason (the FK-constraints were found).


texts: Get correct row version based on combination of dates - query simplification

texts:
In the example below, I need to fetch the row that has the latest data based on a combination of dates. I cannot simply do MAX(insert_date), MAX(update_date) as it does not return the correct data. The way that it works right now is to get the MAX(insert_date) then do a self join to obtain the MAX(update_date) then self join to return the row values.

Is there a better and more efficient way of doing this? The example below only contains 4 rows but in production I will be processing about 1 million rows every few minutes.

Example:

create table #temp (
iud char(1) not null,
id int not null,
date date not null,
value decimal(9,2) not null,
insert_date datetimeoffset not null,
update_date datetime2 not null
);

insert #temp
values
('i', 1001, '2001-01-01', 2, '2001-01-01 00:00', '2001-01-01 00:00'),
('i', 1001, '2001-01-01', 9, '2001-01-01 00:00', '2001-01-01 01:00'),
('i', 1001, '2001-01-01', 7, '2001-01-02 00:00', '2001-01-01 00:30'),
('i', 1001, '2001-01-01', 4, '2001-01-02 00:00', '2001-01-01 00:00');

-- this is wrong as it returns no results
select t.*
from #temp as t
join (select iud, id, date, max(insert_date) as insert_date, max(update_date) as update_date
      from #temp
      group by iud, id, date) as x
on t.iud = x.iud
and t.id = x.id
and t.date = x.date
and t.insert_date = x.insert_date
and t.update_date = x.update_date;

-- this works, but can it be simplified?
select n.*
from #temp as n
join (
    select n.iud, n.id, n.date, n.insert_date, max(update_date) as update_date
      from #temp as n
      join (select iud, id, date, max(insert_date) as insert_date
              from #temp
          group by iud, id, date) as i
    on i.iud = n.iud
  and i.id = n.id
  and i.insert_date = n.insert_date
group by n.iud, n.id, n.date, n.insert_date) as x
on x.date = n.date
and x.insert_date = n.insert_date
and x.iud = n.iud
and x.id = n.id
and x.update_date = n.update_date
order by n.iud, n.id, n.date;

drop table #temp;


texts: How to convince Oracle to create optimal execution plans in SQL queries with bind variables?

texts:
An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans.
For example, I'd like to write SELECT like this:
SELECT * FROM MY_TABLE
WHERE (COL1 = :B1 OR :B1 IS NULL) AND (COL2 = :B2 OR :B2 IS NULL) AND (COL3 = :B3 OR :B3 IS NULL);

Its execution plan should depend on the values of the bind variables, bearing in mind that Oracle CBO creates optimal execution plans for each.
As shown in the following execution plan examples, using OR FROM NULL or NVL results in table level filter predicates instead of index level access predicates.
Is there any other way to parameterize SQL query in Oracle?
Test 1
select * from employees where employee_id = :B1;

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=TO_NUMBER(:B1))

Test 2
select * from employees where (employee_id = :B1 OR :B1 IS NULL);

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     6 |   414 |    35   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |    35   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:B1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:B1))

Test 3
select * from employees where employee_id = NVL(:B1,employee_id);

Plan hash value: 71496665

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |   108 | 14364 |    36   (0)| 00:00:01 |
|   1 |  VIEW                          | VW_ORE_B4851255 |   108 | 14364 |    36   (0)| 00:00:01 |
|   2 |   UNION-ALL                    |                 |       |       |            |          |
|*  3 |    FILTER                      |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES       |     1 |    69 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | EMP_EMP_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  6 |    FILTER                      |                 |       |       |            |          |
|   7 |     TABLE ACCESS FULL          | EMPLOYEES       |   107 |  7383 |    35   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:B1 IS NOT NULL)
   5 - access("EMPLOYEE_ID"=:B1)
   6 - filter(:B1 IS NULL)


texts: Dump all binary columns from a table as files

texts:
I have a table in a postgresql database with a column of type bytea. I want to drop each binary entry as a file using the id as filename.

What would a SQL query look like for this?


texts: Erro MYSQL #1193 - Unknown system variable 'lc_messages'

texts:
Can anyone help me with this error. From several searches on the Net I have not found a solution. I'm using version 7.0.10 of PHP and when I try to log in to phpMyAdmin, the following message appears:

Erro
Comando SQL: Edita Edita
SET lc_messages = 'pt_PT';
Mensagens do MySQL : Documentação
#1193 - Unknown system variable 'lc_messages'

One other detail is I have installed Workbench version 6 and MySQL 5.1.


texts:
Can I nest writeable CTEs?

WITH "parent_cte" AS (
  WITH "child_cte" AS (
     INSERT INTO ....
  )
  ...
)
...

I know that I can refer to a CTE in the FROM clause of another CTE.


texts:
Suddenly today we started seeing non-yeilding scheduler errors on SQL 2008 R2 failover cluster. We are unable to connect so we had to restart the SQL services and everything is back to normal now.

I did some research and found a Microsoft connect item and users reporting this issue but not sure if there was fix and not entirely sure if it's because of backups but i do see log backups running during this time frame
and we do use SQL native compression option for backups.

Microsoft announced a fix for (please see the fix URL below) and we are in SQL 2008 R2 SP3 but they had the fix in SP2. not sure why we are still seeing the issue, based on the cause provided in fix url (This issue occurs because the profiling session is not closed correctly. The profiling session is used to calculate the cost of an object to be cached, and is based on memory usage and CPU usage.), i'm thinking this has something to do with our SQL Sentry profiling session. We use Idera SQL dm for monitoring and we are also testing SQL sentry and i do see some sql sentry login failures around this time frame.

Can anyone shed some light on this ?

Here are the error details:

  Process 0:0:0 (0x1184) Worker 0x0000000110D6A1A0 appears to be
  non-yielding on Scheduler 9. Thread creation time: 13137300580167.
  Approx Thread CPU Used: kernel 327 ms, user 37487 ms. Process
  Utilization 58%. System Idle 38%. Interval: 80545 ms. Process 0:0:0
  (0x1184) Worker 0x0000000110D6A1A0 appears to be non-yielding on
  Scheduler 9. Thread creation time: 13137300580167. Approx Thread CPU
  Used: kernel 483 ms, user 66316 ms. Process Utilization 57%. System
  Idle 39%. Interval: 140747 ms.

Connect Item
FIX for similar issue


texts: postgres read query resample time series data for hourly averages

texts:
I am new to SQL trying to learn how to do read queries on time series data. Can someone give me a tip on how to resample interval time series data to hourly averages on the postgres read query?
My table is named building_data where there are a few columns named time, metric, value, kv_tags, m_tags
time is my date/time stamp column where I am trying to see if I can resample the data in the value column into hourly averages. The WHERE in the query below is to filter out for a specific device that I am interested in looking at the data. I apologize if that doesn't make sense.
For a first timer this sql query appears to work but its not incorporating some process to resample the data in hourly averages, any tips greatly appreciated.
SELECT
  "time" AS "time",
  metric AS metric,
  value,
  kv_tags,
  m_tags
FROM building_data
WHERE kv_tags->'equip_name' = '["35201"]' AND
  m_tags IS NOT NULL
ORDER BY time desc limit 1000


texts: Reference a schema from public table column

texts:
I have a multi-tenant Postgresql database with each schema belonging one of our customers (tenants). Each tenant has a table called Messages. So CustomerX.Messages contains all of the messages that are sent to CustomerX.

All of the messages come in through a generic endpoint and are processed a bit to determine which customer should receive the message. So the message pipeline looks like this from a high level:

RAW MESSAGE (public.Messages)
    |
    |
[PROCESSING]
    |
    |
DECRYPTED MESSAGE (some_tenant.Messages)

I would like to put the "pre-processed" messages into a public table containing the exact, unmodified input to the system. After going through a round of processing, I'd like to drop the final message into the customer's CustomerX.Messages table.

My Question

What is the best way for me to model this? I need to ensure the following:

There is exactly one message in "some" schema for each message in the public table.
There is exactly one message in the public schema for each message in a customer schema.
Given some message (in either the public or customer schema), I can find the other one.


texts: Postgresql: set default psql client encoding

texts:
When I connect to a Postgresql DB using psql, I often get these messages:

=> SELECT * FROM question_view ;
ERROR:  character with byte sequence 0xd7 0x9e in encoding "UTF8" has no equivalent in encoding "LATIN1"

Following this SO answer, I understand that I should change the client encoding accordingly:

SET client_encoding = 'UTF8';

Changing the client_encoding every time I connect to the DB is cumbersome. Is there a way to permanently configure this setting, either in the .pgpass file or anywhere else?


texts: Passing a variable in OPENJSON WITH statement in SQL Server

texts:
Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason statement to read value of FX(last statement of below script). I am not able to pass this as a parameter to make SampleValue dynamic.
I tried using dynamic SQL but did not succeed. Please help.
/*
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'Ole Automation Procedures';
GO

*/
DECLARE   @Object   Int
        , @hr       int
        , @Base     char(3)
        , @Symbol   char(3)
        , @Url      varchar(1000)
        ,@Param varchar(1000)
DECLARE   @json  as table(Json_Table nvarchar(max))

SET @Base   = 'USD'
SET @Symbol ='GBP'

SET @Url = 'https://api.ratesapi.io/api/latest?base=' + @Base + '&symbols=' + @Symbol    ;--Your Web Service Url (invoked)
SET @Param = '$.rates.' + @Symbol;

select @Url;  --https://api.ratesapi.io/api/latest?base=USD&symbols=GBP

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL,'get', @Url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

SELECT  *
FROM OPENJSON((select Json_table from @json), N'$')
WITH (
      SampleValue nvarchar(max) '$.rates.GBP' ,
      SampleDate DATE '$.date'
      ) ;

    /*DECLARE @SQL NVARCHAR(MAX) =
    'SELECT  *  FROM OPENJSON((select Json_table from @json), N''$'') WITH (           SampleValue  nvarchar(max)  ''$.rates.GBP'',  SampleDate DATE ''$.date'' ) ;'

 select @SQL;
 EXEC sp_executesql @SQL;

      DECLARE @SQLstring NVARCHAR(MAX) =
'SELECT  *
FROM OPENJSON((select Json_table from @json), N''$'') WITH (
      SampleValue nvarchar(max) '+ ''''+ @param + ''''+ ' ,
      SampleDate DATE'  + ''''+ '$.date'  + '''' + ' ) ;'

      select @SQLstring ;
EXEC sp_executesql @SQLstring ;*/

EXEC sp_OADestroy @Object


texts: How to print text in select query instead of NULL value in DATE field in postgresql?

texts:
I have a table test as follows :

create table test (id int, enroll date)

I have inserted some rows as follows :

insert into test values (1,'2012-01-02');
insert into test(id) values (2);

Now I want to select all rows and want to print 'Error' where the enroll field that has date datatype has NULL values.
I wrote the following query which gives me error :

select case when enroll is null then 'Error' else enroll end as Enroll from test;

My desired output is :

2012-01-02
Error

Thanks in advance for help.


texts:
is it possible to connect ASP website which is on web server to connect to SQL server 2008 database which is on another database machine? In my case the database machine has windows XP SP1 installed on it, so it is just like desktop machine on the network. Both machines are on the same domain.
If possible how can we setup the connection? What security settings are required on database side? what network settings are required? What code can we use on ASP file?
Is there any test that we can perform with the simple connection string code?

I had used this code

Provider=SQLOLEDB.1;Data Source=myDbServer;Initial Catalog=myDatabase;Uid=myUsername; Pwd=myPassword;
I got this code from the .udl file where test connection works but it does not work when I put it on dataconn.inc file. Why?
I have tried various connection strings but it does not work. I am confuse.

How can I debug the datconn.inc file? it is just the text file. Can I use any other asp file rather than using dataconn.inc? I am conecting to database to check my login credentials and then log into this website and I am getting http 500 error.


texts: Complicated unique constraints in Postgres

texts:
I have a table transactions that belongs to an account (through two foreign keys recipient_id and sender_id). Each account can have many sources through a joint table accounts_sources (a user can link records of an existing source through that joint table into their own source and write their own data on top of it on the joint record).

The field address is a unique identifier used in the source (the source representing one implementation of a blockchain) in the format of 0x558ce31604b6df7f936d48c7932a3a8114dd1ddd985a1e9f1f6707456c7744fc.

I want to make sure that I have no more than one entry for each transactions.address and accounts.address in each source.

How can I add such a constraint to an existing database in Postgres?

CREATE TABLE transactions (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    sender_id uuid NOT NULL,
    recipient_id uuid NOT NULL,
    address character varying NOT NULL
);

CREATE TABLE accounts (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    address character varying NOT NULL
);

CREATE TABLE accounts_sources (
    id uuid DEFAULT uuid_generate_v4() NOT NULL,
    source_id uuid NOT NULL,
    account_id uuid NOT NULL
);

CREATE TABLE sources (
    id uuid DEFAULT uuid_generate_v4() NOT NULL
);

ALTER TABLE ONLY accounts
    ADD CONSTRAINT accounts_pkey PRIMARY KEY (id);

ALTER TABLE ONLY accounts_sources
    ADD CONSTRAINT accounts_sources_pkey PRIMARY KEY (id);

ALTER TABLE ONLY transactions
    ADD CONSTRAINT transactions_pkey PRIMARY KEY (id);

ALTER TABLE ONLY sources
    ADD CONSTRAINT sources_pkey PRIMARY KEY (id);

ALTER TABLE ONLY transactions
    ADD CONSTRAINT fk_rails_27b65ad5c4 FOREIGN KEY (sender_id) REFERENCES accounts(id);

ALTER TABLE ONLY transactions
    ADD CONSTRAINT fk_rails_e3b3f6fd6c FOREIGN KEY (recipient_id) REFERENCES accounts(id);

ALTER TABLE ONLY accounts_sources
    ADD CONSTRAINT fk_rails_3825abd5e3 FOREIGN KEY (source_id) REFERENCES sources(id);

ALTER TABLE ONLY accounts_sources
    ADD CONSTRAINT fk_rails_d1c491b354 FOREIGN KEY (account_id) REFERENCES accounts(id);


texts: Compare 2 products output all difference

texts:
Is it possible to compare using product id in phpmyadmin so after running in mysql it will output any differences between a product id which has been resaved and showing on front end and another which is not - to show exactly what settings and attributes and everything internal as well

Basically, I want to know what difference is for example Product entity_id 1 and entity_id 2 --- from all the tables in the same one database -anything product A has which product B doesn not and any table Product A belongs to and Product B doesnt


texts: How do I run a stored procedure only on newest data loaded?

texts:
My database has a table where the data is loaded from an Excel upload. Then, a stored procedure is run on the data to validate it and insert it to another table.

Currently, my stored procedure will run the validation on all the data again when it's executed.

How do I make it so that it runs the query on the newly loaded data only?

My current stored procedure has the following query:

INSERT INTO dbo.Data_Pass(
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
)
SELECT
   [ACC_NO]
  ,[ORGN]
  ,[DEST]
  ,[CTRY]
  ,[CRNCY]
  ,[PRDCT_NAME]
  ,[PRDCT_CODE]
  ,[DETAILS]
FROM dbo.SOURCE_TBL AS ST
JOIN UPLOAD_DATA AS UD ON ST.ACC_NO = CAST(UD.ACC_NO AS nvarchar)
    AND ST.CTRY = CAST(UD.CTRY AS nvarchar)
WHERE ST.[PRDCT_CODE] = '102'
    AND CAST(UD.[PRDCT_CODE] AS nvarchar) LIKE '%%2'
    AND ST.Val LIKE ('%' + CAST(UD.CorrectionVal AS nvarchar)+ '%');

UPDATE Data_Pass
SET REPORT_TY = 'Correction',
    CHECK_DT = GETDATE(),
    CHECK_SRC = 'Correction Report',
    Data_Pass.STATUS = 'Validated'
WHERE REPORT_TY IS NULL AND CHECK_DT IS NULL AND CHECK_SRC IS NULL

I also have an id field in Data_Pass which is a primary key and is auto incremented.

The loaded data can be around 200,000 or sometimes more in one go, so I'm looking for the most efficient way to do this.

Edit:
Added in my create table statement:

CREATE TABLE [dbo].[Data_Pass](
    [id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [ACC_NO] [nvarchar](50) NULL,
    [ORGN] [nvarchar](50) NULL,
    [DEST] [nvarchar](max) NULL,
    [CTRY] [nvarchar](max) NULL,
    [CRNCY] [nvarchar](50) NULL,
    [PRDCT_NAME] [nvarchar](50) NULL,
    [PRDCT_CODE] [nvarchar](50) NULL,
    [DETAILS] [nvarchar](max) NULL,
    [REPORT_TY] [nvarchar](max) NULL,
    [CHECK_DT] [datetime2](7) NULL,
    [CHECK_SRC] [nvarchar](max) NULL,
    [STATUS] [nvarchar](50) NULL
)

| id  | ACC_NO    |  ORGN | DEST | CTRY |  CRNCY |  PRDCT_NAME  | PRDCT_CODE |  DETAILS |  REPORT_TY   |  CHECK_DT  |      CHECK_SRC      |   STATUS   |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 979 | 900000000 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 980 | 900000001 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 981 | 900000002 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 982 | 900000003 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   15:47.8  |   Correction Report |  Validated |
| 983 | 900000004 |  TYO  |  SIN |  SG  |   SGD  |    NULL      |   A12      |    NULL  |  Correction  |   09:09.0  |   Correction Report |  Validated |


texts:
I have a SQL SELECT statement that has joins on 27 of our warehouse tables.
Original statement was taking more than an hour everyday so I decided to optimize it.

I created some indexes but still it was taking some time. To find out the main culprit, I changed my select statement to SELECT COUNT(*), commented all the joins and started uncommenting the joins one by one.

With some NOLOCKS and more indexes, I managed to bring down the execution time from 65 mins to 6 mins but as soon as I added my columns back in SELECT statement instead of COUNT(*), it started taking more than 70 mins.

So can some one please explain why this is happening and how I can resolve it.

P.S.: I don't have any case statement or any other calculations going on in my select statement. Its purely selecting columns. (around 150 columns)


texts: PgAdmin III - Number of lines returned during insertion

texts:
Not crucial question here, just curiosity.

I am inserting data via multiple scripts in a base running in PostgreSQL.

I wanted to test the time requested to end the insert, so I requested many times the number of lines in the table as follow :

SELECT COUNT(*) FROM "table";

I noticed that everytime the number of line is following a schema : last digits (hundreds, tens and units) are the same as the firsts (millions and thousands).

By example :

if there was around 39k lines, the number of lines returned was 39039,
if there was around 8518k lines, the number of lines returned was 8526518 (8518000 + 8518)

Any idea from where it come? Is it by default? or maybe a personal config?

More info :

Same behaviour on psql.
I am adding 1k inserts by connection to the base, so I don't think it's related.
When the inserts are over, the correct number of lines is returned.
I did enought requests to ensure that's not coincidence.
I am working on Windows.


texts: sys.dm_db_partition_stats - incorrect row count for InMemory tables

texts:
I have a problem with sys.dm_db_partition_stats DMV. When having InMemory tables (with hash indexes, columnstores, classical b-tree) and trying to get row count via this query there is 0 displayed for all InMemory ones:

WITH cte AS
(
SELECT t.name as table_name,
       SUM(s.used_page_count) AS used_pages_count,
       SUM(CASE
            WHEN (i.index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
            ELSE lob_used_page_count + row_overflow_used_page_count
           END) AS pages,
       MAX(s.row_count) AS row_count
  FROM sys.dm_db_partition_stats s
  INNER JOIN sys.tables t
          ON s.object_id = t.object_id
  INNER JOIN sys.indexes i
          ON i.object_id = t.object_id
         AND s.index_id = i.index_id
GROUP BY t.name
)
SELECT cte.table_name,
       CAST((cte.pages * 8.)/1024 AS DECIMAL(10,3)) AS table_size_mb,
       CAST(((CASE WHEN cte.used_pages_count > cte.pages
                THEN cte.used_pages_count - cte.pages
                ELSE 0
              END) * 8./1024) AS DECIMAL(10,3)) AS indexes_mb,
       cte.row_count
  FROM cte
ORDER BY 2 DESC
;

I know that space used by table and indexes for InMemory table cannot be get by this query, but the row count should work.

When you execute just:

SELECT * FROM sys.dm_db_partition_stats

you can see that in row_count column is non-zero row count for all InMemory tables. Is there some bug in Microsoft SQL Server (I tried this on SQL2016SP1 and older versions)? Or am I doing something wrong? All InMemory tables I had created are not empty - so SELECT * displayed proper row count.

Another point - when you add filtering WHERE clause with table name or LIKE which filters InMemory tables only, non zero row count is also returned...


texts: DB select query performance when queried by partial key of primary key

texts: My primary key is composite - {a,b}. Will there be any performance gain if I query only {a} or DB is going to scan the whole table as there is no index only on {a}. How much storage overhead would be, if I add an index for {a}?


texts: setting a foreign key to an empty string

texts:
I'm trying to set a foreign key to an empty string but i keep getting this error.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`msis230`.`deptchair`, CONSTRAINT `deptchair_ibfk_1` FOREIGN KEY (`PROFESSORID`) REFERENCES `professor` (`PROFID`))

this is my code:
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('SJ001', 'MSIS');
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('', 'BIO');
INSERT INTO DEPTCHAIR
VALUES ('JS001', 'PHY');
INSERT INTO DEPTCHAIR
VALUES  ('NS001', 'MKT');
INSERT INTO DEPTCHAIR
VALUES ('', 'ECO');


texts: How to connect remotely to MSSQL database from local, using windows Authentication?

texts: I have to connect remotely to MSSQL database from my local machine, using remote server windows Authentication.


texts: mysqldump with --single-transaction seems to be "fixing" InnoDB tables with page corruption - why?

texts:
I'm trying to find out what exactly is happening here. I've been intentionally corrupting index pages in InnoDB tables, changing bytes in user record areas that actually contain information (largest changed section being 40 bytes so far), then confirming checksum failure with innochecksum.

I go into the mysql command line, execute a few queries to observe that it's crashing, attempt an 'ALTER TABLE ... ENGINE=InnoDB" and observe the failure, then I go back to the shell and confirm one more time that innochecksum still shows a checksum failure on the table.

After all of that, I run "mysqldump --single-transaction db table > dump". It fails of course, with an error like "mysqldump: Couldn't execute 'show table status like 'tablename'': Lost connection to MySQL server during query (2013) ", but then, I run innochecksum again, and the table no longer shows corruption. I jump into MySQL, and I can execute queries on the table without issue. For all intents and purposes, it seems to be completely repaired.

In a test environment, I've done this numerous times (about 20-25 times so far), and where other methods fail, this seems to succeed, and I have no idea why that is. This only seems to occur when I use the "single-transaction" flag. I know that mysqldump is not actually fixing anything (right?), and that InnoDB is likely just recovering the data from the doublewrite buffer or the logs, but WHY is it only doing this when mysqldump --single-transaction is run on the table?


texts: Formal Database Modeling Technologies at Wikipedia

texts:
I don't understand this table from Wikipedia completely:

row1 1      1   one-to-one                               person ←→ weight
row2 0..1   1   optional on one side one-to-one          date of birth ←→ person
row3 0..*   0..* optional on both sides many-to-many     person ←→ book
row4 1      1..* many-to-one                             person ←→ birth place

row2: I think every person has exactly one date of birth. I don't understand this

row4: I think every person has exactly one birth place. I don't understand this.

What am I missing?


texts: Can i view per Database memory usage on SQL Server?

texts:
I have a test server with a number of databases on, which is filling with memory
i would like to analyse which databases are claiming the memory, is this possible?


texts: Query To Show YTD Total Summing Each Month Prior To Current

texts:
I need a query to give a YTD total that should sum each month before it.  For example, January YTD would equal January Total, February YTD would equal January Total + February Total, March YTD would equal January Total + February Total + March Total.  My issue is that my query is showing the SUM() of the Total for all months.
This is DDL that illustrates my issue
Create Table empSales
(
    spendType varchar(100)
    ,spendAmt decimal(10,2)
    ,spendMonth varchar(100)
);

Insert Into `empSales` (`spendType`, `spendAmt`, `spendMonth` ) VALUES
('James', '1.00', 'January'),
('Richard', '3.28', 'January'),
('Barb', '4.13', 'January'),
('James', '3.00', 'February'),
('Richard', '3.28', 'February'),
('Barb', '4.13', 'February'),
('James', '2.00', 'March'),
('Richard', '5.28', 'March'),
('Barb', '7.13', 'March');

And this is the query I tried that only gives the overall total instead of just the YTD total I'm after
with MonthAgg as (
select IFNULL(spendType,'Total') spendType
, Sum(Case when spendMonth='January' then spendAmt else null end ) January
, Sum(Case when spendMonth='February' then spendAmt else null end ) February
, Sum(Case when spendMonth='March' then spendAmt else null end ) March
, Sum(Case when spendMonth='April' then spendAmt else null end ) April
, Sum(Case when spendMonth='May' then spendAmt else null end ) May
, Sum(Case when spendMonth='June' then spendAmt else null end ) June
, Sum(Case when spendMonth='July' then spendAmt else null end ) July
, Sum(Case when spendMonth='August' then spendAmt else null end ) August
, Sum(Case when spendMonth='September' then spendAmt else null end ) September
, Sum(Case when spendMonth='October' then spendAmt else null end ) October
, Sum(Case when spendMonth='November' then spendAmt else null end ) November
, Sum(Case when spendMonth='December' then spendAmt else null end ) December
FROM empSales
GROUP BY spendType WITH ROLLUP)
, mycteSum as (
select spendMonth , Sum(spendAmt) Amt
FROM empSales
GROUP BY spendMonth
)
,mycteYTD as (
select spendMonth , sum(Amt) Over(order by Cast(spendMonth+ ' 01, 1900' as date) ) YTD
from mycteSum)
Select * from MonthAgg
UNION ALL
Select 'YTD' as summarySpend,
max(Case when spendMonth='January' then YTD else null end ) JanuaryYTD
, max(Case when spendMonth='February' then YTD else null end ) FebruaryYTD
, max(Case when spendMonth='March' then YTD else null end ) MarchYTD
, max(Case when spendMonth='April' then YTD else null end ) AprilYTD
, max(Case when spendMonth='May' then YTD else null end ) MayYTD
, max(Case when spendMonth='June' then YTD else null end ) JuneYTD
, max(Case when spendMonth='July' then YTD else null end ) JulyYTD
, max(Case when spendMonth='August' then YTD else null end ) AugustYTD
, max(Case when spendMonth='September' then YTD else null end ) SeptemberYTD
, max(Case when spendMonth='October' then YTD else null end ) OctoberYTD
, max(Case when spendMonth='November' then YTD else null end ) NovemberYTD
, max(Case when spendMonth='December' then YTD else null end ) DecemberYTD
from mycteYTD


texts:
I saw this E-R diagram in an answer on Stack Overflow:

Does anyone know which software was used to create it?

I use LucidChart but that output is really nice!


texts: Using node-postgres and storing required data into postgres tables

texts:
I am new to Postgres and currently using node-postgres and trying to insert the following json object into specific tables:
{
    "job": "Demo One",
    "jobId": "007",
    "jobType": 1,
    "jobGroups": [
        {
            "jobGroupName": "G1",
            "jobTypes": [
                {
                    "jobTypeName": "ABC",
                    "jobTypeId": "1"
                },
                {
                    "jobTypeName": "EFG",
                    "jobTypeId": "2"
                }
            ]
        }
    ]
}

I have the following index.js (node) and route:
const express = require("express");
const app = express();
const cors = require("cors");
const pool = require("./db");

app.post("/build-job", async (req, res) => {
  try {
    const { job } = req.body
    const { jobIdentity } = req.body
    const { jobType } = req.body

    const jobGroupDetails = req.body.jobGroups;

    const newJob = await pool.query(
        "INSERT INTO job_info (job, jobIdentity , job_type) VALUES($1, $2, $3) RETURNING job_id",
            [job , jobIdentity , jobType]
    );
    let jobId = newJob.rows[0];
    res.json(newJob.rows[0]);

    // need to insert data as per below info into job_groups and job_group_types

  } catch(err) {
    console.error(err.messasge);
  }
})

My question is, I now need to use jobGroupDetails which is an array and insert this data and the data within it, into two separate parent-child tables, using the job_id from newJob
job_groups ( jg_id, name, job_id )
job_group_types ( jgt_id, job_type_name, jg_id, job_type_id )

Note: jg_id and jgt_id are PK auto sequences
Unsure how to do this?
I was going to use:
jobGroupDetails.map((jobGroupDetail, i) => ()

but wasn't sure if this was possible within the middleware.
Hopefully someone can assist or put me on the right path.


texts:
I would like use the OFFSET in a SELECT query so I can use pagination in my webapplication. The only problem is I only know the ID of the row from which I would like to start, not the actually OFFSET. An ID looks like this: asdfasfdf894t54yrqewrqew.

So what I do, I SELECT all results from a table and ORDER it by the created_at column and set the LIMIT to a certain value. Now I only know the last ID I get returned, so I would like to start the next page from that ID.

Anyone who knows an easy way to accomplish this using a MySQL query?


texts:
One of my procedures failed because it ran out of temp tablespace. 700 GB was not enough for the procedure. I looked on it, and found it was caused by an inefficient join type, chosen by the optimizer. It works well with nested loops, and is fast enough. So, I disabled the hash and merge joins inside the procedure.
I have a question. If I disabled this hash join, would that change affect other procedures?
Would it affect procedures which calls modified procedure?
And would it affect procedures which are called by the modified procedure?


texts: Will a specific denormalization increase performance in a specific scenario?

texts:
Suppose I have a table with fields | a | b | c | d | ... | z | which contains millions of records and which will serve millions of queries a day. And the field z holds the id of some parameter which can be one of the five possible values. This parameter will never get new values.

What I need is the maximum possible speed and performance of select from this table. All other query types performance does not matter. Disk space doesn't matter much too.

Very important point is that in each select query I will always specify the value of z to only one value ( e. g. where z = 1 ) Also ib the select query I will always filter record by other fields and also I will make some joins.

The question is: will it boost the performance of select to split the table into five tables each corresponding to one of the five possible values of z? I am going to use MySQL database but I would like to hear the general answer if one exists.


texts: Recover a broken or damaged database from the transaction file or "journal"

texts: A greeting fellow, I would like to know if in MySQL (I understand that all engines must have), you can recover from damage to the database using some kind of file "journal" transactions, if so, what is that file? What is the correct procedure to recover it ?. I know that I can recover it from the * .frm files present in /var/lib/mysql, but I would also like to know this other option. And if someone dominates the subject, I will appreciate your comments.


texts: MySQL master-slave replication over an unstable internet link

texts: Connection from our main office to couple remote sites are pretty unstable, if I want to do a slave replication to those remote sites, I might need to increase multiple timeout values, anything else I need to aware? Also, does replication are atomic? what if connection breaks in the middle of getting data? does everything will retransfer from master to slave or slave is unaware of broken pieces?


texts: innodb_page_size is not present in [mysqld] section in my.ini file in MySQL Server 8.0?

texts: I have to edit the innodb_page_size to 32K but I cannot find innodb_page_size in [mysqld] section in my.ini file. I am using MySQL Server 8.0.13. Where can I find it?


texts: DBCC CHECKDB Data Purity errors but no rows found

texts:
Any idea why the value -96.00 is out of range for the normalization_data column (defined as numeric(14,2) )?

DBCC CHECKDB with DATA_PURITY flags this row:

Msg 2570, Level 16, State 3, Line 1
Page (1:807256), slot 0 in object ID 379148396, index ID 1, partition ID 72057594093830144, alloc unit ID 72057594099269632 (type "In-row data"). Column "normalization_data" value is out of range for data type "numeric".  Update column to a legal value.

Page info for slot 0:

PAGE: (1:807256)

BUFFER:

BUF @0x0B826044

bpage = 0x607FC000                   bhash = 0x00000000                   bpageno = (1:807256)
bdbid = 10                           breferences = 0                      bUse1 = 45874
bstat = 0x3c00009                    blog = 0x9ca22159                    bnext = 0x00000000

PAGE HEADER:

Page @0x607FC000

m_pageId = (1:807256)                m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x0
m_objId (AllocUnitId.idObj) = 936    m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594099269632
Metadata: PartitionId = 72057594093830144                                 Metadata: IndexId = 1
Metadata: ObjectId = 379148396       m_prevPage = (0:0)                   m_nextPage = (1:807257)
pminlen = 16                         m_slotCnt = 129                      m_freeCnt = 768
m_freeData = 7166                    m_reservedCnt = 0                    m_lsn = (19457:12767:78)
m_xactReserved = 0                   m_xdesId = (0:0)                     m_ghostRecCnt = 0
m_tornBits = 323122309

Allocation Status

GAM (1:511232) = ALLOCATED           SGAM (1:511233) = NOT ALLOCATED
PFS (1:800712) = 0x40 ALLOCATED   0_PCT_FULL                              DIFF (1:511238) = NOT CHANGED
ML (1:511239) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 54

Record Type = PRIMARY_RECORD         Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x69A5C060

00000000:   30001000 53595302 80250000 00000000 †0...SYS..%......
00000010:   0500e003 0024002b 00360041 43482046 †.....$.+.6.ACH F
00000020:   4f4f4453 30342d32 30303534 36303030 †OODS04-200546000
00000030:   20303233 3130†††††††††††††††††††††††† 02310

Slot 0 Column 0 Offset 0x4 Length 3

company_id = SYS

Slot 0 Column 1 Offset 0x1b Length 9

normalization_set_id = ACH FOODS

Slot 0 Column 2 Offset 0x24 Length 7

time_period_id = 04-2005

Slot 0 Column 3 Offset 0x2b Length 11

code = 46000 02310

Slot 0 Column 4 Offset 0x7 Length 9

normalization_data = -96.00


texts: Update fields with multiple values from different tables in MySQL

texts:
I have 3 tables:

Product_categories (created from a .csv import). The limit number of categories in subcategories is 4.

product_reference | category_id | subcategories

REF.001           | 1           | 1/2

Categories (created from a .csv import)

id | name

1  | example_category_1
2  | example_category_2

Importer* (created to store all the data I need from the previous tables)

sku     | category

REF.001 | null

How could I update Importer to show the following? The limit number of categories in subcategories is 4.

sku     | category

REF.001 | example_category_1>example_category_2

*Data in Importer would be to export in .csv and be able to import in WooComerce, this is why I need to import the categories by this way. https://docs.woocommerce.com/document/product-csv-import-suite-column-header-reference/#section-4


texts: Mongodb return Error : $cursor stage :: caused by :: operation was interrupted

texts:
In recently, i meet a problem with mongodb. After i surveyed on google, there is no good result unfortunately. So i really wish anyone could answer it.

As title, there is a kind of error message in my mongo.log. The error
 occasionally occurs while i query which using aggregate with $group on secondary member. And i found that the error usually happens with the queried data is more lager than others.

The specification of vm machine is following,

cpu core : 16
ram : 64 gb
disk : 500gb

  Error Message :     aggregate { aggregate: "AAA", pipeline: [ {
  $match: { _id: { $gte: ObjectId('5cdd97100000000000000000'), $lt:
  ObjectId('5cdda5200000000000000000') } } }, { $group: { _id: "$gtn",
  pid: { $first: "$pid" }, oid: { $first: "$oid" } } } ], allowDiskUse:
  true, cursor: {}, lsid: { id:
  UUID("f57cce95-3b14-4467-8c10-853b8595ffe1") }, $clusterTime: {
  clusterTime: Timestamp(1558036863, 4160), signature: { hash:
  BinData(0, 7B2CC773CD95E611B6466D84CD39CB2CF48E1773), keyId:
  6665898640503996417 } }, $db: "db1", $readPreference: { mode:
  "secondary" } } planSummary: IXSCAN { _id: 1 } numYields:71781 ok:0
  errMsg:"Error in $cursor stage :: caused by :: operation was
  interrupted" errName:CursorKilled errCode:237 reslen:277 locks:{
  Global: { acquireCount: { r: 72599 } }, Database: { acquireCount: { r:
  72598 } }, Collection: { acquireCount: { r: 72598 } } }
  protocol:op_msg 101051ms

Thank you for patient.