texts: How to scale MySQL transaction wrapped queries?

texts:
So I'm working on an app that wraps many of it's SQL queries in a transaction for it's SQL usage and I'm trying to think of some ways to scale the DB layer up....

Up until now we've been throwing hardware at the problem, slowly upgrading our Rackspace cloud servers...but we're reaching the upper echelons of their cloud server offerings.

I'm thinking the standard R/W split won't work for us because the transaction gets stuck to a single server eliminating the benefits of the R/W split. (Is that right?)

I've looked at dbShards and ScaleBase both of which look like interesting solutions to the problem, but as a cash strapped startup we'd prefer to use something OSS or devote some man hours creating a sharding strategy instead.

So I suppose the question boils down to, are Galera/MySQL Cluster and sharding my only two options for scaling this setup?

EDIT

To answer some more questions...

We manage a great deal of financial and inventory related data, for this reason we wrap most of our SQL traffic in a transaction so it's all or nothing.

We have ~100 tables in our DB with ~30 of them getting a great deal of use. The most commonly used tables have anywhere from from 15MM to 35MM rows.

Our transactions span across 20-30 tables with joins between ~10 of them. The queried tables are rather large and we have made some inroads with respect to query optimization and proper indexing, but no one is a fully fledged DBA in house....closest is me haha

A lot of our DB traffic is machine generated, we do a lot of syncing with third party data sources. As such, most of the SQL queries are wrapped in a transaction to ensure data consistency when performing all the calculations and syncing with the third parties (and to allow for a rollback if the link to the third party gets disrupted)

Based off of our cacti monitoring it looks like a great deal of our SQL traffic is reads (~85%R to ~15%W)

Just as a note, we haven't hit too much of a bottleneck with our current cloud server but I'm doing my due diligence ahead of time to make sure we don't run into the wall without a plan.


texts: What are the best practices regarding the slow query log? Should it be active in production environment? If so, what would be a reasonable long query time? I know its different for every application, but on general, what would you set it in a data warehouse application with a mysql database of 15gb? Thank you very much


texts: Redshift: How to fix serializable isolation violation(1023) caused by concurrent MERGE operations?

texts:
My use case is to extract, transform and load data incrementally  and in real time from x number of Lambda functions. I expect multiple Lambda functions to be running concurrently and Redshift to stay alive for read queries.

Since Redshift doesn't enforce primary key(s) constraints, I'm using aws documentation Merge examples - Example of a merge that replaces existing rows to enforce unique rows. This method works fine when there is only 1 instance of lambda function running.

-- Start a new transaction
begin transaction;

-- Delete any rows from SALES that exist in STAGESALES, because they are updates
-- The join includes a redundant predicate to collocate on the distribution key
–- A filter on saletime enables a range-restricted scan on SALES

delete from sales
using stagesales
where sales.salesid = stagesales.salesid
and sales.listid = stagesales.listid
and sales.saletime > '2008-11-30';

-- Insert all the rows from the staging table into the target table
insert into sales
select * from stagesales;

-- End transaction and commit
end transaction;

-- Drop the staging table
drop table stagesales;

But as soon as > 1 lambda functions are running concurrently and accessing the same table, I'll receive:

"ERROR: 1023 DETAIL: Serializable isolation violation on table in Redshift" when performing operations in a transaction concurrently with another session.

How should I modify this example to allow it to run in a concurrent environment?


texts: SQL Server Database backup regressed to historical date

texts:
I have the following code snippet that takes backup of my production database every night -

use My_database
go
checkpoint
go
use [My_database]
go
DBCC SHRINKFILE (N'my_db_log', 500)
go
backup database My_database
to disk = 'W:\...\My_database._dmp1.bak'
, disk = 'W:\...\My_database._dmp2.bak'
... ... ...
, disk = 'W:\...\My_database._dmp30.bak'
with stats = 10
go

When I'd tried restoring the database to a test server I identified the backup was regressed to a historical date, as identified from different application related tables. This was not in sync with the date backup had been actually generated.

I am actually a Java / Java EE developer and not very versatile with SQL Server, any help in this area will be much appreciated. This database runs SQL Server 2008R2.

There was no latency, manually the files were copied 5 files at a time, was complete in 30-45 minutes. The command used for restoration was:

RESTORE DATABASE [MY_TESTDB]
FROM DISK = N'W:\...\My_database._dmp1.bak',
DISK = N'W:\...\My_database._dmp2.bak', ... ... ...,
DISK = N'W:\...\My_database._dmp30.bak'
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

Also this backup had run last time on 23 Oct 2015 but the restored test DB got updated only till 31 Jul 2015.

There was only one backup in each backup device. I would hardly believe there had been a problem with restoration, as it was refreshed to multiple databases by others with DBA knowledge, but result was always 31 Jul 2015.


texts:
I have a query for a custom opencart store and I am doing something wrong with the LEFT join as because of the subquery on some products I am loosing the Product Name. I am not sure why. Any suggestions?

SELECT
    w0.name AS 'Product Name',
    p.sku AS 'Product(sku)',
    p.product_id AS 'product_id',
    p.parent_id AS 'parent_id',
    COALESCE(w0.quantity, 0) AS 'Denmark - main'
FROM
    oc_product p
        LEFT JOIN
    oc_warehouse_product wp ON (p.product_id = wp.product_id)
        LEFT JOIN
    oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
        LEFT JOIN
    (SELECT
        pd.name, wp.quantity, p.sku
    FROM
        oc_product p
    LEFT JOIN oc_warehouse_product wp ON (p.product_id = wp.product_id)
    INNER JOIN oc_warehouse_locations wl ON (wp.warehouse_id = wl.warehouse_id)
    LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id)
    WHERE
        wl.warehouse_name = 'Denmark - main') AS w0 ON p.sku = w0.sku
GROUP BY p.sku
ORDER BY 'Product Name'
LIMIT 0 , 20


texts: How to split a string to and get info from another table in mysql

texts:
I have a system to assign score for each sentence stored in a column:

TABLE 1: word_scores

word       score
this       3
is         4
a          5
test       1
another    0
sentence   8

TABLE 2: sentences (score column is calculated from TABLE 1)

sentence           score
this is a test     13
another sentence   8
this is            7

Now I need to
1. fetch each sentence into PHP,
2. then split the string to words,
3. then catch the score for each word,
4. then calculate the sum of word_scores

Is it possible to calculate the sentence score within mysql?


texts: SQL Server let the sql client decide the language (or force it)

texts:
I have 2 SQL Server Management Studio clients, one in French, one in US English.

I have 2 database servers, that I suppose are configured absolutely identical, but it looks like it's not the case !

select @@LANGUAGE

from the French Management Studio on server 1 returns us_english.

select @@LANGUAGE

from the English Management Studio on server 1 returns us_english.

select @@LANGUAGE

from the French Management Studio on server 2 returns français(french).

select @@LANGUAGE

from the English Management Studio on server 2 returns us_english.

How can this be ?

Is there a parameter somewhere on the server that says server 1 does not care about the client language, but server 2 does ?


texts: How to ensure no duplicate records are added when using multiple Node JS processes

texts:
We are working on an appointment scheduler. We have a table Appointment that stores an appointment with starttime, endtime, assigneeId and eventId.
We are using Bookshelf.js as an ORM with Node JS. The problem we are facing is if we send parallel thread requests using JMeter for the same combination of starttime, endtime, assigneeId, eventId and our app ends up booking an appointment for both the threads which is incorrect.
Only one of them should succeed and other one should fail. How do we go about solving this problem?
CREATE TABLE `Appointment` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vendor_Id` int(10) unsigned NOT NULL,
  `account_Id` int(10) unsigned NOT NULL,
  `event_Id` int(10) unsigned NOT NULL,
  `assignee_Id` int(10) unsigned NOT NULL,
  `starttime` datetime NOT NULL,
  `endtime` datetime NOT NULL,
  `book` tinyint(4) DEFAULT '0',
  `code` varchar(20) DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `deleted_By_Id` int(10) unsigned DEFAULT NULL,
  `updated_Appointment_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_Appointment_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `appointments_code_unique` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=106 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

What we do is before inserting we see if any overlapping appointments exist in the DB. Iif not, then we add it but with multiple Node JS processes: both of the processes do not find any appointment, and both end up inserting.
First Solution :  INSERT IGNORE but it's difficult in our case to define a Unique key based on starttime and endtime as we need to check for overlapping values as well.
Second Solution: Let both of them insert the record in the Appointment table and after the transaction is committed we check for overlap - if we find an overlap we delete the record which was added later. This will result in a waste of computation as well as ids.
Third Solution: Use an App Level Queue to manage all requests to the Appointment table and only 1 process will resolve all Queue messages. I wonder how this approach will scale.
How do I ensure at the time of insert that only one thread can book an appointment safely? Will a stored procedure be helpful? We are using transactions provided by Bookshelf.js


texts: How to step through linked servers after failing to connect to one

texts:
I would like to change my stored procedure so it continues executing after failing to connect to a linked server. How could I write this stored procedure better?

I am using TRY...CATCH to query servers that I am creating from a list to track transaction log backup history.

ALTER PROC [dbo].[spLogBackup]

AS

TRUNCATE TABLE dbo.tLogBackup

DECLARE servers_cursor CURSOR
FOR
select  distinct LinkedserverName
from dbo.Environment
join master..sysservers on srvname COLLATE DATABASE_DEFAULT  =
LinkedserverName COLLATE DATABASE_DEFAULT
where LinkedServerName not in ('TestDB')

and ServerUse in ('PROD', 'DR')
and IncludeInstats = 1
order by LinkedServerName

OPEN servers_cursor
DECLARE @Servername varchar(250)

DECLARE @sql varchar(8000)

FETCH NEXT FROM servers_cursor INTO @ServerName
WHILE (@@FETCH_STATUS = 0)
BEGIN TRY
--Looping throught the servers.
set @sql = 'insert into dbo.tLogBackup (servername, rundate, runtime,
jobname, Status, CurrentStatus, enabled) select ''' + @ServerName +
''',last_run_date, last_run_time, name, last_run_outcome,
 current_execution_status, enabled
 from openquery([' + @ServerName + '],''exec
 msdb.dbo.sp_get_composite_job_info'' )'
 print @ServerName
exec (@sql)
--SELECT (@sql)
FETCH NEXT FROM servers_cursor INTO @ServerName
END TRY

BEGIN CATCH
SELECT
    ERROR_NUMBER() AS ErrorNumber
    ,ERROR_SEVERITY() AS ErrorSeverity
    ,ERROR_STATE() AS ErrorState
    ,ERROR_PROCEDURE() AS ErrorProcedure
    ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

CLOSE servers_cursor
DEALLOCATE servers_cursor


texts: Does Oracle cache queries when using a database link

texts:
Some vendor told us that we should not use database link between our database and theirs as the queries executed over a database link would not be cached, nor optimized.

I can't find any information confirming/infirming this.

Naively I would not see why Oracle would not be able to do query caching when using a db link.


