texts: SQL Server - Restore only primary Filegroup but bring the database online

texts:
I'm trying to find out if it's possible to bring a SQL Server 2016 Enterprise database online by restoring only the primary Filegroup. My scenario is, we have a fairly large OLTP database, of around 2TB. About 25% of that size is a single 'history' table, which as the name suggests, just tracks activity.
Each day, this database (in full recovery mode) is backed up and restored to several other environments where it is used for various, typically none-critical purposes. This restore process is slow due to the database size, and the 'history' table is obviously a significant contributer.
Although this 'history' table needs to exist in these other environments (it is referenced by numerous stored procedures), it doesn't need to contain any data. We just need it to exist but it could be blank, or ideally, just contain a few thousand of the most recent rows (not essential).
My first thought in achieving this was to:

Move the 'history' table to a secondary filegroup (the database has a single, primary filegroup currently)
Perform a backup of only the primary filegroup (this would probably be an extra 'copy_only' backup).
Restore this backup to the other environments.

The problem is, after step 3, I can bring the database online, however, I obviously can't reference the 'history' table, since it belongs to an FG that hasn't been restored. If I do, I get an error as expected:
The query processor is unable to produce a plan for the table or view 'History' because the table resides in a filegroup that is not online.
So my questions are:

Is what I'm trying to achieve even possible using this (or a similar) approach?
After step 3, is there a way by which I could 'drop' the secondary filegroup and recreate a blank copy of the 'history' table in the primary filegroup? This would allow the application/stored procedures that rely on its presence (but not it's data) to function. This doesn't appear possible without restoring the secondary filegroup first.
Is there just a flat-out better approach that I've not considered?

Note - The 'history' table is not referenced by any FKs, so there are no data integrity issues caused by not having the data there.
I'm aware that there are alternative ways of 'synchronising' multiple databases rather than a backup/restore (replication being one). Unfortunately, that is somewhat out of my control so for now, I'd like to focus on the backup/restore approach. Of course, I'm happy to hear suggestions.
UPDATE - Just to be clear, the rest of the data in the database is needed in each environment, but minus the 'history' table.


texts:
My app has a requirement wherin we need to capture attendance details of a class. A class typically has 50-60 students and there will be records with attendance/absense status of each student for a day.

My idea is having a table like this:

| student_id | class | section | date       | status      |
------------------------------------------------------------
| 2          | 7     |     A   | 2013-10-24 | 1 or X      |

X - Absent , 1 - Present

This would have records for all months for all students across all classes. So 1000 students, 30 record per student per month, this table must be ending with 1000*30*12 = 360,000 records. We'll be pulling attendance report student-wise and class-wise.

My friend has a suggestion to create a table for each year which would look like this:

| student id  | class   | section | month  | date1 | date 2 | date 3 |......| date30 |
--------------------------------------------------------------------------------------
| 1           | 2       |     C   | Jan    | X or 1|  X or 1| X or 1 |      |X or 1  |

This would mean that I'll have the attendance details of each student for a month in a single record. There would be 1000 (student) * 12 (months) = 12000 records only for an year

Please suggest which would be more suitable here, and if there's any other better way of doing it.

Thanks.


texts:
I am new to the Database region while have to migrate the company's database from AWS RDS to AWS EC2 and subscribe MySQL Enterprise edition.

What I am confused now is which OS should I choose, Windows or Linux? The most important thing is the performance, which one would MySQL perform better? If its Linux, is it possible to use Ubuntu OS? Can anyone help to advise?

Really appreciate!


texts:
I have the following table:

CREATE TABLE word(
word CHARACTER VARYING NOT NULL,
id BIGINT NOT NULL,
repeat INTEGER NOT NULL
);
ALTER TABLE public.word OWNER TO postgres;
ALTER TABLE ONLY  word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id);

When I try to restore it using following command:

psql -U postgres -h localhost -d word -f word.sql

it gives me this error:

  multiple primary keys for table "word" are not allowed

How can I use multiple primary keys in postgres?


texts: Simple DELETE, but complicated execution plan

texts:
When I run this delete:

DELETE FROM ETLHeaders WHERE ETLHeaderID < 32465870

...it deletes 39,157 rows. It should be simple because it is deleting on ETLHeaderID which is the clustered index and primary key. But (according to the execution plan) it seems to be hitting 361,190 rows and using other indexes. The table does have a field with an XML data type (in case that affects this DELETE).

Any ideas why and how I can speed up this DELETE?

Execution Plan here:
http://sharetext.org/qwDY
Table schema here:
http://sharetext.org/Vl9j

thanks


texts: PostgreSQL Error: server closed the connection unexpectedly

texts:
I cannot connect to PostgreSQL server from PgAdmin III , so I get this error:

Error connecting to the server:  server closed the connection
unexpectedly
This probably means the server terminated abnormally before or while
processing the request.

I get the same error when I try connecting from psql.
I have got this error 2 days ago but after restarting the computer the problem disappears , but this time I have restarted it too many times vainly.
I'm using PostgreSQL  9.0.5 on windows 7 with ArcSDE 10.1 (Arcgis Spatial Database add-on), I have deactivated the antivirus and firewall.
I have tried to reinstall PgSQL but the problem persists.
How I can connect to my Pgsql service and get rid of this error?


texts: What privileges are need for a normal Oracle user to access V$ and DBA_ views?

texts:
I'm connecting to Oracle 19c as HR user. I want to execute the following SQL and some dba_... views:

select s.sid
      ,s.serial#
      ,s.username
      ,s.machine
      ,s.status
      ,s.lockwait
      ,t.used_ublk
      ,t.used_urec
      ,t.start_time
from v$transaction t
inner join v$session s on t.addr = s.taddr;

Oracle tells me the objects doesn't exist. It seems like a permission issue. What privileges are needed for me to access dynamic performance views and DBA_... views?


texts: Importing xml data to postgres database table

texts:
I used the following script to import data from xml to postgres db table-
DO $$

DECLARE xml_string xml;

BEGIN
xml_string := XMLPARSE(DOCUMENT convert_from(pg_read_file('C:\\Users\\pink\\Desktop\\receipt.xml'), 'UTF8'));
CREATE TABLE my AS
SELECT
    unnest(xpath('//receiptNo/text()', xml_string)) as receiptNo,
    unnest(xpath('//receiptLineNo/text()', xml_string)) as receiptLineNo,
    unnest(xpath('//receiptDate/text()', xml_string)) as receiptDate,
    unnest(xpath('//poNumber/text()', xml_string)) as poNumber,
    unnest(xpath('//receiptDate/text()', xml_string)) as receiptDate,
    unnest(xpath('//poLineNo/text()', xml_string)) as poLineNo,
    unnest(xpath('//itemNo/text()', xml_string)) as itemNo,
    unnest(xpath('//receivedQty/text()', xml_string)) as receivedQty;
END$$

but,I'm getting the following error -

couldnot open the file
[C:/Users/pink/Desktop/receipt.xml]


texts: SSIS FTP Package job fails when scheduling through SQL Server Agent job

texts:
I am using WinSCP inside SSIS Execute task for downloading and moving the file in FTP location. It works great through visual studio. I am passing parameters inside the SQL Server Agent job and the package is getting failed. Here is what I am getting:

  Executed as user: xyz. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.1601.5 for 32-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Option "/script= /Par $Project::WINSCP_FileDestParam;\filedest\dest /Par $Project::WINSCP_ScriptFile;D:\Temp\Script.txt /Par $Project::WINSCP_HostName;ftp.com /Par $Project::WINSCP_Password;sjhgjd /Par $Project::WINSCP_UserName;usname /Par $ServerOption::LOGGING_LEVEL(Int16);1 /Par $ServerOption::SYNCHRONIZED(Boolean);True /CALLERINFO SQLAGENT /REPORTING E " is not valid.  The command line parameters are invalid.  The step failed.

I have the parameters scripted the script.txt file and passing the parameters through SQL Server Agent job. This works when I am running through Visual Studio but not through SQL Server Management Studio.

Any help would be greatly appreciated.


texts: Finding Microsoft.AnalysisServices.Xmla dll in SQL Server 2008 R2

texts:
I'm not able to find Microsoft.AnalysisServices.XMLA.dll that I need to import

using Microsoft.AnalysisServices.Xmla;

I have found some articles and answered questions for SQL Server 2005 and 2012. But nothing works for me in the 2008 R2 version. My SQL Server/Analysis services is installed with all features enabled as far as I can tell and still there is no dll with the name I want anywhere on my computer. I don't need a redistributable solution because all computers using this product will have a full SSAS installation on them.

This thread adresses the same problem but without any useable answer: http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/84f06276-208a-48e8-be88-b6bdc24406d6

Do I need to install something extra or did I miss it in some way? No it is not in \windows\assembly\gac_msil\microsoft.analysisservices.xmla.dll as the reply in the above forum post suggests.

Any help will be greatly appreciated.


texts: Why a copy of my.ini of other MySQL installation not working on my PC

texts: I have two MySQL installations on two different PCs not connected to each other. I made some changes in the my.ini file of PC 1 and tried using this file on PC 2, but MySQL service fails to start. Why the copied My.INI not working on my PC when both installations were configured same and the instance names also similar?


texts: SSRS Query Timeout vs DatabaseQuery Timeout

texts:
In SSRS there are appear to be two separate query timeout options. The Query Timeout option can be found in the Dataset Properties dialog window on the Query tab. The DatabaseQuery Timeout option is found in the rsreportserver.config file. I realize the Query Timeout option is specific to the dataset, and the DatabaseQuery Timeout option is specific to Report Server.

What are the reasons for using different settings? Why not just make the Query Setting to always default to the overall report server DatabaseQuery setting? If I have a lower DatabaseQuery setting will that timeout before the Query setting is met?

Edit: I also want to know if this is in fact a serverside setting that can be overriden with the report specific setting. I can't seem to find any documentation on how these two settings are related/different.


texts: Restricting user to a single db in postgresql

texts:
I've a PostgreSQL Server with 5 db and 2 users: postgres and forge, both with Superuser attribute.

I want to create a third user that can only see and use one and just one of the db and by using it I mean only perform select.
I've tried different solutions but seems that the brand new user can access to all the other dbs.

Thank you


texts: Deny view definition at DB level and symmetric keys

texts:
I don't want a restricted database role to be able to view table and stored procedure definitions and code.

So I do

deny view definition to some_db_role

It does appear though that this is not capable of being overridden.

grant view definition on dbo.some_proc to some_db_role

seems to have no effect.

Worse

grant view definition, references on symmetric key::some_key to some_db_role

seems to have no effect. Basically if you deny view definition at the db level then symmetric keys are unusable by that role\user.

Am I missing something? Is there any way to grant rights to objects that override the rights of the db level deny?


texts:
It took me quite a while to learn how to deal with timestamps in MySQL and I think I accomplished my goals, but I want to double-check two things:

1) Are these doing what I expect

