texts: One table to hold all ids related to one user - bad idea?

texts:
I was wandering if it's a very bad idea to have one table that holds all the ids related to one entity?

For example let's say i have 3 tables each containing data about one user. Should i use an extra column called user_id in profile and settings table or should i use an extra table called user_index and make use of joins?

[1]USER
    id
    email
    password
    salt
    created

[2]PROFILE
    id
    full_name
    bio

[3]SETTINGS
    id
    max_packets
    delivery_day

[4]USER_INDEX
    user_id
    profile_id
    settings_id

Will this make queries extra slow? Is it worth the extra code? Is it even a pattern? Maybe EAV?(I mean i saw something similar in Magento but i'm not sure if this is exactly the same). Can this provide any performance enhancement or just a 'OCD-feel-good' moment?

Currently using MySQL 5.7.26 and i was thinking about using InnoDB as a storage engine. The app is going to be written in PHP 7.3. OS Ubuntu 19.04.

Thank you!


texts:
I read the good posts here and here, but my scenario is a bit different, I cannot consider that older data becomes read-only data because any record in my Transactions table can be subject to updates at any time.

The (short?) story is: I have a Transactions table that should hold over 2 billions of items in it.
The data is produced by distinct jobs, each job generates its own data into the Transactions table. One column is a foreign key: Transactions.JobId. This table contain working datasets logically splitted between jobs (jobs are kept in a separate Jobs table).

The operations on the Transactions table are:

BULK INSERTs (using C# SqlBulkCopy Class)
UPDATEs for single rows (rows get their statuses updated)

As the Transactions table gets bigger, we start getting poor performance, even sql deadlocks. The hardware won't keep up with the needs, so I am thinking of splitting the table Transactions into multiple smaller tables (let's say max of 100M records/table). Then, I can define a "covering view" to aggregate the data when I need to perform some general query. When the first Transactions1 table is full (contains 100M records), any new jobs will be pointed to store their data into the second table Transactions2 and so on.

Question is, do you see this as a feasable approach, is there other options?
Another one would be an expensive Enterprise license for SQL Server.

PS: Some modules of my C# apps use ADO.NET, other modules make use of EF Code First and Linq2Sql to access the data.

UPDATE 1

Here is the approximative Transactions table structure:

PK: [Id] [bigint] IDENTITY(1,1) NOT NULL,
FK: [UserId] [bigint] NOT NULL,
FK: [JobId] [int] NOT NULL,
FK: [ItemStatusCodeId] [tinyint] NOT NULL,
+ other 7 columns

(indexes are created for each FK).

The column that is frequently updated is ItemStatusCodeId which is a FK that points to another table that holds 10 records only. The update is as simple as it can get:

UPDATE Transactions SET ItemStatusCodeId = <value> WHERE Id = <key>


texts:
I need to associate customers' order with their "level" (Silver, Gold, etc.) when they placed the order:

CRM server::CRM db::CRM table
----------
CustomerID   PreviousLevel    NewLevel    NewLevelGrantedOn

Order server::Order db::Order table
----------
OrderID     CustomerID    OrderPlacedOn

In an SSIS package I did this:

Execute SQL against Order db to extract the orders and put them in an object variable;
Loop through each order using "foreach container", in which I put a data flow task select top 1 * where CustomerID = ? and LevelGrantedOn < ? order by LevelGrantedOn desc that extract data from CRM db (both parameters come from step one), derive some columns and write the output to another table.

There are more then twenty thousand records in the order db, which means the data flow task will be executed for more than twenty thousand times. The CRM db will be queried for more than twenty thousand times, too. It takes more than an hour to do these.

Can I utilize some built-in features to speed up these (or do it in a "smart" way)? And, is an hour a long time, in the context of ETL and / or SSIS?


texts: Why is dbo the only grantor in Microsoft SQL Server?

texts:
I can't understand why is dbo the only grantor in SQL Server? Or am I wrong?

I mean it as follow:
No matter as which user I am currently loged in in SQL Server Management Studio, when I go to properties of a database under Securables and grant some permissions to a user and save it and come back to Securables: dbo is shown as Grantor for those permissions, which I granted before and not the current loged in user.

Why is it so?

Under which scenario could I see another user than dbo as grantor?

Thanks


texts: How to represent foreign key in an Extended ER Model in Chen notation?

texts:
This is a spin-off of the question at How to represent foreign key in an ER diagram? which is too vague about the needed notation.
There the idea of underlining FKs with a dotted line is stated. I also remember this from an exam. I am not sure though, it could have been a double underlining as well, this question is not determined to the dotted line. I have searched a bit, finding that a dotted line represents a weak key attribute.

Source: https://conceptdraw.com/a977c4/p1/preview/640/pict--chen's-erd-design-elements---er-diagram-(chen-notation).png--diagram-flowchart-example.png
Can I use a certain format to show FKs in an EERM Extended Entity-Relationship Model in Chen notation?


texts:
I am installing a MySQL HA cluster in kubernetes v1.16.0 like this:

./helm install report incubator/mysqlha -n middleware

and now I connect to the MySQL using this connection:

jdbc:mysql://report-mysqlha.middleware.svc.cluster.local:3306/meow_report_pro?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&useSSL=false&verifyServerCertificate=false

but the server side give me this tips:

aused by: java.sql.SQLException: The MySQL server is running with the --super-read-only option so it cannot execute this statement
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:953) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$d84hfK2o(ClientPreparedStatement.java:370) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]
    at com.mysql.cj.jdbc.ClientPreparedStatement.execute$original$d84hfK2o$accessor$wvgzu2VR(ClientPreparedStatement.java) ~[mysql-connector-java-8.0.19.jar!/:8.0.19]

so what shoud I do to fix this problem?


texts: SQL Server job with powershell script fails but the script works fine in Windows powershell window

texts:
I have setup a SQL Agent job to delete files older than 7 days.
The script does it job when run through windows powershell window. However the same script does not work from SQL Agent job

$erroractionpreference = "Stop"
$computer = gc env:computername
Get-ChildItem -path Microsoft.PowerShell.Core\FileSystem::F:\SQLBackups\RestoreScripts\$computer_DatabaseRestore*.txt | where {$_.Lastwritetime -lt (date).addhours(-168)} | remove-item -recurse

Error:

Message
Unable to start execution of step 1 (reason: line(1): Syntax error).  The step failed.

What could be the reason to fail from SQL Agent?


texts:
One sql request is performed 10 times longer with Postgresql than Oracle.
How can we speed up that query with Postgresql?

Hereunder the request:

select t1.c1, t1.c2 from MyTable t1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MyTable t2 where t2.c4=t1.c4);

The complete query:

select t1.c1, t1.c2 from MyTable t1
left outer join Table t3
on t1.c1=t3.c1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MyTable t2 where t2.c4=t1.c4);


texts:
The following code returns this message in case something goes wrong:

  Transaction count after EXECUTE indicates a mismatching number of
  BEGIN and COMMIT statements. Previous count = 0, current count = 1.

OPEN extensionViews
     FETCH NEXT FROM extensionViews
     INTO @Viewname
     WHILE @@FETCH_STATUS = 0
     BEGIN
        BEGIN TRY
                exec sp_refreshview @Viewname
        END TRY
        BEGIN CATCH
            print 'ERROR sp_refreshview'
        END CATCH

        FETCH NEXT FROM extensionViews
        INTO @Viewname
     END
     CLOSE extensionViews
     DEALLOCATE extensionViews

I already found out that there is something going wrong with the implicit transactions, but I do not really understand it entirely.

In case of a CATCH, do I have to explicitly ROLLBACK the transaction? Does this mean that I have to start the transaction explicitly as well insode of the Cursor to rollback THIS one command instead of all?


texts: MySql shell command not replicated to slave - "LOAD DATA INFILE"

texts:
I have a shell script that looks something like that:

 #!/bin/bash
 mysql -udb_user -pdb_password -e "LOAD DATA LOCAL INFILE '/srv/some_file.csv'
 INTO TABLE db_name.db_table FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'