texts:
What are the advantages of Mnesia over major SQL database implementations and how it differs to them?

Can I use the database to hold really huge amounts of data without noticeable performance degradation ?


texts: How can one calculate percentage from previous week in aggregate query?

texts:
I have a challenge I set out to do that seemed initially trivial. Not so for my developper brain.

Consider the following simple view, used to validate a cron I wrote that queries a subset of 200 000 statements every saturday.

It goes as follows:

mysql> SELECT
    ->     DATE_FORMAT(s.created, "%Y-%m-%d") as "Date",
    ->     count(s.id) AS "Accounts credited",
    ->     sum(s.withdrawal) "Total Credited"
    ->   --  100 * (sum(s.withdrawal) - sum(prev.withdrawal))
         --   / sum(prev.withdrawal) "Difference in %"
    ->     FROM statements s
    -> --    LEFT JOIN prev
    -> --        s.created - interval 7 DAY
    ->  --     ON prev.created = s.created - interval 7 DAY
        --     AND (prev.status_id = 'OPEN'
        --     OR prev.status_id = 'PENDING')
    ->     WHERE (s.status_id = 'OPEN' OR  s.status_id = 'PENDING')
    ->     GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created)
    ->       ORDER BY s.created DESC
    ->     LIMIT 8;

+------------+-------------------+----------------+
| Date       | Accounts credited | Total Credited |
+------------+-------------------+----------------+
| 2019-01-19 |             18175 |        3173.68 |
| 2019-01-12 |             18135 |        4768.43 |
| 2019-01-05 |             17588 |        6968.49 |
| 2018-12-29 |             17893 |        5404.18 |
| 2018-12-22 |             17353 |        7048.18 |
| 2018-12-15 |             16893 |        7181.34 |
| 2018-12-08 |             16220 |        9547.09 |
| 2018-12-01 |             15476 |        7699.59 |
+------------+-------------------+----------------+
8 rows in set (0.79 sec)

As is, the query is efficient and practical.  I merely would like to add a column,  difference in percentage, from previous week's total, as seen with the -- commented out code.

I have tried various approaches, but because of the GROUP BY, adding an inline column to get the sum(withdrawal) of previous week makes the query run ... forever.

I then tried the LEFT JOIN approach, but this has the same problem, Obviously.  I think the added JOIN has to fetch the sum of previous week for every row of the outer select.

I then had the (not so smart) idea of querying my view, even but then it seems I would have the same issue.

I assume there are much more optimal approaches out there to this simple task.

Is there an elegant way to calculate a percentage from such a query?

Would a stored procedure or some other 'non-plain-sql' approach be more optimal?


texts: Are there any reasons to not use smallint when it fits the data?

texts:
Since PostgreSQL doesn't have 1-byte tinyint, the second best option would be smallint. However, I've read from various posts that it may actually be slower because CPU's are optimized to operate on 32-bit integers, or there may be implicit conversions to 32-bit integers.

Besides these reasons, are there any other reasons I'm not yet aware of for not using integers smaller than 32-bits?


texts: Determining that Indexes on a table are unused

texts:
I’ve been running this script to try to find extraneous indexes

select o.name as TableName, i.name as IndexName, p.reserved_page_count * 8.0 / 1024 as     SpaceInMB, s.*
from     sys.dm_db_index_usage_stats s
inner join sys.objects o on s.object_id = o.object_id
inner join sys.indexes i on i.index_id = s.index_id and i.object_id = o.object_id
inner join sys.dm_db_partition_stats p on i.index_id = p.index_id and o.object_id =      p.object_id
where o.name = ‘TableName’

I know that when last_user_seek/scan/lookup are all null, that no users have used the index since last restart.  But I’m wondering what system_scans/lookups/seeks …are?  Because on a certain table I found 5 that had no user activity, but one had system activity 10 days ago.  Do anyone have any insight on what system scans/seeks/lookups might be?  These tables seem really over-indexed and I’d like to trim the fat.


texts: ssrs expression to display first 3 parameter values if "ALL" is selected in Parameter List

texts:
I have a parameter to display a report, if the parameter is selected as "Select All" then all the values in the parameter list are selected.

But I need the report to display values only for the first 3 parameter values selected.

Is there any filter expression available for this?


texts: Find out what encryptor name my database is using

texts:
I want to find what encryptor name my database is using to encrypted. This is database encryption thing.

This is the syntax I see up site of Micosoft.

CREATE DATABASE ENCRYPTION KEY
       WITH ALGORITHM = { AES_128 | AES_192 | AES_256 | TRIPLE_DES_3KEY }
   ENCRYPTION BY SERVER
    {
        CERTIFICATE Encryptor_Name |
        ASYMMETRIC KEY Encryptor_Name
    }
[ ; ]

Now I don't know if a symmetric key or a certificate is used to encrypt database.
The encryptor_name for one encrypted with certificate is I find in sys.certificates of master database. It is name of the row display in sys.certificates

What about the ASSYMETRIC KEY. Can you give give me examples to encrypt database with it, then the script to display information of the encryptor_name that it use. (my English is not good. Please suffer me).


texts:
We recently increased sga_max_size from 2g to 4g but didn't increase sga_target. Is it OK not to increase sga_target? The reason we wanted to increase sga is to reduce wait time on sequential reads in our db. Should we increase sga_target too?

Below is what we currently have.

NAME           TYPE       VALUE          DISPLAY_VALUE
sga_max_size    6         4194304000     4000M
sga_target      6         1996488704     1904M


texts: How to use Limit in query on my index column without scan all rows?

texts:
Here is my table :

In my table

Clustering_key (Primary key and auto incremental)
ID (Index Column)
Data (Text datatype column)
Position(Index column) maintain the order of Data

My table have 90,000 rows with same ID equal to 5. I want to fetch first 3 rows and my query like this

Select * from mytable where ID=5 Limit 3;

ID column is index column So I think mysql scan only first 3 rows but mysql scan around 42000 rows.

Here Explain query :

Any possibility to avoid all rows scan.

Please give me some solution

Thanks in advance


texts: SQL Server 2008 R2 - Change distributor schedule

texts:
We have configured SQL Server 2008 R2 replication. Distributor is configured in continuous mode, publisher is scheduler is set to execute after every six hours.

Now we want to change distributor schedule from continuous to schedule frequency without re-creating new replication.

We have tried below stored procedure, but it throws an error. Please help to distributor settings.

EXEC sp_changepublication
@publication = 'your_publication_name',
@property = 'allow_anonymous',
@value = 'false'
GO

EXEC sp_changepublication
@publication = 'your_publication_name',
@property = 'immediate_sync',
@value = 'false'


texts:
I have a whole bunch of views that I need to create. Many of these views depend on other views.

If I create a view that depends on a view not yet created, I get an Invalid Object error.

Rather than go through the whole lot and work out the dependencies and then deal with creating them all in the correct order, is there a way I can just switch this checking off until all the views are created?

It is possible to delete a dependant view after the view is installed, so it is possible for the database to be in a state where a view exists that depends on a non-existent object. I need it to accept being in this state during creation..


texts: Mysterious missing transactional replication tokens

texts:
I am looking at implementing Transactional Replication subscriptions in an AlwaysOn AvailabilityGroup. I tried to use this as a guide:

https://www.simple-talk.com/sql/backup-and-recovery/expanding-alwayson-availability-groups-with-replication-subscribers/

The reason I say tried is not in anyway besmirching the author - in this case he is dealing w/ push subscriptions and we are doing pull. There was a spectacular flame out trying to Initialize from LSN (The transactions required for synchronizing the subscription with the specified log sequence number (LSN) are unavailable at the Distributor. Specify a higher LSN.
) (perhaps b/c this is a pull subscription and it moved on, whereas in the example the push agent was disabled and then brought up on the now Primary?). Following this I just 're-initialized' the subscription (it is not that big a publication).

Anyhow, now Replication Monitor, which from experience can be an unreliable narrator up there with the greats of literature, tells me this subscription's performance is 'Excellent' and that it was last synchronized 1 minute ago as of this writing, yet when I try to insert Tracer Tokens to get a feel for latency this subscription is nowhere to be seen. In other areas of Replication monitor or checking out the SQL Server Agent jobs there are no big oddities.

What is going on? Is using pull as opposed to push subscriptions not advised in this scenario? I am somewhat befuddled at the moment and any enlightenment would be welcomed and appreciated.


texts: How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app

texts:
What is the syntax for a stored procedure that outputs a refcursor, a varchar2, and a number?

I would like to output all three in the result, for the purpose of displaying count, and a message which indicates a successful query in addition to the sys_refcursor output but I cannot seem to find a good example of it.

My current C# code looks like this:

using Oracle.DataAccess.Client;
//...

OracleCommand myCmd = new OracleCommand();
myCmd.Connection = myConnection;
myCmd.CommandText = "mypackage.myprocedure"
myCmd.CommandType = CommandType.StoredProcedure;
OracleParameter param0 = myCmd.Parameters.Add("myinput", OracleDbType.Varchar2, myinput, ParameterDirection.Input);
OracleParameter param1 = myCmd.Parameters.Add("myretcursor", OracleDbType.RefCursor, ParameterDirection.Output);

myConnection.Open();
OracleDataReader resultReader = myCmd.ExecuteReader();
mydatatable.Load(resultReader, LoadOption.OverwriteChanges);

and the Oracle stored procedure looks like this:

    PROCEDURE myprocedure (myinput IN Varchar2, myretcursor OUT sys_refcursor)

    IS

    BEGIN
    OPEN myretcursor FOR
    SELECT * FROM MYTABLE

    END myprocedure

I guess I want to add param2, param3 to the the c# as additional output parameters, then also somehow also output them in the stored procedure?  I was not sure how to do this and kept encountering errors.  Ideally I would, for exmple, pass the select count (*) as output into the c# application, as well as a varchar2 string message like 'Query Successful'.

Thank you.

UPDATE:
Vincent's solution works below... regarding the C# code behind, the answer to the link he provided recommends enclosing the reader = executereader in a USING( ) for loading the table, and closing the reader before loading the other variables.

https://stackoverflow.com/q/6882690/613799

Depending on the size of the string from oracle, the calling c# code may need to specify the size of the varchar2 in the output parameter definition for the string message.  Finally the following link was helpful for type casting the count from OracleDecimal to C# int:

https://stackoverflow.com/q/3026036/613799


texts: How does Differential backups knows about full backup

texts:
In my environment we have SQL Servers in Azure IAAS. We use netbackup as backuptool. we have an issue where differential backups were failing with the error saying that.

cannot find the Full backup.

So when investigated, azure VM level backups are running which is affecting the backup chain in the database level. For testing purposes I stopped the SQL VSS writer on a server and the VM level backups did not affect the database Level backups. Differential with netbackup was successful. But now I saw there is a native job (Ola helengren-not copy_only) backing up the databases but it didn't cause any problem to the Netbackup differential backups. I even tested it clearly taking a full backup through a native job and taking a differential through netbackup.
How come?