2) Are there faster replacements for these queries / functions

Today

SELECT COUNT(*) AS count FROM log WHERE DATE(datet) = DATE(NOW())

This Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(NOW()) = YEARWEEK(datet)

Last Week

SELECT COUNT(*) AS count FROM log WHERE YEARWEEK(datet) = YEARWEEK(DATE_SUB(NOW(), INTERVAL 1 WEEK))

This Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 1 MONTH)

Last Month

SELECT COUNT(*) AS count FROM log WHERE datet > DATE_SUB(NOW(), INTERVAL 2 MONTH) AND datet < DATE_SUB(NOW(), INTERVAL 1 MONTH)


texts: Most effective way of logging user query activity

texts:
We are logging and permanently storing all the queries performed about private persons in our database. Due to GDPR we need to be able to show all the entities who have requested personal data and the timestamps.
The queries can be single or batch involving hundreds of thousands of individuals.

What is the most cost-effective way of keeping such logs? The primary index should be the unique personal code which allows efficient retrieval of query information. For analytics purposes we sometimes need to index by entity and/or query date, but this doesn't need to be optimized. The number of unique personal codes is about 1.5 million and the number of entities is ~300,000 and growing.

I have tried InfluxDB, but the limitation is that the time makes up a part of the key and batch queries will contain the same timestamp. I have tried storing the data as JSON in a relational database but it takes too long to update both single and batch entries. With DynamoDB the problem was batch write efficiency. It took 2 hrs to batch write million items at 500 WCU.

The data could look like this:

{'personal_code':123456789,
 'data_requests':{
    'entity_1':{
        'query_type_1':['2018-09-25 12:00:00.000000',...],
        'query_type_2':['2018-10-02 12:00:00.000000',...]
        },
    'entity_2':{
        'query_type_1':['2018-09-25 12:00:00.000000',...],
        'query_type_2':['2018-10-02 12:00:00.000000',...]
        }
    }
}

And the goal is to use as little hardware resources as needed to achieve <2 sec latencies retrieving the data by personal code and appending new timestamps to the arrays.


texts:
A running mysql service uses CPU in a crazy way. (See attached screenshot from htop)

I followed the instructions here with no luck.

My server specs:

CPU: 1900Mhz . Intel Hexa-Core Xeon E5-2420
Number of CPUs: 2. Number of cores: 6.
RAM: 8GB
Hard disks: 4 × 300GB SAS
RAID 10
OS: Cent OS 6.5

Anything I can do?

SHOW PROCESSLIST; output

DB query:

SELECT COUNT(*) AS expression
FROM
(SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.language AS node_language, node.created AS node_created, users_node.picture AS users_node_picture, users_node.uid AS users_node_uid, users_node.name AS users_node_name, users_node.mail AS users_node_mail, taxonomy_term_data_node.tid AS taxonomy_term_data_node_tid, taxonomy_term_data_taxonomy_term_hierarchy.tid AS taxonomy_term_data_taxonomy_term_hierarchy_tid, 'node' AS field_data_field_news_photo_node_entity_type, 'node' AS field_data_field_op_section_term_node_entity_type, 'node' AS field_data_body_node_entity_type, 'user' AS field_data_field_user_realname_user_entity_type, 'user' AS field_data_field_user_organization_user_entity_type, 'user' AS field_data_field_profile_picture_user_entity_type, 1 AS expression
FROM
node node
LEFT JOIN users users_node ON node.uid = users_node.uid
LEFT JOIN (SELECT td.*, tn.nid AS nid
FROM
taxonomy_term_data td
LEFT JOIN taxonomy_vocabulary tv ON td.vid = tv.vid
LEFT JOIN taxonomy_index tn ON tn.tid = td.tid
WHERE  (tv.machine_name IN  ('sections')) AND (td.language IN  ('ar', 'und')) ) taxonomy_term_data_node ON node.nid = taxonomy_term_data_node.nid
LEFT JOIN taxonomy_term_hierarchy taxonomy_term_data_node__taxonomy_term_hierarchy ON taxonomy_term_data_node.tid = taxonomy_term_data_node__taxonomy_term_hierarchy.tid
LEFT JOIN taxonomy_term_data taxonomy_term_data_taxonomy_term_hierarchy ON taxonomy_term_data_node__taxonomy_term_hierarchy.parent = taxonomy_term_data_taxonomy_term_hierarchy.tid
LEFT JOIN field_data_field_news_photo field_data_field_news_photo ON node.nid = field_data_field_news_photo.entity_id AND (field_data_field_news_photo.entity_type = 'node' AND field_data_field_news_photo.deleted = '0')
WHERE (( (node.status = '1') AND (node.type IN  ('news')) AND (field_data_field_news_photo.field_news_photo_fid IS NOT NULL ) AND (node.language IN  ('ar')) )))


texts:
Last month we upgraded our Azure database from Business to S1 plan. S1 plan was chosen based on an article on MSDN giving guidance on how to choose the best plan for us. Also, Azure by itself suggested to upgrade to S1.

However, after a week of usage we felt that S1 is low on power for our need, and it was way too slow for our, then, 1.5GB of database. Then we shifted to S2 plan as a test and immediately found that S2 is the right plan for us. All queries that on Business took <1 sec and were taking 20-30 sec on S1 are now taking <2 sec on S2.

We were happy, but now after one month our site that was < 1GB for 3-4 yrs and recently grew to 1.5GB and remained that size for 3-4 months is 4.5 GB. There is absolutely nothing changed from our side. The data we add is hardly 100MB this month. If we think of the space indexes take, even then 3GB increase in size is simply unanswerable to us.

We do not have big operation except that we upload CSV of 1000-10K records per day but that has been part of site for years. We delete these rows after a few days, so the database size remains constant.

Is there any explanation for this?

I tried Google and found nothing that can explain this growth. Growth is like 100-200 MB per day. Is it the logs taking space? If so how can I check, or reduce them?

EDIT:

I rebuild indexes and now the size is reduced to 1.7GB.


texts: Fill in a table name using a variable from another query

texts:
I have a general query I need to run for many tables

SELECT sum(honoraria_amount)
FROM table_1

The table IDs I have a list, from another query. For example, I want to run this for: table_1, table_39, table_42, etc.

Since I have a query that returns the IDs:

table_id
----
1
39
42

Is there anyway I can feed these query results, into the sum query, like some form of replacing with a variable or array to iterate through?

If its possible I'd like to replace x below with the IDs and maybe loop through all the queries to print out all the sums:

SELECT sum(honoraria_amount)
FROM table_x


texts:
I have fixed length text files that I have to import into SQL Server daily. These files contain header records (01) and Detail records (02). I am trying to build an SSIS package to do this. I have a script component that splits the data out but I cant get it to work. I am new to SSIS ...please help.

Here is sample data:
01CA0022-0002                I0000000002618
02      AJ -000000000000375626 -0000000000003
02      AJ -000000000000372226 -00000000000037
02      AJ -000000000000738726 -00000000000073

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void PreExecute()
    {
        base.PreExecute();
    }

    public override void PostExecute()
    {
        base.PostExecute();
    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        if (Row.Line.Substring(0, 2) == "01")
        {
            HeaderBuffer.AddRow();
            HeaderBuffer.NAME_EDC = Row.Line.Substring(231, 60);
            HeaderBuffer.DUNS_EDC = Row.Line.Substring(291, 13);
        }
        else if (Row.Line.Substring(0, 2) == "02")
        {
            DetailBuffer.AddRow();
            DetailBuffer.REC_TYPE_DTEAIL = Row.Line.Substring(1, 2);
            DetailBuffer.PMT_NUMBER = Row.Line.Substring(3, 6);
            DetailBuffer.PMT_ACTION_CD = Row.Line.Substring(9, 2);
            DetailBuffer.PMT_AMOUNT = Row.Line.Substring(11, 18);
            DetailBuffer.ADJUSTMENT_CD = Row.Line.Substring(29, 2);
            DetailBuffer.ADJ_AMOUNT = Row.Line.Substring(31, 18);
            DetailBuffer.ACCT_EDC = Row.Line.Substring(49, 50);
            DetailBuffer.ACCT_OLD_EDC = Row.Line.Substring(99, 50);
            DetailBuffer.NO_ACCT_EGS = Row.Line.Substring(149, 30);
            DetailBuffer.PMT_POST_DATE = Row.Line.Substring(179, 8);
            DetailBuffer.XREF_NUMBER = Row.Line.Substring(187, 30);
            DetailBuffer.UNDISCOUNTED_AMOUNT = Row.Line.Substring(217, 18);
            DetailBuffer.DISCOUNT_ADJUSTMENT_AMOUNT = Row.Line.Substring(235, 18);
            DetailBuffer.INVOICE_ADVICE_NUMBER = Row.Line.Substring(253, 30);
        }
    }

    public override void CreateNewOutputRows()
    {
    }

}

The error I get says:

  Error at Data Flow Task [Script Component [85]]: The binary code for the script is not found. Please open the script in the designer by clicking Edit Script button and make sure it builds successfully. Error at Data Flow Task [SSIS.Pipeline]: "Script Component" failed validation and returned validation status "VS_ISBROKEN". Error at Data Flow Task [SSIS.Pipeline]: One or more component failed validation. Error at Data Flow Task: There were errors during task validation


texts: Is it bad form to use "LIMIT 1" on queries that should only return one row anyway?

texts:
If you have a query like this:

SELECT * FROM table WHERE primary_key_column = 123 LIMIT 1;

What do you make of the LIMIT 1?
It is technically not necessary, as we are selecting on a unique column. But I have always had the habit of putting the LIMIT 1 on the end as it shows explicitly what you expect.

I think the main negative perhaps is it could mask bad logic in the query that makes it return more than one row, and make a straightforward error harder to solve.

And what about UPDATE or DELETE? This sort of 'belt and braces' approach:

UPDATE table SET a = 'ABC' WHERE primary_key_column = 123 LIMIT 1;

We all know the LIMIT 1 is good sense here when typing queries into the terminal to protect against updating rows we did not intend to. But do you still advocate it for hard-coded and tested queries in the application?

Question is general in nature no specific DBMS in mind.


texts: What is the best way to clear couple of tables each night at specific hour in Postgres SQL?

texts:
I need to clear couple of tables every night on my db so in the morning they will be empty and ready to use for the end users.

I already wrote the query to truncate them but I'm not sure what is the best way to run it scheduled every midnight.

What is the best way to schedule such a task on windows server ?


texts: How to know exactly why SQL SERVER 2014 Express installation failed?

texts:
I downloaded ExpressAndTools 64BIT\SQLEXPRWT_x64_ENU.exe from Microsoft. When I tried to install it I get the following error:

But it does not say exactly what I have missing.

My environment is configured as follows:

Windows 7 Ulitimate 64 bits
4 GB of RAM and enough HDD space
Intel i3 processor (enough for SQL SERVER 2014)
.Net 3.0, 3.5, 4.0 installed on my computer