(\`col1\`,\`col2\`,\`col3\`);"

the problem is that when I run this script on my master server the call doesnt get replicated to the slave server (even though /srv/some_file.csv exists on both servers).

any ideas how to make shell command replicate over servers?


texts: What kind of database is best with conditionals?

texts:
I'm looking for the "best" (I know there is probably never an objective best anything) database-kind for chaining conditionals. To use the popular "person" analogy:
I have several characteristics that should all be applied, but it is not known whether all are set.

haircolor
ZIP-Code
brother's name
cat-Owner

A person may have all characteristics filled in or none at all. Assuming the following dataset:
ID:    tobias
color: blonde
cat:   false

ID:    james
cat:   true
zip:   09648

ID:    linda
bro:   john
cat:   false
zip:   01558
color: brown

ID:    ellie
cat:   true

ID:    alice

Querying cat-owners should give ellie, james
Querying brown-haired non-cat-owners should give linda
Data-Set estimation would be 500.000+ entries. With few writes and way more reads.
I thought Graph DBs might be a good idea at first and by making edges the various conditions I could maybe filter the entries relatively easy. Relational Databases would be easy to implement, but my goal is maximum performance so I'm not too sure about that.


texts: High MySQL CPU usage, queries seem fine, not running ntp

texts:
I've had about 48 hours of extremely high CPU usage on my main MySQL database for no apparent reason.

I realize this question is quite similar to the one posed here: High CPU usage from MySQL with no queries at all running

However, this MySQL server does not have ntp installed at all, and as the leap-second bug is from a few months ago, I don't think that's the explanation.

SHOW PROCESSLIST does not show any long-running processes/queries, and there have been no recent changes to DB schema, etc.

The server is also extremely locked down so that only other application servers within the VPN may access it, and has plenty of RAM (17 GB) which is not maxed out either.

Is there anything else that I should look into, or any other common causes of high CPU?

The server is an EC2 m2.xlarge instance: 17.1 GB RAM, 6.5 EC2 "Compute Units" with this configuration:

user        = mysql
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
skip-external-locking

key_buffer      = 256M
sort_buffer_size    = 16M
read_buffer_size    = 16M
max_allowed_packet  = 32M
thread_stack        = 192K
thread_cache_size       = 8

max_connections        = 200
thread_concurrency     = 12

query_cache_limit   = 4M
query_cache_size        = 128M

log_error                = /var/log/mysql/error.log

log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

server-id       = 1
log_bin         = /var/lib/mysql/mysql-bin.index
expire_logs_days    = 10
max_binlog_size         = 1000M
sync_binlog     = 1

innodb_data_file_path   = ibdata1:2000M;ibdata2:10M:autoextend
innodb_buffer_pool_size = 11G
innodb_additional_mem_pool_size = 128M
innodb_log_file_size    = 1000M
innodb_log_buffer_size  = 32M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


texts: SSRS access for AD-trust *groups* not working

texts:
I have the following setup:
AD domain "developer.local" has a two way external non-transitive trust with AD domain "customer.local".  There is a 2014 SQL Report server in the developer.local domain.

If I assign SSRS permissions to a group from customer.local, group members receive a "does not have required permissions" when logging in the the SSRS WebUI.  If I assign permissions to individual users then it seems to work.

Is this not a supported scenario, or what should I check to get this resolved?


texts: Failover and Failback SQL Server Log Shipping

texts:
I had a question on Log shipping Manual Fail over

Let me explain about environment, Primary Server A (with Cluster setup A-P Mode) Secondary server B(DR server standalone).

We had enabled Log Shipping on a database TEST from Primary Server and now we would like to test by doing a manual fail over and i had few questions.

Log Shipping Jobs are disabled on both A and B Servers and Log shipping is not disabled or removed, Once the Server B is brought online by restoring the all the logs from Server A

Can i re-attach/reconfigure the secondary database to existing Log shipping once it is online.
I tried using backup with no recovery option on secondary database and log shipping restore jobs are skipping.
Is there any method expect role changing can we reconfigure the log-shipping without restoring the database from first?

any references would be appreciated.

Thank You


texts: Search all columns as a single text per row

texts:
Need an extension that offers a full-text-search with a kind of contains(*,text) instead to concatenate each field-value.

There are times when a formal structured query language is overkill, and the easiest way to find the right content is to perform a search, like you would with a search engine such as Google or Yahoo (...) JCR-SQL2:
SELECT * FROM [nt:base] WHERE CONTAINS([nt:base],'full-text-query')
Full+text+search example in other SQL engine


texts: How can I use Count() or Sum() with advanced criteria such as matching the most recent rows where a condition is true?

texts:
I have created a table with the following structure:

| ID | RecordDate       | SerialNumber | ProductID | StageID | Result |
|----+------------------+--------------+-----------+---------+--------|
|  0 | 2019-05-23 06:00 | A123456789   |         3 |       6 |      1 |
|  1 | 2019-05-23 06:04 | B123456789   |         4 |       6 |      0 |

It is interesting to be able to generate some basic statistics anchored on the StageID or the ProductID.  Like this:

DECLARE @StartDate datetime = '2019-05-10 08:00:00';
DECLARE @EndDate datetime = '2019-05-18 09:00:00';
DECLARE @StageID int = 6;

SELECT ProductID,
    COUNT (*) 'Total',
    SUM (CASE WHEN Result = 1 THEN 1 ELSE 0 END) 'Output'

FROM table1
WHERE RecordDate BETWEEN @StartDate AND @EndDate AND StageID = @StageID
GROUP BY ProductID;

This yields the following typical output:

| ProductID | Total | Output |
|-----------+-------+--------|
|         3 |  4533 |   4211 |
|         4 |  3444 |   3001 |
|           |       |        |

Where:

Total = total number of product processed including passes, fails, duplicates (re-attempts) etc.

Output = count of good product which has been sent on from the process.

Now, one problem is that my count of output isn't very precise.  If someone were to process the same product twice, I would count it twice even though the true output stays the same.
I have also counted product whose result was 1, but then it was processed again and the result was zero.  I count this even though the true output has actually gone down.

What I really want to do in this scenario is count the most recent record for each SerialNumber where that result was 1.  I can get that information with a basic query like this:

SELECT COUNT(*)
FROM ( SELECT MAX(ID) AS 'ID'
       FROM table1
       WHERE StageID = @StageID
         AND Result = 1
         AND RecordDate BETWEEN @StartDate AND @EndDate
       GROUP BY SerialNumber ) ID

This query gives me the single result that I want, but I can't figure out how to apply such criteria to the original format so that I get the given table grouped by ProductID.  I feel like I want another count() or sum() function with some critieria attached.

How do I do it? Or is there a better way? I am very new to SQL so there could be a basic answer staring me in the face here.


texts: How to properly use moveChunk when chunks of certain range needs to be moved?

texts:
A MongoDB 3.6.3 database has two shards and a mongodump file with partial data of one collection needs to be restored. The first shard is named "fast" while second "slow". The idea is to restore the dump to the "slow" shard. According to sharding rules the data should go to the "slow" shard, but it actually goes to the wrong one when restore is tried.

Before restoring the data, I want to manually move a range of chunks from fast to slow shard, but are unable to properly issue the command. All examples found are showing of moving only one exact chunk. _id is used as a sharding key.

Try 1:

use admin
db.runCommand({ moveChunk: "db.use", bounds : [ {_id : ObjectId("58b60e73e5d4e7019aa2be17")}, {_id : ObjectId("58bca60f5067031c77b03807")} ], to: "rs1" })

This is the response:

{
        "ok" : 0,
        "errmsg" : "no chunk found with the shard key bounds [{ _id: ObjectId('58b60e73e5d4e7019aa2be17') }, { _id: ObjectId('58bca60f5067031c77b03807') })",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1523459407, 14673),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        },
        "operationTime" : Timestamp(1523459407, 14673)
}

Try 2:

sh.moveChunk("db.use", {_id:{$gt: ObjectId("58b60e73e5d4e7019aa2be17"), $lt: ObjectId("58bca60f5067031c77b03807") }},"rs1")

Response:

{
        "ok" : 0,
        "errmsg" : "no shard key found in chunk query { _id: { $gt: ObjectId('58b60e73e5d4e7019aa2be17'), $lt: ObjectId('58bca60f5067031c77b03807') } }",
        "$clusterTime" : {
                "clusterTime" : Timestamp(1523460271, 11742),
                "signature" : {
                        "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="),
                        "keyId" : NumberLong(0)
                }
        },
        "operationTime" : Timestamp(1523460271, 11742)
}

Any idea how to move chunks that belong to certain shard key range?


texts: How to store user activity that supports undo?

texts:
I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables:

post_activity:
post_id | user_id | time_created

post_users:
post_id | user_id | time_created | time_last_seen

With these 2 tables, I can query all records from post_activity where the post_user.time_last_seen is less than the post_activity.time_created for a count of all new items.

The problem is that post_activity is essentially an anonymous log, with no associated rows or notion of the type of action that caused it. What I can’t solve is for example this scenario:

User A creates new post (1 new activity stored)
User B comments on new post (1 new activity stored)
User C sees "2 new!" but...
User A or B deletes their contribution.

As far as the system is concerned, there are 2 unseen items but the user may see nothing!

Can anyone provide a simple demonstration of how I might have a stronger relationship between activity and user actions here? Particularly one that supports unseen deleted activity?


texts: Surrogate key resolution, or what am I missing in the Surrogate v Natural argument?

texts:
I've read a lot from both camps in the surrogate vs. natural key debate. What I am struggling to understand, and what seems to be glossed over, especially in explaining how surrogate keys are more efficient, is how do referencing tables resolve a surrogate key?

Say, I am using an ORM in an application, and have a userID in an application object. now we want to insert into a SelectedTask table and a SubTask table that are defined as follows (ignore the awkwardness of the real word example):

CREATE TABLE SelectedTask(
userID INT References User(userID)
taskNumber INT,
name TEXT ,
jobSiteID INT References Site(jobSiteID),
PRIMARY KEY (???)
);

CREATE TABLE SubTask(
assignee INT References User(assignee),
subTaskID INT References Subtasks(subTaskID),
timeAllowed INT,
reportTo INT References Group(reportTo),
-- how do we reference SelectedTask?
);

If we use a surrogate key for the SelectedTask table, how do we use that in the SubTask table? If we use natural keys, the primary key could simply be a compound primary key of userId and taskNumber, and we could insert into both at the same time assuming we have programmatically gathered all of the pieces.


texts:
I have a de-normalized table as

Original De-Normalized Table

id  text
23  first,second|third,fourth,fifth|sixth

I want to normalize the database to create the following tables

Table 1: (Split by "|")

id  parent_id   value
1   23          first,second
2   23          third,fourth,fifth
3   23          sixth

and then,

Table 2 (Split by ",")

id  parent_id   value
1   1           first
2   1           second
3   2           third
4   2           fourth
5   2           fifth
6   3           sixth

The easiest and straightforward method is to use sub-query (SELECT), but my concern is the performance, as this process should be conducted for a very long table and regularly (new data coming).

Is there an efficient way to normalize this table with high speed?


texts: shall I use multiple separete tables or one table with multiple partition on oracle

texts:
I have an entity lets call it Response, and another entity called ResponseType, Response entity contains the ResponseType; so when I am building the physical model I confused with how to implement it; I have two option ; 1. I can create a Response table for each of ResponseType. 2. I can create a Global Response table with a ResponseType Column and create a partition for each ResponseType; Note that; Response Entity is same for each of ResponseType. Each ResponseType is equivalent.

What is your idea?


texts: SQL Server DROP Database which is in a long roll back process

texts:
I have a DataBase that is taking very long to rollback a transactions.
Which could be normally because it is rolling back a hugh amount of data.

However I have the scripts to create the DB again and just want to drop the DB so that this roll back stops...

How can I achieve this?

thanks already!

kind regards


texts: Are there any databases with out of the box Point-in-Time-Architecture support?

texts:
I am looking for the descriptive term of a database system (available in the cloud, or to be deployed on a local server) that has build-in support for a point-in-time (PTA) architecture. I am not sure that PTA is the common term for these kind of databases (which is part of my problem), but at least the article Database Design: A Point in Time Architecture comes close to what I am looking for.

Requirements

Simply said, what I am looking for is approximated by an Excel file under version control like git/hg/svn.

At my company we'd like to keep track of a 'table' with data, where we want to be able to easily see how the table looked at a specific point in time (even 5 years later).
Over time, the table will both change in rows (adding records, obviously) and in columns (i.e. a change in database schema).
The system should also provide support for logging which user made which changes.
Additionally, the table should be easy to query, so that we can use the data contained in it for some automation.
Furthermore, it should be easy to compare two tables from different points in time (like you can do with the page history on Wikipedia articles).
Finally, the database should support multiple of such tables.

This sounds very much like we need audit trail functionality (e.g. using history tables). However, two key differences: (a) we don't want to build from the ground up such a database ourselves, and (b) the time rollback and querying functionality should be build-in and an integral part of the system (rather than audit trail functionality which might be a bit cumbersome to use ?).

What we do now & solutions we're considering

Previously we've email updated Excel sheets back and forth. As it's never entirely clear who has the master copy, we switched to putting the Excel files on our file server, with the filename indicating the date. All this is is obviously not very practical. We've considered solutions like putting CSV (or XML, JSON, ...) files in Git and developing some supporting scripts, but this would require the users to have at least some basic knowledge about things like Git, and we would also need to develop a (html?) GUI to (a) avoid people requiring command line knowledge and provide a (b) provide an easy way to view the data. A wiki (like wikipedia) also provides part of the functionality we're looking for (the 'View History' page makes it easy to compare the different snapshots of the page), but lacks some other features (like easy querying the records, as it's just a bunch of 'text').

Final question

So, is there a common term to describe this sort of snapshot table/database functionality that I am looking for? (so that I can search google if there is any such software available). I would assume this to be a commonly needed feature, but apparently it is not.


texts: Workout change operation with temporal table records

texts:
I have just started using using temporal tables/system versioning, its very easy to setup and get going in terms of querying the data. The one thing missing for me is storing the change operation (update/insert/delete) or generating it on the fly from the system_time period. Can anyone point me in the right direction to achieve this? Or isn't this achievable?

Thanks in advance.


texts:
I am trying to execute a sql procedure for a list of tables in sqlcmd mode to redirect all the output into one file per table.

Unfortunately only the last file of the redirected outputs of all commands gets content.

For example I execute the following:

:OUT  c:\test\tab0.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo', @table_name ='tab0', @include_timestamp = 0, @ommit_images = 1, @CreateProcedure = 1
:OUT  c:\test\tab1.sql
 EXEC [dbo].[sp_generate_merge] @schema = 'dbo', @table_name ='tab1' , @include_timestamp = 0, @ommit_images = 1, @CreateProcedure = 1

Both files are created, but only the last file gets all the content.

Any hints how I can change this in order to get the results in one file per command?


texts:
I came across a question asking whether it is possible to have unnamed rows and columns in a database. I assumed unnamed implied primary key. I'm not sure whether my assumption is correct. When I searched it on the internet, I came across an article in Wikipedia with the following statement:

The same row can appear more than once in an SQL table. The same tuple cannot appear more than once in a relation.

I thought that both row, tuple, and record mean the same thing.
What does this mean?


texts: MySQL: Capture every 5 values from column a in Table A to create one row in Table B

texts:
I have submissions of a form from a web page records in a database.
the table has the following schema

submit_time    form_name   field_name  field_value field_order file

the form has 5 fields, each form submission results in 5 rows in the previous table, one row per field.
the following is an example of 5 rows created after a submission.

    submit_time       form_name     field_name  field_value field_order file
    1427715050.2890 Contact form 1  your-name       Jung            0   NULL
    1427715050.2890 Contact form 1  your-email      Jung@Jung.Jung  1   NULL
    1427715050.2890 Contact form 1  your-subject    Jung maybe :)   2   NULL
    1427715050.2890 Contact form 1  your-message    Jung :D         3   NULL
    1427715050.2890 Contact form 1  Submitted Login Mohammad    9999    NULL

I want the data to be written in another table in another database the new table has the following schema:

Name        email       subject         message         Login

What I tried
I thought about writing a trigger like the following:

CREATE
    TRIGGER copy_data
    AFTER INSERT
    ON TABLE_A FOR EACH ROW
    BEGIN
    # check if submission entry exists in table B

    # NO :
        # INSERT the data in the corresponding column

    # YES:
        # UPDATE the data in the corresponding column

    END

but I can't insert NULL values in the second table.

what can I do in such situation ?

Note: I can't guarantee the order of the rows in Table A ( which field will be inserted first is something I can't control)


texts: Copy whole database from SQL Server to SQL Server using linked server

texts:
I am using SQL Server 2014 Management Studio installed in my system. And using linked server I have connected to a linked server.

Now here I need to copy whole database from linked server to my local SQL Server using linked server itself.

I am using a linked server instead of backup/restore because using linked server I can copy remote database.

Tool used: Microsoft SQL Server 2014 Management Studio.


texts: How Oracle DB maintains client identification

texts:
I know in 'dedicated server' mode we have one user process for each client and pool of user process in 'shared server' mode. in either case How oracle maintains the client identification ? Please correct me if my understanding is wrong ?

Thanks,


texts:
I want product name to display blank field if same product came before .

product_name    | colors_available | items_left
JUMPAN TSHIRT   | red              | 3
                | blue             | 2
                | black            | 9
B.BALL CUP      | red              | 10

can i get this ouput using mysql query?


texts:
I've been tasked with identifying a solution to help identify possible duplicates when inserting new user records into a MySQL database. Does anyone know of any cheap, preferably free, solutions?

The sort of duplicates I need to detect are common forms of shortened English names - such as Bill for William, Bob for Robert etc - along with misspellings as well as plain duplicates. I've read a bit about using Lucene but it seems to be more for full text searching and I'm unsure if it supports the dupliatcae name matching I'm after?


texts:
I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference:

In Oracle - I performed an update for one row without issuing commit or rollback and in another session I can view the underlying record, of course, I could view the last committed data and not the value that was yet to be committed.

In MSSQL Server - When I did the same operation in another session the SQL Server kept waiting for commit or rollback of the row that is getting updated.

Could somebody please explain locking mechanism between MSSQL server and Oracle.


texts: What does "InnoDB stores its tables and indexes in a tablespace" mean?

texts:
Was reading the mysql engine innoDB tutorial, I came across this sentence

  "InnoDB stores its tables and indexes in a tablespace"

Can any body explain me this what does it means.

Thank you :)


texts: Align auto increment index to next thousand

texts:
I am using MySQL.

When applying a big change to a production database, I would like to change the next auto-increment index of all my tables to align them to the next available thousand.

In other words, for each table, make the next new row in that table bear an ID that is 1000-aligned.

That would make it easier for humans to detect pre- and post-migration records without impairing DB functionality.

Is there a quick automated way to do that?
More importantly, is there an important drawback I missed?


texts:
I have a database with 250 small and medium sized tables. These tables are related with both MyISAM and InnoDB storage engines. There are multiple triggers that have been created on different tables. These triggers are getting executed on Insert, Update and Delete events. My system has started to sometimes perform bad. On check, I found that there are a huge number of DB connection threads running.

Now my question is:

Do these triggers affect my system performance? If yes, Is there
any better alternative for these triggers?


texts: How to set up bidirectional replication with Postgresql

texts:
I have two Linux servers having Postgresql-10 on each of them. On both of them, there is a database, say dbexample, having same schema, table, everything. Both of them are capturing data from an application and storing them on their database (dbexample).Let me tell you since both are capturing data from the same application, there is no mismatch in data at a time.

Now, what is my problem? At a time server 'A' fails due to any reason. That moment it is not able to capture data from the application. But since second is UP and have captured all the data on its database, it must have captured all the data for that time being when server A was DOWN.

Now, When Server A is UP I want all the data to be replicated on this server A from server B for that time, so that I can have all the data same on both the servers.

Considering the same scenario in case of failure of server B, I want the things to work in the opposite direction, i.e. data from Server A to be replicated to Server B.

I have gone through many tutorials and have come-up with below solutions:

To set Logical replication between both servers and make Publisher and Subscriber of each other. (But that is possible one way not bidirectional, if I am not wrong). So please suggest what could be the possible solution.
To arrange a server C and restore the schema only of database, and make Server A publisher and Server C its subscriber. Server C publisher and Server B its subscriber. Server B Publisher and Server A its subscriber. It will be like:-

A will pull data from B,

B will pull data from C, and

C will pull data from A.

Is it feasible when A and B already have data in their databases?

Which approach is better, or is there any other solution?


texts: APEX 5: Page item (Select List) with LOV must have values from a another table which return PK (FK of current table)

texts:
I have a page form where the user can make a new account. Every user has a type. The user must choose which type of user it is.
I have two tables (Users and UserTypes). What I want to do is user must choose the type from a select list which contains UserTypes.usertype in a table which has UserType_id a PK and FK in USERS.

How to make every value of that select list represent it's PK? Thanks.


texts: MySQL create index with LOCK=NONE still locks a table

texts:
I have the following MySQL RoR Migrations:

class ReindexRpushNotification < ActiveRecord::Migration
  def up
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed, processing, deliver_after), ALGORITHM=INPLACE, LOCK=NONE;")
  end

  def down
    execute("DROP INDEX `index_rpush_notifications_multi` ON rpush_notifications;")
    execute("ALTER TABLE rpush_notifications ADD INDEX index_rpush_notifications_multi (delivered, failed), ALGORITHM=INPLACE, LOCK=NONE;")
  end
end

during this migration I'm trying to perform some requests (GET, COUNT, DELETE, UPDATE) but nothing work, all these requests just wait

I found an info about index creation in background here

https://stackoverflow.com/a/36064200

http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

but it doesn't work for us

Did anybody try LOCK=NONE?

We use MySQL 5.6.23 on AWS RDS


texts:
We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ignores the non clustered index on the search column. An example query below:

select top 100 *
from   [dbo].[t_Call]
where  ID > 0
  and  throwtime between '3/20/2014 7:00:00 AM' and '3/24/2014 6:59:59 AM'
order by id

The ID column is clustered Index and Throwtime has nonclustered index. In this case, we noticed that ordering by throwtime instead of ID changes the query plan and nonclustered index is used.We are also planning to archive some of the old data (it currently has 20 mln rows!!). But making these changes in application is going to take some time and I need to find a way to make reports run reasonably fast, without making changes at the application level (oh well, such is life!).

Enter plan guide. I created the below plan guide with a nonclustered index query hint and for some reason, the nonclustered index is still not used. Am I missing something?

EXEC sp_create_plan_guide
@name = N'[prod2reports_callthrowtime]',
@stmt = N'select top 100 *
          from   [dbo] . [t_Call]
          where  ID > @0 and @1 < = ThrowTime and ThrowTime < = @2 order by ID',
@type = N'SQL',
@module_or_batch = N'select top 100 *
                     from   [dbo] . [t_Call]
                     where  ID > @0 and @1 < = ThrowTime and ThrowTime < = @2 order by ID',
@params = N'@0 int, @1 datetime, @2 datetime',
@hints = N'OPTION (TABLE HINT( [dbo] . [t_Call],
                   INDEX(IDX_NC_t_call_ThrowtimeProblemCodes)))'
GO


texts:
I have a NodeJS app that stores all of its data, even images, to MongoDB. Now I want to use an S3 (DigitalOcean Space) to store my app's files (images, 43gb) and so, I need to transfer there all files that I have in my mongo.

I found the s3cmd tool that can transfer my files to the S3 instance (DigitalOcean space), but I can't find a way to actually access those files. I know they are stored in the fs.files and fs.chunks collections, but I can't figure out how to actually use them as input to s3cmd!

Any help will be much appreciated!!!

  EDIT: This is the format the images are being stored with:

fs.files

{
  "_id" : ObjectId("5ae97922c1dabec8d2d0bdb0"),
  "filename" : "2b57455f3878d11dabc9c984da7de314_postImage.jpeg",
  "contentType" : "binary/octet-stream",
  "length" : 2291623,
  "chunkSize" : 261120,
  "uploadDate" : ISODate("2018-05-02T08:38:58.549Z"),
  "aliases" : null,
  "metadata" : null,
  "md5" : "9ad420eaa7c28a73e449199430627802"
}

fs.chunks.findOne()

{
  "_id" : ObjectId("5ae2d77f6616b4a9d93cb4b1"),
  "files_id" : ObjectId("5ae2d77f6616b4a9d93cb4b0"),
  "n" : 0,
  "data" : BinData(0,"iVBORw0KGgoAAAANSUhEUgAAAuAAAAJvCAYAAAA6OGQEAAAABGdBTUEAALGPC/xhBQAAACBjSFJNAAB6JgAAgIQAAPoAAACA6AAAdTAAAOpgAAA6mAAAF3CculE8AAABWWlUWHRYTUw6Y29tLmFkb2JlLnhtcAAAAAAAPHg6eG1wbWV0YSB4bWxuczp4PSJhZG9iZTpuczptZXRhLyIgeDp4bXB0az0iWE1QIENvcmUgNS40LjAiPgogICA8cmRmOlJERiB4bWxuczpyZGY9Imh0dHA6Ly93d3cudzMub3JnLzE5OTkvMDIvMjItcmRmLXN5bnRher38tcAAAAASUVORK5CYII=") }


texts: Poor performance of JOIN, WHERE, ORDER BY combination (MySQL)

texts:
Goal: select 10 most recent rows from posts which are related to a specific user.

There are two tables: posts (~5,000,000 rows) and relations (~8,000 rows).

posts columns:

--------------------------------------------------------------------------------------------
|  id (int)  |  source_id (int)  |  title (varchar)  |  content (longtext)  |  date (int)  |
--------------------------------------------------------------------------------------------

relations columns:

----------------------------------------------------
|  id (int)  |  source_id (int)  |  user_id (int)  |
----------------------------------------------------

I tried using JOIN for retrieving 10 most recent rows from posts which are related to a specific user:

SELECT      p.id, p.title, p.content, r.id AS rid
FROM        posts AS p
JOIN        relations AS r
ON          r.source_id = p.source_id
WHERE       r.user_id = 1
ORDER BY    p.date DESC
LIMIT       10

However, it takes ~30 seconds to execute it (SDD hosting!). I tried lots of index combinations for both tables, including indexes for single and multiple columns – none of that interfered the time of execution anyhow. Is there any way to speed up the selection?

For user_id=1 there are ~1,000 source_id's and ~450,000 rows in posts.

SHOW CREATE TABLE... results:

CREATE TABLE `relations` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` bigint(20) unsigned NOT NULL,
 `source_id` bigint(20) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `source_id` (`source_id`),
 KEY `source_id_2` (`source_id`,`user_id`),
) ENGINE=InnoDB AUTO_INCREMENT=7692 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `posts` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `source_id` bigint(20) unsigned NOT NULL,
 `title` varchar(512) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `content` longtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
 `date` int(10) unsigned DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `source_id` (`source_id`),
 KEY `date` (`date`),
 KEY `date_2` (`date`,`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4867283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

EXPLAIN results:


texts:
We have a mail profile that is in use by a number of users all of which are spread between 2 AD group logins (basic users & elevated users).  This mail profile works fine no issues and has done for 2+ years.

We had a new starter who got added to the basic users group but when she runs an SP using DBmail with this same mail profile she gets the 'profile name is not valid' error, however, the mail still gets sent.  Other users have been setup in the same way in the previous month with no issues.

Another user (same rights) runs the same SP and no errors are generated.  We moved her to the elevated rights group and it works with no errors, move her back and we get the error again.

I'm not sure what i can try next to stop this from happening, any suggestions are welcome, thank you.


texts:
for example, we have:

element | group_value | value
a       | 1           | 2000
a       | 2           | 1500
a       | 2           | 2500
b       | 1           | 1000

I'd like to return only last 3 records as those are the records with max group value for each element.

I know for a solution with subquery but is there an efficient one?

To clarify: for element 'a':

2 is highest group_value so it returns rows 2 and 3 > (and not the first row as it's group value is not highest),

for element 'b':

1 is > highest group_value so it returns row(s) 4

My (performance-vice not good) solution to the problem is:

select *
from   x x1
where (element, group_value) in (select   element, max(group_value)
                                 from     x x2
                                 where    x1.element = x2.element
                                 group by x2.element)


texts: Odd behavior of between operator when updating

texts:
I have to maintain FRP (Financial Resource Planning) Software that was implemented by some shady consultants (they came up with the jobs), and it stores the data in a SQL Server database. The software loads some info in the morning from a text file (from an ERP) and then 3 hours after it runs 4 jobs. If I run all these processes by hand, I get the same result at the first job. This is why I have isolated this one job.

I have checked for triggers and there are none. I have checked for procedures and all belong to stuff users can do in the software. Even with every user logged out (I took down the service, so no users would log in) it still happens.

Job number 1

UPDATE SOME_TABLE SET VALUE_DATE = DATEADD(dd, DATEDIFF(dd,0,GETDATE()),0)
WHERE VALUE_DATE < DATEADD(d,0,GETDATE()) AND FLAG BETWEEN '0' AND '1'

This baffles me because Flag can only take 3 values 0,1,2 and are numeric values.

DataSet prior to job

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-05-22  |    0    |
                |  AAA  |   19999  |   2018-05-21  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-05-15  |    0    |
                |  CCC  |   15000  |   2018-04-15  |    1    |
                +-------+----------+---------------+---------+

DataSet after the is job run (today's date is 2018-06-14)

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    1    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    1    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

Second job executed

UPDATE SOME_TABLE SET FLAG='0'
WHERE OTHER_FLAG IN ('COND1', 'COND2'...) AND FLAG='1'

Result of dataSet after second job - expected

                +-------+----------+---------------+---------+
                |  ENT  |  AMOUNT  |   VALUE_DATE  |   FLAG  |
                +-------+----------+---------------+---------+
                |  AAA  |   10000  |   2018-06-14  |    0    |
                |  AAA  |   19999  |   2018-06-14  |    1    |
                |  BBB  |    1000  |   2017-12-21  |    2    |
                |  BBB  |    2000  |   2018-02-21  |    2    |
                |  BBB  |   10000  |   2018-06-14  |    0    |
                |  CCC  |   15000  |   2018-06-14  |    1    |
                +-------+----------+---------------+---------+

This query doesn't only update the VALUE_DATE it also sets FLAG to 1 on every entry. Then the second job is in queue to set the FLAG value back to 0 where it updated. I find it convoluted and inefficient. I want to update it because it takes a great deal of time and people once a month to curate the information in the db.

Why is the FLAG column getting updated? Will changing FLAG BETWEEN '0' and '1' to FLAG in ('0','1') have any corrective effect? Or any adding "context" delimiter will help ("",'',(),[],{},etc)?

How can I make the first job only update the Value_date when flag is 0 or 1 and ditch the second job completely? This way I can get the expected table results

I'm sure it's not a software issue, because the date integrity holds just fine when doing stuff in the software. It only happens when this job is executed.


texts: How to find out what caused the database(DB2) in a locking state

texts:
I know how to check the DB is in a lock state or not, but my question is how to find out which query caused the lock on table /db.

IS there any log file getting created if something unexpected happens to the database?


texts: Scope of memory optimized table variable

texts:
I have a stored procedure Schm.tmp_Sproc where I'm using memory optimized table variable @memTableVariable
CREATE PROCEDURE Schm.tmp_Sproc
    @user INT,
    @data VARCHAR(100)
AS
BEGIN
    DECLARE @memTableVariable Mem.typ_MemVar

    INSERT @memTableVariable
    FROM dbo.tbl_UserData ud
    WHERE ud.User = @user

    DELETE t
    FROM @ImpactedRev AS r
    INNER LOOP JOIN dbo.tbl_Repo AS t ON r.repoID = t.repoId
END

My question is: I have multiple processes executing this stored procedure. I mean at the given time all of the following can be triggered
EXEC Schm.tmp_Sproc @user = 1, @data = 'data1'
EXEC Schm.tmp_Sproc @user = 2, @data = 'data2'
EXEC Schm.tmp_Sproc @user = 3, @data = 'data3'
EXEC Schm.tmp_Sproc @user = 4, @data = 'data4'

I assume this is not an issue with normal table variable but can this be an issue with memory optimized table variable? I mean can the the memory optimized table variable @memTableVariable get messed up if the second or third data comes in if the first is already executing? Or does SQL Server have locks at the object level?


texts: Is there a tool like Microsoft's "SQL Server Profiler" for MySQL?

texts:
While developing on MySQL I really miss being able to fire up a profiler. I find SQLyog is a good enough replacement for Query Analyzer but have not found a tool that works like SQL profiler.

For the MySQL folk who have not seen Microsoft's SQL Profiler, here is a screenshot

At my previous job we had a tool that trumped SQL Profiler and even gave us stack traces

Does anyone know of any tools like the ones I mentioned that works with MySQL.

(FYI, I can get the Altiris Profiler to work with MySQL but it will involve running Windows furthermore its not really a Symantec sku so licensing is really tricky)


texts:
I got the follwing error while running a query from Java:

The database returned the following error:
[Oracle] #11 ORA-00449: background process 'MMON' unexpectedly terminated with error 448
[Oracle] #11
Database Error State: S1000
Database Error Code: 449

The following are the exception generated by compiler:

Report Error [615] The database returned the following error: [Oracle] #11 ORA-00449: background process 'MMON' unexpectedly terminated with error 448
[Oracle] #11
Database Error State: S1000
Database Error Code: 449
    at com.inet.report.ReportExceptionFactory.createReportExceptionWithCause(SourceFile:165)
    at com.inet.report.ReportExceptionFactory.createReportExceptionWithCause(SourceFile:230)
    at com.inet.report.cr.oE(SourceFile:1422)
    at com.inet.report.cr.run(SourceFile:1357)
    at com.inet.report.renderer.thread.b.a(SourceFile:104)
    at com.inet.report.renderer.thread.b$b.run(SourceFile:227)
Caused by: Report Error [615] The database returned the following error: [Oracle] #11 ORA-00449: background process 'MMON' unexpectedly terminated with error 448
[Oracle] #11
Database Error State: S1000
Database Error Code: 449
    at com.inet.report.ReportExceptionFactory.createReportException(SourceFile:95)
    at com.inet.report.ReportExceptionFactory.createReportExceptionWithCause(SourceFile:197)
    at com.inet.report.ReportExceptionFactory.createReportExceptionWithCause(SourceFile:230)
    at com.inet.report.Database.getReportData(SourceFile:2580)
    at com.inet.report.cf.b(SourceFile:219)
    at com.inet.report.dl.b(SourceFile:1628)
    at com.inet.report.dl.a(SourceFile:1610)
    at com.inet.report.cr.oC(SourceFile:1083)
    at com.inet.report.cr.run(SourceFile:1209)
    ... 2 more
Caused by: com.inet.ora.Ora4SQLException: [Oracle] #11 ORA-00449: background process 'MMON' unexpectedly terminated with error 448
[Oracle] #11
    at com.inet.ora.aj.a(Unknown Source)
    at com.inet.ora.ax.a(Unknown Source)
    at com.inet.ora.ax.a(Unknown Source)
    at com.inet.ora.ax.a(Unknown Source)
    at com.inet.ora.bj.next(Unknown Source)
    at com.inet.report.rowsource.q.a(SourceFile:161)
    at com.inet.report.rowsource.q.<init>(SourceFile:42)
    at com.inet.report.dv.b(SourceFile:169)
    at com.inet.report.dv.a(SourceFile:155)
    at com.inet.report.Database.a(SourceFile:3270)
    at com.inet.report.Database.getReportData(SourceFile:2575)
    ... 7 more

Please tell me what is the exact cause of this and also tell me the solution for it.


texts: Maria/MySQL Possible to allow user to GRANT drop though not use DROP?

texts:
I'm setting up an SQL user (setup process for new websites) which will need to GRANT the DROP command to another user (admin user (for authenticated admins), not the public-facing user) though is there a way to prevent the user from using the DROP command itself (through SQL)?

No, I can not use an existing administrator-permission SQL user as the setup process is public facing.

I've read that an SQL user can not GRANT permissions it does not have though this was listed on a third-party website and not Maria's documentation.


texts:
I have a table with the following structure:

Id (int identity PK)
FooId (int FK)
BarId (int FK)
QuxId (int FK)
Other fields

The following indexes have been defined:

PK (Id), clustered
Index1 (FooId)
Index2 (FooId, BarId)
Index3 (FooId, BarId, Id, QuxId)

Is it redundant to keep both Index1 and Index2?

Considering Id is the PK, does it make any sense to have it as the third column in Index3, before QuxId?

Would it make more sense to have just one index, (FooId, BarId, QuxId)? Would there be any benefit in including Id?

(as a sidenote, I'm a developer, not a DBA)


texts: Apache Phoenix Upsert not working while trying to update null/empty columns

texts:
Step 1: Update a column with null value.

Upsert into abc.tablename (Rowkeycombo1, Rowkeycombo2, columnname) values (123,to_date('1988-08-06'),null);

This works fine and the column then appears to have no data while doing a select.

Step 2:Update the same column with a valid value.

Upsert into abc.tablename (Rowkeycombo1, Rowkeycombo2, columnname) values (123,to_date('1988-08-06'),'TRYVAL');

Shows 1 row affected but the column still appears null/empty.

However there is no issue while updating a column which has a valid value to any other value.

I'm using a Phoenix server to query an Hbase DB and I'm new to both of this.


texts:
I use Amazon RDS as mysql db backend for highload project and try to optimize insert performance for innodb tables.
According to information from different sources - good candidate to speed up inserts is innodb_flush_log_at_trx_commit parameter.
My local tests show significant boost when I set it to 2 (or 0), but changing this param on RDS has no effect.
What may be a problem?
I tried all available values and results are the same (30-40 inserts per second into empty table) while local tests show ~9000 inserts per second.
I also tried M3.large General Purpose SSD RDS test instance (0 load) with same result.


texts: MySQL can't import a custom library for udf

texts:
I am working on Linux Mint 17 Qiana with MySQL Ver 14.14 Distrib 5.5.53, for debian-linux-gnu (x86_64).

Following the instructions found here:

http://dev.mysql.com/doc/refman/5.7/en/adding-udf.html

and subsequent pages, I have created a library file for my own user-defined function. But MySQL gives an error when I create the MySQL function from the library file.

Here are the steps I made.

I have created the following C file:

#include <math.h>
#include <stdint.h>
#include <stdlib.h>
#include <string.h>

#include <mysql/mysql.h>

//*** Functions for compressing a protein sequence into a binary
my_bool compress_protein_sequence_init(UDF_INIT* initid, UDF_ARGS* args, char* message) {
    if(args->arg_count != 1) {
        strcpy(message, "Error: compress_protein_sequence needs a string");
        return 1;
    }

    if( args->arg_type[0] != STRING_RESULT ) {
        args->arg_type[0] = STRING_RESULT;
    }

    return 0;
}

char* compress_protein_sequence(UDF_INIT* initid, UDF_ARGS* args, char* result, unsigned long* ret_length, char* is_null, char* error) {
    unsigned long length = args->lengths[0];

//*** Checking on the arguments

    *ret_length = (unsigned long) length;

    if(!(result = malloc(sizeof(char) * ((*ret_length) + 1)))) {
        *is_null = 1;
        *error=1;
        *ret_length = 0;
        return 0;
    }
    if(initid->ptr) {
        free(initid->ptr);
    }
    initid->ptr = result;

    memset( result, '\0', *ret_length + 1 );

//*** Some logic

    result[*ret_length] = '\0';
    return result;
}

void compress_protein_sequence_deinit(UDF_INIT* initid) {
    if( initid->ptr )
        free( initid->ptr );
}

I compile the C code:

gcc -Wall -I/usr/local/include -shared -o libcompress_sequence.so -c compress_sequence.c

MySQL plugin directory is given by:

SHOW VARIABLES LIKE '%plugin%';

the answer is:

+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| plugin_dir    | /usr/lib/mysql/plugin/ |
+---------------+------------------------+

So I put the libcompress_sequence.so in the plugin folder of MySQL:

sudo cp libcompress_sequence.so /usr/lib/mysql/plugin/

I start MySQL in command line as root:

mysql -u root -p

I chose a database then I execute this MySQL command:

CREATE FUNCTION COMPRESS_PROTEIN_SEQUENCE RETURNS STRING SONAME 'libcompress_sequence.so';

and MySQL shows this error:

ERROR 1126 (HY000): Can't open shared library 'libcompress_sequence.so' (errno: 0 /usr/lib/mysql/plugin/libcompress_sequence.so: only ET_DYN and ET_EXEC can be loaded)

What is ET_DYN? What is ET_EXEC?
What is happening?
How to solve the problem?


texts: Query hanging in ClientRead and blocking all others

texts:
This is related to Debugging a hanging session/lock

Every so often (~1-2 times a month) we have our database lock up because there is one query that doesnt finish, and that has requested locks that block all others. This is on a table that is core to our business, so it results in downtime.

The query in question is one of a couple of candidates, all of them are just simple UPDATEs running on just 1 row. The best lead I have so far (I am open to other avenues) is from RDS performance insights I see that while all other queries are locked on "tuple", there is always 1 query that is locked on "ClientRead". From my research, this appears to be Postgres waiting on the client lib to send it the bound parameters or similar. So my theory is that there is one query that randomly sometimes ends up in this state, and this blocks all others after it. Is this a reasonable evaluation?

If this is the case, how can I go into investigating and resolving it? We use sequelize 5.9.4 (node-js). We did upgrade it recently, ~1-2 months back, so I am wondering if there might be a bug in this version which might cause a error on the sequelize side when processing a query, is that possible? It could be a bug in node-postgres as well, this came up in my searching https://github.com/brianc/node-postgres/issues/1952.

I will try to update with a screenshot of RDS performance insights next time it happens, its retention was shorter than anticipated.


texts:
I have a MySQL database on a Linux server that I am able to access through putty logging into servers terminal. When I try to connect to the MySQL database remotely, I get the error:

  Lost connection to MySQL server at 'reading initial communication packet, system error: 0

I have added bind-address:127.0.0.1 in my.cnf file and port 3306 is already opened. What other configurations do I need to change to be able to connect to MySQL remotely?

I am using Navicat / HeidiSQL + SSH to connect remotely.


texts: What happens to a transaction if the network connection fails?

texts:
What happens to a transaction when the client connection is lost.
Example the client is a web application code that starts a transaction to a remote server instance. The client sends the sql code to the server and waits for the transaction to complete.
What happens if the network connection fails? Does it depend on the point we are in a transaction? Is it irrelevant? Is it aborted?


texts: PostgreSQL JSONB update array elements query

texts:
Given a table in PostgreSQL 10:

CREATE TABLE forms (id serial, form jsonb);
INSERT INTO forms (id, form) VALUES (
  1, '"id":"a uuid_v1 here",
      "widgets": [ {"id":"1", "permissions": ["user1", "user2"]},
                   {"id":"2", "permissions": ["user1", "user3"]}]');
INSERT INTO forms (id, form) VALUES (
  2, '"id":"a uuid_v1 here",
      "widgets": [ {"id":"1", "permissions": ["user1", "user2"] },
                   {"id":"2", "permissions": ["user3", "user4"]}]');

I need to remove a user, say "user1" from all assigned permissions in any widget. I also need to add a user, say "user5" to all assigned permissions in any widget.


texts: Copy SSRS Subscriptions from one SSRS server to another

texts:
I am having to migrate reports and subscriptions from one SSRS server to another. I have already migrated the RDL and Datasources. I only have the subscription information to move. I am trying to use the ReportingServicesTools (https://github.com/microsoft/ReportingServicesTools) and an example from here http://wragg.io/managing-sql-server-reporting-services-subscriptions-with-powershell/ to accomplish this.  I exported the subscription information using Get-RsSubsctiption | Export-RsSubscriptionXml to a file.
I am trying to import it using Import-RsSubscriptionXml | Set-RsSubscription. When this executes, I get no messages, error or informational. When I look in the SSRS database, I don't see the subscription.
Here are the commands:
Get-RsSubscription -ReportServerUri 'http://sql2017/reportserver' -RsItem '/Cooperative Technologies/1035/1035YellowPages/Billing Reports/LookupActivityReport' | Export-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"

Import-RsSubscriptionXml "c:\users\logsdone\desktop\SSRS Subscriptions\1.xml"  -ReportServerUri 'http://sql2017/reportserver'  | Set-RsSubscription -ReportServerUri 'http://sql2017/reportserver'

Thanks for any suggestions or additional documentation or examples.
Eric


texts: Cause of High Disk Queue Length in SQL Server

texts:
I have a fairly high-throughput application that occasionally decides to collapse on me. It's not very often - about once every ~3 weeks or so. When it does, if I check out perfmon, I see 100% "Avg. Disk Queue Length" pegging the server.

During these times, I also see lots of nice connection failed messages from SQL Server.

I'm no SQL Server expert, I can do the basics for indexing, taking backups, etc., but that's it.

What would cause something like this? I was thinking perhaps it was a resize of the database (it was down to ~300MB available [and it's a 30 gig database]), or maybe some reindexing gone nuts?

I do have one table in particular that has tons of inserts. Very few reads, but many inserts per second isn't unusual at all.

The server has only ~4 gig of RAM as well, but we do have a dedicated warehouse box that rolls up data every night where most of the heavy querying is directed.

Anyone got any thoughts on what might cause that huge queue length?


texts:
I create an monitoring template for my database on OEM GC, apply it on somes database and now i want to know what database is using this template.
Can someone help me please?


texts: Unpredicatable single insert performance on SQL Server table

texts:
I have inherited a system that sometimes exhibits extremely poor single record insert performance.

The table is something like 8 million rows, so is relatively large but not exceptionally so. The following simple insert took over 7 minutes and blocked the entire system up in the process:

   INSERT INTO dbo.Communication(comm_action,comm_channelid,...)
   VALUES (N'Deskwork',11,...)

The overall database design is fairly poor. For example, although this particular table does have a Clustered Index, it has 34 other indexes as well despite being an OLTP system with heavy insert/update activity.

There are no triggers that I can see and the performance of inserts for the most part is reasonably good. Good being a relative term.

Could the number of indexes really account for such poor performance and is there any approach I could take to prove this before undertaking work.

Are there any other areas I could look into if the consensus is not the indexes being a problem?

The customer is very sensitive to changes, so I will need evidence before being able to make any changes.

I'm using SQL Sentry to highlight the blocking and the insert is the first block that shows up, followed by many others.

It's actually weirder than just there being another process blocking the inserter. I have a process (51) blocked by process 105. However, when I check what process 105 is about, there is no process 105.

I've repeated this several times, each time with a different process saying it's being blocked for a prolonged period of time, but the "blocker" not really existing. Does anyone know why sys.dm_exec_requests can say something is being blocked, but the same DMV say the blocker isn't really there?

What happens is, we get rows in dm_exec_requests with a blocking session id of, say 127. However, although there is a session in dm_exec_sessions, it says it's sleeping and there are no equivalent rows in the requests DMV.

I'm not clear on how a session can block something for 30 minutes despite seemingly not doing any requests?

For the blocked requests, in the case I had this morning it was waiting for a LCK_M_S. Any ideas?


texts: Need help about giving relationships in database design

texts:
I am developing a simple blog application in which users can write posts and others can like, comment, share etc. i am facing difficulty in designing database.
What I have tried is:

user (user_id(pk), user_name)

post (post_id(pk), post_content, likes, user_id(fk))

comment(comment_id(pk), comment, post_id(fk))

PROBLEM: One user can have many posts and many users can like and comment on many posts. Whether I have to separate two tables like: user, post and userpost?

Can anyone tell me if anything wrong in this design?


texts: Should binary files be stored in the database?

texts:
What is the best place for storing binary files that are related to data in your database? Should you:

Store in the database with a blob
Store on the filesystem with a link in the database
Store in the filesystem but rename to a hash of the contents and store the hash on the database
Something I've not thought of

The advantages of (1) are (among others) that atomicity of transactions is preserved. The cost is that you might dramatically increase storage (and associated streaming/backup) requirements

The goal of (3) is to preserve atomicity to some degree - if you can enforce that the filesystem you are writing to does not allow files to be changed or deleted, and always has the correct hash as filename. The idea would be to write the file to the filesystem before permitting the insert/update referencing the hash - if this transaction fails after the filesystem write but before the database DML, that is fine because the filesystem is 'faking' being the repository of all possible files and hashes - it doesn't matter if there are some files in there that are not being pointed to (and you could clean them up periodically if you are careful)

EDIT:

It looks like some RDBMSs have this covered in their individual ways - I'd be interested to know how others do it - and particularly in a solution for postgres


texts: MySQL: migration huge tables between two databases

texts:
Hello guys (and sorry for my English).

I have 2 DB's - DB1 (staging) and DB2 (production), each day some contend added/changed in some tables in DB1 and should be moved to DB2.
Right now it works like (e.g. we move tableA):

Drop DB2.tableA
Copy DB1.tableA to DB2

it's bad way for many reasons, i have proposal do next:

Copy DB1.tableA to DB2.tableAtemp (change name)
Drop DB2.tableA
Rename DB2.tableAtemp to DB2.tableA

and repeat this operation for each table.

Question:

The most important thing why now it's done with "dropping tables" - performance.
Perhaps there is a better way to insert/update many data (~1M lines) between databases?


texts: Is Service Broker the best choice for auditing data changes on SQL Server Express?

texts:
My project is to audit 5 to 10 existing tables in our system without prolonging transactions. Whatever method is used, it has to work on SQL Server Express 2005 to (eventually) 2016.

I've done research on Change Data Capture (CDC) and Change Tracking. Change Tracking doesn't capture the specific changes, and CDC is only available in Enterprise Edition.

Then I stumbled upon Service Broker. I was intrigued by Service Broker, so I started creating a prototype. Service Broker is working fine, but answers received in two of my other posts lead me to believe that this might not be the right way to go. Too complicated for nothing. I'm still in the analysis stage and trying different things as part of my analysis.

Right now the results of the service broker are not convincing... a bulk update of 105000 items to a price table, takes 38 seconds while the processing of the Queue (the auditing part) takes 17 seconds...  but the 38 seconds includes the double processing of inserting into 2 #temp tables that are then used to insert into the TMPins and TMPdel.  So I figure that I can cut that in half... I'm now questioning the use of the service broker... logically the trigger would probably take the same amount of time just by inserting the info straight into the audit table...

To clarify, when I say Bulk insert, It Isn't the "Bulk Insert" Function.  I'm talking about a big chunk of data that is inserted or updated at once.  when updating 105000 items in the price table, I want to audit the changes that happened. When I say changes that happen, I decided to insert the new values in the audit table (if it's an insert or update) or insert the primary key with all other fields null  (for records that have been deleted)... So yes! it can be after the data is loaded in but I don't want to lose any audit (I don't want a transactions to pass out of order)

The two other posts will help get the context of what I'm trying to do and what I've tried:

Trigger to create a variable Table to be sent to service broker
triggers - using the inserted / deleted tables in Dynamic SQL

I value every idea.


texts: Why are there execution plan differences between OFFSET ... FETCH and the old-style ROW_NUMBER scheme?

texts:
The new OFFSET ... FETCH model introduces with SQL Server 2012 offers simple and faster paging. Why are there any differences at all considering that the two forms are semantically identical and very common?

One would assume that the optimizer recognizes both and optimizes them (trivially) to the fullest.

Here is a very simple case where OFFSET ... FETCH is ~2x faster according to the cost estimate.

SELECT * INTO #objects FROM sys.objects

SELECT *
FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #objects
) x
WHERE r >= 30 AND r < (30 + 10)
    ORDER BY object_id

SELECT *
FROM #objects
ORDER BY object_id
OFFSET 30 ROWS FETCH NEXT 10 ROWS ONLY

One can vary this test case by creating a CI on object_id or adding filters but it is impossible to remove all plan differences. OFFSET ... FETCH is always faster because it does less work at execution time.


texts: can I specify each receiver can receive its own data in ssrs subscription data driven

texts:
I need to send only the receiver content report for in the To based on its content and filtered by its email using data-driven SSRS subscription
Ex: I have an attendance report and I would like to send this report to all employees but each employee should receive  it's copy report with his data Can I do this with SSRS data-driven


texts: Is it OK to have an entity in an ER diagram without a relationship?

texts:
I'm creating an ER diagram for a class assignment and I was wondering if it would be possible to create an entity that had no direct relationship to any other entity, and simply use foreign keys to connect them. The diagram below illustrates what I'd like to do

Basically, my diagram is starting to get really complex and it would be great if I didn't have to maintain a connection between every single entity.

FWIW, I'm using Chen and Crow's Foot notation, if that's any help.


texts: How to ignore columns in parquet/polybase import?

texts:
I'm using polybase to import a parquet file.

Over time, it is likely we may add or remove named columns in the file.

When I add an additional column, I get the below error:

  External file access failed due to internal error: 'File test.parquet: HdfsBridge::CreateRecordReader - Unexpected error encountered creating the record reader: HadoopExecutionException: Column count mismatch. Source file has 16 columns, external table definition has 15 columns.'

This is because I added an additional column that wasn't in the external table definition.

As parquet contains a file schema and the external table knows the name of each column, is there a way it can be set to ignore the extra unused column?


texts:
I'm having a problem with my SQL command running on MariaDB.
I have the following tables:
reservations:

id
child_id
time_from
time_to
user_id

1
1
2021-05-17 13:20:00
2021-05-17 14:20:00
NULL

2
2
2021-05-19 08:00:00
2021-05-19 12:30:00
2

3
2
2021-05-14 10:00:00
2021-05-14 13:00:00
NULL

users:

id
email
role
name
surname
contact

2
one@email.com
admin
peter
peterson
123456789

3
two@email.com
assistant
john
johnson
NULL

4
three@email.com
admin
mike
mikeson
987654321

5
four@email.com
admin
leo
leoson
NULL

6
five@email.com
admin
jane
janeson
NULL

I'm trying to join those tables by the reservations.user_id and users.id using LEFT JOIN, however unsuccessfully.
This is the command I made and edited many times without success:
SELECT reservations.*, IF(DATE_FORMAT(reservations.time_from, '%Y-%m-%d') = CURDATE(), IF(reservations.user_id IS NULL, TRUE, FALSE), TRUE) AS removable FROM reservations
LEFT JOIN (
    SELECT users.id AS userID, users.name AS name, users.surname AS surname FROM users
) AS users ON reservations.user_id = users.userID
WHERE reservations.time_from >= CURDATE()
ORDER BY reservations.time_from;

However this is all I'm getting:

id
child_id
time_from
time_to
user_id
removable

3
2
2021-05-14 10:00:00
2021-05-14 13:00:00
NULL
1

1
1
2021-05-17 13:20:00
2021-05-17 14:20:00
NULL
1

2
2
2021-05-19 08:00:00
2021-05-19 12:30:00
2
1

Basically I'm getting the first table without the data of the second table.
I do need to include rows with user_id NULL as it is used for confirmation reasons. Am I missing something?
Here is the requested dbfiddle: dbfiddle
The column is users.id but in the LEFT JOIN SELECT query I rename the column to userID. user_id of the reservation is a column allowing NULL, as it is a user assigned for the reservation. NULL => no one has been assigned yet.
What I'm trying to do is to get all the reservation rows and users assigned by staff to the reservation. Imagine it as assigned guard for the child. That's why I need to get all upcoming reservations with information who the guard is going to be.
If the user_id is not NULL, the table should contain even the columns users.name and users.surname since I'm selecting them in the join. That's what I'm looking for.


texts: Row numbering difference between mariadb and mysql

texts:
I've recently updated my server into a docker setup and switched from mysql to mariadb. No I've run into an issue with different behavior between Mysql 5.7 on my local machine and MariaDB 10.5.6 on the server.
The problem is a different result for a query which should return row numbers.
I'll explain using a minimal working example
Consider the following two tables:
>SHOW CREATE TABLE cars\G
*************************** 1. row ***************************
       Table: cars
Create Table: CREATE TABLE `cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `value` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

>SHOW CREATE TABLE used_cars\G
*************************** 1. row ***************************
       Table: used_cars
Create Table: CREATE TABLE `used_cars` (
  `brand` varchar(128) DEFAULT NULL,
  `model` varchar(128) DEFAULT NULL,
  `price` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

And some example data:
INSERT INTO `cars` (`brand`, `model`, `value`) VALUES
('Volvo', 'S40', 1),
('Porsche', 'Carrera', 4),
('Opel', 'Corsa', 3),
('Renault', 'Clio', 1),
('Renault', 'Megane', 3),
('Volvo', 'V40', 2);
INSERT INTO `used_cars` (`brand`, `model`, `price`) VALUES
('Volvo', 'S40', 2500),
('Porsche', 'Carrera', 7500);

Let's say I want to run a query which lists all cars and returns whether there is a used car in stock. And I want these to be ordered in a certain way:
SELECT (@row := @row + 1) AS score,
   qry.brand,
   qry.model,
   qry.used_present
FROM
  (SELECT cars.brand,
          cars.model,
          (used_cars.price IS NOT NULL) AS used_present
   FROM cars
   LEFT JOIN used_cars ON used_cars.model = cars.model
                          AND used_cars.brand = cars.brand) AS qry
CROSS JOIN
  (SELECT @row:=0) AS r
ORDER BY qry.used_present DESC,
         qry.brand ASC

this returns the following in MySQL
+-------+---------+---------+--------------+
| score | brand   | model   | used_present |
+-------+---------+---------+--------------+
|     1 | Porsche | Carrera |            1 |
|     2 | Volvo   | S40     |            1 |
|     3 | Opel    | Corsa   |            0 |
|     4 | Renault | Clio    |            0 |
|     5 | Renault | Megane  |            0 |
|     6 | Volvo   | V40     |            0 |
+-------+---------+---------+--------------+

While I get the following in MariaDB
+-------+---------+---------+--------------+
| score | brand   | model   | used_present |
+-------+---------+---------+--------------+
|     2 | Porsche | Carrera |            1 |
|     1 | Volvo   | S40     |            1 |
|     3 | Opel    | Corsa   |            0 |
|     4 | Renault | Clio    |            0 |
|     5 | Renault | Megane  |            0 |
|     6 | Volvo   | V40     |            0 |
+-------+---------+---------+--------------+

Can anyone help me how to fix this? In reality the innerquery qry is much more elaborate, but the results are similar as this MWE. Adding the ORDER BY to the innerquery doesn't change the results.
db<>fiddle


texts:
I've search on google and stackoverflow and dba.stackoverflow and couldn't find any content related to this question. The basic question is Mysql.pdb useful somehow?

Reason to remove: The file is big[160mb]. I'm about to deploy a desktop application that download and installs mysql on the local machine. I store the zip in my web filestorage[traditional host and amazon s3] so I can rely on myself and prevent from Oracle removing it, etc.

The mysql version is: https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.17-win32.zip

The installer basically:

Download and Unzips the file
Execute the command "mysqld --initialize --init-file={app}\create_db.sql"
Configure the mysql server aas a Window Service
{Continue installation process}

Can I remove it without having unwanted behavior? Is there any other "useless" file on mysql?


texts: SQL Server spatial geometry properties on index

texts:
Is there a way to add geometry.STSrid(object property) to a spatial index or is it already added when I create a spatial index in SQL Server 2012.

I have mixed SRID table and I was wondering if it could help?

We collect spatial data from several source to one database structure. I don't have a simple solution to transform spatial data on our stack (SQL Server) and I need to keep original data. One solution that I have considered is to keep original geometry and add another transformed geometry on side.


texts:
I have problems when I want to generate a query in posgretsql, I was trying and I do not know how to calculate the following

My table has the following data:

CREATE TABLE "abc-ventas"
AS
  SELECT *
  FROM ( VALUES
    ( '0001', 'sandía' ,  2.000, 3.000 ),
    ( '0002', 'lápiz'  ,  3.000, 5.000 ),
    ( '0003', 'manzana',  1.000, 2.000 ),
    ( '0004', 'naranja',  2.000, 3.000 ),
    ( '0005', 'arroz'  ,  1.000, 5.000 )
  ) AS t(CODIGO, DESCRIPCION, COSTO, NETO);

I need to calculate the following information

PARTIC= NETO / SUM(TOTAL NETO)

CODIGO   DESCRIPCION    COSTO     NETO   PARTIC PARTIC-ACUM
0001     sandía        2.000      3.000   17%
0002     lápiz         3.000      5.000   28%
0003     manzana       1.000      2.000   11%
0004     naranja       2.000      3.000   17%
0005     arroz         1.000      5.000   28%
                                 -------------
                                 18.000   100%

Now to calculate the "partic-acum" order DESC "partic"

PARTIC-ACUM= PARTIC + PREVIOUS PARTIC

CODIGO   DESCRIPCION    COSTO     NETO   PARTIC PARTIC-ACUM CLASIF
0002     lápiz         3.000      5.000   28%          28%
0005     arroz         1.000      5.000   28%          56%
0001     sandía        2.000      3.000   17%          72%
0004     naranja       2.000      3.000   17%          89%
0003     manzana       1.000      2.000   11%          100%

Once generated the result I want to classify it in A B C

If the value is 0 to 80:   A
If the value is 81 to 95:  B
If the value is 95 to 100: C

This is my desired output

CODIGO   DESCRIPCION   COSTO      NETO    PARTIC PARTIC-ACUM CLASIF
0002     lápiz         3.000      5.000   28%          28%    A
0005     arroz         1.000      5.000   28%          56%    A
0001     sandía        2.000      3.000   17%          72%    A
0004     naranja       2.000      3.000   17%          89%    B
0003     manzana       1.000      2.000   11%          100%   C

The query I have is the following

SELECT
  codigo,
  descripcion,
  cantidad,
  costo,
  neto,
  (SELECT sum(neto) FROM "abc-ventas") as total,
  (round(neto /(SELECT sum(neto) FROM "abc-ventas"),4)*100) AS participacion
FROM "abc-ventas"
ORDER BY participacion DESC;


texts:
I have a piece of SQL that creates a TABLE form a SELECT statement in PostgreSQL ; I want to add constraints to the table so that for example a column cannot be null.

I cannot find valid SQL for this in the Postgres Documentation, so it seems it is not possible.

What would be the best approach to achieve this kind of functionality? I would like creation of the table to fail if the constraint is violated.

I cannot create the table with the constraint beforehand because there might be some variation in some of the columns, as they are the result of a dynamic process.


texts: Is there a better way to create dynamic time averaged series over a dynamic frequency?

texts:
I've got a series of tables with lots of high precision data collected from various devices.  The intervals that they were collected on varies and even wanders over the time series.  My users want the ability to pick a date range and get an average / min / max over these variables at a specific frequency.  This is the second stab I've taken at this, and it works, but I wonder if there is a better/faster way to accomplish this?

declare @start datetime
declare @end datetime
set @start = '3/1/2012'
set @end = '3/3/2012'
declare @interval int
set @interval = 300

declare @tpart table(
dt datetime
);

with CTE_TimeTable
as
(
select @start as [date]
union all
select dateadd(ss,@interval, [date])
from CTE_TimeTable
where DateAdd(ss,@interval, [date]) <= @end
)
insert into @tpart
select [date] from CTE_TimeTable
OPTION (MAXRECURSION 0);

select t.dt, avg(c.x1), min(c.x1), max(c.x2), avg(c.x2), min(c.x2), max(c.x2)  from clean.data c ,
@tpart t
where
ABS(DateDIFF(ss, t.dt , c.Date) ) <= @interval /2
and
Date >= @start
and
Date <= @end
group by t.dt

Right now over 32721 rows for this 3 day period this query takes about 43 seconds to run and gives me the 577 rows I expect but I'd like to get this faster.  The big hit comes from the nested loop to do the inner join.


texts:
We are running Oracle 11.2.0.4.0.  It is a transactional database that has been online since 2011.  We have been tasked to clean up old records.

The table structure is (parent->child):

A->B-C->D->E

E has child tables F, G->H

All tables have dates on them but H.  There are PK-FK relations for the table structure that is described.

Right now all tables except for G have around 70 million records in them.  G has around 500 million records.

If I try I delete by joining the tables the query alone on a production backup takes several minutes to run just for the query.  Also, the requirements for delete will remove several million rows at once.  we have a limited time window to run deletes.

I've run some data analysis and currently the delete will remove about 40% of the data.  Also, the system cannot be taken down for any extended period of time.

What's the best strategy for removing the old records?

Possible Strategy/Approaches:

Seven deletes using sub queries/joins to get the ids to remove.  We would be joining up to 7 tables (lowest child).
Query each table for IDs to delete, store them in a temporary table.  The use that temporary table get the records for the next child.  Then once have all ids, to delete the records with something like this:

delete from H where PKID IN (Select PKID from tempH)
delete from G where PKID IN (Select PKID from tempG)

Would there be any other possible alternatives?  Most likely I would run this process several times deleting X amount of data.  (Week's worth/Months worth, etc.)  I don't think I can catch up with one massive delete.


texts: Selecting rows with a foreign key OR a join table relationship

texts:
I have tables like this

# Things
id
user_id

# ThingUser
id
thing_id
user_id

I want to select all Things where user_id=123 OR it is related to user 123 through ThingUser.

I think maybe I'm close with something like this:

select * from things
  left outer join thing_users on things.id=thing_users.thing_id and thing_users.user_id = 123
  where
    things.user_id = 123 OR
    thing_users.id IS NOT NULL

But on my very large dataset this hangs for several seconds and then I quit it.


texts:
we have a Tuesday/Thursday Maint window for one application we support. On Tuesdays we modify one facility and thursday if there are no errors we roll all the changes out to the other facilities.  The application is 3ed party, and at times makes changes to the databases which render views invalid on our "reporting" server for the application.

Is there a way I can roll through all the views on my report server (There are about 70 of them) with TSQL to find the Binding Errors after Tuesdays Depolyment?

Errors I would like to find are:

Executed as user: <>. Statement(s) could not be prepared. [SQLSTATE 42000] (Error 8180)  Could not use view or function 'PAT.dbo.Reorders' because of binding errors. [SQLSTATE 42000] (Error 4413)  All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. [SQLSTATE 42000] (Error 205).  The step failed.

Is there a way to do this programatically, or do I just have to keep right clicking each view and trying to see if it comes back with data?

Thanks,


texts: What Happens to dirty pages if the system fails before the next checkpoint?

texts:
Assuming a database using full recovery model, when a record is written in SQL Server (by INSERT / UPDATE etc) write ahead logging will ensure the change is written to the log file before modifying the data page.

Both the log and data page entries are made in RAM and committed to disk later by a Checkpoint.

If there is a system crash (power loss for the sake of argument) what will happen to dirty pages (IE data that is changed in RAM but not committed to disk) as the contents of RAM do not survive system restarts, is this data lost?

EDIT

After some testing, I can see the dirty pages are not lost, but I am not sure why:

using this tutorial

create a test database

CREATE DATABASE DirtyPagesDB
GO
USE DirtyPagesDB
GO

turn off automatic checkpoints

DBCC TRACEON(3505, -1);
DBCC TRACESTATUS();

create a table, insert some data and issue a checkpoint:

CREATE TABLE t1 (Speaker_Bio CHAR(8000))
GO
INSERT INTO t1 VALUES ('SQL'),('Authority')
GO
CHECKPOINT

confirm no dirty pages

-- Get the rows of dirtied pages
SELECT
database_name = d.name,
OBJECT_NAME =
CASE au.TYPE
WHEN 1 THEN o1.name
WHEN 2 THEN o2.name
WHEN 3 THEN o1.name
END,
OBJECT_ID =
CASE au.TYPE
WHEN 1 THEN p1.OBJECT_ID
WHEN 2 THEN p2.OBJECT_ID
WHEN 3 THEN p1.OBJECT_ID
END,
index_id =
CASE au.TYPE
WHEN 1 THEN p1.index_id
WHEN 2 THEN p2.index_id
WHEN 3 THEN p1.index_id
END,
bd.FILE_ID,
bd.page_id,
bd.page_type,
bd.page_level
FROM sys.dm_os_buffer_descriptors bd
INNER JOIN sys.databases d
ON bd.database_id = d.database_id
INNER JOIN sys.allocation_units au
ON bd.allocation_unit_id = au.allocation_unit_id
LEFT JOIN sys.partitions p1
ON au.container_id = p1.hobt_id
LEFT JOIN sys.partitions p2
ON au.container_id = p2.partition_id
LEFT JOIN sys.objects o1
ON p1.OBJECT_ID = o1.OBJECT_ID
LEFT JOIN sys.objects o2
ON p2.OBJECT_ID = o2.OBJECT_ID
WHERE is_modified = 1
AND d.name = 'DirtyPagesDB'
AND
(
o1.name = 't1'
OR o2.name = 't1'
);
GO

confirm time of last checkpoint

SELECT  f1.[Checkpoint Begin], f2.[Checkpoint End]
FROM    fn_dblog(NULL, NULL) f1
        JOIN fn_dblog(NULL, NULL) f2
             On f1.[Current LSN] = f2.[Previous LSN]
WHERE   f2.Operation IN (N'LOP_BEGIN_CKPT', N'LOP_END_CKPT');

Add more rows

INSERT INTO t1 VALUES ('SQL'),('Authority')

Use query above to confirm that there were dirty pages

Kill the SQL Server task from task manager to simulate a power off.

Start the service

Re-run the command above to get the last checkpoint time, it was the same (IE no checkpoints have run other than the one we did manually)

SELECTed from the table t1 and all four records were there


texts: MySQL Add/Grant user if it does not exist

texts:
I am trying to create a update/install script that adds a user and database only if it's a fresh install. The script uses a randomly generated password for the user, so using the GRANT statement as suggested in a StackOverflow thread will not work.

Is there a way to create, set password, and grant privs a user only if it does not exist?
Am I going to have to run a check in the script and then run the statement or can I build it into a SQL file?


texts: Joining three tables with intermediate nullable option and filter condition

texts:
Sorry if this seems like a dumb question, but I've read through tens of answers and tried many different options, yet my query results were either overly inclusive or exclusive.

The basic scenario is, having tables A <- B <- C, and the foreign keys A.b_id (nullable) and B.c_id, how can I get A's where either:

b_id is null (B is absent)
b_id is not null (B is present), AND its associated C fits a particular criterion (C.active is true, or similar)

To restate, an A may be independent of a B, but every B belongs to a C, and I would like to get the A's that either have no B or have a B that belongs to a C with a specific condition.

Many answers seem to deal with an A that is joined to both B and C, but in this case it's the middle or intermediate table that is optional. (And I haven't been able to figure out how to assemble parts of different examples into a working solution.)

It may be possible through a UNION, but that also seems like a last resort. Is there a way to do this through joins?

I had initially tried two outer joins, but of course that's not the same thing.

I later tried inner joining B and C and then having A left join that as a derived (aliased) table, but this would still return all results, even where (using the example) active was false.

In that case it's like everything is still being outer-joined, but I thought the derived table strategy (seen in different posts) was supposed to solve that problem.

Thanks.


texts: How can I install a provider for linked server?

texts:
I have a provider for a linked server called PervasiveOLEDB.11.0 installed on a sql server 2005 machine. It always me to have a connection to a pervasive sql database.

I would like to use this same provider on a sql server 2012 machine.

Is there a way to transfer this provider from the 2005 to the 2012 machine?


texts:
I use Lubuntu 16.04 64 bit and I tried to install PostgreSQL using the Ubuntu software service.

I'm unable to run the server.

Many found the same problem and solved their issues.  I tried to follow their suggestions but nothing worked. Can you help me?

psql: could not connect to server: File o directory non esistente
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

when I run systemctl start postgresql-9.5.service I obtain

Failed to start postgresql-9.5.service: Unit postgresql-9.5.service not found.


texts: How to get query runtime in PL/pgSQL to be as fast as console query runtime?

texts:
Basically I want to assign the result of a query to a custom type attribute. However I noticed that querying directly from the PostgreSQL console was about 0.071 ms and inside the function was 0.400 ms and 0.170 ms after a few calls. explain analyze even shows the usage of indexes in the first case but not on the second.

Here's what I'm doing.

CREATE OR REPLACE FUNCTION fun_isliked(
    par_client client.id%type,
    par_feed feed.id%type
)
RETURNS boolean
AS
$$
BEGIN
    RETURN (
        EXISTS(
            SELECT
                1
            FROM
                feedlike fl
            WHERE
                fl.client = par_client
                AND fl.feed = par_feed
                AND fl.state = 1
        )
    );
END;
$$ LANGUAGE plpgsql STABLE;

Here are the outputs of explain analyze of the two cases described above:

postgres=# explain analyze select exists (select 1 from feedlike where client = 13 and feed = 68 and state = 1);
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=8.30..8.31 rows=1 width=0) (actual time=0.037..0.037 rows=1 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using feedlike_client_feed_unique on feedlike  (cost=0.28..8.30 rows=1 width=0) (actual time=0.034..0.034 rows=1 loops=1)
           Index Cond: ((client = 13) AND (feed = 68))
           Filter: (state = 1)
 Total runtime: 0.086 ms

postgres=# explain analyze select * from fun_isliked(13, 68);
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Function Scan on fun_isliked  (cost=0.25..0.26 rows=1 width=1) (actual time=0.398..0.398 rows=1 loops=1)
 Total runtime: 0.416 ms

What is a possible workaround in order to effectively get same runtime inside the function? Is it even possible? Also, I'm running PostgreSQL 9.3.

I found that this question in SO had what I needed but after I tried everything in the selected answer and had no success on reducing the runtime I decided to ask a new question.


texts: RETURN statement before DEALLOCATE CURSOR

texts:
Question about use of Cursors in combination with RETURN in a SQL Server 2008 Stored Procedure

Consider this example:

CREATE PROCEDURE [dbo].[test]
    @ReturnEarly BIT = 0
AS
BEGIN
    SET NOCOUNT ON

    SELECT 1 AS Result INTO #Test

    DECLARE @Result INT, @HasResult INT = 1

    DECLARE TestCursor CURSOR FOR
        SELECT Result
        FROM #Test
        WHERE Result = 0

    OPEN TestCursor
    FETCH NEXT FROM TestCursor INTO @Result

    IF (@@FETCH_STATUS <> 0)
    BEGIN
        IF (@ReturnEarly = 1)
            RETURN 0
        ELSE
            SET @HasResult = 0
    END

    CLOSE TestCursor
    DEALLOCATE TestCursor

    DROP TABLE #test --technically superflous

    IF (@HasResult = 0)
        RETURN 0
    ELSE
        RETURN 1 -- in reality many more checks on the actual results
END
GO

-- Allow DEALLOCATE CURSOR to be called
DECLARE   @RC int, @ReturnEarly bit
SET @ReturnEarly = 1
EXECUTE @RC = [test] @ReturnEarly
PRINT @RC
GO

-- Exit before DEALLOCATE CURSOR is called
DECLARE   @RC int, @ReturnEarly bit
SET @ReturnEarly = 0
EXECUTE @RC = [test] @ReturnEarly
PRINT @RC
GO

DROP PROCEDURE [dbo].[test]

Both EXEC calls of the Stored Procedure return the same result. My question is this: is there a potential leak associated with returning from inside the Cursor? There is not for the temporary table (#test), for example.

Bonus: Can you point me to docs suggesting either way? My google skills weren't up to it.

Extra Bonus: If there is a problem, is there any way I could find evidence of the leak? perhaps a dmv, event log, etc which would show the problem?

Thanks in advance!


texts: Query condtion for values not being the default value?

texts:
Can I reference a columns default value directly in a query?

Something like this: SELECT column FROM table WHERE column <> column.DEFAULT_VALUE

Background: I'm writing a application test, and I want to easily find out which columns have non-default values. I could parse out the default value from the schema definition which is stored on disc, or figure out what the default is in various more or less convoluted ways, but I'm hoping there's a syntax for asking the server directly.


texts: Role db_ddladmin not working properly on SQL Server 2008

texts:
I want to allow certain user to create/modify objects on a database(tables, views, sp's, functions, schemas etc) and don't want to give db_owner.

I thought I could do it with the db_ddladmin role, but somehow that doesn't wort as expected. The error messages i get is that are like this:

Your are not loggend on as the database owner or system administrator.
You might not beable to sache changes to tables that you do not own.

Table dbo.MyTable is set to read only,
user doesn't have enough right on this table.

etc

Any idea how i can achieve my goal?


texts:
So I've always in general been confused about what the difference between the security of a vew and of Row Level Security.
so I guess my general question is
Why is row-level access needed when views can limit a person's access to rows and/or columns?


texts: How can I write a trigger that can subtract values between two tables

texts:
I have a table called transaction that has these fields

CREATE TABLE transaction (
    transaction_id INT AUTO_INCREMENT,
    status varchar(255),
    myPIN varchar(255),
    amount_paid varchar(255),
    PRIMARY KEY (transaction_id)
)  ENGINE=INNODB;

Another table called defaulters that has the following

CREATE TABLE defaulters (
    defaulters_id INT AUTO_INCREMENT,
    myPIN varchar(255),
    debt_amount varchar(255),
    PRIMARY KEY (defaulters_id)
)  ENGINE=INNODB;

Now I want the value to subtract if the value of status=1 in transaction table where myPIN is equal to the amount_paid then it subtract debt_amount-amount_paid to get debt_amount remain
 and it reflect the remaining amount in defaulters table in debt_amount field.

fiddle