texts: Sync mysql database between 2 geo locations (datacenters)

texts:
We have a database cluster built on Percona MySQL. We are going to extend our redundancy with another database cluster with a new data center located in another GEO location. So that in case of failures in one location, we can switch the service to other location.

What is the right technology to keep data in both of GEO sites synced always?


texts:
I want to do same commande shell CLi into a script

Command shell without script (CLI)

su - oracle
export ORAENV_ASK=NO
export ORACLE_SID=HRPRD
. oraenv
env | grep ora

output:

USER=oracle
**LD_LIBRARY_PATH**=/u01/pe/oracle/u/25/bas/lib
ORACLE_BASE=/u01/pe/oracle
MAIL=/var/spool/mail/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/u01/pe/oracle/u/25/bas
PWD=/oracle
HOME=/oracle
LOGNAME=oracle
**ORACLE_HOME**=/u01/pe/oracle/u/25/bas/

My script

rr=`su - oracle`
export ORAENV_ASK=NO
export ORACLE_SID=MySID
uu=`. oraenv`
searchOraHome= `env | grep ora`
echo $searchOraHome

output:

USER=oracle ORACLE_BASE=/u01/app/oracle
PATH=/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/oracle/bin
MAIL=/var/spool/mail/oracle PWD=/oracle HOME=/oracle LOGNAME=oracle

Why are the variables ORACLE_HOME and LD_LIBRARY_PATH not present with the script shell?

Is the line:

uu=`. oraenv`

... not correct?


texts: Adding a default constraint in Oracle while altering a column

texts:
I'm new to Oracle. I need to port a SQL Server command to Oracle.

I want to alter a column to add a new constraint with a default value.

SQL Server command

ALTER TABLE <schema_name>.<table_name>
ADD CONSTRAINT [<constraint_name>]
DEFAULT (1) FOR [<column_name>]

Oracle command

ALTER TABLE <table_name>
ADD CONSTRAINT <constraint_name>
DEFAULT (1) FOR <column_name>

I get the following error when running the Oracle query:

Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

It might be because FOR is an invalid identifier?

But then how do I add a constraint with a default value for a specific column?

Do we need to alter a column and set the default value?

Which is the right way to do this?


texts: Error in creating a database dump on PostgreSQL

texts:
When trying to make a postgreSQL database dump we got the following error and the process stops immediately.

Command used:

openbravo@master.akluck.com:~

07/26 11:48:11> pg_dump -U tad -h localhost -p 5932 -F c -b -v -f /home/openbravo/dump26072018.dmp openbravo

Output:

pg_dump: reading schemas

pg_dump: reading user-defined tables

pg_dump: schema with OID 67046 does not exist

pg_dump: *** aborted because of error

Can anyone guide me how to sort this issue?

Update:

I followed this tutorial

http://www.aukema.org/2011/06/fixing-complex-corruption-in-my-dna.html

And I can see there are objects without a schemaname in the pg_tables.

But I don't know how to update those missing schemanames in the pg_tables. The last part of the tutorial is not quite explanatory. Hope someone can throw some light.


texts: Non Super User cannot alter stored procedures and functions

texts:
I am running into an issue granted a user access to alter stored procedures and functions within MySQL 5.7.22-0 running on Ubuntu.

User has ALL PRIV on the database except GRANT
User has SELECT PRIV on mysql.proc
User can DROP and CREATE --> they can not ALTER

Here is the output from SHOW GRANTS

SHOW GRANTS FOR 'userA'@'%'

GRANT USAGE ON *.* TO 'userA'@'%'
GRANT ALL PRIVILEGES ON `mydatabase`.* TO 'userA'@'...
GRANT SELECT ON `mysql`.`proc` TO 'userA'@'%'

Output for SHOW CREATE PROCEDURE on a specifc pro

SHOW CREATE PROCEDURE mydatabase.spAddEmailListContact

spAddEmailListContact   NO_AUTO_VALUE_ON_ZERO

CREATE DEFINER=`userB`@`%` PROCEDURE `spAddEmailListContact`(
        IN `EmailListID` INTEGER(11),
        IN `CustID` INTEGER(11),
        IN `ContactID` INTEGER(11))
    MODIFIES SQL DATA
BEGIN
INSERT INTO
  emaillistcontacts
(
  EMAILLIST_ID,
  CUST_ID,
  CUSTCNT_ID
)
VALUES (
  EmailListID,
  CustID,
  ContactID
);
END utf8mb4 utf8mb4_general_ci  latin1_swedish_ci

Any thoughts on what I am missing?

Note: A key requirement is to allow SEVERAL developers to edit procedures without requiring them to be super users.  (this is so simple to do in mssql :-/ it surprises me that this is difficult in mysql)


texts:
I am facing one issue in getting student name who scored MAX marks in each subject.

Here are my tables below

First one is Student table having STUD_ID , STUD_NAME & Second one is MARKS table having STUD_ID, SUBJECT, MARKS.

Can you help me in getting query to retrieve STUD_NAME, SUBJECT, MAX(MARKS).
I having tried below but facing issue in getting STUD_NAME.

select SUBJECT, MAX(MARKS) as Highest
from MARKS inner join STUDENT
ON STUDENT.STUD_ID = MARKS.STUD_ID
GROUP BY SUBJECT


texts: Linked Server from On-Premise to Azure SQL Database

texts:
I am using:
SSMS-18.4
SQL Server-SQL 2019 CU3
Windows 10

I was able to create a linked server successfully from on-premise SQL 2017 to the Azure SQL database without exposing my password.

--Read the password from text file
DECLARE @password VARCHAR(MAX)
SELECT  @password = BulkColumn
FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x

--Drop and create linked server
IF EXISTS(SELECT * FROM sys.servers WHERE name = N'AzureDB_adventureworks')
EXEC master.dbo.sp_dropserver @server=N'AzureDB_adventureworks', @droplogins='droplogins';
EXEC master.dbo.sp_addlinkedserver
 @server = N'AzureDB_adventureworks',
 @srvproduct=N'',
 @provider=N'SQLNCLI',
 @datasrc=N'ugdemotargetserver.database.windows.net',
 @catalog=N'adventureworks';

EXEC master.dbo.sp_addlinkedsrvlogin
 @rmtsrvname=N'AzureDB_adventureworks',
 @useself=N'False',
 @rmtuser=N'taiob',@rmtpassword=@password;
GO

But the password is not getting the correct value.  I am getting a login failure.

Some of the error message:

Login failed for user 'taiob'.
(.Net SqlClient Data Provider) Server
Name: .\SQL2019
Error Number: 18456
Severity: 14
State: 1
Line Number: 1

I confirmed that it is reading the correct password from the file by running the following code:

DECLARE @password VARCHAR(MAX)
SELECT  @password = BulkColumn
FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x
SELECT @password

I am using the same file in other places using PowerShell without any issues.

#Replace with password file location
$password = Get-Content "C:\Azure SQL Database - Where is my  SQL Agent\password.txt"

If I hardcode the password it works fine.  If I print the variable I can see the value is correct. It is not a firewall issue as I can directly connect from the same SSMS that I am running the code from.


texts: How can I generate a consistent entity graph (say from 10 tables) from a database fetch

texts:
Suppose I have many tables in a database that need to be joined together to get a single consistent view of the entire entity graph. Am I forced to do this as a single query? I don't care about locks, I just want to ensure the graph is consistent as of the time of the query execution.
My concern is one of performance of joining a large number of entities together in a single query. Is it possible to do this in multiple queries but still realize the same level of consistency as having performed these individual selects as a single query?


texts: Find and Insert Missing data in Mongodb Collection

texts:
I want to write python3 code to check and insert missing data. My MongoDB collection documents have one field named "height" which is BTC block number. I want to traverse in a range start to the latest block number and check which number is missing from range. The number which is missing I want to insert that. Can somebody help me with the logic?
I have MongoDB version 4.


texts: Create view shows single column values as columns aliases (names) in view

texts:
I am making a simple application for survey for clients (lets say students), storing the answers in answer table with the following structure:

CREATE TABLE answers
(
    id INT(11) PRIMARY KEY NOT NULL,
    answer VARCHAR(255),
    studentId INT(11),
    questionIdentifier VARCHAR(255)
);

The answers will be stored like this:

INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('5',222,'math')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('4',222,'subject1')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('1',222,'subject2')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('5',222,'subject3')
INSERT INTO answers (answer ,studentId, questionIdentifier ) values ('3',222,'subject4')

I created view to get those answers as a columns so I return them back as JSOn to front end, shows the answers as columns, and students as rows:

CREATE OR REPLACE VIEW answers_view AS
  SELECT
    s.id AS `studentId`,
    s.number AS `studentNumber`,
    s.name AS `studentName`,
    ad.streetAddress AS `address`,

    ait1.answer AS `math`,ait2.answer AS `subject2`,ait3.answer AS `subject3`,
    ait4.answer AS `subject4`,ait5.answer AS `subject5`,ait6.answer AS `subject6`,

  FROM students s

    LEFT JOIN answers ait1 ON ait1.studentId = s.id AND ait1.questionIdentifier = 'math'
    LEFT JOIN answers ait2 ON ait2.studentId = s.id AND ait2.questionIdentifier = 'subject2'
    LEFT JOIN answers ait3 ON ait3.studentId = s.id AND ait3.questionIdentifier = 'subject3'
    LEFT JOIN answers ait4 ON ait4.studentId = s.id AND ait4.questionIdentifier = 'subject4'
    LEFT JOIN answers ait5 ON ait5.studentId = s.id AND ait5.questionIdentifier = 'subject5'
    LEFT JOIN answers ait6 ON ait6.studentId = s.id AND ait6.questionIdentifier = 'subject6'
    LEFT JOIN addresses ad ON ad.id = s.id
  GROUP BY s.id;

This just a sample of my code. I have like 50 questions, each question has its own questionIdentifier.

The view above is working so good but so slow too and the performance is so poor. I have like 6 students but the view lines is big and takes a minutes to finish.

How I can fix this view?

I can smell its bad smell, the code is clear but so bad for performance, and I am not that experienced in SQL.

I appreciate the help for his.


texts: Will using bigint vs mediumint have a performance impact?

texts:
I'm considering using mediumint or bigint for an ID column. I prefer to use bigint, because that way I'm very sure that the huge numbers that the client sometimes uses are stored just fine. However, I can also technically split up the numbers, and that way use a mediumint. I do not care about storage, but I do care about speed/performance. Is it better to use mediumint than bigint?

This is on an indexed, auto-increment ID column which will be used a lot for SELECT queries.


texts: How to provision the SQL Server Agent service after SQL Server Edition upgrade

texts:
I have upgraded SQL Server 2008 R2 Express to SQL Server Standard Edition. After I have upgraded it, the SQL Server Agent was stopped.

I have tried to start but it fails.

Only when I have changed his built in account from "Network Service" to "Local System" it started.