Still it says something is wrong without even telling me what.

How to solve that issue?


texts: SQL Deadlock graph - Find exact statement holding the lock and the time it started

texts:
I'm trying to troubleshoot a complex deadlock issue.

There are 2 separate processes (service and agent job) which often concurrently execute the same SP but with different @BatchID parameters. This SP is called within an explicit BEGIN TRAN. There are 30 different Insert/Update statements within the SP. The 2 processes deadlock 5 times a day on the same table/index, around the same time each day, and the service process is always the victim. The table has many unnecessary/redundant indexes and a couple of Insert/Update Triggers. Lock Escalation is Disabled.

Deadlock graph:

Deadlock statements:

We know that the survivor/victim are SQL sessions (SPID), NOT SQL statements.

We also know that the UPDATE statements shown in Frame1 of both sessions are involved in the Deadlock. And How are they involved ? They are both REQUESTORS of the U lock on the Index Key. Am I correct so far..

But which statements are the OWNERS ? And when did they start holding the X lock on the Index Key?
One of the standard recommendations for resolving/reducing deadlocks is to Shorten Transactions.
But if there are a hundred similar statements, without knowing WHICH statement started holding the lock and WHEN, it's not easy to go about shortening a transaction..


texts: How to backup full transaction logs for SQL Server 2008 R2

texts:
I have a 32GB database with over 7GB logs (DB name: XYZ). I'd like to copy the DB with all of the logs. I thought that I can simply run:

...
ALTER DATABASE XYZ SET RECOVERY FULL;
BACKUP DATABASE XYZ TO DISC = foo.bak;
BACKUP LOG XYZ TO DISC = foo.bak;
...

RESTORE DATABASE N_XYZ FROM DISC = foo.bak WITH NORECOVERY
  MOVE LogicalNameData TO ....\mylocation\n_xyz.mdf
  MOVE LogicalNameLog TO ...\mylocation\n_xyz.ldf

RESTORE DATABASE N_XYZ FROM DISC = foo.bak WITH NORECOVERY
RESTORE LOG N_XYZ FROM DISC = foo.bak WIT RECOVERY

But when I checked the N_XYZ DB size it turned out that data takes 32GB but logs take only 10MB. I searched on the Internet and I found this link. It says that the DB transaction logs will only contain the pages between now and the last backup. Could that be a reason why my logs are so small? Can I force SQL Server to always copy the full logs? If so how?

Edit: Sorry for such a vague question. Below I described the whole story.

I have a CI which tests the newest code base every night. To be more specific the steps are:

Get the newest code from the repository,
Copy the database (the snapshot of production)
Test/Run the whole App

The CI works fine but way slower than regular development environment. After a couple of hours I figured out that the slow down is caused by the second step. In other words: If I run the CI on freshly copied DB it takes 10 hours but if I rerun the CI on the same DB (so no DB is copied) it takes 3 hours. It looks like I'm loosing DB statistics when copying the backup.

So by all of the logs I meant the transaction logs with statistics.


texts: Can I create a semaphore with locked transactions in MySQL?

texts:
I have a service that requires maintaining a pool of external resources. Therefore I have a table to track the pool. Each row can be used by one user once. My thought was that I can start a transaction, use a lock, try to update row, and see if it errors from having a lock. However it seems my attempt during a lock just makes MySQL wait for the lock to end before making the update happen.

# process 1
START TRANSACTION;
SELECT * FROM pool WHERE id=1 FOR UPDATE;
UPDATE pool SET claimed=1 WHERE id=1;
# (run process 2 below before this commits)
COMMIT;

# process 2
START TRANSACTION;
SELECT * FROM pool WHERE id=1 FOR UPDATE;
# this hangs until above commit
UPDATE pool SET claimed=1 WHERE id=1;
COMMIT;

I know I can lower the lock timeout but that would possibly impact other queries negatively. Is there a way to accomplish what I'm going for via MySQL?

I should note that in reality I will be doing a SELECT on the table for things where claimed=0 ORDER BY RAND() LIMIT 1. If the update throws an exception I plan to do another SELECT to find another row, while the successful path would DELETE the row. Doing the example with id=1 is for simplicity in testing :)


texts:
If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary?

I'm looking for answers that cover both possibilities for plan forcing:

Plan Guides
Query Store Forced Plan

I have read the following that suggest QS forced plans do not carry over, but cannot find anything authoritative in the documentation, or anything about plan guides.

Query Store and Availability Groups by Erin Stellato
Query Data Store Forced Plan behavior on AlwaysOn Readable Secondary by Vikas Rana

Conclusive evidence of forcing would be the presence of Use Plan or PlanGuideName and PlanGuideDB properties in the secondary's execution plan.


texts: Horizontal and Vertical Fragmentation on Distributed Database

texts:
My Architecture:

I need to build a database with SQL Server, where the data from each server is distributed to the other server.

How can I achieve this kind of architecture?

How do I maintain consistency between the tables location on insert, and when selecting?

How can I select all the data of both servers with the same code?

Both instances need to accept writes. The idea is to work like DDB so one server knows that the other has the data and requests it.


texts:
I have an SSRS report which gets its data by firing a series of stored procedures.

Now the report is timing out big time when run in production, yet when I pull down the prod database and restore to development the report runs fine.

I was thinking to set up a sql server profiler trace in production and hopefully that will tell me something... eg high Disk I/O at the time it's being run.

What else should I be doing? Something with perfmon?


texts: An admin (GUI or shell tool) for managing all kinds of databases

texts:
I needed an interface/admin (web or desktop) from which I could manage all the databases in my system. Currently I use separate admins for each (phpmyadmin for mysql, pgadmin/phppgadmin for postgres, sqlite manager in firefox for sqlite etc). Whenever I have to work with all the databases, the only option I am left with is shell access to all the databases.

I wanted to know if there is an admin which can manage all kinds of databases in a single interface, possibly similar to the one used by jennifer widom in her coursera lectures?


texts: Query to List Encryption Certificate for Databases

texts:
What certificate is being used to encrypt each of the databases on the instances.

I can get the data using the following but how do I write the queries

USE master
GO

-- this provides the list of certificates
SELECT * FROM sys.certificates

-- this provides the list of databases (encryption_state = 3) is encrypted
SELECT * FROM sys.dm_database_encryption_keys
 WHERE encryption_state = 3;

I noticed that columns sys.certifcates.thumbprint and sys.dm_database_encryption_keys.encryptor_thumbprint contain the same data.


texts:
I am new to AOAGs and Can Anyone please help me get out of below confusion.

I have 3 Replica AOAG.
I have a Archival Job in 3 Replicas scheduled at same time which will run every 15 minutes.

AlwaysOn Job logically will always look for the Primary replica.Is it correct ?
If the same job exists in all the AOAG replicas(Primaries and Secondaries) and scheduled at same time and why it is succeeding still with any failure ?Same job it runs in Primary and secondary without any conflict?can you please help me understanding this logic ?
Which do you recommend ,scheduling the job at same time or with a difference of time around 5 minutes in secondaries.
Can you hint me on what if same time and what if different time scheduled?
Same Job if it runs in Primary will it run even in secondaries also as they are scheduled at same time ?


texts: How to know a backup command failed or worked in a script

