texts: Most Efficient way to compare large 'text' type values in MySQL using InnoDB

texts:
I have a staging table like this :

CREATE TABLE `staging` (
  `created_here_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `desc_text`  TEXT NOT NULL );

And destination table as :

  CREATE TABLE `final_tbl` (
      `row_id` BIGINT NOT NULL AUTO_INCREMENT,
      `created_here_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `desc_text`  TEXT NOT NULL );

I want to insert desc_text into final_tbl only if it is not present. I am thinking of two options :

check if staging.desc_text exists in final_tbl.desc_text, if not
then insert into final_tbl
Maintain a column in 'final_tbl'
that will store the SHA224 value of desc_text column. compare SHA224
value of staging.desc_text with SHA224 column in final table and
then decide whether to insert or ignore.

I want to know which option will be faster?


texts:
I added a report project to my existing solution in VS2015, built some reports and now I am trying to deploy the project to a report server. I have gotten the URL from the config manager ( http://MyServer/ReportServer_SQLEXPRESS ) and put that in the report project properties as the target server URL.

When I click Deploy I get an error saying "The specified report server http://localhost/reportserver could ot be found.

I have no idea where it is getting this localhost reportserver URL from, that is not what is saved in the properties. The Report project is inside a solution with an MVC project. Do I need to change something at the Solution level?


texts:
The system has 3 main entities:

Staff
Courses
Students

I will try to explain only the Student table, and I hope the rest of the data will be clear.
[ Students ]

student_id: Primary Key
full_name: *
phone_number: varchar, because of the (+) symbol in country code.
email_address: *
birthdate: *
gender: *
id_card_number: A student’s government issued ID card number, the number might contain letters, that’s why it’s chosen as varchar.
passport_number: *
id_card_photo_url: I also store a photo copy of a student’s ID card, and this links to that file, hosted either locally or somewhere like S3.
passport_photo_url: *
street_address: *
address_nearby_point: The person’s home is located nearby what? X supermarket? Or maybe Y pharmacy.
contact_name: The name of a friend or family member close to the person, to contact in emergencies.
contact_phone_number: *
contact_relationship: Is the chosen contact a friend? A family member? A relative?
educational_level: Does the person have a BSc? MSc? PhD? Nothing?
specialization: Is he an electrical engineer? A doctor? A nurse? A plumber?
medical_condition: Does he have any chronic illnesses or allergies?
medication: The name of the medication he uses for his medical condition.
bus_service: Did the student opt-in for the bus service or not (boolean)
is_postponed: Did the student leave one of our courses halfway through due to an emergency?
postpone_reason: If the above is true what was the reason? If false this would be NULL
course_id: A foreign key that links to the Courses table. It’s a One-To-Many relationship, because a single student can have more than one course, or a single student might finish one course and start another one.
payment_type: there are three types, either cash payment, payment in two installments, or three installments.
registration_date: This field is filled in automatically, which is the date of the form being filled in.
discount_amount: How many US dollars was made as a discount?
discount_type: Why was the discount provided? Was the student a friend or a relative of a staff member? He won a competition? etc.
student_attendance_id: A foreign key that links to the StudentAttendances table. It’s a One-To-Many relationship, because a single Student can have many attendances.
test_id: A foreign key that links to the Tests table. It’s a One-To-Many relationship, because a single student can have many tests.
student_payment_id: His payments. If he paid cash it will link to only one record in the StudentPayments table, if he chose to pay in three installments a record will be made for each payment he makes.


texts: Copy data through SSIS package from one machine to another

texts:
I have data in few excel sheets which are to be loaded into tables. I've created a few SSIS packages to do this on my local machine.

Now I have to move these tables onto my client's machine, which is in another location. What's the simplest way to do this?


texts: How can I drop a Memory Optimized Table?

texts:
Running SQL Server Standard 2016 SP2-CU1.  I am not as concerned with the In memory Filegroup, but I must be able to drop the table objects? Or possibly rename them?

Running:

DROP TABLE [REF].[Work_xxyyzz]

Gives:

  Msg 12332, Level 16, State 111, Line 1
  Database and server triggers on DDL statements CREATE, ALTER and DROP are not supported with memory optimized tables.

There are no user triggers or events on my In Memory Tables.  5 simple tables with primary keys.

There are no dependencies or references to these tables.  They are like temp tables, we name them Work_xxxyyzz tables.  However, they do belong to a database schema.

I am fine to keep the filegroup in place for now.  I want to drop these tables in order to create default objects of the same name.


texts: Is it ok to have 'view count' column in the same table?

texts:
Let's say I have a website similar to YouTube where I'm storing and displaying the 'view counts' for each video. It is ok if I'm storing the view count column in the same table as the actual view table - I.e the row which defines the video information?
Or should I have a separate table that stores the view count information - I.e having two tables, one that stores the video information such as video Id, title, description, and another table that's only stores the counts, such as having columns: ID, video_id, view_count

The reason I'm asking this is because obviously the view count columns will need to be updated so many times throughout the years - lets say 1million views. I'm wondering if having 'view count' in the same database will have negative performance when it comes down to reading the main video for other users since that column will need to be locked for writing view count so many times throughout the day.
Because the video table will be pretty much static in content, and it's pretty much just reads.

Thanks,


texts: Creating temp tables SQL Server 13.0.5426 with dynamic SQL doesn't work

texts:
I'm trying to create a temp table using dynamic sql (MS SQL Server 13.0.5426).  This is a simplified example:

Using straight SQL works:

DROP TABLE #tmp_ts; -- will error, but ignored on the first execution
SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts;
SELECT * FROM #tmp_ts;

Creating it via dynamic SQL does not work.

DROP TABLE #tmp_ts; -- drop from previous test.
EXEC sp_executesql N'SELECT CURRENT_TIMESTAMP ts INTO #tmp_ts';
SELECT * FROM #tmp_ts;

Output:

  (1 row affected)
  Msg 208, Level 16, State 0, Line 63
  Invalid object name '#tmp_ts'.

My workaround is to not use a temp table, but I'm curious if anybody knows why this doesn't work.

Thanks!


texts: Execute Sql statement and send result to email

texts:
I have to execute SQL statement every day and send result to dba by email.I configured database email.

Below is query:

 SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs
            ON afs.accountid = account.accountid
                and afs.featureid = 'Schedules'
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0

For this I created Sql job having 2 steps:

execute select statement
send result:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = 'recipients@company.com',
        @subject = 'queryresultset',
        @body='testmail',
        @query_attachment_filename='warning.csv'

It is throwing an error and the result set is not attached with query.

  Error: Parameter @attach_query_result_as_file cannot be 1 (true) when no value is specified for parameter @query.
  A query must be specified to attach the results of the query.
  [SQLSTATE 42000] (Error 14625)

Edit:

Use MSDB
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'abc',
        @recipients = 'recipients@company.com',
        @subject = 'queryresultset',
        @body='testmail',
        @query_attachment_filename='warning.csv'
    DECLARE @msg VARCHAR(250);
    declare @query varchar(2048);
    SELECT @msg = 'Please refer to the attached spread sheet for the report.';

    set @query='
        SELECT
        account.accountID,
        account.name
    FROM
        account
        LEFT OUTER JOIN accountfeaturesetting afs
            ON afs.accountid = account.accountid
                and afs.featureid = 'Schedules'
                and afs.settingid = 'EditReasons'
    WHERE
        ISNULL(afs.Value, '0') = '1'
        AND NOT EXISTS
 (SELECT 1 FROM program
WHERE program.AccountID = account.AccountID
 AND program.Active = 1 AND
(program.ScheduleEditReasonFlags <> 0 OR
 program.ScheduleEditReasonFields <> 0))
        AND account.IsMaster = 0
        go';
        exec (@query)

Above query is not taking schedules and editreasons value column.  I am still getting a syntax error after declaring all variables like:

declare @featureid varchar(100);
declare @settingid varchar(100);
declare @value varchar(100);


texts:
I am running a series of UPDATE commands on a nearly empty InnoDB table, and around 1 out of every 20-30 queries will inexplicably take 10 times as long as the others. For example, the first 20 updates will take 20ms, and the 21st update will suddenly take 200ms. I've set up an incredibly basic test:

CREATE TABLE `test` (
  `col1` int(11) DEFAULT NULL,
  `col2` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

I insert a single row into the table, and then I have a C# console program that does a series of updates:

for (int i = 0; i < 15; i++) {
   long start = CurrentTimeMillis();

   MySqlCommand cmd = new MySqlCommand();
   cmd.Connection = conn;
   cmd.CommandText = "UPDATE test SET col1=" + i + " WHERE col2=1";
   cmd.Prepare();
   cmd.ExecuteNonQuery();

   Console.WriteLine("Duration: " + (CurrentTimeMillis() - start));
}

This is the output I see from the program:

MySQL version : 5.5.17
Duration: 36
Duration: 30
Duration: 16
Duration: 26
Duration: 152
Duration: 47
Duration: 71
Duration: 77
Duration: 46
Duration: 28
Duration: 21
Duration: 25
Duration: 17

If I run "SHOW profile FOR QUERY" with the 152ms time, both the "Updating" and "query end" values are abnormally high.

If I switch the table to MyISAM, the query duration is perfect, but I don't want table-locking. Does anyone have a guess as to what is making InnoDB act this way?


texts: Automating database restoration of SQL Server transaction log files on standby mode

texts:
I am wondering if I get help from this house on the following issue.
I have a standby database that should be restored with the set of transaction log files I get from the vendor's(eClincalWorks) sftp site daily.

The automation I am looking for is to do the following tasks:

1- Download the daily transaction files from the vender's sftp site and save to the local drive.
Note: the files in the sftp server are in a zip folder named as db_xxxx_today'sDate contains the daily transaction log files that are backed-up every 30 min at the production server.

Extracting the zipped folder into a different folder in the local drive
Scheduling restoration of the extracted-log files into the SQL database in the correct LSN order and in standby mode.

Please let me know if I need to explain more to my question and thanks in advance for any of your help on this matter!

Lilly.


texts: PostgreSQL - Truncate a table on cascade and reset all hierarchic sequences with 1

texts:
Is there any way to reset all the sequences of tables, when truncate a table on cascade.

I already read this post How to reset sequence in postgres and fill id column with new data?

ALTER SEQUENCE seq RESTART WITH 1;
UPDATE t SET idcolumn=nextval('seq');

It work only for one sequence, but my problem is to restart all the sequence of the truncated tables.

Consider when I use TRUNCATE sch.mytable CASCADE; It affect 3 related tables, which mean three sequences, Is there any solution to restart this sequence in one shot.


texts:
We have a SSAS database (SQL Server 2012 Enterprise) with 4 cubes in it. 1 of the cubes is significantly bigger than the other 3. We want to process the 3 smaller cubes at regular intervals during the day and update the large cube every night.

A really basic question, if I just process a selected cube, does that still process all changes in measure groups and dimensions that are included in that cube?

To put it another way, will the data in the cube that I process be exactly the same if I process just 1 cube or if I process the whole database?


texts:
I have an SSIS package targeted at SQL Server 2012.

I have it deployed into the Stored Packages in Integration Services and then have a SQL Server Agent job which executes it.

The first part of the package which moves data from a number of tables from a live database into a staging database execute successfully. I then have a data flow task which uses a Recordset Destination to collect a list of IDs of other records to move into the staging database.

When I execute the package manually by doing a Run Package from Integration Services (through SQL Server Management Studio) the package executes successfully, running the dataflow task which uses the recordset destination, however when run through the SQL Server Agent job I get the following error

  Message
  Executed as user: xxxx. Microsoft (R) SQL Server Execute Package Utility  Version 11.0.2100.60 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  09:51:49  Error: 2013-03-18 10:16:02.73     Code: 0xC000F427     Source: xxx_dataflowtask_xxx SSIS.Pipeline     Description: To run a SSIS package outside of SQL Server Data Tools you must install Recordset Destination of Integration Services or higher.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  09:51:49  Finished: 10:16:02  Elapsed:  1452.78 seconds.  The package execution failed.  The step failed.

does anyone know how to resolve this problem?


texts: what is "local block" in pg_stat_statements?

texts:
https://www.postgresql.org/docs/9.4/static/pgstatstatements.html

local_blks_hit  bigint      Total number of local block cache hits by the statement
local_blks_read bigint      Total number of local blocks read by the statement
local_blks_dirtied  bigint      Total number of local blocks dirtied by the statement
local_blks_written  bigint      Total number of local blocks written by the statement

Doc says "local block" and where does it point to??


texts: HA PostgreSQL installation using patroni and pgbouncer

texts:
I'm not having a problem yet, just searching for advise

I want to build installation of PostgreSQL on 3 nodes using patroni: master and 2 standbys (for failover only). There are PostgreSQL and pgbouncer on each node with same configuration. All applications connect to PostgreSQL via pgbouncer

If PostgreSQL becomes dead, patroni automatically promotes on of the standbys as new master and everything just works fine. But if pgbouncer becomes dead nothing will happen

Is there any good way to make patroni watch pgbouncer (like it is doing for PostgreSQL) or I shouldn't use patroni for this case? What should I use?

I'm really not sure that it is what I want (cause pgbouncer is pretty stable), but here is my considerations:
- If pgbouncer becomes dead, entire node becomes useless for applications. So from application point of view it is the same as PostgreSQL becomes dead
- I don't really care about type of failure, even if pgbouncer restart could fix everything, so standby promotions suits me well

How do I suppose to solve problems like this?


texts: What happens when you modify (reduce) a column's length?

texts:
Lets say I have two columns of type NUMBER (without precision, and scale) and VARCHAR(300). I saw that these columns are way too large for my data, so I want to modify them to NUMBER(11) and VARCHAR(10). So If I run this SQL statement:

ALTER TABLE FOO
    MODIFY(BAR NUMBER(10));

Will I be able to do that on nonempty column?
If so, what If there is one value greater than NUMBER(10), will oracle tell me about it?
Will column default values remain unchanged if previously defined?
Will column nullable option remain unchanged?
Will primary, foreign, unique key on that column remain unchanged?
Will constraints involving that columns remain unchanged?
Will indexes on that columns remain unchanged?

Is there any official documentation answering my questions?


texts: Posts, comments, replies, and likes database schema

texts:
I have a site where a users can comment on posts or reply to a comment. The user can also like replies or comments. Replies are not nested, so a reply doesn't have a reply. However, there is another field called reply_to within the reply table. Here's my current schema:

Comment
id
user (foreign key)
post (foreign key)
comment

Reply
id
user (foreign key)
comment (foreign key)
reply

CommentLike (Table that shows which user liked which comments)
id
comment (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

ReplyLike (Table that shows which user liked which replies)
id
reply (foreign key)
user (foreign key)
like (1 = likes, 0 = dislikes)

Does this seem like a good schema to use, or is there a better way to create this sort of structure?


texts: Why can the SA account no longer access the master database?

texts:
I'm somewhat stuck. We have a database server which was set up and configured by a third party. It's for a third party product so unfortunately we're stuck with the way they've configured it.

Their brilliant configuration includes using the sa account to make a number of connections. For about a week now we've had connection failures filling up all our event logs, two every ten seconds. I have managed to identify that it is the master database this login failure is for.

It's definitely the sa account accessing the master database. It is not an incorrect password (which is good, because we don't know the sa password - and neither does the vendor, it seems. Yay...) because we're getting error state 16 so I believe the sa account has some problems with the master DB.

It's connecting to everything else without problem - I can see the connections made to all other databases successfully. I have checked the sa account permissions and I really can't see anything wrong. I'm at the point of restoring the master database from a backup two weeks old and wanted to open this up to you awesome people out there in case any of you have come across this before and have any alternative suggestions...

Yours hopefully...

Summer

Edit: Aaron kindly took a look as per his answer so I was going to post up a screen print, but I've been on stackoverflow mostly, and only recently come to the dba site...therefore I don't have 10 reputation yet, and can't post an image.  However I've added it to stackoverflow so please check it out here.


texts: Lots of "FETCH API_CURSOR0000..." on sp_WhoIsActive ( SQL Server 2008 R2 )

texts:
I have a strange situation. Using sp_whoisactive I can see this:

Ok, with this query, I can see what is triggering ( does this word exists in english? ) it:

SELECT c.session_id, c.properties, c.creation_time, c.is_open, t.text
FROM sys.dm_exec_cursors (SPID) c --0 for all cursors running
CROSS APPLY sys.dm_exec_sql_text (c.sql_handle) t

the result:

it's a simple select. Why is this using fetch_cursor?

Also, I see a lot of "blank" sql_texts too. Does this has something with this "cursor"?

DBCC INPUTBUFFER (spid) shows me this:

there's this question  Here ( made by me ) but i don't know if this is the same thing.

EDIT1:

Using the query provided by kin, I see this:

EDIT2:

Using Activity Monitor, I can see this:

It is the most expensive query ( The first one is intentional, we know about it ).

And again, I would like to know, why this select * from... is the reason of FETCH CURSOR...

EDIT3:

This "select * from..." is running from another server ( via linked server ).

Well, Now i'm having problems to understand what @kin said.

This is the execution plan of the query ( running in the same server of the database):

this is now, the execution plan, running in the other server, via linked server:

Ok, Not a problem too. And NOW! the execution plan , via **activity monitor** ( the same select * from ):


texts: How to change directory of binlogs on Ubuntu 18.04

texts:
Current config:
[mysqld]
datadir=/data/mysql
log-bin=test

Updated config:
[mysqld]
datadir=/data/mysql
log-bin=/binlogdata/test
log-bin-index=/binlogdata/test.index

What I did was:

Stop mysql service
Update my.cnf
Move the current binlogs to destination path using:

mv /data/dir/test.* /binlogdata/

Update /etc/apparmor.d/usr.sbin.mysqld I added /binlogdata/** rw
Reload apparmor
I excecuted chown on binlogdata

chown -R mysql:mysql /binlogdata

Start mysql service

Error on /var/log/mysql/error.log is
mysqld got signal 11 ;


texts:
I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature.

The query I have below is what I've come up with. It's returning the two closest results with the difference in length and width. The difference returned could be positive or negative, though, negative of course being an issue because that would indicate that the returned size is too small for the user's dimensions.

The piece I'm looking for help on is how to exclude sizes that would be smaller than the user input.

Query

SELECT
    size_name,
    length,
    ABS(length - ' . $input->length . ') AS length_difference,
    width,
    ABS(width - ' . $input->width . ') AS width_difference
FROM
    SIZES
LIMIT
    2
ORDER BY
    length_difference

Results

{
    "size_name": "8",
    "length": 10.3,
    "length_difference": 0.15,
    "width": 3.8,
    "width_difference": 0.4
},
{
    "size_name": "9",
    "length": 10.6,
    "length_difference": 0.15,
    "width": 3.9,
    "width_difference": 0.3
}

UPDATE

I've been tweaking and realized this doesn't have to be as hard as I was making it. In the screenshot below I have something close. I'm using PHP so I'm thinking I can just filter out the negative values and from there I clearly have size 9. Does this seem like an effective solution?


texts:
Version: PostgreSQL 11.4 (Ubuntu 11.4-1.pgdg19.04+1).

Question: Is possible export to json file? Something like csv export.

COPY ( SQL )
TO stdout DELIMITER '"' csv;

Thanks.

Update: Or can I add escape character \ just for fields param_name and value for this?

string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",

Result is:

{name:"připojení",value:"1/2""},

I need this (use \ before " only for fields param_name and value:

{name:"připojení",value:"1/2\""},

Update2: Whole sql command added. How to implement function row_to_json? Just add row_to_json to every item in select I have? For example:

select row_to_json(string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params"),

...etc...

Whole sql.

with recursive
cte as(
select
category_id,
category_parent,
category_name::text,
category_id::text category_ids
from s_category as c
where category_parent = 0
union all
select
c.category_id,
c.category_parent,
concat(cte.category_name, ' > ', c.category_name),
concat(cte.category_ids, ':', c.category_id::text)
from s_category as c,cte
where cte.category_id = c.category_parent
)

select distinct
s_product.product_id as "itemID",
s_product.product_id as "itemGroupID",
product_shop_id as "productCode",
product_ean as "ean",
product_name as "title",
product_short_label as "description",
concat('https://eshop.unihobby.cz/',product_url,'/',s_product.product_id,'p/') as "link",
concat('https://eshop.unihobby.cz/bin/product/4/',filename) as "image",
price_tax as "price",
price_rec as "priceOriginal",
available_count as "available",
case
when available_count >= 5 then 'SKLADEM > 5 KS'
when available_count < 5 then 'SKLADEM < 5 KS'
when available_couNt = 0 then 'NENÍ SKLADEM'
end as "availability",
producer_name as "brand",
concat('Úvod > ',cte.category_name) as "category",
s_category.category_id as "categoryID",
cte.category_ids as "hierarchy",
string_agg(distinct '{name:"' || param_name || '",value:"' || value || '"}',',') as "params",
case when price_tax = price_rec then '' else 'Akce' end as "label"

from
s_product
left join s_cf_j_product_value on s_product.product_id = s_cf_j_product_value.product_id
left join s_product_image on s_product.product_id = s_product_image.product_id
left join s_pricelist_generated_lists on s_product.product_id = s_pricelist_generated_lists.product_id
left join s_producer on s_product.producer_id = s_producer.producer_id
left join s_category on s_product.category_id = s_category.category_id
left join cte on s_product.category_id = cte.category_id
left join s_cf_j_product_value as a on s_product.product_id = a.product_id
left join s_cf_value as v on a.value_id = v.value_id
left join s_cf_param as p on v.param_id = p.param_id

where
image_order = '0'
and s_category.category_name is not null
and product_active = 'y'
and s_pricelist_generated_lists.group_id = '10'
--and s_product.product_id = '133471'

group by
s_product.product_id,
s_product_image.filename,
s_category.category_id,
s_pricelist_generated_lists.price_tax,
s_pricelist_generated_lists.price_rec,
s_producer.producer_name,
cte.category_name,
cte.category_ids
;


texts: PostgreSQL WAL file database system identifier wrong

texts:
I'm having a problem restoring a copy of a PostgreSQL database to another server.

I have taken a backup using the pg_basebackup tool and can restore that but when I restore WAL logs from the archive I get this error

WAL file is from different database system: WAL file database system identifier is 6355383437015733568, pg_control database system identifier is 6366125487137379747

Though the source database reports it's identifier as   WAL file is from different database system: WAL file database system identifier is 6355383437015733568, pg_control database system identifier as 6366125487137379747

I am 100% certain the the WAL archive is from the correct source it just looks like the WAL files have the wrong id . any ideas how to resolve ?  a pg_xlogreset in the source db made no difference

Version 9.6 on Amazon Linux all fully patched

Thanks


texts:
We are observing high latency for some queries, digging further we found that every 5 minutes the queries take long time by examining the slow query logs.

We observed that

PURGE BINARY LOGS TO 'mysql-bin-changelog.041045';

Running every 5 mins and taking more than 1000ms. At the same time queries like

SET timestamp=1492673425; commit;

Also taking time and

SELECT @@session.tx_isolation;

We are using MySQL 5.7.11 on Amazon RDS. Any reason why PURGE BINARY LOGS will take longer and causing the other queries to also stuck at the same time?


texts: Should I defragment InnoDB table with fixed-length fields?

texts:
I am running MariaDB 10.2.13 on Linux (Debian).

I am setting up an InnoDB table that will record a lot of data but I will keep only the last one-hour rows. Thus, the number of rows will remain constant.

I should expect on this table:

many INSERTs
many UPDATEs
some DELETE (rows > 1 hour) from time to time

Example:

Table is defined with fixed-length fields only and some indexes.

CREATE TABLE `tbl_log` (
  `ip` int(4) unsigned NOT NULL,
  `date` datetime NOT NULL,
  `external_id` smallint(6) unsigned NOT NULL,
  `counter` smallint(6) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`ip`,`external_id`),
  KEY `external_id` (`external_id`),
  KEY `counter` (`counter`),
  KEY `date_idx` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Inserts (and updates, through on duplicate key) may look like this (ip and external_id will vary):

INSERT INTO tbl_log
  SET ip = INET_ATON('192.168.1.1'),
      date = now(),
      external_id = 123,
      counter = 0
  ON DUPLICATE KEY UPDATE counter=counter+1;

Finally, deleting old rows will be done with a query:

DELETE FROM tbl_log WHERE date < DATE_SUB(NOW(), INTERVAL 1 HOUR);

Question:

Will such a table fragment over time?

If so, I think I should defragment it. If necessary, I planned to run OPTIMIZE TABLE tbl_log; (with option innodb-defragment=1) right after delete...


texts:
A deadlock issue is encountered when adding column to an existing table, from my attempt to understand the graph, a linked server calling sp_columns_100_rowset is having an update lock on sysschobjs, which is very strange, any idea why is this happening?
Deadlock xml: https://pastebin.com/ssGrYZ7q


texts: Assign foreign keys only after populating database

texts:
I am porting a MongoDB database over to a PostgreSQL (relational) database. I would like to perform this task concurrently, and I have developed code with parallelism in mind, but dependencies between tables (foreign keys) are not allowing it. This happens when a row (containing a foreign key) is added, but the table it points to hasn't had that record (primary key) added, throwing the following error:

insert or update on table "observernodeoccurrence" violates foreign key constraint "observernodeoccurrence_canonical_id_fkey"
DETAIL:  Key (canonical_id)=(ba956ef88bcc366fa7d1d5fb696fe98d) is not present in table "canonical".

This is logical, since the record being added, which contains a FK, is pointing to a record from another table which hasn't been added yet.

Possible solution: I thought about a solution consisting of declaring the foreign keys as regular TEXT attributes, containing no dependencies to other tables. As soon as the port was completed, those attributes would be converted back to foreign keys. This way, every foreign key would have a record in the destiny table.

Is there a way to achieve this in Postgresql? If not, is there any other thing I can try to achieve parallelism in populating the database?


texts: How to store likes and comments of posts in database?

texts:
I am building a social networking website, so I wanted to know how the database structure for storing likes and comments of a post.
I have created a table post for storing posts but I want to know how to store "likes" and "comments" so that I can show who are the people who liked your post and what are the comments of the post.
Do I need to create a table for likes which stores user id of every user who liked the post and post_id as foreign key, but this may be very large table as it will create a new row for every user who liked the post. For example if one post has 1000 likes then it will create 1000 rows for the same post in "like" table. The same thing happens with comments as well.
Please suggest me the table structure for post, like and comments. Thanks!


texts: Geting rows from self-referencing single table using Postgres

texts:
I am trying to get rows from self-referencing single table in Postgres.

I got table A and I would like to get Table B. For given ProcID using Input_proc_Id I want to get another row until i get capture row 'YES'. So it's kind of loop that will append row to new table until it get 'Yes' in capture filed.

Is it a way to do it in single query or i need to create function loop? If so how to do it?

Setup code:

CREATE TABLE table_A AS
  SELECT 1 AS procid, 3 AS input_proc_id, false AS capture
  UNION ALL SELECT 2 AS procid, 4 AS input_proc_id, false AS capture
  UNION ALL SELECT 3 AS procid, 6 AS input_proc_id, false AS capture
  UNION ALL SELECT 4 AS procid, 6 AS input_proc_id, false AS capture
  UNION ALL SELECT 5 AS procid, 7 AS input_proc_id, false AS capture
  UNION ALL SELECT 6 AS procid, 8 AS input_proc_id, false AS capture
  UNION ALL SELECT 7 AS procid, 8 AS input_proc_id, false AS capture
  UNION ALL SELECT 8 AS procid, 9 AS input_proc_id, false AS capture
  UNION ALL SELECT 9 AS procid, 10 AS input_proc_id, false AS capture
  UNION ALL SELECT 10 AS procid, 12 AS input_proc_id, false AS capture
  UNION ALL SELECT 11 AS procid, 13 AS input_proc_id, false AS capture
  UNION ALL SELECT 12 AS procid, 14 AS input_proc_id, false AS capture
  UNION ALL SELECT 13 AS procid, 15 AS input_proc_id, false AS capture
  UNION ALL SELECT 14 AS procid, null AS input_proc_id, true AS capture
  UNION ALL SELECT 15 AS procid, null AS input_proc_id, true AS capture;


texts:
I've been searching online for a solution to my problem without any luck. I'm trying to connect to a remote server (~ 700 km of distance), but since I'm new in this Database administration world I'm afraid of messing this up. So I've been practicing with a VirtualBox machine (Windows 7) on my Ubuntu real Machine for almost a week and nothing seems to work.

This are the steps that I'm following to achieve the connection:

ON SERVER SIDE:

Start database: conn sys as sysdba.
Start LISTENER: lsrnctl start on cmd.
listener.ora:

listener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\xe)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\xe)
      (PROGRAM = extproc)
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

tnsnames.ora:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )

ON CLIENT SIDE:

tnsnames.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/xe)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
      (ADDRESS = (PROTOCOL = TCP)(HOST = ServerHostName)(PORT = 1521))
    )
  )

DEFAULT_SERVICE_LISTENER = (XE)

Start oracle client: sudo service start oracle-xe start
Try to connect: conn SYS/pass@HostName (tried with different users and host IP)

Please help!


texts:
When is the _id index used by MongoDB?

I could not find anything specific about it in Index Introduction, Analyze Query Performance, and Query Plans.

When sorting by _id at the end of the query via .sort({_id: -1}), .explain() explicitly shows the index use. It might be that it is implicitly used in every COLLSCAN operation, though.

So is it just a normal index that is only accessed when sorting/querying by _id, or does it have hidden uses not shown by .explain()?


texts:
I am trying to join two tables on the criteria that the value one column of table1 is between the values of two columns of table2.

Table 1

CREATE TABLE `values`(
  `id` INT,
  `name` VARCHAR(50),
  `num_addr` BIGINT UNSIGNED
);

Table 2

CREATE TABLE `ranges`(
  `id` INT,
  `range_name` VARCHAR(50),
  `range_start` BIGINT UNSIGNED,
  `range_end` BIGINT UNSIGNED,
  INDEX `idx_start` (`range_start` ASC),
  INDEX `idx_end` (`range_end` ASC),
  INDEX `idx_range` (`range_start` ASC, `range_end` ASC)
);

The query:

SELECT
  `v`.`name`,
  `v`.`num_addr`,
  `r`.`range_name`
FROM
  `values` `v`
  LEFT JOIN `ranges` `r` ON `v`.`num_addr` BETWEEN `range_start` AND `range_end`

An EXPLAIN EXTENDED for the query shows that no index is used, with the information 'Range checked for each record (index map: 0x7)'. This is a performance issue as the ranges table has over 500,000 rows and the query is time sensitive.

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, SIMPLE, r, ALL, idx_start,idx_end,idx_range, , , , 1, 100.00, Range checked for each record (index map: 0x7)

A FORCE INDEX ON JOIN actually makes things worse as the optimizer only sees the suggested index, but does not use it.

Is there a way to use an index on such joins?

Additional notes:

Changing the BETWEEN to value >= range_start AND value <= range_end does not change the execution plan.
Removing idx_start and idx_end indexes does not improve the situation.
Adding an index to num_addr does not affect the join execution.
The intervals (range_start, range_end) can overlap.
There are num_addr that are not in a range.
A good analogy is phone numbers: In the ranges table there would be a record: ('UK', 44000000000000, 44999999999999) and another ('UK Vodafone', 44700000000000, 44799999999999).


texts: Bacpac Import fails with error 'The Element or Annotation class SqlDatabaseOptions does not contain the Property class CatalogCollation'

texts:
I want to restore some production azure database on my local machine for development purposes.
So I exported the production database to a .bacpack file & downloaded that file on local.
Now I am trying to restore that file on my local machine using sqlpackage utility but it is throwing following error

  *** Error importing database:An error occurred during deployment plan
  generation. Deployment cannot continue. The Element or Annotation
  class SqlDatabaseOptions does not contain the Property class
  CatalogCollation.

Can someone please tell me what does this error means & how to fix it?

Thanks in advance.


texts:
We had a SQL Server crash and most of the data is unobtainable. I have found some old full backups and some differential backups in different servers.

Is there a way to tell which differential belongs to which full backup?


texts: Galera cluster problem after full crash with mariadb

texts:
I have a galera cluster with 2 mariadb nodes running. I know that this isn't best practise but I hadn't a chance to do it in another way.

I had a crash and needed to reinitiate the cluster on one node using galera_new_cluster.

The first node a is now running fine, is accessible and the cluster size shows 1 correctly.

But now when I try to start mariadb on the second node b I got the following:

    mariadb.service - MariaDB 10.3.13 database server
    Loaded: loaded (/usr/lib/systemd/system/mariadb.service; disabled; vendor preset: disabled)
    Drop-In: /etc/systemd/system/mariadb.service.d
           └─migrated-from-my.cnf-settings.conf
    Active: failed (Result: timeout) since Tue 2019-12-03 17:19:01 CET; 3h 51min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/

    Dec 03 17:17:31 node-b.my.domain systemd[1]: Starting MariaDB 10.3.13 database server...
    Dec 03 17:19:01 node-b.my.domain systemd[1]: mariadb.service start-pre operation timed out. Terminating.
    Dec 03 17:19:01 node-b.my.domain systemd[1]: Failed to start MariaDB 10.3.13 database server.
    Dec 03 17:19:01 node-b.my.domain systemd[1]: Unit mariadb.service entered failed state.
    Dec 03 17:19:01 node-b.my.domain systemd[1]: mariadb.service failed.

While this process is trying to start mariadb it fills up my /tmp very quickly.

I tried to enable error logging unsing this in server.cnf:

    log-error=/var/log/mariadb.log

nothing was logged, I even gave chmod 777 to the file, but still no luck.

I dont know what to do next.

the last times I needed to do galera_new_cluster the second node was starting and connecting to the cluster just fine.

here is the config of node a:

    [galera]
    # Mandatory settings
wsrep_on=ON
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://172.17.175.200,172.17.175.201"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='172.17.175.200'
wsrep_node_name='node-a'
wsrep_sst_method=rsync
wsrep_sst_auth=username:password

and node b:

wsrep_on=ON
binlog_format=ROW
default-storage-engine=innodb
innodb_autoinc_lock_mode=2
innodb_locks_unsafe_for_binlog=1
query_cache_size=0
query_cache_type=0
bind-address=0.0.0.0
datadir=/var/lib/mysql
innodb_log_file_size=100M
innodb_file_per_table
innodb_flush_log_at_trx_commit=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address="gcomm://172.17.175.200,172.17.175.201"
wsrep_cluster_name='galera_cluster'
wsrep_node_address='172.17.175.201'
wsrep_node_name='node-b'
wsrep_sst_method=rsync
wsrep_sst_auth=username:password

grastate.dat on node a:

version: 2.1
uuid:    da45dbcb-15b9-11e9-b1fc-e7c9d7353265
seqno:   -1
safe_to_bootstrap: 1

grastate.dat on node b:

version: 2.1
uuid:    00000000-0000-0000-0000-000000000000
seqno:   -1
safe_to_bootstrap: 0

I checked file permissions on /tmp already and they are fine.

thank you for any hints or possible next steps.


texts: Redgate SQL Compare vs Visual Studio 2010 Premium/Ultimate database project

texts:
I currently use the Visual Studio Professional Edition which has the database project as a project template, but some of its features are not available, for example Schema Compare tool. The schema comparison and database update scripts generation are only available in Visual Studio 2010 Premium/Ultimate versions.

But are the schema comparison and update scripts generation features in the Visual Studio as rich as those in Redgate SQL Compare tool? (I didn't use it either) I didn't manage to find the feature comparison list. Could anybody, who used both of them, help to make it clear?


texts: How to change initial extent of an existing partition

texts:
I'm having trouble with something that should be rather simple ... I'm sure I'm missing something obvious.

I'm trying to change the initial extent of an existing table partition.

For some reason, I have an existing table with initial extents of various sizes, and I'm trying to realign them as I'm just doing some reorg and moving them about (DEV environment).

I'm working on Oracle 11.2.0.3.0

I have a table, range partitioned. Residing in a locally managed tablespace.
It was originally system managed, however, I'm trying to create a new tablespace using uniform extents. I've done that, and am trying to MOVE the objects over, however, I have a couple (empty) partitions which seem to want to have multiple extents (initial 8M, despite uniform of 1M). (oddly some partitions are fine with an initial of 1M ..)

I tried:

alter table my_table modify default attributes storage ( initial 1M next 1M );

that ran, but didn't seem to do anything. So I tried this:

alter table my_table modify default attributes for partition part101 storage ( initial 1M );

but that throws an exception:

ORA-14264: table is not partitioned by Composite Range method

How do I alter the INITIAL extent of a (range) partition ?


texts: attach database - verify - detach from a script or a batch file

texts:
for a hundred good reasons .... and I promise that at least a few are good ... I find myself with almost 200 SQL server databases (mdf,ldf files) generated by different versions of sql server, most 2005.

I need to verify the integrity of each and follow up on the ones that fail.

after all that .... My question is:

Is it possible to attach - verify - detach each database using a batch file or a simple script?

I've used sqlcmd for similar jobs, but with this volume I thought I'd see if there was a simpler way.


texts: Optimize slave .ibd tables, where slave has innodb_file_per_table and master does not

texts:
We are trying to reclaim file space on our replication server. The space we want to reduce are .ibd files on the slave. We have a simple Master -> Slave (read only) replication setup, for some reason, we have innodb_file_per_table set up on the slave but not the master. So on the slave we have very large .ibd files, but not on the slave, of tables that have already been purged, but have not reduced in size.

Is there a way we can reduce those .ibdfiles on the slave with out dropping the db and re importing it?

Would running optimize table on the master reduce them?

Thanks


texts: SQL Server Database grew too much too fast

texts:
I have an issue with one database. The issue is as follows:
The database is being running on SQL Server 2000 Standard for the last 6 years in Full Recovery mode.

In the beginning of this summer the database was about 5GB.

Since then, the only thing we did beyond normal usage is some extensive deletions.

This week, there was a problem with the PC and I was forced to do a backup and restore it on an SQL Server 2008R2 Express and set the database in 2008 mode.

The backup file was about 1GB. When I restored it, the MDF was 9GB of size! I checked the old MDF and it was of the same size.

I checked the size of the tables and they cannot reach the 9GB reported!

I did a shrink but the size did not change.

Any clues or where to check?

Is there a chance that the Full Recovery, can affect the size of the MDF files?

I am thinking of setinng the recovery model to Simple, back-it-up and restore it. Is it going to make a difference? Can I do it on a live database?

Thanx in advance!

UPDATE:The initial size of the database is 1306 MB

UPDATE2 sp_spaceused: Database Size=8646.88 MB  Unallocated Space= 0.00 MB

reserved=1336984 KB  data=1020376 KB  index_size=210408 KB unused=106200 KB


texts:
In SQL Server 2012,

Does a merge statement like this :

WHEN MATCHED THEN
update SET A.len = B.len

updates a row even if A.len equals to B.len ?

Or does the SQL SERVER knows to ignore this situations ?

If not, would it be better to add the matching condition the following :

WHEN MATCHED AND A.len <> B.len THEN
update SET A.len = B.len

Is there any difference in this on other Sql servers ?

Thanks,


texts: I (student) was encouraged to use SYSDBA and I broke my Oracle database. How do I fix it?

texts:
It all started when I tried to run catqm.sql as SYSDBA yesterday:
SQL> @?/rdbms/admin/catqm.sql xdb XML_DATA TEMP NO

Afterwards, I'd get this error whenever trying to execute DDL:

ORA-04098: trigger 'SYS.XDB_PI_TRIG' is invalid and failed
re-validation

After troubleshooting for a while, now I get this error whenever trying to execute DDL:

Error report - ORA-04098: trigger 'SYS.XDB_INSTALLATION_TRIGGER' is
invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
found to be invalid.  This also means that compilation/authorization
failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
disable the trigger, or drop the trigger.

I've been troubleshooting for a while, but have only made things worse. This is quite pressing, so any help is appreciated. This stuff is mostly over my head if it wasn't obvious, so talk to me like the idiot that I am =)
I'm guessing I can't just rollback everything from the last 2 days...


texts: pg_dump custom format between postgres versions

texts:
I was wondering how stable the custom pg_dump format is between Postgres versions.
Can I for example restore a custom format dump created with Postgres 8.x in postgres 9.x?

Background:

I am building a virtual appliance which contains all configuration in a PostgreSQL DB. I have to decide whether I use the SQL format or the custom format for the backup. As the appliance evolves over time (couple of years) I will use newer PostgreSQL versions and still need to be able to restore old backups without a lot of trouble.


texts: How bad is a tempdb drive failure (with multiple tempdb files on different drives)?

texts: I'm getting a new 16 core server for my SQL Server 2005 database. I would like to create 16 files for my temp DB, and put each one on a separate drive. If one of these drives fails, can SQL Server continue to function using the other 15 files?


texts: Amazon RDS CPU Usage keep increasing without any long running process in Show Processlist

texts:
We are using Amazon RDS(db.m2.2xlarge), CPU usage is suddenly increasing gradually even after  we reboot the DB Server. This is happening just past 2 days and we didn't give any new build recently.

I tried to debug using "SHOW PROCESSLIST" and identified long running process. I killed it but still CPU % is not reduced. Actually we just have 10 process at a time


texts: Any DBMS with table structures and data defined separately?

texts:
I'm working on an application which is  being used in multi-tenant mode, and have created a MySQL database instance, where each tenant has its own   schema. The problem I'm facing is, because the application is constantly changing, due to addition of features, etc., the database tables are also changing very often. This means, I have to keep changing the table structures in all schemas for every single change.

Is there any database management system (DBMS) where table structures are defined once, and each instance only has the table data?


texts: Order by taking less time than max() in oracle

texts:
I am using oracle db and I am running 2 queries to find the maximum id from an indexed column.

1st query -

select max(id)
from tableName

Explain Plan:

Plan hash value: 3791258597

ID
Operation
Name
Rows
Bytes
Cost(%CPU)
Time

0
 SELECT STATEMENT

1
6
1(0)
00:00:01

1
 SORT AGGREGATE

1
6

2
  INDEX FULL SCAN (MIN/MAX)
Index Name
1
6
1(0)
00:00:01

It takes 2.839 seconds to execute on a table having nearly 80000000 rows.

2nd query -

select id
from (
   select id
   from tableName
   order by id desc)
where rownum < 2

Explain Plan:

Plan hash value: 1514191988

ID
Operation
Name
Rows
Bytes
Cost(%CPU)
Time

0
 SELECT STATEMENT

1
13
27876 (1)
00:05:35

* 1
 COUNT STOPKEY

2
VIEW

84M
1046M
27876 (1)
00:05:35

3
 INDEX FULL SCAN
INDEX Name
84M
482M
27876 (1)
00:05:35

It takes 2.6 seconds to execute on the same table.

When I see the explain plan of the 1st query, I see that it is using SORT(AGGREGATE). Now I don't understand is that since the column is indexed, it should directly return the max value instead of using SORT(AGGREGATE) function.

Why max() is taking more time than order by?


texts: updating same column as in the select query

texts:
When I try to do this, I get an error saying I cannot use the same table in where select as the column that I am updating.

UPDATE table_name
   SET quantity = 19
 WHERE  productID = 148357
               AND productCost IS NOT NULL
               AND exampleDate1 >=
                      (SELECT min(exampleDate1)
                         FROM table_name
                        WHERE exampleDate1 >=
                                 (SELECT min(exampleDate)
                                    FROM table_name2
                                   WHERE description LIKE "We are true"))


texts:
I'm trying to import a CSV file into a table in Postgres, and it's bombing out on the first date field. The dates are in the format dd/mm/yyyy and there isn't a date in the field in question in the CSV.

As such I'm using the following:

set datestyle = 'ISO,DMY';

\copy companies_house_snapshot
FROM 'BasicCompanyDataAsOneFile-2017-04-01.csv'
WITH DELIMITER ',' CSV HEADER NULL AS '';

Which I thought should be sufficient, the error message is:

ERROR:  invalid input syntax for type date: ""
CONTEXT:  COPY companies_house_snapshot, line 2, column dissolution_date: ""

Where dissolution_date is nullable and in the CSV as ""

I'm using PostgreSQL 9.5.6 on Ubuntu 16.04

Where am I going wrong?

If I use '""' as NULL, I get:

ERROR:  CSV quote character must not appear in the NULL specification


texts:
i'm trying to generate a dump from my database but when i try to load the dump file it gives me an error. What should i do ?
My MySQL ( loader) version is mysql "Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1" my MySQL(dumper) version is "mysqldump  Ver 10.11 Distrib 5.0.51a, for debian-linux-gnu (x86_64)".

The error is:
ERROR 1166 (42000) at line 1572: Incorrect column name 'DATEDIFF(current_date,DATE_ADD(created_at,INTERVAL (updated_at*30) DAY))'

head  file:

$ head dump.sql

> -- MySQL dump 10.13  Distrib 5.1.34, for apple-darwin9.5.0 (i386)
> --
> -- Host: localhost    Database: moov3_production
> -- ------------------------------------------------------
> -- Server version 5.0.51a-24+lenny2


texts:
I have three Database servers, running PostgreSQL 9.2:

  master01 (Own data center USA)

  slave01 (Own data center USA)

  slave02 (AWS Australia)

Both slaves use streaming replication + wal_files (delivered by master01 via ssh).

The postgres process on the slave02 server has been recently stopped, due a maintenance on the slave02's postgres disk volume(/var/lib/pgsql).

Postgres is running on: /var/lib/pgsql

Wal files are shipped to this volume: /var/lib/walfiles

Once the volume was restored, I started postgres and got this log:

cp: cannot stat '/var/lib/walfiles/0000000500008AA800000069': No such file or directory
cp: cannot stat '/var/lib/walfiles/0000000500008AA80000006A': No such file or directory
consistent recovery state reached at 8AA8/8AD22F70
record with zero length at 8AA8/8AD22F70
database system is ready to accept read only connections
streaming replication successfully connected to primary

I have a very large table, that is constantly updated. I ran this query on slave01 and slave02 server:

select max(id) from ids_table;

SLAVE01:

   max
----------
 45148099
(1 row)

SLAVE02:

   max
----------
 45148095
(1 row)

Can you see the results are different? And that shouldn't be different!

Question:

How can I check the data between servers? How can I see if there is something wrong with slave02 due to those No such file or directory errors?

UPDATE 1:

As far as I could see, checksum is only available on Postgres 9.3+ ? So can't use that!

UPDATE 2: Anwsering Sahap Asci's questions

On the master:

synchronous_commit = on
#synchronous_standby_names = ''


texts:
I have 2 MyISAM tables:

table: search_hit with primary key: search_hit_id, and foreign key: search_id (150 million rows and growing with constant inserts)
table: search with primary key: search_id (about 30,000 rows and growing)

search_hit table has a number of indices,including search_id, but this query is excruciatingly slow:

select sq.search_id, count(ssh.search_hit_id)
from search sq
inner join search_hit ssh
use index(by_search_id) on sq.search_id = ssh.search_id
where sq.search_id >= 47000
group by sq.search_id

here is the explain results:

1, 'SIMPLE', 'sq', 'range', 'PRIMARY', 'PRIMARY', '4', '', 202, 'Using where; Using index'
1, 'SIMPLE', 'ssh', 'ref', 'by_search_id', 'by_search_id', '4', 'lims.sq.search_id', 3358, ''

query returns with status like:
/* 0 rows affected, 1,575 rows found. Duration for 1 query: 3.198 sec. (+ 5.975 sec. network) */

Our server setup MySQL 5.0.45 on 64 bit linux with 8GB dual core (hyperthreaded quancore).
Would dropping the index, optimize table then re-create the index help here, or anything?

Thanks

David


texts: Count children of a parent in recursive CTE

texts:
I use this query to find comments by a parent id:

WITH RECURSIVE cte (id, content, path, parent_id, depth)  AS (
    SELECT  id,
        content,
        array[id] AS path,
        parent_id,
        1 AS depth
    FROM    comment
    WHERE   id = 1

    UNION ALL

    SELECT  comment.id,
        comment.content,
        cte.path || comment.id,
        comment.parent_id,
        cte.depth + 1 AS depth
    FROM    comment
    JOIN cte ON comment.parent_id = cte.id
    WHERE depth < 3
    )
    SELECT id, content, path, parent_id, depth FROM cte
ORDER BY path LIMIT 200;

It works well except I'm limiting the depth to 3. How can I find out if the current row has more children and if so get the count of them so I can display the number, ex. "Load X more".

What is the best way to go about this?

In this fiddle, id 4 and 5 have children, so I want to count them.


texts: How are GTIDs used to determine what transaction should be executed?

texts:
I use MariaDB 10.14 on servers M1, S2, S3.
M1 is active master, and servers S2 and S3 replicate from M1.
S3 also replicates from S2.
All servers have GTID domain ID=1
then M1.server_id=100, S2.server_id=2, S3.server_id=3
Since S3 replicates from S2, when I execute a command on S2, I expect it to propagate to S3.
So e.g. I created a table on S2 and I expected this table to show in S3. But it does not!
99.99% of GTID transactions have ID 1-100-XXXXXXXXX. Then when I execute command on S2, its 1-2-XXXXXXXX. This makes me wonder - does the transaction 1-2-XXXXXXXXXXX get omitted because it is "lower" than all the other 1-100-XXXXXXXXXX transactions?
I would like to convert in the future S2 into M2, and have a standby master. S3 would still replicate from both.
My second question is: should I use different GTID domain for the two masters? And what GTID domain should be used on S3, since its sourced from both masters. You can assume I will always write ONLY to one master, never the 2 will be used simultaneously.
Thanks!


texts: Code creates different plan when ran ad-hoc vs. in a stored procedure

texts:
I have a delete statement that is using a bad plan when run inside a stored procedure, but is choosing a much better plan when run ad-hoc.

I have rebuilt all the indexes for the tables used by the query and dropped all of the caches. The optimizer still chooses the wrong plan for the stored procedure.

I'd like to know why the optimizer is using a different execution plan for the stored procedure versus the ad-hoc SQL.

UPDATE: I guess it must have been the parameters after all- when I ran the ad-hoc code with the variable hardcoded, I can get the "bad" plan with the right value (it is a date, values that are a year old seem to generate the "good" plan). Now on to trying to force the "good" plan on the proc by using a query hint.

SOLUTION: I ended up getting the plan I wanted by using the OPTIMIZE FOR UNKNOWN hint.


texts:
I'm at loss with this. What would be the simples way to achieve this?

I have "Customers" table like this:

USER_ID| NAME    | J_ID   |K_ID    |Y_ID
-----------------------------------------
    1  |  CUST1  |  A     |  1     |  AB
    2  |  CUST2  |  B     |  2     |  NULL
    3  |  CUST3  |  C     |  3     |  EF
    4  |  CUST4  |  NULL  |  NULL  |  GH
    5  |  CUST5  |  E     |  5     |  IJ

Then "R_ID" table like this:

   R_ID  | Y_ID
------------------
    1A9  |  AB
    2B8  |  CD
    3C7  |  EF
    4D6  |  GH
    5E5  |  IJ

Then "JK_ID" table like this:

   R_ID  | J_ID | K_ID
-----------------------
    1A9  |  A  |  1
    2B8  |  B  |  2
    3C7  |  C  |  3
    4D6  |  D  |  4
    5E5  |  E  |  5

This is what I would like to get:

USER_ID| NAME    | J_ID   |K_ID    |Y_ID
-----------------------------------------
    1  |  CUST1  |  A     |  1     |  AB
    2  |  CUST2  |  B     |  2     |  CD
    3  |  CUST3  |  C     |  3     |  EF
    4  |  CUST4  |  D     |  4     |  GH
    5  |  CUST5  |  E     |  5     |  IJ

So I need to get the missing values from R_ID or JK_ID tables by joining those. But what is the simplest way to get that result, so that the nulls are replaced by the values from joined tables?


texts:
I want to enable query_cache_type and change its value to DEMAND.
When I am doing it via terminal its giving this error:

  ERROR 1651 (HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

The path for my.cnf is /usr/local/mysql/my.cnf.


texts: restoring SQL Server from backup into a database with different database name?

texts:
I have been provided SQL Server backups to restore but they have to be restored into new databases with different names on same server.

To explain it, say I have database "MyDB" and its backup "MyDB_full.bac" has been provided. I want to restore it into "MyDB_new".

What I have tried ?

I have tried to restore  it in SSMS but was unsuccessful, since I m getting error message :

  the backup set holds a backup of a database other than the existing

Kindly note I don't want to lose basic database "MyDB".


texts: Is there an alternative to "Create or replace procedure" in MySQL?

texts:
Is there an version of "create or replace procedure" for MySQL? I can't seem to do this or script the dropping of the procedure if exists before recompiling without getting an error message that the stored procedure exists.

DELIMITER $$

-- would love to be able to drop procedure if exists db.sp_tmp_90days;
-- or use "create or replace"

create procedure db.sp_tmp_90days()

BEGIN
drop table db.tmp_90days;

create table db.tmp_90days (
    user_name varchar(128),
    first_name varchar(50),
    last_name varchar(50),
    system varchar(10),
    last_login datetime
);

alter table db.tmp_90days add index idx_user_name(user_name);
alter table db.tmp_90days add index idx_system(system);
alter table db.tmp_90days add index idx_last_login(last_login);

insert into db.tmp_90days (user_name, first_name, last_name, system, last_login)
    SELECT
        [...]
END $$


texts: SQL server 2016 SP1 CU4 issues, upgraded from SQL Server 2016 RTM CU1

texts:
Mirrored Servers (primary and secondary) running fine on SQL Server 2016 RTM CU1.. After the upgrade to SQL Server 2016 SP1 CU4 both servers OS became unresponsive after 23-25 hours, bizarrely SQL Server is still running.

Nothing writes to event log when windows becomes unresponsive. The only odd thing I can see are the log backups which run every 30 mins which stop just before this happens and don't resume. However diff and full continue. Also spotted a couple times a long running query after/during log backup.

Process Full Command: (@P1 nchar(86))RESTORE LABELONLY FROM DISK=@P1

Has anyone seen this issue with SP1 CU4? If so any fix?

Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64)
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: )


texts: Using MySQL Workbench 8 on RHEL7 gui - When I highlight text in the SQL editor it turns black

texts:
I'm assuming this is a config issue in MySQL Workbench or in RHEL.
How can I change this to a readable color?


texts:
How does Postgresql UPDATE work internally?

In some questions on SO/DBA I found that technically it works like DELETE/INSERT though in the docs it is only said about partitioned tables:

  In the case of a partitioned table, updating a row might cause it to no longer satisfy the partition constraint of the containing partition. In that case, if there is some other partition in the partition tree for which this row satisfies its partition constraint, then the row is moved to that partition. If there is no such partition, an error will occur. Behind the scenes, the row movement is actually a DELETE and INSERT operation.


texts:
One of my company's SQL database generates a lot of deadlocks per day recently (someday >100)

I'm trying to troubleshoot the causes of these deadlocks, i noticed that many deadlocks occur at the same tables, I'm a newbie in Database Administrator so i don't know what is the best way to do some statistics on the deadlock reports, like i want to calculate and visualize things like what tables involed, how manytimes, what time the deadlocks usually happen.... so i can report to my boss, is there an easy way to import the all the deadlock xml columns to something like microsoft excel?

I attached here the deadlock reports (.xel file)

Deadlocks_reports

Sorry for english is not my first language.

Thank you for your time,


texts:
I created a function that accepts a start and end date, with the end date being optional. I then wrote a CASE in the filter to use the start date if no end date is passed.

CASE WHEN @dateEnd IS NULL
    THEN @dateStart
    ELSE @dateEnd
END

When I call the function for the most recent month of the data:

SELECT * FROM theFunction ('2013-06-01', NULL)

... the query hangs. If I specify the end date:

SELECT * FROM theFunction ('2013-06-01', '2013-06-01')

... the result is returned normally. I took the code out of the function and ran it fine inside a query window. I can't duplicate the issue the fiddle either. A query like:

SELECT * FROM theFunction ('2013-04-01', '2013-06-01')

... also works fine.

Is there anything in the query (below) that could be causing the function to hang when a NULL is passed for the end date?

SQL Fiddle

Execution plan for SELECT * FROM theFunction ('2013-06-01', '2013-06-01')
Estimated plan for SELECT * FROM theFunction ('2013-06-01', NULL)


texts: Counting occurrence of words in table is slow

texts:
Consider these simplified tables:

CREATE TABLE dbo.words
(
    id bigint NOT NULL IDENTITY (1, 1),
    word varchar(32) NOT NULL,
    hits int NULL
)

CREATE TABLE dbo.items
(
    id bigint NOT NULL IDENTITY (1, 1),
    body varchar(256) NOT NULL,
)

The words table holds about 9000 records, each holding a single word ('phone','sofa','house','dog', ...)
The items table holds around 12000 records, each with a body text of no more then 256 characters.

Now, I need to update the words table, counting how many records there are in the items table that hold (at least once) the text in the word field. I need to account for partial words, so all these 4 records should be counted for the word dog:

'This is my dog'
'I really like the movie dogma'
'my cousin has sheepdogs'
'dog dog dog doggerdy dog dog'

That last example should count as just one record (contains the term 'dog' at least once).

I can use this query:

UPDATE dbo.words
SET hits = (SELECT COUNT(*) FROM dbo.items WHERE body like '%' + word + '%')

But, this is extremely slow, this will take over 10 minutes to complete on the not to heavy server I have for it.

AFAIK indexes won't help, I'm doing LIKE searches.
I also think full-text won't help me, since I'm looking for words starting, ending or containing my search term. I could be wrong here.

Any advise on how to speed this up?


texts: My company are looking to bring in Redgate's SQL Clone to deploy live databases into other environments. I wanted to see if running CheckDB on these clones was a viable option instead of restoring a database to a non production environment or running checkdb in production.


texts: SQL Server : CRUD Editor that will track data changes

texts:
I'm looking for a tool that allows CRUD changes to SQL Server data. Our company currently utilizes a product from Pro Data called DBU (database utility) to make IBM DB2 / AS400 changes. This tool tracks CRUD activity, including before and after data changes. Then it produces a log that can be reviewed for audit purposes.

I tried several tools that track object level changes on the server, including SQL Server built-in tracking, however this is not the desired affect. We would like to expose a utility to a group of users and track only that particular activity.

Any suggestions or recommendations?


texts: Is it possible to redirect queries to another server?

texts:
I want to set up a server that redirects queries. It would receive incoming SQL Server traffic, redirect to the actual SQL Server, and return results back to the client.

Is this possible? What are my options?


texts: Different default innodb_buffer_pool_size on Windows with MySQL 8.0.18

texts:
I have installed MySQL 8.0.18 Community with the same configuration (mysql-installer-community-8.0.18.0.msi installer, custom install with server + workbench, standalone server, legacy authentication, rest are the defaults )  on multiple Windows environments with surprisingly varying results.

3 out of 5 of these machines ran MySQL insanely slow when I was inserting a few million lines into the DB

Task Manger said that the disk activity time is 100% but barely moved any data (50-500 KB/s for half an hour)
I went into MySQL Workbench, noted that the data graphs on the Server Status window are not working,
the InnoDB Disk Writes section on the Dashboard says that the writing speed is about 40 KB/s.

Then I went to check the my.ini file and found this nonsense:

  innodb_buffer_pool_size=8M

which was the default setting for some odd reason.

The other two machines had this line commented out in the ini file so they were working properly, MySQL was behaving as expected.

The bad environments:

Desktop machine, Windows 10, 16 GB RAM 7th gen i5 CPU with 4 cores. MySQL was never installed on it before.
Physical machine, Windows Server 2016, 44 GB RAM, some beefy Xeon CPU (don't remember the exact model but it was not bad), MySQL was never installed before
VM (on the laptop described in good #2) with 16 GB ram and 6 vCPUs. MySQL was never installed before.

The good environments:

Desktop machine, Windows 10, 16 GB RAM, 4th gen I5 CPU with 4 cores. MySQL 8.0.17 was installed previously, it was working properly. I did a clean install to 8.0.18 and the buffer pool size value was commented out
Laptop, Windows 10, 32 GB RAM, 8th gen I7 CPU with 4 cores. Same story with MySQL.

I could not find anything in the MySQL documentation or on the web describing this behavior. I mean innodb_buffer_pool_size=8M is laughable, I see NO reason why would this be the default value

Does anyone have any idea why this would happen?


texts: Optimal query for comparing table column vs. summary column

texts:
What is the most efficient way to stack a field on one table with a summary field summarizing child data in another table?

For example, I have an account table and a task table. Below is the query I'm currently executing to compare a stamped value on the account table to actual counts from the task table.

SELECT
    account.sfid,
    account.lastactivitydate,
    account.sales_activities_last_30_days__c AS expectedcount,
    (
        SELECT COUNT(*)
        FROM salesforce.task
        WHERE task.accountid = account.sfid AND
              task.activitydate >= CURRENT_DATE - INTERVAL '60 days'
    ) AS actualcount
FROM salesforce.account
;

This is a naive attempt to get the data I want, and I'd appreciate any suggestions on optimizing this query to get results like what's shown in the table below.

sfid lastactivitydate expectedcount actualcount
---- ---------------- ------------- -----------
abc  2019-11-30       3             3
def  2019-11-27       5             4


texts: I'm a beginner, studying Data modelling as part of my course. I'm having trouble understanding Normalisation. How can i improve?

texts:
It was difficult to frame the title given the word limit, so apologies for that.

Currently I'm studying DBA and Data modelling as a part of my ICT Apprenticeship course in a Web Development team. I'm kind of grappling with understanding Normalisation. I understand the concept, of eliminating redundant data, stopping anomalies, using keys and relationships, but in practice I'm struggling. In an effort to help me understand, I've tried to make and normalise a small table of information:

My UNF table:

My attempt at normalising the table to 3NF:

So, questions. I suppose straight away, from an expert, am I doing this right? Or am i completely goofing up? Or am i settling in the middle?

Right now I'm worried that, in this example, the 'Assignments' table has Employee ID as a Foreign Key, meaning Porject ID would have to be the Primary. But that seems counter intuitive seeing as the 'Projects' table should be using Project ID as the primary key. And I'm not quite sure how i should proceed in a situation like this.

Apologies if this question is too rookie/generally not appropriate for the forum. I've had some trouble finding basic resources that cover this material.

Many thanks.


texts: Proper way to create table friends in single statement

texts:
I have table users

CREATE TABLE IF NOT EXISTS users(
    id int AUTO_INCREMENT PRIMARY KEY,
    user_id INTEGER NOT NULL UNIQUE,
    goal VARCHAR(255) DEFAULT NULL,
    age INTEGER DEFAULT NULL,
    gender VARCHAR(255) DEFAULT NULL,
    country VARCHAR(255) DEFAULT NULL,
    city VARCHAR(255) DEFAULT NULL,
    comment VARCHAR(255) DEFAULT NULL)

I want to create table friends. One key feature that is every user by default friend for itself.

This is my current statement

create table friends (user_id int,  friend_id int, FOREIGN KEY (user_id) REFERENCES users (user_id), foreign key (friend_id) references users (user_id)) as select user_id from users;

update friends set shown_id = user_id;

I think it looks pretty kludgy, creating a table and changing it immediately, may be there a better way to implement it?

Another important note: the database loses connection after creating the friends table.

create table friends (user_id int,  friend_id int,
        FOREIGN KEY (user_id) REFERENCES users (user_id),
        foreign key (friend_id) references users (user_id)
) as select user_id from users;
ERROR: 2006: MySQL server has gone away
The global session got disconnected..
Attempting to reconnect to 'mysqlx://root@localhost:33060/test_db'....
The global session was successfully reconnected.


texts: Postgres query cache analyzing table basis?

texts:
The PostgreSQL application I'm currently on is solely based on functions. Every request from the front end maps to a PostgreSQL function call. Many of these functions call other functions. Like: web application server calls function A, function A calls function B, function C queries data from table D and E and so on.
Now, I'm running into big performance issues. While anyway trying to optimize the functions themselves, I'm wondering if query caching might help me.

The problem is: The cache mechanism should analyze which tables are actually concerned by a certain function call and thus refresh or invalidate the cache entry if one of these tables is updated.

Let me give you an example. Consider functions like this:

CREATE FUNCTION public.pets_get_all(user_id integer)
$BODY$
SELECT dogs_get_all($1) AS dogs
$BODY$ LANGUAGE sql IMMUTABLE;

The dependent functions being

CREATE FUNCTION public.dogs_get_all(user_id integer)
$BODY$
SELECT id, name FROM dog WHERE user_may_read_dog($1,dog_id)
$BODY$ LANGUAGE sql IMMUTABLE;

CREATE FUNCTION public.user_may_read_dog(user_id integer, dog_id integer)
$BODY$
SELECT owner_id = $1 FROM dogs WHERE dog_id = $2
$BODY$ LANGUAGE sql IMMUTABLE;

Let's say, for the first time pets_get_all is executed with 1337 as the parameter, the query works like normal and it returns.

+----------+-----------+
| ID       | Name      |
+----------+-----------+
| 1        | Bella     |
| 2        | Max       |
+----------+-----------+

So, for the next time pets_get_all(1337) is executed, the query will be fetched from the cache, the inner functions won't be executed and thus, it will be pretty much faster.
However, when the dog with the id 2 is updated, the cache entry will be updated too (taking some time of course). So, the cache will be always up to date. Of course, changes in the dog with the id 3 will not affect the cache entry for pets_get_all(1337)`, since the user the id 1337 may not read dog 3.

Does such a thing exist?

I had a rough look into "PostgreSQL Query Cache" ( https://groups.google.com/forum/#!forum/pqc-dev ) However, it seems outdated and the expiration of cache entries is only time-based (which is not what I want).


texts:
I have a problem regarding SUBSTRING_INDEX.

As the image shows, I can get the city by

SELECT SUBSTRING_INDEX(location,'-',1)) FROM my_table

However for the state_country column, I need the value which is after the '-'.

Is there a way to get that value and add it to the state_country column?

Thanks in advance.


texts:
Background: I’m designing a 2 node SQL 2019 availability groups installation hosted in VMs.
We would have limited (single node) usage of PolyBase.  The Microsoft Docs don’t reference installation for any HA architectures. It only references how to create a PolyBase cluster. I have reviewed the Microsoft documentation, but I am unclear about the Polybase cluster vs. Windows cluster/ availability group behavior.

Question:
How to install PolyBase in an availability groups architecture for high availability?

Goals:
The goal is that PolyBase queries would still execute post failover event (without manual intervention). The decision I face is, installing 2 PolyBase head nodes (1 Polybase head node per HA node) with my current understanding.

I'm looking for any resources, articles, or advice for this situation. Specifically, how to install PolyBase in an availability groups for high availability?


texts: Is a query inside mysql IF clause allowed?

texts:
DELIMITER $$

CREATE PROCEDURE `InsertUser`(IN passwd VARCHAR(100), IN userType VARCHAR(20), IN mobileNo VARCHAR(20), IN refId BIGINT(20), IN companyName VARCHAR(100), IN companyMail VARCHAR(200), IN status CHAR(1)  )
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
DECLARE c VARCHAR(20);
Select COUNT(*) from `user` into c where `mobile_no` = mobileNo;

IF c > 0
THEN

    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Mobile Number Exists';
ELSE

    INSERT INTO `user`
        (
        `password` ,
    `user_type` ,
        `mobile_no` ,
        `reference_id` ,
        `company_name` ,
        `company_mail_id` ,
        `status`
        )

    VALUES
        (
        pass ,
        usertype ,
        mobileno ,
        refid ,
        companyName ,
        companyMail ,
        status
        );
END;
END$$

DELIMITER ;

Can anyone point out the error? It might be something very small, but I've tried a lot, and I'm just not able to make this run.
Documentation
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 'where mobile_no = mobileNo;
IF c > 0
THEN

    SIGNAL SQLSTATE '' at line 7


texts: SQL Server Isnull Returning 1900-01-01 when field is null

texts:
The following piece of code is returning 1900-01-01 when the DOB field is null. I wanted (and expected) it to return an empty string ('') but it's not. How should I proceed to get my desired results?

isnull(convert(date,DOB,1),'')


texts: Enabling TLSv1 in postgres12 / openssl1.1.1f

texts:
After upgrading a server to postgres12 / OpenSSL 1.1.1f (Ubuntu 20.04) I got tlsv1 alert protocol version error messages from a client using an old openssl 1.0.1 library supporting only tlsv1.
I thought setting  ssl_min_protocol_version = 'TLSv1' in postgresql.conf should enable the protocol allowing that client to connect. However it does not. I used nmap to check for supported protocols and found only one section with the TLSv1.2 ciphers:
➜  ~ nmap --script ssl-enum-ciphers -p 5432 127.0.0.1
Starting Nmap 7.80 ( https://nmap.org ) at 2020-09-23 22:28 CEST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000064s latency).

PORT     STATE SERVICE
5432/tcp open  postgresql
| ssl-enum-ciphers:
|   TLSv1.2:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (secp256r1) - A
|       TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (dh 2048) - A
<snip>

I used this SE answer Ubuntu 20.04 - how to set lower SSL security level? to change the system default MinProtocol setting in openssl.cnf to TLSv1 and postgres picked up this setting (also the openssl1.0.1 client could connect with this setting):
➜  ~ sudo service postgresql restart
➜  ~ nmap --script ssl-enum-ciphers -p 5432 127.0.0.1
Starting Nmap 7.80 ( https://nmap.org ) at 2020-09-23 22:31 CEST
Nmap scan report for localhost (127.0.0.1)
Host is up (0.000062s latency).

PORT     STATE SERVICE
5432/tcp open  postgresql
| ssl-enum-ciphers:
|   TLSv1.0:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_CBC_SHA (dh 2048) - A
|       TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA (secp256r1) - A
<snip>
|   TLSv1.1:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_CBC_SHA (dh 2048) - A
|       TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA (secp256r1) - A
<snip>
|   TLSv1.2:
|     ciphers:
|       TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (secp256r1) - A
|       TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 (dh 2048) - A
|       TLS_ECDHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (secp256r1) - A
|       TLS_DHE_RSA_WITH_CHACHA20_POLY1305_SHA256 (dh 2048) - A
<snip>

For curiosity I changed ssl_min_protocol_version = 'TLSv1.2' in postgresql.conf to see if the postgres server uses the intersection of the two configured ranges and after some debugging help from Laurenz i found that this setting was applied.
My question remained:
How can I configure my system so that postgres will accept TLSv1 through TLSv1.2 connections while keeping the openssl default MinProtocolsetting of TLSv1.2?


texts: Merging one-to-many and one-to-one relation in the same JSON object in postgresql

texts:
LINK TO FIDDLE: https://www.db-fiddle.com/f/a6FXTqJHpU9smuJTUKqwuV/0

My tables are as follows:

=> \d ops;
                                                   Table "public.ops"
          Column           |           Type           | Collation | Nullable |                      Default
---------------------------+--------------------------+-----------+----------+----------------------------------------------------
 op_id                     | integer                  |           | not null | nextval('ops_op_id_seq'::regclass)
 name                      | text                     |           |          |
Indexes:
    "ops_pkey" PRIMARY KEY, btree (op_id)

=> \d events;
                                                   Table "public.events"
         Column         |         Type         | Collation | Nullable |                        Default
------------------------+----------------------+-----------+----------+--------------------------------------------------------
 event_id               | integer              |           | not null | nextval('events_event_id_seq'::regclass)
 desc                   | text                 |           |          |
 op_id                  | integer              |           |          |
Indexes:
    "events_pkey" PRIMARY KEY, btree (event_id)

=> \d op_extra_info;
                                   Table "public.op_extra_info"
      Column       |  Type   | Collation | Nullable |                      Default
-------------------+---------+-----------+----------+----------------------------------------------------
 op_extra_info_id  | integer |           | not null | nextval('op_extra_info_op_extra_info_id_seq'::regclass)
 op_id             | integer |           |          |
 extra_info1       | text    |           |          |
 extra_info2       | text    |           |          |
Indexes:
    "op_extra_info_pkey" PRIMARY KEY, btree (op_extra_info_id)

For each op, there can be many events (each event refers to its corresponding op by means of the "op_id" field) but only a single extra info (again, using "op_id" to refer to the op it's related to). My goal is to return a JSON result as follows:

{ "name": ....,
  "extra_info": { "extra_info1": ....., "extra_info2": ..... },
  "events": [ { "desc": .... }, { "desc": .... }, ... ]
}

I've been able to get partway through with this query:

SELECT to_json(x) FROM (
  SELECT ops.name, array_agg(events.descr) as events
    FROM ops
    LEFT JOIN (select event_id, op_id, descr FROM events) events USING(op_id)
    GROUP BY ops.name) AS x;

But I can't find a way to:

get a real object for each element of the "events" array (ie, a real { "descr": .... } object instead of just the value of the "descr" column)
add the extra_info data as sub-object of the "extra_info" key at the top level.


texts:
I am quite new to MongoDB ops manager backup process

I have understood basic ops Manager setup from the document  https://docs.opsmanager.mongodb.com/v4.0/tutorial/install-basic-deployment/

My doubt is for using Filesystem snapshot storage do we need to have separate storage mount point for keeping Oplog store or can we use the same mongodb instance which running backup database ?


texts: Determining what algorithm MYSQL View is using

texts:
When creating a MySQL View, the algorithm is set to UNDEFINED.

How do I see which algorithm MySQL is choosing for the query ?

I tried using "EXPLAIN", but it is not obvious from the results what algorithm was used for the query.


texts: Select data from three tables using sub query and group by

texts:
I would like to write a query for SQL Server to select “Sales” by “month”
  (Sales from one table[sales_fact] and month from another table[time_by_day])
  where sales is grouped by  “ product_name"
  and it is again form another table[product] i.e. sales/product by month. Here is my query:

SELECT
    time_by_day.the_month ,
    ( SELECT sum(store_sales)
      FROM sales_fact
        INNER JOIN product
        ON  sales_fact.product_id = product.product_id
      GROUP BY product_name
    )
    AS total_sales
FROM sales_fact
  INNER JOIN time_by_day
  ON  sales_fact.time_id = time_by_day.time_id
GROUP BY
    time_by_day.the_month
ORDER BY
    DATEPART(mm,CAST(the_month+ ' 1900'  AS DATETIME)) asc;

But it is not working. Getting error for the subquery. Can I get some advice pls?


texts: When changing the default port number from 5432 to 5332, why do I get a unix domain socket error?

texts:
I successfully followed the Postgres 11 installation instructions on Centos 7 found here. I am able to successfully create schemas / tables and insert data into the database over port 5432 with no problems.
Now I would like to change the default port to 5332 (or something else, doesn't matter), and I did the following:
 vi /var/lib/pgsql/11/data/postgresql.conf

Changed the line:
#port = 5432                            # (change requires restart)

to look like:
port = 5332                            # (change requires restart)

Then restarted postgres service by doing:
systemctl restart postgresql-11.service

Then I change to the postgres user:
su - postgres
psql

I'm getting the following error message:
sql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

So apparently there is another reference of port 5432 somewhere, but I am unable to find it.
FYI, if I undo the change I did above (in the file /var/lib/pgsql/11/data/postgresql.conf), then everything works perfectly again over default port 5432.
What else do I need to do to get postgres to run on a different port ?


texts: Update Query DeadLock in SQL Server when ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT are enabled

texts:
Scenario:

We had learnt that the read operations are locked by MSSQL, causing other operations to slow down.

Later, we decided to turn on two factors.

ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT for the entire database.

Upon enabling them, we face update query dead lock from multiple customers (having SQL Server 2008, 2012, 2014)

Blocking Transaction:-

On identifying and running the procedure to identify blocked query, we find that select query on table X has blocked the update / delete process of multiple tables.

One such blocked update statement is

BLOCKED UPDATE: UPDATE "ManagedComputer" SET "AGENT_EXECUTED_ON"= @P0 , "STATUS_UPDATED_TIME"= @P1 , "REMARKS"= @P2 , "MODIFIED_TIME"= @P3  WHERE ( ("ManagedComputer"."RESOURCE_ID" =  @P4 ) );

BLOCKING SELECT: SELECT "UserParams"."USER_ACCOUNT_ID", "UserParams"."PARAM_NAME", "UserParams"."PARAM_VALUE"  FROM "UserParams" WHERE  (("UserParams"."USER_ACCOUNT_ID" = 2) AND ("UserParams"."PARAM_NAME" COLLATE SQL_Latin1_General_CP1_CS_AS = 'stop_agent_workflow')) ;

Isn't the sole purpose of READ_COMMITTED_SNAPSHOT is not to block Update/ Insert statements ?

Query that I executed to find the blocking and blocked statement is as described in the article here


texts:
I installed mariadb via configuration.nix. For the installation I made the following entries in my configurastions.nix.

services.mysql.enable = true;
  services.mysql.package = pkgs.mariadb;
  services.longview.mysqlPasswordFile = "/run/keys/mysql.password";

After I upgraded my system I get this error:

  warning: The option definition services.mysql.rootPassword' in/etc/nixos/configuration.nix’ no longer has any effect; please
  remove it. Use socket authentication or set the password outside of
  the nix store.

Before I always set my password in the configuration.nix with:

services.mysql.rootPassword

I also does a simple systemctl start mysql. However I get the following error message under systemctl status mysql:

● mysql.service - MySQL Server
   Loaded: loaded (/nix/store/2aliiwch37wiis98q6nbplcwyqk987l5-unit-mysql.service/mysql.service; enabled; vendor preset: enabled)
   Active: failed (Result: signal) since Mon 2019-08-12 00:45:56 CEST; 15min ago
  Process: 4784 ExecStartPre=/nix/store/bg6p3jcw23xmag07nzakkqh5hmy0h5yn-unit-script-mysql-pre-start (code=exited, status=0/SUCCESS)
  Process: 4785 ExecStart=/nix/store/r8s4kw7wskyn73ksb5nm9a5ykmyxk5ca-mariadb-10.3.15/bin/mysqld --defaults-file=/etc/my.cnf --user=mysql --datadir=/var/lib/mysql --basedir=/nix/store/r8s4kw7wskyn73ksb5nm9a5ykmyxk5ca-mariadb-10.3.15 $_WS>
  Process: 4820 ExecStartPost=/nix/store/hvfnmysk0hrgfp8pzr19r9sv0np88d1g-mysql-setup (code=exited, status=0/SUCCESS)
 Main PID: 4785 (code=killed, signal=ABRT)
   Status: "Taking your SQL requests now…"

With a mysql or mysql -u root -p I get the following error message:

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

Would be grateful for any help.


texts: SQL Server 2005 Replication Stops after a minute without an error

texts:
Background:

I have a SQL Server 2005 setup with master, slave1, slave2 replication set up as a pull replication from slaves. The distribution database resides on the slave1 machine, both slaves pull.

A problem began today where the replication on slave1 simply stops running. It claims that it completed successfully, but it does not restart, and manually starting the process finishes in roughly one minute, again without an error message.

Replication is running fine on slave2, but I can't seem to figure out what's wrong on slave1. I've tried the obvious Windows debugging 101: "restart the machine" technique, but to no avail.

Has anyone encountered this before Does anyone have an idea of what I could check or change to get it working again? I'm especially at a loss as SQL Server claims that the job is just finishing successfully.


texts: SQL which removes entries with duplicate emails

texts:
DELETE
FROM Person
WHERE Id IN (
 SELECT Id
 FROM Person
 GROUP BY Email
 HAVING COUNT(*) > 1 AND Id <> MIN(Id)
)

The above query produces the error:
You can't specify target table 'Person' for update in FROM clause

Why?
I am just trying to solve this problem to improve my SQL skills.
I checked the above SQL query here and it seems my syntax is fine.


texts: What is the risk of doing a rebuild to my indexes?

texts:
What is the risk of doing a rebuild to my indexes in SQL Server 2012?
I have to do a REBUILD, but I don't know what are the risks.

The server is running and a lot of information is going in and out.


texts: Better defining a parent-child relationship on a table

texts:
Our team is developing a new system and it has a Warehouse table that must have a hierarchical relationship between entries since a customer can have 1st, 2nd, 3rd and Nth degree warehouses.

We first thought that this was the best way to do it. By creating a Parent and a Child column on the table and then making Parent the FK to Child (on the same table) but friend DBA told us that there is a different way.

So, for instance, we had thought about making a table like this:

CREATE TABLE Warehouse(
filterId INT PRIMARY KEY IDENTITY,
parentId INT NOT NULL,
active BOOL DEFAULT 1,
warehouseName VARCHAR(30) NOT NULL,
FOREIGN KEY parentId REFERENCES Warehouse(filterId)

| FILTERID   | PARENTID | ACTIVE | WAREHOUSENAME |
--------------------------------------------------
|          1 |        0 |      1 | N WAREHOUSE 1 |
|          2 |        1 |      1 | ROOM 1        |
|          3 |        1 |      1 | ROOM 2        |
|          4 |        1 |      1 | ROOM 3        |
|          5 |        1 |      1 | ROOM 4        |
|          6 |        2 |      1 | SHELF 1       |
|          7 |        2 |      1 | SHELF 2       |
|          8 |        2 |      1 | SHELF 3       |
|          9 |        2 |      1 | SHELF 4       |
|         10 |        2 |      1 | SHELF 5       |
|         11 |        2 |      1 | SHELF 6       |
|         12 |        2 |      0 | SHELF 7       |
|         13 |        3 |      1 | BOX 1         |
|         14 |        3 |      0 | BOX 2         |
|         15 |        3 |      1 | BOX 3         |
|         16 |        3 |      1 | BOX 4         |
--------------------------------------------------

But this DBA said that we should work on something like this:

CREATE TABLE Warehouse(
filterId VARCHAR(20) PRIMARY KEY,
active BOOLEAN DEFAULT 1,
warehouseName VARCHAR(30) NOT NULL)

| FILTERID   | ACTIVE | WAREHOUSENAME |
---------------------------------------
| 1.00       |      1 | N WAREHOUSE 1 |
| 1.01       |      1 | ROOM 1        |
| 1.02       |      1 | ROOM 2        |
| 1.03       |      1 | ROOM 3        |
| 1.04       |      1 | ROOM 4        |
| 1.01.01    |      1 | SHELF 1       |
| 1.01.02    |      1 | SHELF 2       |
| 1.01.03    |      1 | SHELF 3       |
| 1.01.04    |      1 | SHELF 4       |
| 1.01.05    |      1 | SHELF 5       |
| 1.01.06    |      1 | SHELF 6       |
| 1.01.07    |      0 | SHELF 7       |
| 1.01.01.01 |      1 | BOX 1         |
| 1.01.01.02 |      0 | BOX 2         |
| 1.01.01.03 |      1 | BOX 3         |
| 1.01.01.04 |      1 | BOX 4         |
---------------------------------------

Which way should we follow?

On one hand we have a relationship between parentId and filterId; on the other we do not have any relationship, but it is easy to understand where does the entry belong.

Not all the warehouses are of the same level - that's what I mean by degree. They should be ordered like: For Customer A, 1 is parent of 1.1 and 1.2, 2 is parent of 2.1, 3 is parent of 3.1 and 4 is parent of 4.1 and 4.2 - but 1, 2, 3 and 4 are of the same level (1st level), and 1.1, 1.2, 2.1, 3.1, 4.1 and 4.2 are also of the same level (2nd level) but not of the same parents.


texts: How to move folder of SQL Server LocalDB instances from the default location?

texts:
I get multiple errors with LocalDB (startup error, can't create DB from SQL Server Management Studio and also multiple issues when trying to restore a backup) and none of the fixes I found are helping anything. It seems to me that all errors have their root in permissions not set up correctly.

Now a simple DDL table creation script works fine from SQL Server Management Studio and creates the database in the root of my user account's folder (as explained here). The Instances folder however (located at D:\Users\[My name]\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances despite when installing the SQL Server Express with Advanced Services I specified the DATA folder in another location - where only standard SQL Server Express .mdf files are stored, but not LocalDB instances) seems problematic regarding permissions (in contrary to the account root) therefore and also for having my DB files in a location among my projects I'd like to move the Instances folder to another place. Is this possible? I haven't even found a corresponding registry entry.

I'm using SQL Server Express 2012 and also SSMS of the same version (latest updates installed).

Any help would be appreciated.

Edit:
The Database Settings page KookieMonster mentioned previously threw an error for me, not being able to edit nor see any configuration there. I let Windows Update check for updates again and there were some updates for SQL Server Express that weren't installed (although I'm sure I selected everything to install before), so I installed them. This made it possible for me to go to the settings page.

I've tried to set up the LocalDB instance's root folder to somewhere else but I get an access denied error for every folder I tried, namely

  Alter failed for Settings
  'Microsoft.SqlServer.Management.Smo.ObjectKeyBase'.
  (Microsoft.SqlServer.Smo)

  ------------------------------ ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  RegCreateKeyEx() returned error 5, 'Access is denied.' (Microsoft SQL
  Server, Error: 22002)

I get this even if the folder has Full control set for Everyone! Even more amusing the only folder that works, one that is set as a default (the user account's root) has no special permissions applied - just the ones my user has (just as any other folder I tried to set). In effect this means that all my LocalDB databases' files are saved to my account's root folder.


texts: Configuring Transactional Replication for an OLTP Database

texts:
We conduct backup/restore (every 24 hours) on an OLTP database for reporting and SSRS queries. This prevents contention from simultaneous writes and reads; preventing locking, blocking, writer-reader deadlocks.

Now we want to apply transactional replication (to obtain more real time data). However, would the same issue occur?

If the reporting subscription database is being written into, updated/deleted, and we have read queries, would we still get the locking, blocking issue?

Although at least it would not be on a live OLTP system.


texts: SQL Server 2014 DBCC CHECKDB notification

texts: I want to include the results of DBCC CHECKDB in the notification email sent to me. I have seen some techniques in older versions of SQL Server. Is there any new / easier way to due this in SQL Server 2014?