Looking at MSDN How to: Upgrade to a Different Edition of SQL Server 2008 R2 Procedure 13 it says:

  Provision the SQL Server Agent service account by using SQL Server Configuration Manager

What does it mean provision it?
What built in account should the SQL Server Agent run with?

Thank you


texts:
I have a big fact table in a datawarehouse (40 Gb worth of data in dev, 120gb in production ) which was created in a very classic way

CREATE TABLE BigFactTable
( BigFactTableId bigint IDENTITY PRIMARY KEY [PK.BigFactTable]  CLUSTERED,
  TreatmentId  int FOREIGN KEY [FK.BigFactTable.TreatmentID]
      REFERENCES Treatment(TreatmentId),
 --other columns
)

But I have noticed this table is queried mostly  with the TreatmentId field.

One of the most notable property is that TreatmentId grows along BigFactTableId, working like chapters in a big technical book.

Defining an index on (TreatmentId) with included fields bloats indexspace

30 Gb in dev  for a lit less than 3 millions rows
100 Gb in production for more than 9 millions rows

Goal

Reduce the footprint of the table while keeping or even improving the query/insert performances

What I tried

In dev, dropping the indexes and the former clustered primary key, to rebuild  the unique key associated to the now nonclustered primary key and then the right clustered index

CREATE CLUSTERED INDEX [CI.BigFactTable] on [BigFactTable]
(TreatmentId,BigFactTableId)

The single issue : it took 1 hour and 40 minutes in Dev, during when the system was very busy. But at the very least, the queries based on TreatmentId go much faster.

Questions

What is the preferred approach for this?
Should I create a new table with the right clustered key right off the bat and insert the content of the former table in the right order, and then use a drop and rename approach ?
Should I envision partitioning along TreatmentId ?
Any other suggestion ?

Precisions to answer the comments:

Most of the querying revolves around this TreatmentId field. The other fields are not parts of where clauses.
Space is in issue because of virtual hosting billing.
I tried  the index on TreatmentId alone first, it did not yield good results, as the queries did not pick it up.


texts: Can I implement a 'gapless' identity column in Oracle?

texts:
In SQL Server it is so easy to write:

create table #tmp (
    id      integer identity(1,1) primary key,
    message varchar(256)
);

When I try to migrate this to Oracle, I end up with:

CREATE GLOBAL TEMPORARY TABLE tmp(
    id integer primary key,
    message varchar2(256)
) ON COMMIT PRESERVE ROWS;

CREATE SEQUENCE S_TMP_ID START WITH 1;

CREATE OR REPLACE TRIGGER TR_TMP
BEFORE INSERT ON tmp
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT S_TMP_ID.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

OK. I get unique, increasing id values. But, when two sessions are using the same global temporary table at the same time, I get gaps in the sequence of my ideas and they start with some arbitrary value.

Any ideas how to create better identity values for a global temporary table?


texts:
can anyone explains to me what will happen if i set innodb_table_locks=ON or innodb_table_locks=OFF ?
The pros and cons of the action.

Thanks.


texts:
I'm working with 2 of the same datasets. However, one is derived from a temp table and the other is from a derived table (cross apply operator). The derived table takes much longer to process and it is a simple query. I've been told to avoid apply operator because they use row by row processing.

My question(s):

Does the apply operator use row by row processing?
Should I limit my use of apply operators like cursors and trigger statements? (Only use in specific situations)


texts: PostgreSQL : Master-Slave replication strategy and solution for 3 servers.

texts:
We have currently 3 servers(X,Y,Z) running Debian X64, two of them host projects(Y,Z), which don't require that much database, as they serve more static content, but the third server(X) is largely dependent on database and is a critical component for the project.

All the three servers use PostgreSQL. For this reason, I was thinking of creating a clustering setup, where server X would act like a master, and Y,Z as the slaves.

What I am looking for is, create a setup, where the database from server X is the master. But as the Y,Z host projects as well, the changes done to it, need to be synchronized back to server X.

I presume this technology is called replication & clustering? How can I proceed ahead with this problem for PostgreSQL.

If anyone can help me outline a strategy for this, and how can I proceed ahead. Any help would be nice. Thank you. If you feel, that my understanding of replication and/or clustering is wrong, please feel free to correct me.


texts: MySQL Workbench doesn't automatically refresh the tables content

texts:
I have a database running on localhost and the content of this DB is updated by an app (using Hibernate, for the record). In this order:

In Workbench I run a SELECT * to check to DB content
I add a row in a table through the app
I run the same SELECT in Workbench

The result is the same as the first query.
Actually if I hit the button "Commit the current transaction", running the request again will show the new row.
Why is it necessary to commit the Workbench session to see results committed by an external app ?

Is there a way to auto-refresh the table rows in Workbench?

Thanks for the help.


texts: How to disable "Completion time:..." in SQL Server Messages window

texts:
Every query I run in SSMS append the annoying message: "Completion time:...".

How can I disable that text?


texts: Populate a column using the values of other ones

texts:
I would like to add a new column in my table using SQL code in PostgreSQL / PostGIS, the values in this column must be filled from the first eight columns and updated with a trigger as I insert data to a new line for the first rows, maintaining the Area, x, and y columns with their values. As I show in the attached image.

So, the "New Column" just not receives the values from Area, x and y.
Those three last columns have got the following codes that area working normally

Codes for Area Column:

CREATE OR REPLACE FUNCTION update_area()
  RETURNS TRIGGER AS
    $$
    BEGIN
      NEW.area := round(ST_Area(NEW.geom):: numeric,2);
      RETURN NEW;
    END;
    $$
  LANGUAGE plpgsql;

the trigger of that:

CREATE TRIGGER update_area
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_area();

Code for the x and y columns:

ALTER TABLE "EDIFICACAO_4" ADD X double precision;
ALTER TABLE "EDIFICACAO_4" ADD y double precision;
UPDATE "EDIFICACAO_4" SET X = ST_X(ST_Centroid(geom));
UPDATE "EDIFICACAO_4" SET Y = ST_Y(ST_Centroid(geom));

The trigger of that:

CREATE OR REPLACE FUNCTION update_en()
  RETURNS TRIGGER AS
    $$
    BEGIN

      NEW.X := round(ST_X(ST_Centroid(NEW.geom))::numeric,3);
      NEW.Y := round(ST_Y(ST_Centroid(NEW.geom))::numeric,3);
      RETURN NEW;

    END;
    $$
  LANGUAGE plpgsql;

CREATE TRIGGER update_en
  BEFORE UPDATE OR INSERT ON public."EDIFICACAO_4"
  FOR EACH ROW
  EXECUTE PROCEDURE update_en();


texts: Create a partition for a temporal table based on a period

texts:
To improve the performance of my ETL, I decided to use temporal table instead of a slowly changing dimension.

For my Employee dimension, I created a temporal table. Now I want to split data into partitions based on periods.

I already declared StartTimeV and EndTimeV  (the created columns for my temporal tables) as system period start time and end time periods. I want to create a partition for my temporal table based on the period (StartTimeV and EndTimeV).


texts:
How do I optimise this query . Its taking long time.
The inner query is fine. But outer query is taking more time . Please guide.Have attached the explain output.

SELECT users.ID, IFNULL( CONCAT( users.firstname, ' ', users.surname ) , '' ) AS Name, email
FROM users
where users.enabled= "Y" and users.ID in (
SELECT userID
FROM userData
WHERE paramName = 'Skills' AND paramValue IS NOT NULL AND paramValue != ''
UNION
SELECT users.ID
FROM users
INNER JOIN contributors ON contributors.name = users.email
)


texts:
Hi, how to count value in table like this?

This just example:

SELECT COUNT(name1) AS newResult1 FROM table

As this code just count column name1 and it will give result of 3.

What I want to do is count value in name1, name2, name3 and it give result based on value yes or no. Thanks.


texts: Deadlock Detection | Should the node T4 point to T1 and T2?

texts:
This example regarding deadlock detection was shown during one of my lectures and I have a doubt about it. The image is shown below.
For deadlock detection mechanism, why does T4 point only to T2 and not T1? Chronologically, T1 requests for the exclusive lock before T4. So shouldn't T4 point to T1 as well?


texts: SQL over TLS using Let's Encrypt on Raspberry pi

texts:
I want to be able to connect to a database hosted on a Raspberry Pi (mysql-server) from a C# application. In the real world professionals use a Webservice for this, but I want to use the SQL-connector for C#.

I successfully pulled this off using a certificate from Comodo.
See https://tutorials.pieterjan.pro/?a=Webdesign_Hosting_ExternHosten_HTTPS_SSL_SQL-TLS&lang=nl

I already installed a certificate from Let's Encrypt for pieterjan.pro. But when I configure mysql to use this certificate it doesn't work. While I actually did the same thing.

sudo nano /etc/mysql/my.cnf

contains:

ssl
ssl-cert=/etc/letsencrypt/live/pieterjan.pro/fullchain.pem
ssl-key=/etc/letsencrypt/live/pieterjan.pro/privkey-nopassword.key

The key-file has been reproduced with openSSL, So it wouldn't be password-protected. The paths to the cert and key are correct. Why can't I use SQL over TLS using a LetsEncrypt Certificate?

pi@pieterjan:~ $ mysql -p -u root
Enter password:
mysql> show variables like '%ssl%';
+---------------+------------------------------------------------------------+
| Variable_name | Value                                                      |
+---------------+------------------------------------------------------------+
| have_openssl  | DISABLED                                                   |
| have_ssl      | DISABLED                                                   |
| ssl_ca        |                                                            |
| ssl_capath    |                                                            |
| ssl_cert      | /etc/letsencrypt/live/pieterjan.pro/fullchain.pem          |
| ssl_cipher    |                                                            |
| ssl_key       | /etc/letsencrypt/live/pieterjan.pro/privkey-nopassword.key |
+---------------+------------------------------------------------------------+

EDIT:

So far I've tested some things:

1) The error-log says:

SSL error: Unable to get certificate from '/etc/letsencrypt/live/pieterjan.pro/fullchain.pem'

2) The owner of the specified file is root:root

sudo ls -l /etc/letsencrypt/live/pieterjan.pro/fullchain.pem
-> user=root & group=root

3) The mysql-daemon was started by the mysql-user

ps -aux | grep mysql
-> mysql   /usr/sbin/mysqld

4) Therefore I tried to add the mysql-user to the root group:

sudo usermod -a -G root mysql

5) Double-check -> OK

sudo grep 'root' /etc/group
-> root:x:0:mysql

6) Inspection of certificate-files

sudo ls -la /etc/letsencrypt/live/pieterjan.pro/
-> lrwxrwxrwx 1 root root   37 Jan 18 13:14 cert.pem -> ../../archive/pieterjan.pro/cert1.pem
-> lrwxrwxrwx 1 root root   38 Jan 18 13:14 chain.pem -> ../../archive/pieterjan.pro/chain1.pem
-> lrwxrwxrwx 1 root root   42 Jan 18 13:14 fullchain.pem -> ../../archive/pieterjan.pro/fullchain1.pem