texts:
I have a command in a script to do a 'backup database [name] to [file]  and all the other parameters.
Now, we have a problem where we run out of disk space, the script keeps on trucking, just issuing the message:
BACKUP DATABASE is terminating abnormally.
As if there is no problem... no exception is tossed, or anything... so the next command in the series, to do a restore of that database to another database... fails... and the script keeps on running...
Why doesn't it throw an exception when there is a catastrophic failure like either of these?
I've seen some suggestions that we should query log files to see if there was a database backup failure, but good grief, it should just DIE when you run the script.
Do I need to catch the exception, and throw the exception with a higher severity (I'd assume that the other one is thrown with the max severity, but maybe someone thought that a complete failure of a backup is just minor?)
I've seen other code / questions where people are doing a try/catch around the backup, but I have no interest in catching the exception, I want it to Abort right there, die, and report the error up the chain..
Instead, it just keeps going..


texts: Configure Email to be sent on restoring a database

texts: Is there a way to configure Email in SQL Server such that an Email is sent when a particular database is restored.


texts: Is it safe to insert the newest row with SELECT INTO?

texts:
Trying to keep rows consistent then referencing previous rows.

CREATE TABLE transactions (
    id         int     PRIMARY KEY
       GENERATED BY DEFAULT AS IDENTITY,

    account_id bigint  REFERENCES account,
    change     integer NOT NULL,
    balance    integer NOT NULL,
)

Query from the client is basically

SELECT balance
FROM transactions
WHERE account_id = X
ORDER BY id DESC
LIMIT 1;

INSERT INTO transactions (account_id, change, balance)
VALUES (X, 100, $balance + 100);

This code will produce incorrect results on concurrent inserts.
So, the question is how to make balance calculation always correct?

Will INSERT INTO ... SELECT(...) be enough?


texts: How to optimize information schema table in mysql?

texts:
When I checked slow running queries log, I found out that

select count(*) into @discard from information_schema.PARTITIONS;

This query is taking 6 seconds to execute (a bit higher for this execution). I am not really sure about usage of this query and would look advise on optimizing it.


texts: Which characters are used as letters inside a fulltext index of MySQL?

texts:
I can add a fulltext index to MySQL tables. This will index all words of the related fields inside the table.

But what are "words"? Indeed they incloses letters and digits (A-Z, a-z, 0-9) and maybe the underscrore (_). But the default MyISAM stopwords contains abostrophs too. For example "can't" is a word or it.

So I would like to know, which characters are inside words of a fulltext index in MySQL / MariaDB?


texts: How does unique key help to improve SQL query performance?

texts:
Suppose I have a table with an id column and and I am maintaining unique values manually while inserting and updating records instead of creating a unique key on that column (e.g. by using a date & time for the id column value, such as 201505061648). I am not using any index or key in my table. So, does it affect query performance when I execute a select query with a where clause even if the cardinality is 100% for the id column?

select * from myTable where id=15

I mean does a unique constraint help to improve query performance or does it just force the user to maintain the record uniquely, so that SQL will get higher selectivity on the unique constraint column?

I don't know much about unique constraint and unique index. The answer given on this question helps in understanding the difference between UniqueIndex and UniqueKey. I want to know more about unique key and how it can improve my query performance.


texts:
Sorry if this seems like rambling I'm not typically a DB person just trying to work through an issue.
I have two tables cases and legal_entity the data on the two tables looks like this
legal_entity

first_name
last_name
address_1_zip

Maria
Munoz
33122

John
Glenn
10003

Melissa
Johnson
90210

cases

style
case_number
create_date

Munoz, Maria
SL12345
2021-03-01 11:45:26.657

Glenn, John
SL45678
2021-03-02 12:10:14.552

Johnson, Melissa (DNY)
SG42561
2021-04-10 01:06:10.214

What I am trying to do is combine the last_name and first_name fields from the legal_entity table with a comma between them, and match (contains) it against the style field in the cases table. For everything in the last month basically. This is the query I'm using
SELECT
    first_name, last_name, address_1_zip,
    CONCAT_WS(',', last_name,first_name) AS CombinedName
FROM
    CPSQL.dbo.legal_entity AS LE
INNER JOIN CPSQL.dbo.cases as C ON LE.CombinedName=C.style
WHERE C.create_date >= Convert(datetime, '2021-03-01') AND C.create_date <= Convert(datetime, '2021-04-16')
ORDER BY C.create_date

The issue I run into is that it seems to not like the LE.CombinedName so I'm not sure how to reference the concatenated string against the style column in the cases table.
Any help would be greatly appreciated.


texts: postgres 9.6 GRANT requirements for stored procedure

texts:
I have a role that will execute a stored procedure (function) to update a table.
I give the user acccess to the role, and the role execute on the stored procedure.
Do I also have to give the role update on the table?
Thanks


texts:
I lost the sa password on a machine, and when I log in to the machine directly using an account in the admin group, SQL Server Management Studio will not allow me to log in using Windows authentication.

My plan was to simply log into the server, connect via Windows Authentication, and reset sa to use a new password. Since I cant' connect via Windows Authentication, this won't work.

How else can I reset the sa password?


texts: How can a datapump export have its resource usage limited on linux?

texts: Doing an export of a number of users causes high CPU and I/O usage.  I can mitigate the effects of the CPU usage using Resource Manager and setting the export session to a low priority group, but how can I limit the disk I/O?  I am not running the operation in parallel and have already read Oracle's Datapump Performance documentation.  Is there anything else I can do?


texts: pg_upgrade fails with an unspecified error

texts:
I’m running Postgres 9.1’s pg_ugrade to migrate from 9.0 to 9.1 on Windows x64. After editing the permissions to allow full trust, running the tool as the postgres user, and working around a bug whereby pg_upgrade leaves the "old" store locked and refusing to run again ("there seems to be a postmaster servicing old cluster"), it now fails without explaining what’s wrong:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok
Checking database user is a superuser                       ok
Checking for prepared transactions                          ok
Checking for reg* system oid user data types                ok
Checking for contrib/isn with bigint-passing mismatch       ok
Creating catalog dump                                       ok

There were problems executing ""C:\Progs\Postgres\9.0\bin/pg_ctl" -w -l "nul" -D "C:\Progs\Postgres\9.0-data"  stop >> "nul" 2>&1"

Failure, exiting

Is this tool production-ready or am I wasting my time? If this is what I should be doing, what are your suggestions for solving the current failure?


texts: Weird SSRS reports error while Depolying reports

texts:
I have created the reports by using SSRS 2016 and Visual Studio 2015.
When I build the reports it shows success, then deploying the report to the Target server URL I get the following error:

  Microsoft SQL Server Report Designer:

  Could not connect to the report server http://ServerName/ReportServer_InstanceName.
  Verify that the TargetServerURL is valid and that you have the correct
  permissions to connect to the report server.

  Additional Info: Unable to connect to the Remote server

I got the report sever URL from the configured Reporting Services:

It's a named instance so I added WEB Server URL as:  http://servername/reportserver_instancename and I checked permission all are in place.

Is there any reason behind the URL or Permissions issue?


texts: In SQL Server Replication, at what point is it recommended that one separate the distributor?

texts:
Is there a general rule of thumb for when it becomes good practice to assign distribution responsibilities to a separate server that is not also a subscriber?

e.g.

"At n publishers/publications with m subscribers/subscriptions it is recommended that distribution be handled on its own instance"

or

"At j total rows replicated it is recommended ..."

I assume that this would also be dependent on SQL Server version so I'll just say that publishers are varied (versions 2000, 2000, 2008, and 2008R2), current distributor (also a subscriber) is 2008, and subscribers are also varied (2000, 2005, and aforementioned 2008).


texts: Moving Tables Indexes to Separate file / file-groups, What Happens?

texts:
I'm trying to get a grasp on whats going on when you move objects to a separate filegroup / .ndf file.

*This is for a 3rd party app that sucks that I have no access to source, and is entirely Ad-Hoc OLTP workload.

My setup is datafile's on BB 4G cached raid 5 array.  (Very fast write speeds / Slower then desired reads)

Logs on Mirrored Solid States (Fast Reads / Fast Enough Writes) array

tempdb on 2nd Mirrored Solid States (Fast Reads / Fast Enough Writes)  array

My plan was to move objects with high reads / very low writes to a 3rd SSD array (Very Fast < 1ms Read).  I moved a good amount of tables and indexes by dropping the Clustered Index and  Rebuilding on the Secondary File Group.  When I run

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f
ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o
ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = f.data_space_id
AND o.type = 'U' -- User Created Tables
order by f.name
GO

It Show the objects are indeed on the secondary filegroup.  However when I look at the reads it show that the filegroup is not being used.  Also when I look at the size:

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128.0 AS [Total   Size in MB],
 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In    MB], [file_id]
FROM sys.database_files;

It looks like very little of the .ndf datafile is being utilized.  I tried rebuilding the indexes, same thing.

So long story short my question is what happens when you drop / rebuild on a secondary filegroup.  Am I going about this the wrong way ?

Thanks in advance

Edit -- at Marks Request

USE [TimeMatters10]
GO

/****** Object:  Index [matter_k__2]    Script Date: 08/16/2011 12:41:07 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[lntmuser].   [matter]') AND name = N'matter_k__2')
ALTER TABLE [lntmuser].[matter] DROP CONSTRAINT [matter_k__2]
GO

USE [TimeMatters10]
GO

/****** Object:  Index [matter_k__2]    Script Date: 08/16/2011 12:41:07 ******/
ALTER TABLE [lntmuser].[matter] ADD  CONSTRAINT [matter_k__2] PRIMARY KEY CLUSTERED
(
    [sysid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,      IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,    FILLFACTOR = 90)
ON [Alternate] --Instead of primary
GO


texts:
Installing "SQL Server 2000 Client" and "SQL Server 2000 service pack 4".

I am new to SQL server and want to know if both are different and the exact steps that i need to follow for installation. Please advice.


texts:
There are several logins in my SQL Server that don't have user mappings to any database; they are simply dummy logins.

Someone created the login but it does not have any associated users.

I need to a script to list out those logins.

Thanks in advance.


texts: Should I put followers_no & following_no & product_no & last_login and etc in the user table? Won't updates cause fragmentation on the table?

texts:
I have a table for users in a social networking site. I want it to be efficient from ground up. If I put mentioned fields on the table then I have to update user's table on each changes. Should I have another table for fields like these that change frequently? Should I be concerned about fragmentation?

Is there any better approach that I'm unaware of?


texts: Despite of using GRANT ... ON ALL TABLES (or ALL SEQUENCES) TO user, user cannot access newly created tables (or sequences)

texts:
For a production server, I wanted to separate the super user, admin and app roles to limit scope of mistakes.

So I've created an account that our app uses to access the DB, and an admin account. The admin account is also used to run migrations to create tables.

I thought I'd ironed out all the issues, the app user can access all existing tables (which were created by the super user), however having now run a migration that creates a table, the app has no access to that table.

The users were granted access with the following commands:

GRANT CREATE, CONNECT ON DATABASE test TO admin;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON ALL TABLES IN SCHEMA public
  TO admin;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin;

GRANT CONNECT ON DATABASE test TO api;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public
  TO api;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api;

Then the admin user ran CREATE TABLE new_table; and trying to access that table, the app gets permission denied for relation new_table

1) What grant am I missing to allow api access to the new table
2) Out of curiousity, if I didn't have a record of the previous grants, how would I examine the grants of the users and discover the privilege that's missing


texts: Redundant mirroring of two mongodb servers

texts:
I have built a laravel web application for a client of mine which runs on a dedicated server.

The system is a booking system, and the client needs access to this at any time during the work day. So he came to me with "What happends when I loose my internet connection?".

The solution I am going to implement for my client is that I will set up a Raspberry Pi that will run the same web application locally at their shop. So if the loose their internet connection, they can access the web application served from the Raspberry instead.

However, what I now need, is that the Raspberry at all times when it has internet connection, syncs up with the latest information about booked appointments from the live server. And when the internet connection goes down and they use the local web application to book new appointments, then I need the raspberry to tell the live application about those new hours that are booked when the internet connection comes back on.

I have been looking at replication-sets, but it looks like I need a third server to be a judge here for the system to work in such a way that the slave gets writing capabilities to its mongdb server.

Can anyone help me out with any ideas here?

Thanks.


texts: Why is SQL*Plus' desc table not showing not null constraints after using dbms_redefinition

texts:
I have the following table that I want to redefine:

create table tq84_redefinition (
  id number primary key,
  ts1 timestamp not null,
  ts2 timestamp
);

Please note the not null constraint on the column ts1.

Using dbms_redefinition, I specifically use copy_constraints => true.

create table tq84_redefinition_int (
    id number,                            -- Note: no primary key to prevent «ORA-01408: such column list already indexed»
    ts1 date,
    ts2 date,
    duration_minutes as ((ts2 - ts1) * 24 * 60)
);

begin
  dbms_redefinition.start_redef_table(
    user, 'tq84_redefinition', 'tq84_redefinition_int',
   'id, '               ||
   'to_date(to_char(ts1, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts1, ' ||
   'to_date(to_char(ts2, ''ddmmyyyyhh24miss''), ''ddmmyyyyhh24miss'') ts2');
end;
/

-- set serveroutput on

declare
  cnt_errors binary_integer;
begin

  dbms_redefinition.copy_table_dependents(
    user, 'tq84_redefinition', 'tq84_redefinition_int',
    -------------------------------------------------------
    copy_indexes     => dbms_redefinition.cons_orig_params,
    copy_triggers    => true,
    copy_constraints => true,
    copy_privileges  => true,
    ignore_errors    => false,
    num_errors       => cnt_errors,
    copy_statistics  => true,
    copy_mvlog       => false);

  if cnt_errors > 0 then
     dbms_output.put_line('There were ' || cnt_errors || ' errors.');
  end if;

end;
/

exec dbms_redefinition.sync_interim_table(user, 'tq84_redefinition', 'tq84_redefinition_int');
exec dbms_redefinition.finish_redef_table(user, 'tq84_redefinition', 'tq84_redefinition_int');

Everything seems to work fine except that desc in SQL*Plus doesn't correctly display the not null constraint:

...> desc tq84_redefinition;
 Name                        Null?    Type
 --------------------------- -------- ---------------
 ID                                   NUMBER
 TS1                                  DATE
 TS2                                  DATE
 DURATION_MINUTES                     NUMBER

However, somewhere, the not null constraints is in place. I can see it by issuing a

select constraint_type, constraint_name, search_condition
  from user_constraints
 where table_name = 'TQ84_REDEFINITION';

Also, if I try to insert a record [insert into tq84_redefinition (id) values (999999)] I get the (correct) error message ORA-01400: cannot insert NULL into ("META"."TQ84_REDEFINITION"."TS1").

Edit: The version (v$version) is:

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Edit 2 @Munchi, the result of your suggested select statement

select
  column_name as "Name",
  nullable as "Null?",
  concat(concat(concat(data_type,'('),data_length),')') as "Type"
from
  user_tab_columns
where
  table_name = 'TQ84_REDEFINITION'

is

Name                           N Type
------------------------------ - --------------
ID                             Y NUMBER(22)
TS1                            Y DATE(7)
TS2                            Y DATE(7)
DURATION_MINUTES               Y NUMBER(22)


texts:
I am using following syntax to create an InnoDB table:

create table InnoDB_Test(a INT)
DATA DIRECTORY='/var/log/storage/MySQL'
INDEX DIRECTORY='/var/log/storage/MySQL' engine=InnoDB;`

I want to create table which can store data over mounted partition(/var/log/storage i.e. /dev/sda6), but it is storing the data in /var/lib/mysql.

Files related to table i.e. .idb and .frm files are created there. It is not creating .isl file and neither the directory which is to be created in the directory mentioned.

But if I create the table with MyISAM engine then it is working fine. All files are created and data is getting stored in mounted partition.

I have enabled innodb_file_per_table, have_symlink global variables.

I am using 5.5.44-MariaDB MariaDB Server, CentOS 7.1

My partitions are as Follows:

/dev/sda2       6.5G  3.6G  2.6G  59% /
/dev/sda1       395M   32M  338M   9% /boot
/dev/sda6       5.3G   22M  5.0G   1% /var/log/storage`

So please tell me how to store data of InnoDB table in mounted partition?


texts: Select one of multiple columns into a new column Ask Question


texts: How can I select data from two differents servers?

texts:
I have two databases: data1 and data2 in two different servers (server1 and server2).

I am trying to join two tables of my two databases in MySQL like this:

SELECT left(a.code,9) as fourn,
       SUM(b.price) as total
FROM data1.product as a
JOIN data2.my_items as b ON a.fourn = b.left(p_code)

and this is the error I get when I run the query in server1:

  SELECT command denied to user 'slave'@'' for table 'my_items'


texts: MySQL: sum time ranges exluding overlapping ones

texts:
I need to sum up the time which results from multiple time ranges. For example - we have enter / exit ranges of some office:

Query has to:

Exclude overlapping ranges (8:00 - 10:00)
Exclude "missing" part (12:00 - 14:00)

Expected result in this case is 8:00 (8:00 to 12:00 + 14:00 to 18:00)

Example table structure:

DAY        | TIME_ENTER | TIME_EXIT
2016-01-01 | 08:00      | 10:00
2016-01-01 | 08:00      | 12:00
2016-01-01 | 14:00      | 18:00

Expected result:

DAY        | TOTAL
2016-01-01 | 08:00

Table structure:

CREATE TABLE Registry
 (`Date` DATE,
  `Enter` TIME,
  `Exit` TIME);

Example inserts

INSERT INTO Registry
VALUES
 ('2016-01-01', '08:00', '09:00'),
 ('2016-01-01', '08:00', '12:00'),
 ('2016-01-01', '14:00', '18:00')


texts: Allow only unique combination of values for a composite primary key

texts:
I created the following table called books:

CREATE TABLE IF NOT EXISTS `books` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
`name` TEXT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I created another tablet called compare to compare any 2 books from the books table:

CREATE TABLE IF NOT EXISTS `compare` (
`id_1` BIGINT UNSIGNED NOT NULL,
`id_2` BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (`id_1`,`id_2`),
FOREIGN KEY (`id_1`) REFERENCES books(`id`),
FOREIGN KEY (`id_2`) REFERENCES books(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The previous works as expected but I need to force MySQL to allow ONLY unique combination of values in the compare table.

For example if I have the following row in compare table:

id_1 | id_2
------------
  1  |  2

I want to force MySQL to NOT allow me to insert the following row:

id_1 | id_2
------------
  2  |  1

So I want MySQL to allow only either 1,2 or 2,1 NOT both.

I am using 10.2.14-MariaDB - MariaDB Server


texts: MySQL master master replication, safe to delete rows on both masters?

texts:
We have a MySQL (v5.1.61) cluster with two masters (and two slaves). We have a table logs with a primary key column logID. This log table grows rather huge, and we'd like to delete data from it. So I've written a script that does that. — Is it safe to run this script on both masters? It deletes data only. What I'm wondering is what will the MySQL replication module do, if the row it's about to delete has already been deleted? Will replication break, or is it safe to delete rows on both masters? Does it matter if we use row based or statement based replication?

Example: The log data deletion script deletes row 123 on Master A. At the same time, the script deletes that same row on Master B. Then master B reads its relay log, and sees: "I should delete row 123, because it was deleted on Master A and I'm its slave. Let's delete it... ... ...but it's already gone!" — now what will Master B do? Will it halt replication, as if there was a duplicate key error? Or will it think "Fine, I need do nothing", and everything is okay.


texts:
Long time lurker, first-time poster. I have been looking for a solution to this problem for a while and can't quite think of what to search for anymore.

I work for a farm and we have this farm management system that allows people to put in activities (such as planting/irrigating/etc...). I'm ultimately trying to calculate every activity day from planting so each field is comparable to each other. The current problem I'm running into is calculating the 'season' timeline as we might have multiple crop plantings in a field within the year.

BTW Postgres used as my DB

Essentially my table looks like this:

Ideally, I would like to somehow query this to create a date range seasonality table so I could cross-reference all other activities to see what "season" the activity falls under and as such can grab the correct planting date.

I'm thinking the table would look something like:

I assume some kind of log transform query for user activities on websites might work similarly to categorize time ranges, but I haven't found anything helpful yet.

Let me know if you have any thoughts/ideas!

Thank you!


texts: Logic of Assigning values to variables in Scalar-valued Functions

texts:
I am currently working on updating old scalar valued functions with the hope of making them more efficient or changing them into Inline TVFs.

While troubleshooting my rewrites I came across some oddities in the original code which do not seem to make sense based on my understanding of variables in SQL

If I were to write

 Declare @var as Integer
 Select @var = col1 from table

Then this would always return the last item in col1. However, in the function that I am working with, this does not seem to be the case. Initially, there was a where clause which was changing results based on the input, but, removing that, the above property should hold. For the most part, it does, however there are a couple of cases where this is not true.

The outline is roughly similar to the following:

create function f (
@var1 ... )
returns datatype
begin
    Declare @temp1 ... (several vars declared here)
    select @temp1 = col1, @temp2 = col2, ..., @tempX = max(colX) from(
        select col1, col2, ... ,
        colX * (case when name = 'X' then 1 else 0)
        from table1
        INNER JOIN table2 on ...
        inner join table3 on ...
        left join table4 on ...
        where x) as sq
    group by ...
    return ...
end)

While more convoluted than the basic example above, with the where clause removed, it seems that it should be logically equivalent, however, If i run the function and look at what it's actually returning, it is not always the same value. Please note, that for testing purposes, the function is only returning one of the variables assigned through the select. Which, assuming similar functionality to the simple case given above, should always return the same value.

The values that are passed in as parameters should not affect this specific statement (this is a subset of the whole function, but I've created a smaller function to test this part of the code and decided to keep the same values passed in for consistency's sake). Only the newly declared variables are used and given values; excluding the where clause which I have commented out during my testing.

Then, my question is, how would these tables change in order to change the last element if the joins are always going to be the same and there is no where clause to change the values in the joins? grouping should be consistent as well since all of the tables should be the same every time the function is run.

The subquery should always return the same table and then the variables should be assigned the last value in their specific column, meaning that the return for the function should always be the same value.


texts: When does a port get assigned if SQL Server is set to TCP Dynamic Ports

texts: I'm trying to determine when a dynamic port is assigned to SQL Server.  If the TCP/IP Properties have the TCP Dynamic Ports enabled, does that port get assigned at the time the Server is installed or does it get assigned each time the instance is started.  Is it therefore possible that a new port will get assigned each time the instance is restarted?  I'm mostly curious about the 2008 through 2014 versions.


texts: user privilege and connection with mysql client


texts:
I have the following tables:

surveys            properties   owners_properties        owners
---+------------   ----------   ------------+---------   ------
id | property_id   id           property_id | owner_id   id

I want to find the count of surveys that have more than one property owners.

Here is a rough stab:

SELECT COUNT(*) FROM surveys
JOIN properties ON surveys.property_id = properties.id
JOIN owners_properties...
HAVING count(properties.id > 1);


texts:
I used to see server-related configuration in my.cnf located under [mysqld].
However, I now come across configurations that use [server] instead.
It looks like both of them are synonyms, at least on MySQL 8.0, I can see that options under both groups apply to the MySQL server.
Is this a new feature of MySQL 8.0? Is one of them preferred over the other? Is one of them deprecated?


texts: PostgreSQL vs. Oracle High Availability solutions?

texts:
PostgreSQL has a matrix of different high availability options which represent many different ways of building replication into an RDBMS.

Here is the PostgreSQL  High Availability, Load Balancing, and Replication Feature Matrix

Questions

Which of the approaches in the PostgreSQL High Availability Matrix are supported by Oracle?
Does oracle do high availability with techniques that are not available with PostgreSQL?


texts: SQL Server: How to call stored procedure without schema name?

texts:
SQL Server 2005std/2008web edition
db=user=schema=owner='John'

I'm moving website from one server to another. This piece of code works well on old server with SQL Server 2005.

    Dim sqlCmdVehicle As SqlCommand = New System.Data.SqlClient.SqlCommand("mySP_Name", sqlConn)
    Dim dtVehicle As New DataTable

    With sqlCmdVehicle
        .Parameters.AddWithValue("FullStockNo", "N102010")
        .CommandType = CommandType.StoredProcedure
    End With

    sqlConn.Open()
    sqlAdapter.SelectCommand = sqlCmdVehicle
    sqlAdapter.Fill(dtVehicle)

DB is backed up and successfully restored on new server. When I try to run with new SQL Server 2008 I got an error:

  Could not find stored procedure 'mySP_Name'.

pointing at last line.
I can see procedures with 'MS SQL management studio' not as  dbo.mySP_Name but as John.mySP_Name

When I change

System.Data.SqlClient.SqlCommand("mySP_Name", sqlConn)

into

System.Data.SqlClient.SqlCommand("John.mySP_Name", sqlConn)

all works well, but it's the same with all other procedures and a lot of such kind places in code :(

I got http://msdn.microsoft.com/en-us/library/ms189915.aspx, but caller's default schema is correct.
Again, all works well on old box.

What should I fix in SQL to allow run SP without explicitly mentioned user/schema name?

Thank you.

=======================================

Unfortunately, I didn't find fine solution. The way I took was search-and-replace SP_Name to John.SP_Name for the whole project.
Thanks to all participants.


texts:
## Only 1 query which returns 10 rows.
## It took about 66 secs

mysql> explain
    SELECT al.log_id , al.user_id , al.page_name ,
           al.section_name , al.submit_time , al.url  ,
           alfm1.value as 'source_env'  ,
           alfm2.value as 'website_id'  ,
           alfm3.value as 'index_id'
    FROM  admin_logs al
      LEFT OUTER JOIN
        (select * from admin_log_field_map where field_id = 3351
        )  alfm1 ON al.log_id = alfm1.log_id
      LEFT OUTER JOIN
        (select * from admin_log_field_map where field_id = 911
        )  alfm2 ON al.log_id = alfm2.log_id
      , admin_log_field_map alfm3
    WHERE 1=1
      AND al.page_name='Index Management'
      AND al.log_id=alfm3.log_id
      AND alfm3.field_id=891
      AND alfm3.value='jewelry'
    ORDER BY al.log_id DESC;

+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
| id | select_type | table               | type   | possible_keys      | key        | key_len | ref                 | rows  | Extra                                        |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
|  1 | PRIMARY     | <derived2>          | system | NULL               | NULL       | NULL    | NULL                |     0 | const row not found                          |
|  1 | PRIMARY     | al                  | ref    | idx_622,idx_pgname | idx_pgname | 78      | const               | 27720 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY     | alfm3               | ref    | idx_1533,idx_0311  | idx_1533   | 10      | cms.al.log_id,const |     1 | Using where                                  |
|  1 | PRIMARY     | <derived3>          | ALL    | NULL               | NULL       | NULL    | NULL                | 35358 |                                              |
|  3 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     | 33098 | Using where                                  |
|  2 | DERIVED     | admin_log_field_map | ref    | idx_0311           | idx_0311   | 5       |                     |     1 | Using where                                  |
+----+-------------+---------------------+--------+--------------------+------------+---------+---------------------+-------+----------------------------------------------+
6 rows in set (0.20 sec)

mysql>
mysql>
mysql> show create table admin_logs\G
*************************** 1. row ***************************
       Table: admin_logs
Create Table: CREATE TABLE `admin_logs` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `user_id` varchar(50) COLLATE latin1_bin DEFAULT NULL,
  `page_name` varchar(75) COLLATE latin1_bin DEFAULT NULL,
  `section_name` varchar(125) COLLATE latin1_bin DEFAULT NULL,
  `submit_time` datetime DEFAULT NULL,
  `url` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `source_environment` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  `log` longtext COLLATE latin1_bin,
  UNIQUE KEY `idx_622` (`log_id`),
  KEY `idx_pgname` (`page_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

mysql> \! hostname
fol-piwikdb01.dca.ftd.untd.com
mysql> show create table admin_log_field_map\G
*************************** 1. row ***************************
       Table: admin_log_field_map
Create Table: CREATE TABLE `admin_log_field_map` (
  `row_mod` datetime DEFAULT NULL,
  `row_create` datetime DEFAULT NULL,
  `log_id` int(11) DEFAULT NULL,
  `field_id` int(11) DEFAULT NULL,
  `value` varchar(255) COLLATE latin1_bin DEFAULT NULL,
  UNIQUE KEY `idx_1533` (`log_id`,`field_id`),
  KEY `idx_0311` (`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

how to get better performance using above query?


texts: Is it possible to configure backup filename conventions for Maintenance Plan Wizard in SSMS

texts: Is there a way to configure SSMS so that the backup file names for differential backups (created using the Maintenance Plan Wizard) would have the string  "DIFF" in the name?


texts: Why restoring a large sql file via SSH seems to quit partially through the process

texts:
I have a large (~5GB) .sql file I am attempting to restore via SSH, it seems to quit in the middle of the restore process.

When I come back hours later after issuing the command (the file has 7 tables), there are only 5 tables and nothing is being processed.

Is there a log file I can view to see why it quit half-way through? Is there a configuration option I am probably missing? Any ideas would be greatly appreciated. Thanks!

The command I am using:

mysql -u username -p database_name < file.sql


texts: SQL Server email function invalid syntax

texts:
I have this sp_send_dbmail script. The error log shows "invalid syntax error".

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'PROFILE_NAME',
    @recipients = 'oops@wrongagain.com',
    @subject = ' Search for empty ID result ',
    @query = ' select * from [dbo].[TT_INCI] tt where tt.ts_caseid =''(Auto)'' ',
    @attach_query_result_as_file = 0

Please help


texts: Why doesn't the following stored procedure compile with MySQL Workbench version 6.3.?

texts:
I would like to know how to make the following stored procedure compile with MySQL Workbench version 6.3.

use puppy;
DELIMITER $$
CREATE PROCEDURE spGetSystemLicensing
(
    IN  _licenseName varchar(128)
)
BEGIN

IF (TRIM(_licenseName) = '')
    select * from SystemLicensing;
ELSE
    select * from SystemLicensing where LicenseName=_licenseName;
END IF;

END

Here is my MySQL Workbench error message:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from SystemLicensing' at line 8

Any help is greatly appreciated.


texts: Change all varchar date formats with a single query

texts:
|date(varchar) |
|--------------|
|2016-02-16    |
| -------------|
|03-12-2015    |
|--------------|
|07-07-2007    |
|--------------|
|2016-03-14    |

Dates are stored in different formats and type is varchar. How to change all formats with single query in MySQL?

The formats are d/m/y, d-m-y, and y/m/d.

I need to copy these to a new column in date format y-m-d


texts: Most effective way to determine the minimum set of permissions for user

texts:
I have a database that an application connects to using db_owner permissions.

How do I effectively determine the minimum set of requirements actually needed by this user (application) to run without causing service interruption? (ie. without trial and error)


texts: Dealing with empty strings while loading a table from a CSV

texts:
I am trying to load a CSV file into a table. The CSV file contains some empty strings for example:

1,2,,4

I want to load the empty field as null. One way I know is to use \N i.e:

1,2,\N,4

but it won't suit my condition because my files are large and I don't want to change the original data.

Is there a way that I can specify in the Load command itself that wherever it finds an empty string, it should convert it to a NULL?


texts: How to rollback last executed sql script fired as sysdba

texts:
I have fired one sql script as sysdba which I have to fire using other oracle user.

I have already created some user and each application running on each oracle user. By mistake I had fired a script using one particular user but I have fired it using sysdba.

How to find .dbf file of sysdba user.
Can I revert my changes.
Does it affect other application.
I have list of tables and sequences. Can I drop individual table and sequence?
Do you have any better solution.

That script created lots of tables and sequences and inserted data into some tables.

Thanks!!!


texts: What are the benefits of moving redologs, data- and controlfiles to another location?

texts:
I have installed Oracle 18c XE and was wondering what benefits/disadvantages would come from seperating the data that is stored inside the databases managed by the dbms and the actual Oracle installation (Oracle_Home).
I ask this because it is possible to move Redologs, Datafiles and Controlfiles. I just don't have an idea why anybody would do that.


texts: Does the 'quality' of query optimization differ by database provider?

texts:
I don't have a clear understanding of query optimization in any database, other than that 'it happens'. Now I've just seen a "medium" post that discusses the pitfalls of using a PostgreSQL CTE in terms of optimization since the CTE is only evaluated once and that any optimizations that may be applied in terms of how the CTE is used, the database just can't apply.

But the example in the blog post seems trivial to optimize - i.e.

SELECT * FROM foo WHERE id = 500000;

vs

WITH cte AS (
  SELECT * FROM foo
)
SELECT * FROM cte WHERE id = 500000;

If calculation of the CTE is done lazily on first requirement then I would imagine that these two queries could be optimized in the same way (I think at least).

And that made me wonder... Would SQL Server be able to optimize such a query better than postgres?

Are there known differences between databases to the extent/ability they have in optimizing queries?


texts: We are using SQL Server 2008 with full recovery model, the database size is 10 GB and the log file is 172 GB, we want to clear up the space of the log file internally ,we did transaction log file backup it should be clear up, but it still 172 GB ,what to do?


texts:
In a database project that I am currently working on there is a requirement that at a certain point in time 'old' data should be changed to read only. Is this possible in SQL Server?

I can't change the table permission as the read only permission should only apply to data that is older than a certain threshold i.e. users should still be able to add and edit data that has been added after this point in time.


texts: Update using the value from the closest row


texts: PostgreSQL 10.7: pg_restore deletes access privileges on public schema

texts:
I have created a "custom" dump on one PostgreSQL 10.7 server and am restoring it on another PostgreSQL 10.7 server. Both on Linux.

This is all very standard:

pg_restore --single-transaction --clean --dbname=confluence \
  backups/uploads/confluence_2019-05-02T13\:00\:14.custom

No error messages.

Prior to the restore, the database confluence already existed on the target PostgreSQL server

The access rights to the public schema of the confluence database are as follows:

confluence=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)

After the restore, the owner of the confluence database can no longer read its tables. The reason is that the access rights have been modified:

confluence=# \dn+
                        List of schemas
  Name  |  Owner   | Access privileges |      Description
--------+----------+-------------------+------------------------
 public | postgres |                   | standard public schema
(1 row)

So I have to issue these commands:

confluence=# GRANT USAGE,CREATE ON SCHEMA public TO postgres;
confluence=# GRANT USAGE,CREATE ON SCHEMA public TO PUBLIC;

to get back on track.

What could be the reason for deletion of access privileges?

Update

I have not been able to reproduce the problem. From the Unix account postgres, restoring a dump using either

pg_restore --create --clean --verbose \
/var/lib/pgsql/backups/confluence_2019-05-04T09\:49\:01.custom

or

pg_restore --single-transaction --clean --verbose \
/var/lib/pgsql/backups/confluence_2019-05-04T09\:49\:01.custom

In both cases, the connection from Unix account postgres is passwordless and the restore goes into the database mentioned in the dump, as opposed to the database that would be given on the command line. In the first case, the database is dropped and created, too.

In both cases, the access rights to the public schema are correct after a restore.


texts:
I was checking selectivity of some columns for an index.
Where is this "ignore what I give you" behaviour documented?

This gives 4,851,908, 4,841,060, and 1,000,052

SELECT
     COUNT(*),
     COUNT(DISTINCT Col1), COUNT(DISTINCT Col2)
FROM Sometable;

This gives 4,843,634 unique pairs as per MySQL extension

SELECT COUNT(DISTINCT Col1, Col2) FROM Sometable

The following are wrong: the individual COUNT(DISTINCT colx) all give the 4,843,634 unique pair count regardless of any filler column or expression order.

I expected COUNT(DISTINCT Col1) = 4,841,060, and COUNT(DISTINCT Col1) = 1,000,052.

SELECT COUNT(DISTINCT Col1), COUNT(DISTINCT Col2) FROM Sometable

SELECT COUNT(DISTINCT Col2), COUNT(DISTINCT Col1) FROM Sometable

SELECT COUNT(DISTINCT Col1), 1 AS Filler, COUNT(DISTINCT Col2) FROM Sometable

But this give correct values again with another aggregate (like with COUNT(*) above)

SELECT COUNT(DISTINCT Col1), MAX(col1) AS Filler, COUNT(DISTINCT Col2) FROM Sometable

Questions, in case it wasn't clear:

Why does COUNT(DISTINCT Col1), COUNT(DISTINCT Col2) behave like COUNT(DISTINCT Col1, Col2)
Why is another aggregate required to make it work?


texts: SQL Server, attaching a database with Filestream, error 2

texts:
I'm trying to attach a database sent to me by someone. Unfortunately no backup is available, so I've been sent the raw files themselves.  I'm getting the following error in Management Studio:

Unable to open the physical file
"C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_fs".
Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".

A file activation error occurred.  The physical file name
'C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_fs' may be incorrect.
Diagnose and correct additional errors, and retry the operation.

Could not open new database 'MyDatabase'. CREATE DATABASE is aborted.
File activation failure.
The physical file name
"C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_log.ldf" may be incorrect.
New log file
'C:\Data\SQL Express 2008 Data\MSSQL10.SQL\MSSQL\DATA\MyDatabase_log.LDF' was created.

From what I can see, it's struggling with the FILESTREAM folder (which contains around 21Gb of data).  I've made it writeable, am running Management Studio and running as Administrator. The folder I'm trying to attach from is a folder with other working databases in.  I'm at a loss to interpret this error.  The server version is 2008 (the same as that at the other end).

Can anyone assist here?

Edit:  To add to this, what I did was create a new database with the same name, stop SQL Server, copy the old database files over and then start SQL Server again.  The table that contains the FILESTREAM records can't be enumerated in Management Studio, however, giving me the following error: "A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)".  Some reading leads me to believe there's a permissions issue with the filestream files, although as far as I can see, it has SYSTEM, Administrator and local user permissions, so I'm at a loss to see what I should do here.

The command I used was:

CREATE DATABASE [MyDatabase] ON  (FILENAME =
N'C:\Data\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase.mdf'), FILEGROUP
[MyDatabase_fs_Group] CONTAINS FILESTREAM DEFAULT  (NAME =
N'MyDatabase_fs', FILENAME =
N'C:\Data\MSSQL10.SQL2008\MSSQL\DATA\MyDatabase_fs') FOR
ATTACH_REBUILD_LOG GO

Also, looking in the logs, there's an EXCEPTION_ACCESS_VIOLATION when I do a SELECT involving the FILESTREAM field in the table.

2013-12-06 13:58:22.89 Server      Error: 17310, Severity: 20, State:1.
2013-12-06 13:58:22.89 Server      A user request from the session with SPID 52
    generated a fatal exception. SQL Server is terminating
    this session. Contact Product Support Services with the dump produced
    in the log directory.
2013-12-06 13:58:23.83 spid52      Starting up
    database 'ManageTTX'.
2013-12-06 13:59:25.07 spid52      Using 'dbghelp.dll' version '4.0.5'
2013-12-06 13:59:25.07 spid52      ***Stack Dump being sent to
    C:\Data\MSSQL10.Data\MSSQL\LOG\SQLDump0006.txt
2013-12-06 13:59:25.07 spid52      SqlDumpExceptionHandler: Process 52 generated
    fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is
    terminating this process. 2013-12-06 13:59:25.07 spid52


texts: How can I figure out when a session opened a transaction to debug a session being blocked by a sleeping session that has an open transaction?

texts:
I can reproduce a problem where my SQL Server stops responding.
I can see that one session is blocked by another session.
When I look at the status of the blocking_session_id, I see that it is 'sleeping'.
If I look at what sessions have open transactions I see that the sleeping session has an open transaction.

I have been using SQL Server Profiler to try to determine when it opened the transaction, but I have not been able to figure out how to make the trace log contain any events that look like a transaction being opened.


texts: Is DETERMINISTIC NO SQL function which returns the same value always a good alternative for using as a constant in MySQL?

texts:
MySQL does not support declaring constants which can be used between many stored procedures.
For me, it seems that a deterministic no sql function can be used as an alternative for a constant.
Example:
I want to use a constant named "OPERATION_TYPE_ABORT" with the value "ABORT".
So what I think I can use as an alternative is defining a stored function like below.

DROP FUNCTION IF EXISTS OPERATION_TYPE_ABORT;
DELIMITER //
CREATE FUNCTION OPERATION_TYPE_ABORT() RETURNS VARCHAR(6)
DETERMINISTIC NO SQL
BEGIN
    RETURN ("ABORT");
END//
DELIMITER ;

Output:

mysql> select "test" FROM DUAL WHERE "ABORT" = OPERATION_TYPE_ABORT();
+------+
| test |
+------+
| test |
+------+
1 row in set (0.00 sec)

mysql> select "test" FROM DUAL WHERE "ABORT2" = OPERATION_TYPE_ABORT();
Empty set (0.00 sec)

Do you see any disadvantage or a issue in this approach? (for e.g. Significant performance issue etc).
P.S: The queries which use these constants are using small sized tables in the query. MySQL version 5.6

Thanks


texts: Why Cassandra is not considered a relational database?

texts:
Read this answer
Relational databases are based on the relational model, an intuitive, straightforward way of representing data in tables. In a relational database, each row in the table is a record with a unique ID called the key. The columns of the table hold attributes of the data, and each record usually has a value for each attribute, making it easy to establish the relationships among data points.
Cassandra has tables.
CREATE TABLE movies (
    movie_id UUID,
    title TEXT,
    release_year INT,
    PRIMARY KEY (( movie_id ))
);

Data being relational has nothing to do with support of ACID properties.
Data being relational has nothing to do with normalised data

Why Cassandra is not considered Relational database?

Why Cassandra is considered NoSQL database? despite it has tables


texts: Is there a way to show a string instead if numeric column has specific value?

texts:
I have a float column called "weight". It's default is -1, showing the object wasn't weighted. Is it possible to show string "unweighted" instead of any negative number in the field?

As it is internally stored:

 ________________________
| object_id  |  weight   |
|____________|___________|
|     1      |   12.4    |
|     2      |    7.5    |
|     3      |   -1      |
|____________|___________|

And now how the table should appear to the user:

 ________________________
| object_id  |  weight   |
|____________|___________|
|     1      |   12.4    |
|     2      |    7.5    |
|     3      |unweighted |
|____________|___________|

The table with the combined float/text column can be a view; still, I have no idea how to do it. Is it possible, or is doing the translation (negative number -> 'unweighted') on client side my only option?


texts: Joining and filtering two sets of the same table


texts: How to specify a task in a SQL Server maintenance plan conditionally?

texts:
I have a weekly and nightly SQL Server Agent jobs executing a weekly and nightly maintenance plans, resp.

These weekly (on Sunday) and nightly (on other 6 nights of the week) maintenance plans differ only in the  1st task from total consecutive six steps. That is, in nightly/daily maintenance plan the 1st step is "Reorganize Indexes on all User databases" and in weekly/Sunday maintenance plan the first step is "Rebuild Indexes on All User Databases".

Is it possible and how to run just one (instead of two) maintenance plan, all 7 nights of week, with condition that on 6 days the 1st step is one (Reorganize Indexes) and on the rest 1 day of week the 1st step is another (Rebuild Indexes) ?

SQL Server 2012 R2 on Windows Server 2008 R2


texts: Seaching points within a radius - performance issues

texts:
i'm in trouble and completely lost with a geometry search, using 2 tables, linked by uid, each one contains about 5 millions rows.
One table with address and point coordinates, Second with user status and some other infos as username.

Deal is to find all users having coordinates within a radius of N kilometers. Central point is my proper coordinates.

First question (infos. haversine is a simple mysql function, working well; coordinates is a POINT column with a SPATIAL index)

SELECT uid, city, ROUND(111.045 * haversine(X(coordinates),
       Y(coordinates),40.7484405,-73.9856),2) as userDistance
    FROM userprofile
    JOIN ( SELECT 40.7484405 AS latpoint, -73.9856 AS longpoint,
                  10 AS radius, 111.045 as units
         ) AS g ON 1=1
    WHERE
        MbrContains(GeomFromText (
        CONCAT('LINESTRING(',
              latpoint-(radius/units),' ',
              longpoint-(radius /(units* COS(RADIANS(latpoint)))),
              ',',
              latpoint+(radius/units) ,' ',
              longpoint+(radius /(units * COS(RADIANS(latpoint)))),
              ')')),  coordinates);

This works fine, less than 0.015 to return about 5000 rows, and EXPLAIN tells me that SPATIAL index is used. But I query only the first table.

When adding an ORDER BY userDistance LIMIT 50 for example, works fine too, except the use of EXPLAIN tells me : "Using temporary; Using filesort";
So First question : is the using temporary a problem and how to avoid it ?

SECOND problem is more complex and more serious. If I query both tables with an INNER JOIN and add a WHERE clause like table2.status = 1, query will scan all rows, ignore the SPATIAL index, and take about 10 minutes.

SELECT uid, city, ROUND(111.045 * haversine(X(coordinates),
       Y(coordinates),40.7484405,-73.9856),2) as userDistance
    FROM userprofile
    JOIN ( SELECT 40.7484405 AS latpoint, -73.9856 AS longpoint,
                  10 AS radius, 111.045 as units
         ) AS g ON 1=1
    INNER JOIN users on userprofile.uid = users.uid
    WHERE
        MbrContains(GeomFromText (
        CONCAT('LINESTRING(',
          latpoint-(radius/units),' ',
          longpoint-(radius /(units* COS(RADIANS(latpoint)))),
          ',',
          latpoint+(radius/units) ,' ',
          longpoint+(radius /(units * COS(RADIANS(latpoint)))),
          ')')),  coordinates) AND user_status = 1;

Putting the users table in a sub query provided the same results : 10 minutes.

I try multiple solutions, but still lost. thanks


texts: Can I use Try_Cast to give me a query result column with different datatypes?

texts:
I have a column that is varchar(max).  It contains mostly valid XML.  But sometimes it has invalid xml (that I still need).

I am trying to make a view for this table that allows the value to display as XML (with the nice link feature that comes with SSMS) when the data is valid XML, but still shows the string when it is invalid XML.

Like this:

SELECT TRY_CAST([RunLog] AS xml) as [Log], -- works
       RunLog,                             -- Also Works

       -- This is the one I want, but it tries to parse all of them
       -- (even the ones that fail the cast)
       Case
           When Try_Cast(RunLog as xml) is NULL
                Then NettinRunLoggLog else CAST(RunLog AS xml)
       end as CombinedLog

FROM   LoggingTable

I think that this is a lost cause, because SQL Server will not allow a result to have more than one datatype.  But I thought I would ask the experts just to be sure.


texts: Are "xxx/sec" performance counters in sys.dm_os_performance_counters actually rates or just counters?

texts:
I am trying to track SQL Server metrics using Prometheus by querying the system dynamic management views. I am looking into the Buffer Manager: Page reads/sec counter in sys.dm_os_performance_counters, which I understand to be a rate at which reads are happening, aggregated over an [unspecified] amount of time, based on the description in the SQL Server documentation:

Indicates the number of physical database page reads that are issued per second. ...

However, I have tracked the query for 8 databases (with differing SQL Server versions: 2012, 2017, 2019) over 12 hours, and the counter value for all of them seems pretty high (16K+ for a development database with no traffic, 700K+ for a production database with very little traffic).
Also, the values are not changing, or rather, are not changing very much (+/-200 page reads) and very infrecquently (once for each database over a 12 hour period).
This leads me to believe that this counter may actually not be a rate but a total count over time that must be converted into a rate by aggregating the values and comparing with the previous value. If I select the metric from Performance Monitor, it shows the rate as 0, which seems to confirm my suspicion. Is this correct? And if so, is this the case for all of the /sec counters?
For reference, this is the query I'm using to retrieve the counter.
select cntr_value from sys.dm_os_performance_counters
where object_name = 'SQLServer:Buffer Manager'
and counter_name = 'Page reads/sec';


texts: Subtracting sets within a grouped column, should I be pivoting?

texts:
I'm not the best at explaining, but I have a table that has the format

CREATE TABLE foo
AS
  SELECT type,date::date,tp,price
  FROM ( VALUES
    ( 'A', '2010-10-01', 1, 0.05 ),
    ( 'A', '2010-10-01', 2, 1.04 ),
    ( 'B', '2010-10-01', 1, 0.53 ),
    ( 'B', '2010-10-01', 2, 1.04 ),
    ( 'C', '2010-10-01', 1, 0.05 ),
    ( 'C', '2010-10-01', 2, 1.02 ),
    ( 'D', '2010-10-01', 1, 0.05 ),
    ( 'D', '2010-10-01', 2, 1.08 )
  ) AS t(type,date,tp,price);

And what I want to do is subtract different types where the date and tp are the same. So that would be A-B, A-C, A-D, B-A, B-C, B-D, C-A, C-B, C-D, D-A, D-B, D-C.

To me this seems like I would want a wide format table, with the columns date, tp, A, B, C, D and then do a column-wise subtraction based on possible combinations. If the subtraction is less than 0, then the value is 0

The desired output looks something like this:

combo | date      | tp | price
---+------------+----+-------
A_B  | 2010-10-01 | 1  | 0
A_B  | 2010-10-01 | 2  | 0
A_C  | 2010-10-01 | 1  | 0
A_C  | 2010-10-01 | 2  | 0.02
A_D  | 2010-10-01 | 1  | 0
A_D  | 2010-10-01 | 2  | 0
and so on for all the combinations

Should I be looking at using crosstab ? Or is there a simpler/more elegant solution? My current solution is a view that uses CTE(s) to create all the possible dates and type combos, and then I have a function that goes through every typeA and typeB, date, tp combo. It is very slow.

The initial table in question is 2857658 rows


texts: Failed To Insert Data Into Tables Using Postgres-XL and 4 Datanode

texts:
Recently, I have installed Postgres-XL-9.5r1.5 in a R630, 16 cores, 16 GB RAM.
I configured 1 GTM, 1 Coordinator, and 4 Datanodes.
I created a simple table sales_small_cat

CREATE TABLE sales_small_cat
(
   date_sales            timestamp    NOT NULL,
   store_sid             integer      NOT NULL,
   branch_sid            integer,
   small_cat_sid         integer      NOT NULL,
   total_receipt_amount  integer,
   qty_sales_nonpromo    numeric,
   qty_sales_promo_rep   numeric,
   qty_sales_promo_avp   numeric,
   total_sales           numeric,
   pi                    numeric,
   is_festive            boolean,
   is_store_closed       boolean
)
DISTRIBUTED BY (store_sid)
TO NODE (dnode1,dnode2,dnode3,dnode4);
ALTER TABLE sales_small_cat
   ADD CONSTRAINT pk_sales_small_cat
   PRIMARY KEY (store_sid, small_cat_sid, date_sales);

I want to insert dummy data using

WITH
trxDates AS ( SELECT generate_series('2017-01-01'::DATE,'2017-01-01'::DATE,'1 day'::INTERVAL) AS trx_date),
idmStores AS ( SELECT generate_series(1,100,1) AS store_sid),
scat AS ( SELECT generate_series(1,40,1) AS scat_sid),
temp AS (
  SELECT trx_date,store_sid,round(random()*10+1) AS branch_sid,scat_sid
  FROM trxDates CROSS JOIN idmStores CROSS JOIN scat
),
temp2 AS (
  SELECT
    *, round(random()*1000) AS receipt_amount,
  random()*1000 AS non_promo, random()*1000 AS promo_rep, random()*1000 AS promo_avp
  FROM temp
),
finale AS (
  SELECT *,(non_promo+promo_rep+promo_avp) AS total_sales, random()*1000 AS purchase_index, false AS is_festive, false AS is_store_closed
  FROM temp2
  ORDER BY 1,2,4
)
INSERT INTO sales_small_cat
SELECT * FROM finale;

And I get

An error occurred when executing the SQL command:
WITH
trxDates AS ( SELECT generate_series('2017-01-01'::DATE,'2017-01-01'::DATE,'1 day'::INTERVAL) AS trx_date),
idmStores AS ( SELECT generate_series...

ERROR: unrecognized node type: 119

Execution time: 0.02s
1 statement failed.

I traced the log of datanode below:

[postgres@serveragci ~]$ tail -f pgxc/nodes/dn_master/dnode1/pg_log/datanode-20170508.log
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan
LOG:  disconnection: session time: 0:15:19.504 user=postgres database=postgres host=192.168.101.111 port=37161
LOG:  disconnection: session time: 0:16:52.181 user=admindb database=test host=192.168.101.111 port=37209
LOG:  connection received: host=192.168.101.111 port=37432
LOG:  connection authorized: user=admindb database=test
ERROR:  unrecognized node type: 119
STATEMENT:  Remote Subplan

Is it a bug or my mistake?
Please, enlight me.
Thank you very much.


texts: Does a Key-Value type table still needs a auto increment key?

texts:
Suppose that I have a very simple table to store variable-like data, like this:

+-----------+-----------------+
|    key    |      value      |
+-----------+-----------------+
| site_url  | helloworld.com  |
| site_name | Hello World     |
| time_zone | Asia Taipei     |
+-----------+-----------------+

Do I still need to create a ID column?
or just simply set key column as primary key
(key would be unique


texts: Postgres Upsert Performance Considerations (millions of rows/hour)

texts:
I have tried doing some research for quite a while on the performance implications of the built-in upsert (INSERT ... ON CONFLICT UPDATE...) when a lot of upserts are made. The scale is something like 1 million records/hour, that is split up in groups of around 300 records each.

The table is fairly simple, just a few int/bool fields and a date field. The total size of the table is somewhere between 50-60 million records. Essentially, all the rows are supposed to stay up-to-date within a certain cycle (that is currently about 2 days). Sometimes a lot of information changes, sometimes very little/no information changes (e.g. 300 records get upserted but they are identical to the existing records).

So far, one hypothesis is that this project seems to be suffering from the large amount of writes that happen constantly since even if the upsert results in no inserts/updates, the "failed" inserts from the upsert will still get written somewhere (according to our knowledge). Therefore, the idea is to utilize old-fashioned upserts (writeable CTEs) and do more granular operations that can make sure to only insert data that doesn't already exist, and only update data that has actually changed. Naturally, however, this will put more read-load on the DB and increase query complexity.

The question is, are we right in our assumptions that the built-in upsert is useless from a performance perspective (e.g. it's only good for much smaller tasks) or are we wrong? I read a bit about HOT updates and autovacuum tuning, but nothing that references something more similar to this question.

Worth mentioning is that this DB (PostgreSQL 10.9) is running on Heroku so we are not able to tune it to our needs. We are planning to move at some point, depending on how important it ends up being. Finally, it is also worth mentioning that this DB also has one follower (i.e. read replica).

Would really appreciate some good insight on this! Thanks beforehand.


texts: Can I prevent deadlock using `WITH(NOLOCK)`?

texts:
I have a table which I'm only Insert, Delete and Select (no Updates)

on Insert, Sometimes it used a session lock (WITH (TABLOCKX))

I have a program using ADO which Select from the table using the RecordSet::Open command to open a simple sequential query.

like:

Select * from t1 where id >= @from_id And >= @to_id`

Or:

Select top(10) * from t1 where id >= @id Order by id

The id column is an Identity column.

When I'm open the query, sometimes I'm getting a deadlock. (the table is locked by the Insert query and the id column's index is locked by the Select)

I'm trying to prevent the deadlocks.

Should I open the select queries WITH(NOLOCK)? may it prevent the deadlocks?


texts: Database level access in Azure SQL managed instance

texts:
In my Azure SQL managed instance I have two databases, Database_A and Database_B.
User A_ABC has access only to Database_A and he is part of role db_owner.

When the tries to alter a table we are getting the below error

Query:
alter table mta.mta_dmn
add active char(3)

Error:
Msg 916, Level 14, State 2, Procedure TR_XYZ_DDL_AUDIT, Line 8 [Batch Start Line 0]
The server principal "A_ABC" is not able to access the database "Database_B" under the current security context.

There is no cross database joins or any link between Database_A and Database_B, with Database owner permission my understanding was the user should be able to do any activity within the Database_A. Also there is no procedure 'TR_XYZ_DDL_AUDIT' which exists in either of the databases

I am not able to find why this error is coming/not able to understand this error or able to find a solution. Dropped the login completely and recreated just in case, but the issue persists


texts: Syntax error when creating procedure in mysql

texts: I am trying to create a procedure, but I am always getting syntax error and it is marking me "(" or ",". Iam using version 6.3. Can somebody help me to fix this problem? And here is my code


texts: Select results inclusively based on NOW()

texts:
My query is the following:

SELECT count(db.user_data.page_url) AS total_urls, db.authors.author
FROM db.authors
LEFT JOIN db.user_data ON db.authors.author_id = db.user_data.id
WHERE (db.user_data.date > (now() - INTERVAL 30 MINUTE))
AND db.user_data.domain LIKE '%example.com%'
GROUP BY author;

It returns all results between now() and last 30 minutes, that's fine. But those results are based on period (interval) of 30 minutes starting from current moment (in seconds).

How to select all rows based on now() - INTERVAL 30 MINUTE ,but including all results from the last 30-th minute inclusively? Because now the interval is "moving" by current moment + 30 last minutes.
So each new second I am getting different results, because of "sliding", "moving" interval?