They appear to be just symlinks.

7) Tracked the file down which is mentioned in the error-log

sudo ls -la /etc/letsencrypt/archive/pieterjan.pro/fullchain1.pem
-> -rw-r--r-- 1 root root

The certificate is readable for owner/group/world

Despite the fact that the mysql-user is in the root-group, mysql-server is still unable to read the certificate (after restarting mysql off course).


texts:
I tried exp utility to dump all database. Looks like this exports only the last version of data skipping undo log. Using flashback queries I see:

01466. 00000 -  "unable to read data - table definition has changed"
*Cause:    Query parsed after tbl (or index) change, and executed
           w/old snapshot

What I'm trying to do is to capture db changes, make backup for later use with the ability to flashback to timestamp.

With rman backup I have similar situation:

ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3_2472002983$"  too small
01555. 00000 -  "snapshot too old: rollback segment number %s with name \"%s\" too small"
*Cause:    rollback records needed by a reader for consistent read are overwritten by other writers`.

Update:
I managed to do what I needed only by increasing undo retention and direct copying of data files and control file modification on cloned instance.


texts:
I am trying to figure out how I can index this query below. The table is 155 columns. It has a clustered index on the column which is not in the query. The query is dynamically created from out of the box application and no way I cant tune it except adding index. I could not find an index that will optimize the query. The table has 511919 rows.Now, it is clustered index scan OR non clustered index Scan (with covered index)

SQL Server version : 2016 SP2, We have 22 core and 24 gbs on the server. I can post a query plan.

    Declare
        @P0 VARCHAR(8000)
        ,@P1 VARCHAR(8000)
        ,@P2 VARCHAR(8000)
        ,@P3 VARCHAR(8000)
        ,@P4 INT
        ,@P5 VARCHAR(8000)
        ,@P6 VARCHAR(8000)
        ,@P7 VARCHAR(8000)
        ,@P8 INT
        ,@P9 VARCHAR(8000)

Set @P0 = 'Cancelled'
Set @P1 ='20190321'
Set @P2 ='20190321'
Set @P3 ='20190321'
Set @P5 ='20190321'
Set @P6 ='20190321'
Set @P7 ='20190321'

Set @P4 =1
Set @P8 =0

Set @P9='Service'

SELECT  Col1,
    Col2,
    Col3,
    Col4,
    Col5,
    Col6,
    Col7,
    Col8,
    Col9,
    Col10,
    Col11,
    Col12,
    Col13,
    Col14,
    Col15,
    Col16,
    Col17,
    Col18,
    Col19,
    Col20,
    Col21,
    Col22,
    Col23,
    Col24,
    Col25,
    Col26,
    Col27,
    Col28,
    Col29,
    Col30,
    Col31,
    Col32,
    Col33,
    Col34,
    Col35,
    Col36,
    Col37
FROM Table1
WHERE (
        Col18 IS NOT NULL
        AND Col75 <> @P0
        AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P1, 1, 4) + '-' + SUBSTRING(@P2, 5, 2) + '-' + SUBSTRING(@P3, 7, 2) + 'T00:00:00.000' AS DATETIME)) <= @P4
        AND DATEDIFF(dd, Col146, CAST(SUBSTRING(@P5, 1, 4) + '-' + SUBSTRING(@P6, 5, 2) + '-' + SUBSTRING(@P7, 7, 2) + 'T00:00:00.000' AS DATETIME)) >= @P8
        )
    AND Col39 = @P9
ORDER BY 18 ASC
    ,31 DESC
    ,30 ASC

This is a covered index. It is used as non clustered index Scan.

/****** Object:  Index [NC_OBJ_DID_SQ_CoveredIndex2]    Script Date: 3/21/2019 1:30:31 PM ******/
CREATE NONCLUSTERED INDEX NC1 ON Table1
(
    TableCol39  ASC,
    TableCol6 ASC,
    TableCol75  ASC,
    TableCol18  ASC
)
INCLUDE (   TableCol1,
    TableCol2,
    TableCol3,
    TableCol4,
    TableCol5,
    TableCol6,
    TableCol7,
    TableCol8,
    TableCol9,
    TableCol10,
    TableCol11,
    TableCol12,
    TableCol13,
    TableCol14,
    TableCol15,
    TableCol17,
    TableCol19,
    TableCol20,
    TableCol21,
    TableCol22,
    TableCol23,
    TableCol24,
    TableCol25,
    TableCol26,
    TableCol27,
    TableCol28,
    TableCol29,
    TableCol30,
    TableCol31,
    TableCol32,
    TableCol33,
    TableCol34,
    TableCol35,
    TableCol36,
    TableCol37,
    TablCol146 ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Thanks a bunch!


texts: JSON formatting using group by and table valued function

texts:
I want this query to group based on ClientContactGroupID.

select cg.ClientContactGroupID,ClientID,ContactGroupName
     , gcon.ClientContactID,gcon.ClientContactGroupID
     , gcon.FirstName
     , gcon.LastName,gcon.Email
     , gcon.ProfileImage,gcon.MobilePhone
from ClientContactGroup cg
cross apply dbo.Func_GroupContacts(cg.ClientContactGroupID) gcon
where ClientID='56554a81-d98e-44ac-a5e1-611e564e55e8'
  and cg.IsDeleted=0

As of now, if I apply for json auto to the query it returns json in following format:

       {
       "ClientContactGroupID": "97FD1910-E9B2-4185-AB20-C10F983C77D1",
       "ClientID": "56554A81-D98E-44AC-A5E1-611E564E55E8",
       "ContactGroupName": "country code",
       "IsDeleted": false,
       "gcon": [
        {
        "ClientContactID": "59ECC8D0-C2FA-4DEA-841C-00771CE445C4",
        "ClientContactGroupID": "97FD1910-E9B2-4185-AB20-C10F983C77D1",
        "FirstName": "aaa",
        "LastName": "bbb",
        "Email": "VgDIrd12tl3S1f21YzhE+CQZErF\/L0pIPkdz84ggTbjoxU969e797sD2V818OmZm",
        "MobilePhone": "\/deaMYIoAlgs4vfO8lQORQ=="
        }
   ]}

In this the gcon should list multiple records but is only returning one. I tried different approaches but none seem to give a proper result.

Table valued function used in first query:

ALTER FUNCTION Func_GroupContacts
(
 -- Add the parameters for the function here
 @ClientContactGroupID varchar(max)
)
RETURNS TABLE
AS
RETURN
(
    -- Add the SELECT statement with parameter references here
    select ClientContactID,ClientContactGroupID
         , FirstName, LastName, ProfileImage
         , Email, MobilePhone
    from ClientContact
    where ClientContactGroupID IN(@ClientContactGroupID)
      and IsActive=1
      and IsDeleted=0
)


texts: ERROR WSREP`: gcs/src/gcs.cpp:gcs_open():1379:` Failed to open channel 'test_cluster'

texts:
I have three server that I want to Install Galera clustering on them, I followed this link to install and config it. but when I want to start the first server I got this error :

root@node107:/home/debian# service mysql start --wsrep-new-clusterping
Job for mysql.service failed. See 'systemctl status mysql.service' and 'journalctl -xn' for details.

and :

root@node107:/home/debian# journalctl -xn
-- Logs begin at Tue 2016-03-01 19:02:04 IRST, end at Tue 2016-03-01 19:39:32 IRST. --
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: gcs/src/gcs.cpp:gcs_open():1379: Failed to open channel 'test_cluster' at 'gcomm://192.168.120.107,192.168.120.111,192.168.120.11
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: gcs connect failed: Connection timed out
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] WSREP: wsrep::connect(gcomm://192.168.120.107,192.168.120.111,192.168.120.117) failed: 7
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [ERROR] Aborting
Mar 01 19:39:31 node107 mysqld[5102]:
Mar 01 19:39:31 node107 mysqld[5102]: 160301 19:39:31 [Note] WSREP: Service disconnected.
Mar 01 19:39:32 node107 mysqld[5102]: 160301 19:39:32 [Note] WSREP: Some threads may fail to exit.
Mar 01 19:39:32 node107 mysqld[5102]: 160301 19:39:32 [Note] /usr/sbin/mysqld: Shutdown complete
Mar 01 19:39:32 node107 mysqld[5102]:
Mar 01 19:39:32 node107 mysqld_safe[5378]: mysqld from pid file /var/run/mysqld/mysqld.pid ended

these are configuration file for the three servers :

vim /etc/mysql/conf.d/cluster.cnf

Server1 :

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"
#wsrep_cluster_address="gcomm://192.168.120.107"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.107"
wsrep_node_name="node107"

Server 2:

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107"
#wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.111"
wsrep_node_name="node111"

server 3 :

[mysqld]
query_cache_size=0
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
query_cache_type=0
bind-address=0.0.0.0

# Galera Provider Configuration
wsrep_provider=/usr/lib/galera/libgalera_smm.so
#wsrep_provider_options="gcache.size=32G"

# Galera Cluster Configuration
wsrep_cluster_name="test_cluster"
wsrep_cluster_address="gcomm://192.168.120.107,192.168.120.111,192.168.120.117"

# Galera Synchronization Congifuration
wsrep_sst_method=rsync
#wsrep_sst_auth=user:pass

# Galera Node Configuration
wsrep_node_address="192.168.120.117"
wsrep_node_name="node117"

I don't understand what I have missed. what should I do?


texts: mysqltuner.pl shows [!!] 2 different collations for database


texts: Invalid object when selecting from temporary table

texts:
I'm creating a temporary table called #PrevStatus. I've confirmed that the create statement is correct.

When I run,

exec (@TableQuery)

which contains the table create statement and then right after

select * from #PrevStates

I get,

Invalid object name '#PrevStatus'

Why is this happening?


texts:
In an inherited database I have two tables of current interest, records and logins.  login tells me the location of the user and where and when his login occured (in and out times included).

The fun part is that there is no correlating mark between the login session and the applications ran.  To solve the issue I'm creating a new table login_apps and am trying to use a function to combine the two tables.

So far I have:

drop function if exists ltop;
delimiter $$

create function ltop(id int, u varchar(10), s datetime, e datetime)
    returns text
    language sql
begin
    declare pid varchar(40);
    declare pname varchar(63);
    declare my_return varchar(100);
    declare cur1 cursor for (select usageProgramID
                                from records
                                where usageUser=u
                                    and usageWhen>=s
                                    and usageWhen<=e
                                    and usageProgramID!="");
    open cur1;
    read_loop: loop
        fetch cur1 into pid;
        set pname = (select programName from Programs where programID=pid);
        insert into login_apps(`sid`, `programName`) values(id, pname);
    end loop read_loop;

    close cur1;

    return "finished";
end;$$
delimiter ;

It enters into the system without issue and the insert is working as expected on a test.  The issue I'm coming up with is that when it's run as:

select ltop("265548", "user", "2013-02-21 13:54:27", "2013-02-21 14:32:18");

I get the error:

ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

Shouldn't the data be returned be "finished"?

Secondly, I'm not really sure what would be considered the best way to run this on every value of logins any pointers for going that route?


texts: How do I fix the definer problem The user specified as a definer ('fred'@'192.168.0.%') does not exist

texts:
I dumped an existing database from my web site into a new MySQL setup on my laptop. Everything works except pages that use a view, when the error message above appears. There is only one view on the system. I assumed that this was caused because the user was not in the system, but looking in the users database of MySQL the user is listed correct with the password.

The dump was from an earlier version of MySQL (5.0) into the latest one that comes with Xamp 5.527. At first I thought it could be that the latest version was incompatible with the existing one.

Looking on this forum, there are some existing answers covering this problem that refer to making changes to the database and then dumping it again.

However, my on line database works fine, so I dare not make changes to it in case I kill it. That is assuming that I understood what changes I should make and how to do it. I have PHPmyadmin. Can I modify it on the existing dumped database,or do I have to change it on the on line one and then export it?

And exactly what do I need to do to modify it. The original database was a Microsoft Access one, converted to MySQL, and it has worked without error since 2003.


texts: Overview of how MongoDB uses its various threads

texts:
On one instance I have MongoDB using ~85 threads. In lieu of having time to investigate directly, I am curious:

What are the threads used for? Do they fall into a few high-level usage categories?
How can I control/influence the number of threads used? E.g. max connection params, etc.


texts:
I know that cursor isn't very good idea, but I have some questions for internal structure of cursor.

First step in cursor is determining result set which cursor iterate over. So, did cursor execute SELECT query and put the result in some temporary table or it is implemented somehow different?

SQL will take data pages in memory form disc, first time data pages was needed. So, assume that I was executed simple SELECT query manually, and SQL pull data pages in memory. Assume also that SELECT query has identical query in FOR clause. What happened at the end of cursor, when cursor are closed and deallocated? Did cursor free memory from used data pages, or that data pages remain in memory?


texts: When the primary key is also the foreign key, in Postgres

texts:
In Postgres 12, I am tracking photographs. Each is large, about 6 to 8 megs each. For quick browsing I will store a thumbnail image along with the full image. Both full and thumbnail are BYTEA type columns.

For performance, I want to avoid loading the full image when only the thumbnail is needed. As I understand it, when accessing a row, all the BYTEA field values will be loaded into memory. So displaying a list of thumbnails to the user will necessarily be loading the full photos on the server even when not explicitly requested in the query.

So I will break that photo table into two, storing the thumbnail on the main table, and storing the thumbnail in a separate child table, in a One-To-One.

In such a scenario, the child full-photo table carries the ID of its parent thumbnail row as a foreign key. That foreign key column could logically serve as the primary key as well.

Can I designate that one column of a child to be both a foreign key and also a primary key? If so, are there any caveats to be aware of?


texts: pgsql - Delete data from huge table where not in

texts:
I need to delete some rows from one large table. The rows to delete shouldn't be in another table, example:

DELETE FROM LargeTable WHERE id IS NOT IN (SELECT DISTINCT foreign_id from EvenLargerTable)

but my server can't handle such blunt query, because there are almost a million records in the LargeTable and few million records in the EvenLargerTable

How can I solve it?


texts:
Here is a part of our Database:

Table: Users
ID            INT
First Name    VARCHAR
Last Name     VARCHAR
Email Address VARCHAR
...

Table: Contacts
ID            INT
User_id       INT (Foreign Key)
First Name    VARCHAR
Last Name     VARCHAR
Email Address VARCHAR (Unique)
Phone Number  VARCHAR
Address       VARCHAR
...

A user can have multiple contacts (One-to-many relationship).

It is possible that a contact of one user is also a contact of another user. And the second user may try to enter different phone number/address (but same email address). I cannot allow duplicate email addresses.

One possible solution is to split contacts table into 2 tables:

Table: Contacts
ID            INT
Email Address VARCHAR
...

Table: Contact_attributess
ID            INT
User_id       INT (Foreign Key)
Contact_id    INT (Foreign Key)
First Name    VARCHAR
Last Name     VARCHAR

Is this the best way to reorganize my DB? Do you have other solutions?


texts: Tracking log block movement using Extended Events

texts:
If we try to track the log block movement between 2 sync commit replicas,  why do we need to track the immediate next Log block ID in extended events like hadr_send_harden_lsn_message on secondary  and hadr_receive_harden_lsn_message on primary to track the complete flow of log block until acknowledgement on hadr_db_commit_mgr_harden extended event? Also what are the immediate next log block IDs and how do we track them?

Reference:

Troubleshooting High HADR_SYNC_COMMIT wait type with Always On
Availability Groups


texts: Max from 2 columns with priority date col then the seq col Oracle

texts:
I have a column xdate, column seqnumber, personID

I want 1 record per personID, where it is the most recent xdate max(xdate)
if there is multiple same max xdates i want it to look then for the max seqnumber to determine what row to pull.

obviously this doesn't work:
select max(xdate), max(seqnumber), personID From table1
Group by personID

1 person can have many records, 1 person can have the many of the same xdate, but seqnumber iterates for each xdate starting with 0 going to n

I can't quite figure this out, I can't use a correlated sub-query because there are many records.  I would need to join I believe.

Example data above, I would want the results of the

1st row
3rd row
6th row

Please help,
mike


texts: Suitable database software for small business

texts:
First of all, I apologise if this is in the wrong area.

I'm looking for a suitable database program for a small engineering business, which needs to log various jobs. Currently, we're just using an Excel spreadsheet shared over the cloud, however this has problems with conflicts when two people edit the file at the same time.

What we want is something that can operate over the internet (so that multiple computers can access it from different locations), allowing multiple editors simultaneously. It also needs to be capable of conditional formatting. Essentially, it needs to be a kind of hybrid between a desktop database and a server database. Something that can be saved to the cloud would be the most preferable for sharing reasons.

A screenshot example of what we currently have is here

From my research I have a vague inkling that SQL would be a good base, but I'm really looking for a program that requires absolutely no background knowledge of how the language works. i.e something that isn't going to take a significant amount of time to learn to use. If there's info that I've not included, please let me know.

Thanks in advance.


texts: SQL Server 2008 log shipping via JungleDisk

texts:
Has anyone used Jungle Disk as a common location for SQL Server 2008 log shipping? We use it for backup already and I think it can be used for taking log backup to our off-site location.

It seems like it will work but I have no personal experience using it and would like to know more about Jungle Disk reliability/suitability for this.


texts:
I am trying to model the process of where one co-worker enters a statement of their proposed work procedure and another co-worker commenting and approving the statement.
Currently I have one table as such:

METHODOLOGY
  methodology_id
  entered_by
  statement
  entered_date
  status_code
  reviewed_by
  reviewer_comments
  reviewed_date

status codes are as such:

1 = in progress by originator
2 = finished and locked by originator
3 = in progress by checker
4 = finished, locked and rejected by checker
5 = finished, locked and approved by checker
6 = approved method was unlocked due to revision to methodology
7 = the originator was changed with a new originator after method was approved
8 = the checker was changed with a new checker

Should this be broken out as such:

METHODOLOGY_STATEMENT
  methodology_id
  entered_by
  statement
  entered_date
  status_code

METHODOLOGY_REVIEW
  methodology_review_id
  methodology_id
  status_code
  reviewed_by
  reviewer_comments
  reviewed_date

Right now the requirement is only one Review per Method Statement.


texts: How can I properly merge the rows of several table with default values?

texts:
Problem
I have three tables that are structured like so:
t1
 id | count_1
--------------
 1  | 9
 2  | 4
 3  | 3

t2
 id | count_2
--------------
 1  | 2
 3  | 3

t3
 id | count_3
--------------
 1  | 1
 4  | 8

id is unique in each table.  Note that not all ids occur in each table.  Here is the SQL to create those tables if you'd like to test.
I'm trying to merge all those tables with a column for each count, defaulting to zero if there is no count for that particular id.  Like this:
 id | count_1 | count_2 | count_3
----------------------------------
 1  | 9       | 2       | 1
 2  | 4       | 0       | 0
 3  | 3       | 3       | 0
 4  | 0       | 0       | 8

Attempt
I thought this was a natural use case for a full outer join, like this:
SELECT
  COALESCE(t1.id, t2.id, t3.id) as id,
  COALESCE(t1.count_1, 0) as count_1,
  COALESCE(t2.count_2, 0) as count_2,
  COALESCE(t3.count_3, 0) as count_3
FROM
  t1
FULL OUTER JOIN t2
  ON t1.id = t2.id
FULL OUTER JOIN t3
  ON t1.id = t3.id
ORDER BY id ASC;

But this returns a result with non unique ids, where each row is just a row from one of the original tables with zeroes filling in the remaining columns:
 id | count_1 | count_2 | count_3
----------------------------------
 1  | 9       | 0       | 0         # <- should
 1  | 0       | 2       | 0         # <- be
 1  | 0       | 0       | 1         # <- one row
 2  | 4       | 0       | 0
 3  | 3       | 0       | 0         # <- should also be
 3  | 0       | 3       | 0         # <- one row
 4  | 0       | 0       | 8

Evidently I don't understand outer joins as well as I thought I did.  Can anyone show me the correct way to do this?


texts: MySQL Selecting and deleting whole entries

texts:
I'm a total noob to mysql and i`m still trying to get my head around it. I need to select duplicate entries in a song DB and delete only those entries not in a category. I can get t to work with select. though there's an error but not with delete. Could someone help and point out my total noobness mistake?

Select Code

Select Songlist.ID,Artist,Title,Filename,CategoryID
from Songlist,categorylist
where Artist = "Lady Gaga" AND Title = "Bad Romance" AND
(Categorylist.CategoryID <> 12)

Delete Code

Delete from *
where Artist = "Lady Gaga" AND Title = "Bad Romance" AND
Categorylist.CategoryID <> 12

Thanks in advance.


texts: Splitting a deck of cards, and returning the results as if they were shuffled once

texts:
Lets say I have a table with a deck of cards, numbered 01-52. I could return the top and bottom cards, as if I held each side of the union's select query in my left and right hands by doing a:

select top 26 * from DeckOfCards order by CardNumber desc
union all
select top 26 * from DeckOfCards order by CardNumber asc

It would be an even split.

But how could I have SQL Server intertwine the results returned, as if I had taken both portions of that union, one half in my left hand, and the other in my right, and shuffled them once like a deck of cards?

IE:
CardNumber 52, followed by 1, in the following sequence:
52, 1, 51, 2, 50, 3, 49, 4, etc...

This is not a homework question, just one of those things that passes through my mind when trying to get some shut eye. :)


texts:
I have a mongo-database where existing entries never change but new entries are added every day. I have ~1000 dumps, each is the new data that was added on the corresponding day.

I.e. I have the following files:

/dump1/mydatabase/mycollection1.bson
/dump1/mydatabase/mycollection1.metadata.json
/dump1/mydatabase/mycollection2.bson
/dump1/mydatabase/mycollection2.metadata.json
...
/dump2/mydatabase/mycollection1.bson
/dump2/mydatabase/mycollection1.metadata.json
/dump2/mydatabase/mycollection2.bson
/dump2/mydatabase/mycollection2.metadata.json
...
...
...
/dump1000/mydatabase/mycollection1.bson
/dump1000/mydatabase/mycollection1.metadata.json
/dump1000/mydatabase/mycollection2.bson
/dump1000/mydatabase/mycollection2.metadata.json

I would like to restore "mydatabase" containing all the collections. The content in each of the collections should be appended to the existing collections instead of overwriting them.

When I run

mongorestore /dump1
mongorestore /dump2
...

will this append my data to the existing collections or overwrite them?


texts: How to set up a solid MySQL/MariaDB Server for +3000 active connections?

texts:
Background.

I have an App connecting to an RDS instance on Amazon (r3.Large) and it's working smoothly. However, the boss does not like to spend ~ $1,000 bucks a month on it so we are moving In-house.

We are getting Dell servers with two physical Xeon processors and a ton of RAM, basically an r5.2XLarge r5.4XLarge RDS instance.

I'm no expert on Linux systems and I have installed MySQL through the XAMPP package from Apache friends before but I never get the same result twice. (I'm a newbie, please be kind).

Considerations

The server is going to be used primarily as a LAMP server, so other web-based apps will be using MySQL as well.
The App that uses the current RDS instance on amazon is a set of 3 executables for windows, each one of these executables uses its independent connection to the DB.

We have +500 PCs through 3 different locations but we are planning on expanding to +700 PC by next year.

We estimate we will be having around 5,000,000 to 5,500,000 queries by hour on a busy day.
And an average of 2,300 active connections to the DB.

A few times per day the Admin team runs heavy reports that are quite resource-intensive (the tasks take up to 40 sec to finish).

Question

Besides quitting my job and find a better one, do you have any recommendations on the configuration and/or installation of MySQL on the server?

Edit

Assuming I have MySQL installed...

Should I modify the "my.cnf" file to avoid having errors like "too many connections" or issues trying to import/export the old DB?

Thank you.


texts: MySQL denying access to user with necessary privileges

texts:
I have a MySQL server and a user with access to all databases.  When this user attempts to connect to the database and execute a query, access is denied.

I have ran the following statement (specifics replaced with dummy data):

GRANT ALL PRIVILEGES ON *.* TO user@Macinstosh-2.local IDENTIFIED BY 'password' WITH GRANT OPTION;

I have also verified that all privileges have been granted:

SHOW GRANTS FOR 'user@Macinstosh-2.local';

with results:

GRANT USAGE ON *.* TO 'user@Macinstosh-2.local' IDENTIFIED BY PASSWORD 'a-very-long-string'

Using this user account with MySQL Workbench I try to execute a simple query against a database with the following results:

Error Code: 1044. Access denied for user 'user@Macinstosh-2.local' to database 'dbname' 0.003 sec

Furthermore, I can only see 1 (one) database on the server in Workbench!

What am I not understanding?

MySQL output:

mysql> SHOW GRANTS FOR 'user'@'macintosh-2.local';
+------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for user@macintosh-2.local                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'user'@'macintosh-2.local' IDENTIFIED BY PASSWORD '*AEA56C875A1D92BB258B4E27CA869B3CCBA49271' WITH GRANT OPTION |
+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>

Note that user in the listings is the altered username for security reasons.

Even with this, Workbench does not display any database but one.  I think all databases should be visible.

Possible answer:
I think this was a bug or caching issue with Workbench.  I was just poking around in Workbench and selected:

Server>Users and Privileges menu

A tab opened in Workbench that displayed all the users with the same information that I obtained in BASH MySQL.  It also refreshed the schema listings on the left with ALL databases displayed.  Whatever was going on, it is now working as expected.  I would appreciate if anyone could elaborate on this or point me to where I might do forensics to determine the actual causality.


texts: PostgreSQL UPSERT issue with NULL values

texts:
I'm having an issue with using the new UPSERT feature in Postgres 9.5

I have a table that is used for aggregating data from another table. The composite key is made up of 20 columns, 10 of which can be nullable.
Below I have created a smaller version of the issue i'm having, specifically with NULL values.

CREATE TABLE public.test_upsert (
upsert_id serial,
name character varying(32) NOT NULL,
status integer NOT NULL,
test_field text,
identifier character varying(255),
count integer,
CONSTRAINT upsert_id_pkey PRIMARY KEY (upsert_id),
CONSTRAINT test_upsert_name_status_test_field_key UNIQUE (name, status, test_field)
);

Running this query works as needed (First insert, then subsequent inserts simply increment the count):

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count)
VALUES ('shaun',1,'test value','ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = 'test value';

However if I run this query, 1 row is inserted each time rather than incrementing the count for the initial row:

INSERT INTO test_upsert as tu(name,status,test_field,identifier, count)
VALUES ('shaun',1,null,'ident', 1)
ON CONFLICT (name,status,test_field) DO UPDATE set count = tu.count + 1
where tu.name = 'shaun' AND tu.status = 1 AND tu.test_field = null;

This is my issue. I need to simply increment the count value and not create multiple identical rows with null values.

Attempting to add a partial unique index:

CREATE UNIQUE INDEX test_upsert_upsert_id_idx
ON public.test_upsert
USING btree
(name COLLATE pg_catalog."default", status, test_field, identifier);

However, this yields the same results, either multiple null rows being inserted or this error message when trying to insert:

  ERROR:  there is no unique or exclusion constraint matching the ON
  CONFLICT specification

I already attempted to add extra details on the partial index such as WHERE test_field is not null OR identifier is not null. However, when inserting I get the constraint error message.


texts: Index maintenance operations that are ONLINE in Standard Edition: Is table Read-Write or Read-Only?

texts:
As we understand it, related to SQL Server 2008 and above:

ALTER INDEX ... REORGANIZE

Is on ON LINE operation in ALL flavors of SQL Server.

ALTER INDEX ... REBUILD

Is a BLOCKING operation, unless you have SQL Server Enterprise Edition.... (and even with the Enterprise Edition, is blocking when rebuilding clustered indexes)

If the above is all correct, the question is:

During an ALTER INDEX ... REORGANIZE operation, is the table available for read and write, or are writes blocked?


texts:
I asked this question over at https://stackoverflow.com/questions/52068320/creating-a-table-space-sql-developer, but there was no traction on the question, i thought i might try my luck over here.

I'm creating a tablespace to allocate some tables I've made. I'm new to SQL and i'm not exactly sure what my next step is to create the tablespace.

I understood and found that in order for me create a non-clustered table space i need to:

Calculate the total block header size.
Calculate the available data space per data block.
Calculate the space used per row.
Calculate the total number of rows that will fit in a data block.

I've calculated these figures for each table i have, but i'm clueless on the next step after this.

How do I use my calculated values to create a tablespace?


texts: Excluding particular words from a string on a WHERE clause

texts:
I have a table that stores invoices and I'm trying to get stats out of it, unfortunately the table is poorly build and some crucial information is all mixed up on a nvarchar field, information such as whether the invoice has been cancelled or if part of the charge is exempt resides on this field in a very daliesque string that gets parsed by the frontend. 3453.234;exempt;Invoice Total...

So I want to create a query that would exclude a few words. My problem is how to accomplish a query that can exclude records depending of a list of keywords (cancelled, exempt), so if any of this words is in the field the amount would not be taken in account.


texts:
I have a general question about something I'm seeing in performance tuning in Oracle.  I have a query that I'm testing.  It's too big and complex for me to provide a simple example, sorry!  (I tried lowering it down, but lost the symptom)

The first time I run the query, it's 2 seconds.  Subsequent times it's 3, 4, or even 5 seconds.  This happens consistently - if I change the spacing, I get 2 seconds again, then it goes back up.

This is the opposite of what I would expect - after the first time, the database doesn't need to parse the query, and blocks should be in read into the buffer cache.  The only thing I can think of is that it is writing temp data somewhere, in such a way that it is faster to write and read it than it is to just read it.  But that of course makes no sense.

Does anyone know why this is happening?  Or any red flags in the execution plan that would cause something like this?

FYI, this is a SELECT, with multiple joins, aggregation, and views.  No internal PL/SQL.

Thank you

@Colin It's unlikely another process could be affecting this Dev machine, at least so consistently.  Not sure what dynamic sampling is.  Same thing as cardinality feedback?

@druzin No bind variables, but no predicate changes either - same hash.

@mustaccio Good idea, although I suspect it's something to do with implicit temp tables or caching.  I'll give it a try.


texts: Scenario of huge SQL Server Database backup

texts:
I use SQL Server and have a huge database with large compressed backup size (about 250 GB).
The backup scenario of this database is:

Create full backup at the first day of each month.
Create Differential backup daily exclusive first day of each month.

also I use mirror backup in order to copy my backup file to another server.

Total time that need to create full backup is about 3 hour.

I look for a better scenario to create small backup size and copy faster than now.

In this database I have 2 filegroups (ArchiveFG and PRIMARY). The size of ArchiveFG is 200 GB, also this file group is read-only filegroup.

I don't know that can I use following scenario?

create full backup from ArchiveFG filegroup (One time).
create full backup from PRIMARY filegroup the first day of each month.
create differential backup from PRIMARY filegroup daily exclusive first day of each month.

And how can I restore backup with above scenario.

Thanks in advance


texts:
First time posting so apologies if this has been asked already.

I have a table in SQL Server that is being updated from many different locations with data that I don't want in the table. I have come to the conclusion I need a trigger that will fire before the update happens to check to see if the email_address is valid.

I have two tables tracked and trackOmit.

Tracked
  id
  email_address
  ip_address
  trackedPage

trackOmit
  id
  trackOmit_email

TrackOmit is a list of email domains "hotmail.com, gmail.co.uk" etc, that for whatever reason we don't want tracking, however due to bad design the update statement doesn't take into account the trackOmit table. I do not know the first place to start to write a trigger to check the updated values against the trackOmit table before update.

Thanks for any help that can be provided.


texts: Migrate MySQL spatial data to PostgreSQL(PostGIS)

texts:
We have a web system that uses MySQL spatial databases. We want to migrate from MySQL to PostgreSQL. Our database has geometry and point data types, 21 tables and its size is 1.6GB.
I've been looking for methods to do it. I have found some tools that help you to migrate. However most of them do not support spatial data.
I also read How to migrate spatial tables from MSSQL to PostGIS . I have just seen this post. I haven't tried it yet, I don't know if it could work for MySQL. Furthermore, I'd like to do it using DB managers reather than Qgis or ArcGis.


texts: How to upgrade MySQL to newer version on existing database

texts:
I am having data of 20 GB with MySQL version 5.0.24.

Now I want to update MySQL to newer version 5.5.16.

We can do it by taking complete database backup and uninstall old MySQL and install new MySQL and restore database.

But, Is there any way to upgrade MySQL directly to newer version, as it is a production setup and wanted very less down time of the server.

Please help me on this.

I am running on Windows.


texts: How much space is needed to reclaim space from a table in INNODB

texts:
I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the records off site and DELETED the records from my table, and would like to reclaim the space in the table. I'd like to know if I am going to need about 200 GB of free space in order to reclaim the space, or closer to 700 GB.

innodb_file_per_table is on.

MySQL version is 5.5.

And I am considering either running OPTIMIZE TABLE (eg Recover the disk space after deleting rows from table) or using Percona Toolkit's online schema change (eg http://www.percona.com/blog/2013/09/25/how-to-reclaim-space-in-innodb-when-innodb_file_per_table-is-on/). I'd prefer to do the later, because I can avoid write locks, but I'd use OPTIMIZE TABLE if there was a considerable space difference required.

Will I need closer to 200 gb or close to 700 gb of free space in order to reclaim space from InnoDB? If closer to 700GB, would that answer be any different if I first converted the table to MyISAM (eg http://www.percona.com/blog/2014/08/21/the-mysql-ibdata1-disk-space-issue-and-big-tables-part-1/)?

Thank you for your help!


texts: Any way to make stored procedure code dynamically reference other databases, without dynamic sql?

texts:
I've a situation where I have two databases:

ABCData
ABCUsers

One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded:

INSERT ABCUsers.dbo.Table1 VALUES('a');

But the next problem we run into is that we want to backup and redeploy these database pairs to similarly named other databases for testing and dev purposes:

XYZData
XYZUsers

Which means the XYZData database will contain a sproc that doesn't work - because the insert has a hard coded ABCUsers.dbo.Table1, and we don't want to get into changing it

We could find out the current database name in the sproc, adjust it and run the sql dynamically:

DECLARE @DataDbName sysname = (DB_NAME());
DECLARE @UserDBName NVARCHAR(20) = (SELECT REPLACE(@DataDbName, 'Data', 'Users'));
EXEC CONCAT('INSERT ', @UserDbName, '.dbo.Table1 VALUES(''a'');')

But I think the prospect of doing so actually makes me feel physically ill ;) (not least because this is a massively simplified example of tens of sprocs each making tens of calls to other dbs)

Is there a way to alias a database name during its creation, so the sproc can always say something like:

INSERT UserDbAlias.dbo.Table1 VALUES('a');

And when the ABCUsers database is created, the UserDbAlias -> ABCUsers but when XYZUsers db is created UserDbAlias -> XYZUsers

Alternatively, this is a git/tfs source control - is there a way to write the source control version of the sproc so that it is modified dynamically when it's deployed, and the original source can contain some kind of placeholder for the db names, but they're replaced with actual hard coded varying db names upon deploy (automatically)

Footnotes: I looked at A way to reference other DB without hardcoding its name which does seem to describe my problem, but doesn't have an answer. That question mentioned "I've looked at synonyms but they wouldn't work out with EF6.." (paraphrase)

Would synonyms work out for me? We use EF6, but I'm not sure what the actual problem is in terms of why it wouldn't work out. Our sproc name in the db never changes, and we don't need to alias the sproc itself, it's the insert table name we need to alias. If the sproc is always called Sproc1 regardless of whether it's in ABCData or XYZData, and the synonym resolving happens inside the sproc, would EF6 even have a problem with it?


texts: How to restart PostgreSQL server under CentOS 7

texts:
I installed PostgreSQL 10.1 under a CentOS 7.3 environment.

The service is started (postmaster.pid file present under /var/lib/pgsql/10/data), but I need to reload configuration or restart the server following a change in pg_hba.conf.

However, trying different commands, I get the following:

pg_ctl reload -D /var/lib/pgsql/10/data
bash: pg_ctl: command not found

service postgresql reload
Redirecting to /bin/systemctl reload postgresql.service
Failed to reload postgresql.service: Unit not found.


texts: Current size of a PostgreSQL table (vs disk space used)

texts: I have a table that was recently VACUUMed, which means the disk space it takes is higher than its actual size (what pg_total_relation_size might report). Is there a way to tell how much space it actually takes / how much free space I have "in the table"?


texts: Should I use a storage engine other than MyISAM to optimise these tables or should I get better disks?

texts:
There's a production database I'm working on, whose 4 largest tables contain between 4 million and 10 million rows each and about 15 fields each, with indexes on different field types (numbers, varchar and text). The total index_length is 2.2GB and the data_length is 6GB. All these tables use MyISAM and they have a read/write ratio of 66/33.

Here's the output from /etc/init.d/mysql status during idle time:

Server version          5.1.37-1ubuntu5
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
...

Threads: 27  Questions: 5430994  Slow queries: 59  Opens: 298  Flush tables: 1  Open tables: 128  Queries per second avg: 182.695

The number of queries per second is at about 300 during peak time.

In the last couple of weeks, the database has become so slow that sometimes queries are locking for 2+ minutes. So, I increased key_buffer_size to 4GB (I have 8GB on the machine). The lock times have decreased. Yet, it's still unbearable during peak hours, especially when the reads/writes ratio approaches 50/50.

The server is running on a virtual environment so I/O is not great and most of the select queries do a ORDER BY <some_indexed_field> on results sets with an average of 50k rows.

I've successfully tuned this database before when it was still smaller but now I'm stuck.

Update:
Disk utilization easily reaches 100% during the day.


texts:
Yesterday while I was traveling, one of our SQL Server 2016 servers choked on a TempDB that was full. Since I'm the only DBA, one of the system engineers tried to "fix" the server by rebooting.

This triggered some sort of iSCSI/SAN/LUN/etc. cascade of insanity that resulted in the entire data drive being lost (user DBs, system DBs, error log files... all gone). Their response was to supply me with a nice, new, iSCSI drive since they couldn't get the old one back. (sigh)

So today, I restored the system DBs from backup, then restored the user DBs from backup. I then did a full backup on all of the freshly restored databases. Just in case.

But I noticed something.

I restored a two-day-old backup of Master that was 785,489 KB. After completing all of the other restores, the new backup of Master is 718,519 KB.

I restored a two-day-old backup of MSDB that was 36,019 KB. After completing all of the other restores, the new backup of MSDB is 18,333 KB.

(I can't really do a similar comparison on user DBs because they were Full, Diff, and Log restores...)

The backups are compressed. These were full backups that I restored from, and full backups I created. As system databases, there are no diff or log backups being applied in between.

Is it normal for a just-restored database's backup to not be the same size or at least nearly the same size as the original?


texts:
I have an innodb table with over 140 million rows taking 26GB. I would like to drop this table however this is taking way too long, probably days. How can I speed up this query?

processor: Intel® Xeon® E3-1220 4 Cores x 3.1 GHz (3.4 Turbo Boost)
ram: 12 GB DDR3 ECC


texts: Are views harmful for performance in PostgreSQL?

texts:
The following is an excerpt from a book about db design (Beginning Database Design ISBN: 0-7645-7490-6):

  The danger with using views is filtering a query against a view,
  expecting to read a very small portion of a very large table. Any filtering should be done within
  the view because any filtering against the view itself is applied after the query in the view has
  completed execution. Views are typically useful for speeding up the development process but
  in the long run can completely kill database performance.

The following is an excerpt from PostgreSQL 9.5 Documentation:

  Making liberal use of views is a key aspect of good SQL database design. Views allow you to encapsulate
  the details of the structure of your tables, which might change as your application evolves,
  behind consistent interfaces.

The two sources seem to contradict each other ("do not design with views" vs. "do design with views").

However, in PG views are implemented using the rule system.  So, possibly (and this is my question) any filtering against the view is rewritten as a filter within the view, resulting in a single query execution against the underlying tables.

Is my interpretation correct and PG combines WHERE clauses into and out of the view?  Or does it run them separately, one after another?  Any short, self contained, correct (compilable), examples?


texts: What happens when MySQL/InnoDB buffer pool size is small and a large transaction is run causing the buffer pool overflow?

texts: Let's say we have a transaction running 50,000 inserts which dirties approximately 600 MB of the database. If the buffer pool size is 128 MB, there will be dirty pages in the buffer pool while the transaction is running, and these will need to be evicted to make space. My question is, what does MySQL/InnoDB do with these dirty uncommitted pages?


texts: Logic rules representation and storage in DB column

texts:
I have two kind of "elements". One that generates time series (lets call them generator element) and the other that based on some logic rules applied to the time series became "activated" (activable elements).

This simplification represent the time series

CREATE TABLE data.time_series
(
    generator_element_id uuid NOT NULL,
    epoch bigint NOT NULL, -- UNIX epoch
    level integer NOT NULL, -- values from 0 to 3
    -- other fields
    -- PRIMARY KEY (generator_element_id, epoch),
    -- FOREIG KEY TO the table with the generator_elements
)

The idea is that the activable elements are activated following rules like

Legend:

Generator element GE
Activable element AE

IF  (GE1 has level > 1 AND GE2 has level > 1) OR (GE3 has level > 2) for a given instant T1  THEN AE1 becames activated

I'm trying to find the best way to represent this logic rules in the database, as those will be configured by the user and may include multiple conbinations of "AND" and "OR" rules

The issue is precesily with this combination of using AND's and OR's, as if it would be only one type of those I could create another table with the relation between the different kind of elements, and the thresholds. But using a combination of ANDs and ORs i'm at loss..

My first thought was to store those relations using a plain string, similar to what I described before, but using the corresponding UUIDs for the generator elements, and then have a service that will process those rules, and decide to activate or not the elements.

I'm searching for alternatives to this approach as I don't think that my approach could scalate well enough, nor I like it personally...

For context I'm using PostgreSQL 12.0 with docker

Edit:

Clarifying about the epochs as it was not clear. The epoch value is not included in the logic rule "definition", that only includes the elements and its levels (actually it includes other ids but to keep simple just this two variables).

As for the actual calculation the records will be retrieved and separated in "bins" for each epoch. This way we apply the rule over the records for that epoch (assuming that there will always be records for all the generator elements for the given epoch)

Also adding some test data as an example. In this case I'll use one epoch value 1581638400 that corresponds to 2020-02-14 00:00:00Z

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000001', -- uuid for GE1
    1, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000002', -- uuid GE2
    2, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

INSERT INTO data.time_series
VALUES (
    '00000000-0000-0000-0000-000000000003', -- uuid GE3
    0, --level
    1581638400 -- epoch for 2020-02-14 00:00:00Z
)

Here the rule specified as my proposal would be for the activable element AE1:

-- Omiting the 0s for simplicity
"('000...01' > 1 AND '000...02' > 1) OR  ('000...03' > 2)"

With the dummy data, the result will be that AE1 is activated as the first part of the rule is completed

But with the way that the rule is saved in my proposal, i'll have make a specific processing system for that...

Any help is apreciated