texts: How to get different "modules" from modules table?

texts:
I have "moduller" table in that table keeping modules of system with function name. Two information in one field. Like this:

Slug field mean is like this: "MODULE_NAME/function_name"

I want to get the list:
 1. admin
 2. ajax
 3. analysisusers
 4. bonus

How to get i'm only different modules names like above?


texts:
I try to access to mysql data via filezilla and download to my pc,
What is name of folder where is my mysql data ?
I use ubuntu.

Thanks.


texts: High rise in Aborted_connects on mysql slave servers

texts:
I have a MYSQL replication set up where I have a single master and three slave DBs. The slave DBs are load balanced behind a Zeus Load Balancer.

I can see on slave DB servers that the Aborted_connects are rising regularly. The same is not happening on Master DB server.

mysql> show status like '%Aborted_connects%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| Aborted_connects | 340973 |
+------------------+--------+

To isolate any possible php code connection related issue and or any suspicious connection, I have disconnected on of the slave DB server and run tcpdump. I don't see any unrecognisable connection coming in to the server except for monitoring tool and master db connection (replication).

However, I have see time to time some of the below messages on mysql error log

140213  9:42:17 [Warning] Aborted connection 2099906 to db: 'unconnected' user: 'gvdb' host: '10.140.10.133' (Got an error reading communication packets)

Not sure how this might help, but someone has advised on this Fixing MySQL errors recorded in error logs to increase max_allowed_packet to 128MB.

Even after turning off replication, the Aborted_connects keep rising.


texts: SQL Server 2019 on Ubuntu Throwing security.crypto.symkey errors

texts:
I'm repeatedly seeing the following error, seemingly for every connection to SQL:
04/06/2021 16:39:25.890647613 Error [security.crypto.symkey] <0000004241/0x00000c34> Setting Variable Length cipher failed
04/06/2021 16:39:25.891039957 Error [security.crypto.symkey] <0000004246/0x00000c48> Setting Variable Length cipher failed
04/06/2021 16:39:25.891219869 Error [security.crypto.symkey] <0000003401/0x0000022c> Setting Variable Length cipher failed
04/06/2021 16:39:25.891406611 Error [security.crypto.symkey] <0000006256/0x00000d84> Setting Variable Length cipher failed
04/06/2021 16:39:25.891603673 Error [security.crypto.symkey] <0000003402/0x00000230> Setting Variable Length cipher failed
04/06/2021 16:39:25.891801766 Error [security.crypto.symkey] <0000005939/0x00000d74> Setting Variable Length cipher failed
04/06/2021 16:39:25.891984227 Error [security.crypto.symkey] <0000004242/0x00000c38> Setting Variable Length cipher failed
04/06/2021 16:39:25.892166190 Error [security.crypto.symkey] <0000003501/0x000003ac> Setting Variable Length cipher failed

Pertinent logger.ini file configuration:
[Output:Security]
Type = File
Filename = /var/opt/mssql/log/security.log
Maxfilesizemb = 1000
Maxrolloverfiles = 8

...

[Logger.security]
Level = Info
Outputs = Security

SQL authenticates against AD using a kerberos keytab file:
[network]
privilegedadaccount = xxxxx
kerberoskeytabfile = /var/opt/mssql/xxxxx/mssql.keytab
tlscert = /etc/xxxxx/mssql.pem
tlskey = /etc/xxxxx/mssql.key
tlsprotocols = 1.2
forceencryption = 1
tlsciphers = AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256 ...

Any ideas what might be causing the error or where else I should be looking?  It doesn't seem to be impacting things besides generating a large number of errors in the log.


texts: Can't promote PostgreSQL warm standby server to start serving data

texts:
Scenario:

Two CentOS 7 machines, with ips 10.0.2.15 and 10.0.2.4
Both running PostgreSQL 9.4.6. First one (10.0.2.15) as master, and second one (10.0.2.4) as slave, not listening for clients (just mirroring data via WAL shipping).

Problem: I can make log shipping work (so, slave receives WAL files), but I cannot promote slave to accept connections.

What I did:

In master:

Created user for replication from slave:

psql -c “CREATE USER rep REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD ‘replication’;”

Allowed slave to connect as replication user, with this line in pg_hba.conf:

host    replication    rep    10.0.2.4/32    md5

postgresql.conf:

wal_level = archive
archive_mode = on
archive_command = 'rsync -av %p postgres@10.0.2.4:/var/lib/pgsql/9.4/archive/%f —exclude=postmaster.pid'
max_wal_senders = 3 # Using just 1, but anyway...

In slave:

Deleted data repository

rm -fr /var/lib/pgsql/9.4/data

Made a base backup

pg_basebackup -h 10.0.2.15 -D /var/lib/pgsql/9.4/data -U rep -v -P

(I enter the password, and the base backup is properly made: new data directory now)

Added recovery.conf to new data directory just created. With this info:

standby_mode='off'
restore_command='pg_standby /var/lib/pgsql/9.4/archive %f %p %r'

Directory /var/lib/pgsql/9.4/archive exists in the slave, and it's writable for postgres.

So, the master-slave process is working. WAL files are being sent via rsync. But if I just try this in slave:

pg_ctrl promote

I get the message "server promoting", but nothing happens. And no new messages in the log. Still can't connect to the server via psql. No incoming connections accepted.

What should I do to promote the slave to serve data as a normal server? I'm talking about a high availability scenario, with a warm standby server starting to act as a normal server, allowing clients to connect.

I know I can change parameters in the slave to make it run as normal server, but I just think there should be a quick way to put my slave server to work. I thought it was just pg_ctl promote. But probably, I'm missing something.

Any clues?


texts: How can I know which tables "compose" a view?

texts:
I want to create a trigger on SYS.database_mirroring as it is not a real table, just a view... I can't do it.

I want to know were the field mirroring_state_desc is coming from so that I can create a trigger on that table when it is changed.

How can I know which tables are used to create that view?


texts: Export an Amazon Lightsail DB without putting your website down

texts:
I would like to get an offline local copy of my Amazon Lightsail database for debugging. However, the website is live, and starting an export could cause some serious server slowdowns.
I usually put the website under maintenance when I need to do it, but I would like to know if there is a way for me to do it differently.
Lightsail has some snapshots of the previous states of my DB, but it seems that I need to purchase a whole additional plan to use them.
What is the proper way to get a local copy of your database?


texts:
I am try to reduce machine come to live in production.. MySQL is making bottleneck. What i trying. I update code in one machine and also MySQL restore everything is working fine after this i am making EC2 AMI and launched machine from autoscaling group. now in this instance i am not taking and any live dump. after start MySQL and make it replication but its showing me GTID error. these all processes taking 30 to 40 minutes.

  Last_IO_Error: Got fatal error 1236 from master when
  reading data from binary log: 'The slave is connecting using CHANGE
  MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary
  logs containing GTIDs that the slave requires.

any body can tell what i doing wrong. but if i take fresh dump and restore this in live machine its working. MySQL version 5.6.17.


texts:
This is very strange. I just copied all the binary files from one server to another. The new server comes up just fine with one exception. I keep getting this error message in my log every couple minutes...

2018-09-06T01:27:56.710923Z 284261 [ERROR] InnoDB: Column table_name in table `mysql`.`innodb_table_stats` is VARCHAR(576) NOT NULL but should be VARCHAR(192) NOT NULL (length mismatch).
2018-09-06T01:27:56.711052Z 284261 [ERROR] InnoDB: Fetch of persistent statistics requested for table `mydatabase`.`mytable` but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats are not present or have unexpected structure. Using transient stats instead.

Now I know for a fact that the error is bogus. I have used ALTER TABLE to change the field size all the way down to 64 and the exact same error shows. So i dropped the table and I get the error that the table is missing as expected. Then I create the table with the proper length and this error comes back. It always says the field is 576 characters no matter what the actual size is. And it is only this one field and one table. None of the other innodb_ tables are coming up with an error. And running a database check says all the tables are correct but the log loads up with this error as each table gets checked.

For the life of me, I can't think of anything else to do. Any help out there?

MySQL version: 5.7.22

Script used to drop and rebuild the table run one line at a time checking the log each time.

drop TABLE if exists `innodb_table_stats`;
CREATE TABLE if not exists `innodb_table_stats` (
  `database_name` varchar(64) COLLATE utf8_bin NOT NULL,
  `table_name` varchar(192) COLLATE utf8_bin NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `n_rows` bigint(20) unsigned NOT NULL,
  `clustered_index_size` bigint(20) unsigned NOT NULL,
  `sum_of_other_index_sizes` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`database_name`,`table_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
flush tables;


texts: Order by reduces performance of a grouped result set

texts:
I'm trying to optimize a query which uses a combination of GROUP BY and ORDER BY which together cause a big performance hit (~x15). If I remove either of the conditions, the query runs pretty fast. I tried different indexing strategies and query structures, but have come up empty so far.

The original query looks like this:

SELECT `academies_calendar`.`day`, `academies_calendar`.`start`, `academies_calendar`.`duration`,
 `academies_calendar`.`title`, `academies_calendar`.`id`, TIME_TO_SEC(TIMEDIFF('00:16:00', academies_calendar.start)) AS `diff`,
 IF('00:16' BETWEEN academies_calendar.start - INTERVAL 60 MINUTE AND academies_calendar.start + INTERVAL (academies_calendar.duration + 60) MINUTE,1,0) AS `active`,
 IF('00:16' BETWEEN academies_calendar.start - INTERVAL 60 MINUTE AND academies_calendar.start + INTERVAL (academies_calendar.duration) MINUTE,1,0) AS `starting`,
 `academies_calendar`.`instructor_id`, CONCAT_WS(' ',academies_instructors.first_name,academies_instructors.last_name) AS `instructor`,
 COUNT(members_attendance.id) AS `attending`, GROUP_CONCAT( CONCAT_WS('>|<',members.first_name,members.last_name,members.academy_id,members.photo) SEPARATOR '<^>') AS `members`
FROM `academies_calendar`
LEFT JOIN `academies_instructors` ON academies_instructors.id=academies_calendar.instructor_id
LEFT JOIN `members_attendance` ON members_attendance.event_id=academies_calendar.id AND (DATE(members_attendance.checked_in)=CURDATE())
LEFT JOIN `members` ON members.id=members_attendance.member_id
WHERE (academies_calendar.academy_id=3)
AND academies_calendar.day=2
AND academies_calendar.start > CAST('00:16' as time) - INTERVAL (academies_calendar.duration + 60) MINUTE
GROUP BY `academies_calendar`.`id`
ORDER BY ISNULL(academies_calendar.start),academies_calendar.start ASC

It's pulling a bit of data and organizing it, but due to the filters that hit indexes, it's usually working on small set of data - 1-6 entries in the academies_calendar table.

The EXPLAIN results for that query:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  academies_calendar  ref     PRIMARY,academy_id  academy_id  6   const,const     5   Using where; Using temporary; Using filesort
1   SIMPLE  academies_instructors   eq_ref  PRIMARY     PRIMARY     4   maonrails.academies_calendar.instructor_id  1   NULL
1   SIMPLE  members_attendance  ref     event_id    event_id    5   maonrails.academies_calendar.id     5   Using where
1   SIMPLE  members     eq_ref  PRIMARY     PRIMARY     4   maonrails.members_attendance.member_id  1   Using where

The key used for the main table (academy_id) is a composite key over (academy_id,day,start). The rest are hitting indexes or primary keys. Only 5 rows are found for this specific query, and it complete in 0.015s

If I remove either the ORDER BY clause, or the GROUP_CONCAT() call, the query runs at around 0.0009s.

I'm not sure I understand this behavior - isn't the ORDER BY clause supposed to be applied after the WHERE clauses and GROUP clauses have been applied? why does sorting 5 rows increase query time by more than an order of magnitude? I get the same results if the query returns just 1 result, by the way.

What seems to be happening, is that the grouping multiplies the amount of time it needs to sort the result set by the amount of rows joined to it from the other tables. I tried to resolve that by replacing those joins with a subquery that groups the data and returns one result per row, but it did not have a significant impact on query run time.

Any ideas on how to optimize this query would be much appreciated!


texts: PostgreSQL - problem with INSERT trigger enabled when performing COPY FROM STDIN bulk insert (hangs/locks)

texts:
So I work with a database that regularly imports a significantly amount of records- on the orders of millions. To do this the most efficient way possible, I use the PostgreSQL COPY FROM method, which is well documented to be the fastest way for a bulk load. I am aware of fast ways to load this data, but with really large sets it's orders of magnitude faster to use the COPY FROM method. I am doing it from Python3/psycopg2 so I can actually stream it from memory and use STDIN, without needing to write to disk. This all works great... until recently.

A colleague added an INSERT trigger to one of the tables that I perform these bulk loads on. Once this trigger was added/enabled, the bulk loads simply hang. No resources seem to be spinning on the server.

This specific load normally take about 5-10 seconds to complete, but in this case remains hung, and looking at pgadmin4 indicates there's some sort of transaction lock on my COPY FROM operation. Using strace on the client side shows that it has sent a bunch of sendto() operations, but it is waiting for a response. Finally, after a bunch of sendto() it just stops and is idle, on (if I remember correctly) a select(), waiting for action on the socket.

Can anyone help me to understand why this behaves this way? It appears that the trigger does not fire (which makes sense, I think, since there are no INSERT statements in the COPY FROM) and I don't see any obvious errors in the logs. There are also no errors on the client-side.

Disabling the trigger using ALTER TABLE table_name DISABLE TRIGGER trigger_name allows me to perform the load as before, in about 5 seconds. Re-enabling it then brings back the same issue.

I haven't found documentation saying that these two are fundamentally incompatible. So what the heck is going on? Am I missing documentation? Am I going to have to ditch the COPY FROM approach and go back to regular INSERT batches? Is there a workaround (aside from implementing the trigger a batch job, which is not desired)

I would love to understand the low-level details as well as how to fix or work-around this.

Thanks for the help

EDIT: The trigger looks like this:

DECLARE
BEGIN
  DELETE FROM asi_ip_geolocation WHERE ip_address =ANY(new.ip_address);
  INSERT INTO asi_ip_geolocation (ip_address, country_code, country_name, region_name, city_name, latitude, longitude, block)
  SELECT ip.ip, country_code, country_name, region_name, city_name, latitude, longitude, block
  FROM asi_inventory,
  UNNEST(NEW.ip_address) AS ip
  LEFT JOIN asi_ip2location_geo ON ip.ip << asi_ip2location_geo.block
  GROUP BY ip.ip, country_code, country_name, region_name, city_name, latitude, longitude, block
  ORDER BY block DESC
  LIMIT 1;
RETURN NEW;
END;

The table being bulk loaded is asi_inventory. The asi_ip2location_geo table contains information to map IP addresses to geopgraphic locations.

Looking at pg_stat_activity, I do not see the trigger firing, the only relevant query I see is the COPY:

 asi-dba | (null)          | (null)     | active |   647802417 | COPY asi_inventory ("cname", "ip_address", "fqdn", "resolver", "source", "source_list", "capture_date", "fqdn_root") FROM STDIN WITH CSV

I also see several ROLLBACK queries:

 Client          | ClientRead          | idle   |      (null) | ROLLBACK
 Client          | ClientRead          | idle   |      (null) | ROLLBACK
 Client          | ClientRead          | idle   |      (null) | ROLLBACK


texts:
I am creating spatial index on sqlserver table.

CREATE SPATIAL INDEX [SIndex_udt_cell_SP_GEOMETRY] ON [udt_cell]
(
    [SP_GEOMETRY]
)USING  GEOMETRY_GRID
WITH (
BOUNDING_BOX =(80.0713, 26.389581, 88.169199, 29.974694), GRIDS =(LEVEL_1 = MEDIUM,LEVEL_2 = MEDIUM,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

CREATE TABLE [udt_cell](
    [ID_PK] varchar(9) not null,
    [PRINX] [int] NOT NULL,
    [STYLE] [varchar](254) NULL,
    [SP_GEOMETRY] [geometry] NULL,
 CONSTRAINT [PK_udt_cell] PRIMARY KEY CLUSTERED
(
    [ID_PK] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
)

I am getting error :

  Implicit conversion from data type varchar to binary is not allowed.
  Use the CONVERT function to run this query.

Why am i getting this error although the column on which index is being created is geometry type? I have changed the primary key from varchar to int too.

**


texts: Cumulative data and dates - my joins aren't working

texts:
So I want to get cumulative data, for every day since a start date until an end date (or today). I have constructed a query that will get the data from a table, but for some reason, some dates are missing. My left join isn't working and I am not getting the desired results. Can anybody point out my mistake?

(also, as a small detail, how do I only get the date and not the timestamp?)

Current result (note the missing dates):

          date          |   sum
------------------------+----------
 2012-09-01 00:00:00+02 |    8.000
 2012-09-03 00:00:00+02 |   29.300
 2012-09-07 00:00:00+02 |   31.300

Expected result (a missing date behaves as distance 0, so sum shouldn't increase):

          date          |   sum
------------------------+----------
 2012-09-01 00:00:00+02 |    8.000
 2012-09-02 00:00:00+02 |    8.000
 2012-09-03 00:00:00+02 |   29.300
 2012-09-04 00:00:00+02 |   29.300
 2012-09-05 00:00:00+02 |   29.300
 2012-09-06 00:00:00+02 |   29.300
 2012-09-07 00:00:00+02 |   31.300

Query:

WITH dates  as (SELECT min(date) as start_date,
                       max(date) as end_date
                from training_training
                where athlete_id = 1)

SELECT  distinct(d.date),
        sum(distance) OVER (ORDER BY d.date)
FROM
      (
       SELECT generate_series(start_date, end_date, interval '1 day') as date
       FROM dates
      ) d
LEFT JOIN training_training
     ON         d.date = training_training.date
     WHERE athlete_id  =  1
     AND   kind        in ('t', 'd', 'i', 'w')

GROUP BY d.date, distance
ORDER BY d.date


texts: store result set in temporary table, variable or separate variables within a trigger

texts:
I'm in the process of creating some triggers to store the the average rating for a given product on the product table. I've also been asked to store the total number of reviews for the product. So - I need to retrieve 2 values in my trigger, and then update the target table appropriately.

Should I use separate select statements

SET my_a = SELECT a FROM foo;
SET my_b = SELECT b FROM foo;

a single statement to retrieve a result set as a variable (is this even possible?):

SET var = SELECT (a,b) FROM foo;
UPDATE bar SET c=var.a,d=var.b;

or maybe even store the result set in a temporary table within the trigger(is that even possible?)?


texts: Importing a schema in Oracle with a different name

texts:
I exported an Oracle XE 18c database with:

expdp USER1/pwd DIRECTORY=backups DUMPFILE=file.dmp
      LOGFILE=config_export.log SCHEMAS=USER1

Now, I created in a different machine another user and schema USER2, and need to import all the tables from USER1 in USER2.

In the second machine, I import with:

impdp USER2/pwd DIRECTORY=backups DUMPFILE=file.DMP
      SCHEMAS=USER1 TABLE_EXISTS_ACTION=TRUNCATE LOGFILE=config_import.log

But I get the error that USER1 does not exist. Is there a way to import the schema without creating USER1?


texts: Database design for an E-commerce website

texts:
I am new to database design. I am designing a database for an E-commerce website, there are a lot of products to be updated, but while designing for product specification table I do not understand whether I need specify all the specifications in a table or do I need to use different tables for different products?

For example, consider the products Mobile and book, each of these having unique specifications (such as color,size, cost, model for mobile and title, ISBN, author, cost, year_of_publication, etc. for book), if it is only less number of products then it is possible for me to design, but when there is a thousands of products, it takes a lot of time.

Can anyone tell me how to design my database to manage this situation?


texts:
In my WHERE clause, I'm trying to use LIKE for a range of numbers.  For example:

WHERE rr.sequence LIKE '05600%'
   OR rr.sequence LIKE '05601%'
   OR rr.sequence LIKE '05602%'
OR rr.sequence BETWEEN LIKE('056035%' AND '056038%')

Otherwise, I would need to use a LIKE statement for each potential number combination between '056035%' and '056038%'


texts: Migration path to unify column names in backward compatible way

texts:
We have several naming schema for columns (like id/key or created/creation_date/create_date) and want to unify it.

Also we want to do it in backward compatible way. We want to be able to run old code together with new one until we get rid of old code, so following:

ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name;

is a breaking change.

Is there some form of aliasing for columns like views for tables (for Postgresql)?

ANSWER @CL (tnx for tip!) https://www.postgresql.org/docs/12/ddl-generated-columns.html

  A generated column cannot be written to directly. In INSERT or UPDATE commands, a value cannot be specified for a generated column, but the keyword DEFAULT may be specified.

So if an idea is to keep columns until you update all report script - it is fine. But if you want a rollback to previous app version - you are out of luck ((

Currently we are on Google Postgres Cloud offer: https://cloud.google.com/sql/docs/postgres/db-versions and v12 is in beta only.


texts: What are the trade offs for incrementing ids vs. fulltext keys for foreign key designs?

texts:
In a lot of relational database designs there are fields that are referenced in other tables.

For example consider a user table with a unique username and a second table storing address data.

One possible layout, that I would say is the common approach, because I have observed in most software, is to use auto increment ids like this:

Table users
===========
userId int primary auto_increment
userName varchar unique

Table adressdata
==========
userId int references users.userId
adress_type varchar // for example country
address_value varchar // for example US
(you probably also want to put a unique key on (userId,adress_type))

This is how I used to do it and how I have seen it in most cases.

Another way would be:

Table users
===========
userName varchar primary

Table adressdata
==========
userName varchar references users.userName
adress_type varchar // for example country
address_value varchar // for example US
(you probably also want to put a unique key on (userName,adress_type))

Here we store the full username also in the adressdata table.

To me this has the following advantages:

You can select the username right away from the table without the need to join it to another table. In this example this is from an application point of view probably not so relevant, but it is only an example.
It may be easier to scale the database in a master-master replication environment, because there are no auto_increment conflicts.

But also the disadvantages:

The space requirements for the index and data (but more relevant will probably be the index) on the field in the second table are higher.
A change of the username would need to propagate to all tables, which is more resource consuming than just changing it in one table and leave the ids as they are.

In my opinion it is much easier to work with text fields and don't use increment ids, and the trade offs are minimal and in most applications not relevant.

Of course some object ARE identified with an incrementing number by their nature (for example forum posts should receive an incrementing id because there probably is no other unique field like title or so).

But I before I start designing my database layouts in a completely different way I would like to know whether there are things I did not think of.

Are there any best practices?
Are there pros/cons that I did not think and the effect of which may arise at a later poit in time?
How do you personally design databases regarding the points above and why?


texts: What are the performance differences of inserting a bunch of data with one INSERT versus a bunch of INSERTs in a transaction?

texts:
I'm trying to understand the performance differences between something like

INSERT INTO Person (id, name) VALUES
(1, "Kevin"),
(2, "John"),
(3, "Jane"),
...

and

BEGIN TRANSACTION;

INSERT INTO Person (id, name) VALUES (1, "Kevin");
INSERT INTO Person (id, name) VALUES (2, "John");
INSERT INTO Person (id, name) VALUES (3, "Jane");
...

END TRANSACTION;

I'm aware that during a transaction indexes are temporarily built, but I'm not quite sure otherwise. I also don't know the other performance differences between the two.


texts: Joining tables on DB server vs App-Backend server

texts:
I have two tables stored on the DB server. I need to join these two tables and perform further calculations. I am sure that the calculations part should  be done on backend server. But I am wondering if I should perform the join in the DB server and then bring the new table to the backend or if I should bring the two tables to backend and there perform operations replicating join (using ADO.NET) before continuing with the further calculations.

NOTE:

Tables have few millions records.
It will be great if you provide some references to support your answer.


texts: Linking identifiers across tables in MySQL (Foreign Keys)

texts:
Given a simplified example of two tables

table Contact(ID int primary key, Name text not null)
table ContactPhone(ContactID int not null, PhoneNumber text not null)

Is there a way to tell MySQL that the ContactPhone.ContactID field is referencing the Primary Key of Contact?

Can there be automatic handling? If the Contact is deleted, then relevant ContactPhone.ContactID should be set to zero.

Ideally this flag/code/operation should be attached to the ContactPhone table. This way existing software that interacts with the Contact table can keep on the way it is, and the ContactPhone table and related software can be added or removed as one unit, without the necessity of altering the Contact table.

Specifically I want to be able to be able to empty out the Contact table, reset the AUTO_INCREMENT to =1, and bring in new Contacts AND I don't want to have to tell this program anything about ContactPhone. I want it to be automatic.


texts: How does one connect to a schema within a PDB (other than schema SYS)?

texts:
Using Oracle 18c on RedHat Linux 8

Connecting to CDB as 'SYS', I cannot figure out how to connect to a schema within the PDB other than as 'SYS'.  Maybe I don't understand the CDB/PDB architecture well enough.

Here is what I have tried after logging into the Oracle 18c CDB as 'SYS' (using sqlplus sys as sysdba):

ALTER SESSION SET CONTAINER= PDB;
conn myschema/mypasswd

and I also tried (right from the CDB)

conn myschema/mypasswd@PDB;

Both approaches fail to get me connected to the schema myschema in the PDB.  That is, I am still 'SYS' in the PDB.

Are there any suggestions/explanations on how to resolve this?
Do I have to setup some kind of access from the CDB-to-PDB for the schema myschema?


texts:
I'm trying to improve certain matches that happen often, on many many dimension columns.  So my choices as I understand are:

Match on many columns
Hash the columns, store the hash, match on that.

If I use something like 'checksum' is there a known algorithm to handle the collisions?
So the match would work like:

Partition two tables where checksum hashes match.
If there's more than one row returned, filter the results by matching all the dim columns

Anyone tried using checksum to make hashes faster on a large number of columns?

I should say, I'm looking for a SQL/TSQL solution.  I can easily acheive this match with python or c#


texts: Close button action when manually running a job

texts:
I started a job from SSMS on my laptop using Right Click > Start Job at Step... When I was ready to be done for the day the job was still running so I selected Close thinking it was operating on the server and would continue on the server.

Today I couldn't find the job for that run so I'm second guessing how that popup operates (screen shot of popup in question below).

What happens when the Close button is clicked? If the job is terminated how exactly is it terminated (e.g. something trappable; the job in question is an Operation System type running under a proxy account).


texts: Get the Messages stream in an SSRS RDL Report

texts:
So I'm trying to troubleshoot a particular SSRS RDL Report that literally takes forever to run (mostly because it has a mile of SQL to run).

My question is: Is there any way to retrieve the execution messages from a Dataset? (i.e. 15 row(s) affected).

My reasoning is that executing the SQL directly with static parameters tends to be much faster than using the RDL.

Note I'm not using the pagination thing that tries to keep all of the rows on one page, nor is the ending dataset all that large (a little less than 1500 rows), it's just the processing that's odd.

Ideally I just want the messages stream returned as a string to display as a tooltip.

Clarification: I'm primarily doing this to compare script portion performance between sections of code. Essentially, someone merged a bunch of stored procs into one, and it is my job now to figure out why it's suddenly taking 20x longer to run, but only when called on SSRS.

I was thinking of using a Global Temp table to store "log" entries, but I have no idea  how to control which datasets get executed in which order for rendering, so I haven't attempted that; though it seems almost like the most likely to work based on info below/


texts: How to model an entity type that can have different sets of attributes?

texts:
I’m having some trouble in recreating a database with a one-to-many (1:M) relationship between Users and Items.
This is pretty straightforward, yes; however, each Item belongs to a certain Category (e.g., a Car, a Boat or a Plane), and each Category has a particular number of attributes, e.g.:
Car structure:
+----+--------------+--------------+
| PK | Attribute #1 | Attribute #2 |
+----+--------------+--------------+

Boat structure:
+----+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 |
+----+--------------+--------------+--------------+

Plane structure:
+----+--------------+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 |
+----+--------------+--------------+--------------+--------------+

Because of this diversity in the number of attributes (columns), I initially thought it would be a good idea to create one separate table for each Category, so I would avoid several NULLs and thus making a better use of the indexing.
Although it looked great at first, I couldn’t find a way to create the relationship between the Items and the Categories through the database because, at least in my modest experience as a database administrator, when creating Foreign Keys, I inform explicitly a database the table name and column.
In the end, I would like a solid structure to store all data, while having all the means to list all attributes of all Items a User may have with one query.
I could hardcode dynamic queries with the server-side language, but I feel this is wrong and not very optimal.
Additional information
These are my responses to MDCCL comments:

1. How many Item Categories of interest are there in your business context, three (i.e., Cars, Boats and Planes) or more?

In fact, it’s very simple: There are only five Categories in total.

2. Will the same Item always belong to the same User (that is, once a given Item has been “assigned” to a certain User it cannot be changed)?

No, they could change. In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.

3. Are there attributes that are shared by some or all of the Categories?

Not shared but, from memory, I can tell that at least three attributes are present in all Categories.

4. Is there a chance that the cardinality of the relationship between User and Item is many-to-many (M:N) instead of one-to-many (1:M)? For example, in the case of following business rules: A User owns zero-one-or-many Items and An Item is owned by one-to-many Users

No, because Items would describe a physical object. Users will have a virtual copy of them, each one identified by a unique GUID v4

5. Regarding your following response to one of the question comments:

“In the fictional scenario of the question, it would be like User A sells Item #1 for User B, so the ownership must be reflected.”

It seems that you are planning to keep track of the item ownership evolution, so to speak. In this way, which attributes would you like to store about such phenomenon? Only the modification of the attribute that indicates the specific User who is the Owner of a specific Item?

No, not really. The ownership may change, but I don’t need to keep track of the previous Owner.


texts: SELECT FOR UPDATE (NOWAIT) on auxilliary table for serializing updates?

texts:
Background:
I have an inventory tracking application which needs to avoid the following sort of situation where the same inventory might be used twice due to lack of serialization.  The constraint is that each item of inventory from a pool of inventory must in use only once at any given time (much simplified example):
CREATE TABLE inventory_use (id serial, ref varchar);

Transaction 1:
BEGIN;
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T1 insert');

Transaction 2:
BEGIN;
SELECT * FROM inventory_use;
INSERT INTO inventory_use (ref) VALUES ('T2 insert');

Transaction 1:
COMMIT;

Transaction 2:
COMMIT; -- fails (as desired) at SERIALIZABLE, commits (not desired) at REPEATABLE READ

SERIALIZABLE prevents this from occurring by causing the last commit to fail (and REPEATABLE READ does not).  However, even though my application does not involve many concurrent writes, I'm reluctant to go for SERIALIZABLE because my understanding of its performance characteristics is weak: I'm not yet confident I know how to predict for example what the effect of concurrent reads or of (read or write) throughput will be on performance, perhaps especially on concurrent transactions that aren't connected directly with inventory tracking and probably are not critical to the application.  Probably I should educate myself more about SERIALIZABLE since it looks very useful, but I'd like to understand the alternatives too.
So this question is about alternative approaches to SERIALIZABLE for this kind of problem, in particular using SELECT FOR UPDATE NOWAIT to serialize inventory tracking updates through row locking while running at the READ COMMITTED isolation level.
What I've tried:
I implemented FOR UPDATE NOWAIT locking on the table that records inventory users.  That seems to work fine.
However, that may cause trouble because it can block some other updates to that table that need not be serialized.  This may well not be critical in my application, but it led me to see if I could use a separate table, created just for the purpose of locking, to do the FOR UPDATE locking.  To implement that, before the SELECT FOR UPDATE, I would begin by checking whether an INSERT is needed to represent the pool of inventory whose use needs to be serialized -- perhaps via INSERT ... ON CONFLICT DO NOTHING -- in order to ensure that there is a suitable row that can be locked to serialize the updates.
Question:
I've not been able to find other people using SELECT FOR UPDATE in this style in which:

there is an auxiliary table whose only function is FOR UPDATE row locking, not to store any application data.
FOR UPDATE row locking on existing rows is being used to serialize the creation of new rows

That makes me wonder whether this is a bad way to solve this problem.
I've seen other suggested approaches that are similar, for example using REPEATABLE READ with an auxiliary table and using an UPDATE to that table to implicity acquire a lock: https://peter.grman.at/postgres-repeatable-read-vs-serializable/
Is this (the SELECT FOR UPDATE style I describe above) a sensible technique for solving a problem of this kind, and if so, how should it be done?  I don't have specific "how should it be done" issues in mind other than to benefit from experience here.


texts: SQL Server performance sudden degradation

texts:
I have a SQL Server 2005 that has become unpredictable of late, and I'm scratching my head as to why. Queries that executed in seconds are changing plans and taking minutes (taking the time in full table scan or index spool). Now the first and most obvious thing is, the statistics are obsolete causing the optimizer to get confused but I am convinced this is not the case - firstly because the underlying data isn't significantly changing (e.g. adding one day's data ontop of a year's data already in a table) and secondly because Auto Create Statistics and Auto Update Statistics are both true. However the optimizer is getting confused; running the SQL in the Tuning Advisor gives me lots of multi-column CREATE STATISTICS statements which do seem to fix it (until the next bit of SQL misbehaves).

Any ideas of a strategy I can use to approach root-causing this? Any why the "normal" statistics aren't sufficient?


texts:
I have a simple table in SQL Server 2012 which implements a processing queue. As data has been inserted the query to retrieve the next item has gone from <100ms to a constant 5-6secs. I would be hugely grateful if someone could point me at the cause of this sudden dip in performance. (It seems to have been an almost overnight drop).

Here's the table definition:

CREATE TABLE [dbo].[messagequeue] (
    [id]             INT             IDENTITY (1, 1) NOT NULL,
    [testrunident]   VARCHAR (255)   NOT NULL,
    [filesequence]   INT             NOT NULL,
    [processed]      BIT             NOT NULL,
    [dateentered]    DATETIME        NULL,
    [filedata]       VARBINARY (MAX) NULL,
    [retries]        INT             NOT NULL,
    [failed]         BIT             NOT NULL,
    [msgobject]      VARBINARY (MAX) NULL,
    [errortext]      VARCHAR (MAX)   NULL,
    [sourcefilename] VARCHAR (MAX)   NULL,
    [xmlsource]      VARCHAR (MAX)   NULL,
    [messagetype]    VARCHAR (255)   NULL
);

CREATE NONCLUSTERED INDEX [messagequeue_sequenc_failed_idx]
    ON [dbo].[messagequeue]([processed] ASC, [failed] ASC)
    INCLUDE([id], [testrunident], [filesequence]);

CREATE NONCLUSTERED INDEX [messagequeue_sequence_idx]
    ON [dbo].[messagequeue]([testrunident] ASC, [processed] ASC)
    INCLUDE([filesequence]);

CREATE UNIQUE NONCLUSTERED INDEX [IXd_testrun_sequence]
    ON [dbo].[messagequeue]([testrunident] ASC, [filesequence] ASC);

And here's the query used to retrieve the next row to be processed:

select messagequeue.id, messagequeue.testrunident, messagequeue.filesequence,
    messagequeue.processed, messagequeue.filedata, messagequeue.retries, messagequeue.failed,
    messagequeue.msgobject, messagequeue.xmlsource
    from messagequeue where id = (
        select top 1 id from messagequeue mqouter
        where processed = 0
        AND failed = 0
        AND (filesequence = 0 OR
        filesequence = (
                select max (filesequence) + 1
                from messagequeue mqinner
                where mqinner.testrunident = mqouter.testrunident
                and mqinner.processed = 1
            )
        )
        order by testrunident, filesequence
        )

There are multiple rows with the same testrunident, each has a filesequence which should be sequential, however some may be missing so the query should only return the NEXT row where the previous row has processed = 1 or filesequence = 0 which denotes this is the first row within a testrunident group.

Here's an SQLFiddle to give an idea: SQL Fiddle

Query plan: Query Plan XML

Is there a better way to write the query?

EDIT 1 - Example of ensuring previous row was processed before selecting a row:

Where `id` = testrunident and `fs` = filesequence

id | fs | processed
1  | 0  |  1
1  | 1  |  1
1  | 2  |  1
1  | 4  |  0 -- this shouldn't be next as no row with seqeuence = 3 and processed = 1
2  | 0  |  0 --this should be the next row
2  | 1  |  0

SQLFiddle highlighting this


texts: Can 2 tables lock each other if concurrent read/update do not affect same rows? pagelocks off

texts:
Can 2 tables cause a deadlock, if the pagelocks are off, and the rows are not related.
E.g.

Query 1 runs for a few seconds

Select * from Orders where CustomerID = 1 and DateOrdered between @D1 and @D2

Query 2 starts to run, and ends, before Query 1 is finished, but with a different customerID

Update Orders set Quantity = Quantity + 10 where OrderID = 20 and CustomerID = 2

(These are just examples to prove a point please)

Now there is a PK_Orders which is a clustered index on the OrderID.
There is also a separate index (ix_Order_Customer) which is a non-clustered index, and contains CustomerID as one of the fields to index. This index has pagelocks enabled.

What I don't understand is how SQL (with traceID 1222) showed me in SQL Server logging that the PK_Orders caused a deadlock when the query which was executed did not even affect the same OrderIDs which were updated.

Extract from Log File Viewer:

 keylock hobtid=720229632 dbid=5 objectname=myDB.dbo.Orders
 indexname=PK_Orders id=lock1b0b23c0 mode=X associatedObjectId=720229632

and

 pagelock fileid=1 pageid=195848 dbid=5 objectname=myDB.dbo.Orders id=lock24442a40
 mode=S associatedObjectId=720229274

Can someone help explain how this is possible please, should the Deadlock not only kick in if there were intersecting rows here? I copied the 2 queries and ran them at the same time, yet I cannot recreate the error and in SQL Server Management Studio this error does not occur - the update goes through.

The error in SQL Event Log Viewer specifically says that PK_Orders is the cause here.


texts:
This obviously results:

SELECT *, uuid_generate_v4() FROM fruits;

apple      |f6b9a8a5-31c9-4917-9649-f366b7c12492
orange     |a55da08f-95a0-4f33-b536-866a3eae71e9

I know I could do

SELECT * FROM fruits LEFT OUTER JOIN uuid_generate_v4() ON TRUE;

But is there some nicer way to assign same uuid?
I'm mostly interested on conceptual level, the uuid() function is just example.


texts: TEMP Database is not showing Encrypted in TDE in SQL Server 2008R2

texts:
As per my knowledge, when we encrypt any user database in SQL Server through TDE, the TEMP database automatically gets encrypted.

I have done TDE in SQL Server 2016, and when I check through SELECT name, is_encrypted FROM sys.databases, it shows user and temp database as 1 (Encrypted).

But when I performed TDE on SQL Server 2008R2 (using same method), and I fired the command, the user database was encrypted, but TEMP database status for 'is_encrypted' is still showing as '0'.

What could be the reason or what wrong has happened?


texts:
I manage an application that has what I would call a medium sized workload (20,000-30,000 records inserted per day with around 2GB of data total). We're on RDS Postgres 9.6 with a db.m4.xlarge (4 vCPU and 16 GB of RAM) instance (I'm way over provisioned, I know). Everything has been running smoothly for months with the average CPU utilization under 10%.

Yesterday I got an escalation from the application support that our CPU has spiked above 80% and since then I haven't gotten it to go back down under 50%.

This is the graph of the last 4 days of CPU Utilization

There's been no spike in usage over this same time.

I've looked into long running queries through out the day using

SELECT max(now() - xact_start) FROM pg_stat_activity
                           WHERE state IN ('idle in transaction', 'active');

The largest amount I've seen is 1.5 seconds

We have about 40 connections but most are inactive.

This same thing happened a few months ago and we moved to our now way overprovisioned server to hopefully not have it happen again. So now I'm trying to figure out the root cause.

Any help would be appreciated.


texts: Does MySQL have a version of Change Data Capture?

texts:
We're in the process of phasing out an old system and migrating onto a new one. The last time that we phased out an old system, we ran both systems in parallel and integrated data between both until everything was fully migrated out in our field. During that process, I was able to build an integration between our legacy system and our new system that leveraged SQL Server's Change Data Capture to track changes and integrate those over incrementally.

For this next migration, the legacy system that we will be phasing out is based on MySQL v5.1.69 instead of SQL Server. I am not familiar with MySQL and I was wondering if there are any technologies similar to CDC that can be leveraged on MySQL in our current version or a newer version that would be worth migrating towards?


texts: LSCopy job fails on SQL Server 2005 - Insufficient space

texts:
I'm having some trouble getting my LSCopy job of Log-Shipping on my SQL Server 2005 server, to work.

The LSBackup job is configured so the backup is made locally (D: drive)

The LSCopy job is configured to copy from the primary (\server_name\d$) and copies it locally to the secondary server.

The LSRestore is restores the local copy to the secondary database with NORECOVERY

The LSBackup job runs fine, copies the file to the location specified.

The LSCopy job fails. The error message states that it goes to the source for settings. Gets the settings (I assume) then begins the copying.

This errors saying:

  Error: Not enough server storage is available to process this command.

What I'm not sure about is whether the destination folder needs to be a network share, or if just \server_name\d$ is accepted.

Any help will be really appreciated!


texts: How to properly downgrade mysql 8 to 5.7 in centos 8

texts: I would like to downgrade MySQL 8 to MySQL 5.7 on centos 8, can I get the proper steps/commands. Thanks in advance.


texts: Difference between the following postgresql indexes

texts:
On a table with roughly 15 million rows, I've got a bunch of boolean fields indicating performed actions and also timestamps of when the action happened for a row.
The following 2 indexes, I would think will act the exact same way, but they don't:
1.
"cleanupAndAlerted001" btree (doneCleanup, alertedUser, doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

"cleanupAndAlerted002" btree (doneCleanupTimestamp DESC) WHERE doneCleanup = true AND alertedUser = true;

The query performed is of the form:
select distinct id, field1, field2, ... from my_table
WHERE
doneCleanup = true AND
alertedUser = true AND
doneCleanupTimestamp<='2021...validtimestamp'
order by id asc, initialTimestamp asc limit 100 offset 50000;

Considering the conditions, I would expect identical behavior. Yet, the first index, even when the 2nd doesn't exist, does not get picked up.
Creating the 2nd index and doing the query again works, the new index gets used.
Am I assuming things I shouldn't be assuming? If so, what should I know about this behavior?
Edit: using postgres 11.2


texts: Efficiently storing irregular/repeating intervals (think calendar/events)

texts:
I am developing a service that relies on users being able to recieve messages that they themselves choose. These messages need to be stored somewhere before they are send for processing.

Right now I'm storing them in a postgres database, but I have a feeling it doesn't scale well.

The current layout is:

ID - MESSAGE - DATE - TIME

The DATE and TIME field holds the time and date for when the message should be send for processing. This doesn't scale well, as if a message needs to be send the first monday every month, it would take up 12x as much space.

Problem is that I can't seem to find another way to represent when a message should be send for processing? Ideally I'd love to be able to represent each and every date in a single row.

We were also disucssing using Redis, but quickly decided not too, as we would need the database for the webfrontend.

Anyone have any idea how to optimize the message storage? How to represent when a message should be send for processing?

I am also open for any other suggestions on how to tackle this.


texts:
We have been working on a large-ish PostgreSQL database migration to a Google Cloud VM instance (Linux 14.04) for several weeks now, and are having issues with the final step: pg_restore.

We are trying to restore from a 17GB .backup file. We have tried several permutations of the pg_restore command, including:

No existing target database, include the --create flag.
Connect to existing target database, include --clean and --create flags (per these Stackexchange answers).
Specifying --host=localhost (per 68 here).

Several attempts resulted in the rows of the restore being written to the nohup.out file.

A few permutations we've tried:

nohup pg_restore --host=localhost --username=postgres --password --role=postgres --dbname=dbname_to_restore path/to/data.backup > nohup.out 2> err.out

pg_restore --username "postgres" --password --role "postgres" --create --clean "/path/to/data.backup"

When we nohup we are getting a few issues in our err.out file, such as

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 187; 1259 17762 TABLE ping arcgis
pg_restore: [archiver (db)] could not execute query: ERROR:  type "geography" does not exist
pg_restore: [archiver (db)] could not execute query: ERROR:  schema "arcgis" does not exist

What are we missing? What are the bare minimum flags etc. to load the data into the PostgreSQL database? Are there some environmental considerations we need to address? Why isn't the data being loaded into the db?


texts:
Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique?

Data examples:

ENUM val1, val2, val3

[{p1: val1, p2: 'something'}, {p1: val2, p2: 'something'}] <-- valid
[{p1: val1, p2: 'something'}, {p1: val4, p2: 'something'}] <-- not valid, val4 not an ENUM value
[{p1: val1, p2: 'something'}, {p1: val1, p2: 'something else'}] <-- not valid, p1 not unique

If it is possible, using PostgreSQL and sequelize, how would I go about it, setting up the column?


texts: Update column with value of another column or another column

texts:
With PostgreSQL (I'm using the version 9.1) is it possible to do mass update with a single query a column value with the value of another column, but if the other column value is null, to use the value of a third column, and if the third one is absent to use the current datetime (all the column have type timestamp)

I need to change

columnA columnB columnC
null    foo     bar
null    null    baz
null    null    null

to

columnA columnB columnC
foo     foo     bar
baz     null    baz
quz     null    null

where quz is the current datetime.


texts:
I made a CRUD web application that uses a small PostgreSQL database. It is a donors database with the columns name, telephone, blood type, etc.

I have some SELECT and INSERT INTO queries, but now I need a query to back up the database. Nobody has asked about it on this site before, so I think it can't be done, or else I have to make a script using pg_dump or a maintenance plan).

Is it possible to write a SQL query to perform a PostgreSQL database backup?


texts: How locate error "Login failed for user 'user_test'" inside huge stored procedure?

texts:
I am in the process of refactoring legacy system which relies on huge a stored procedure with large calling tree of dependent stored procedures/UDFs and a lot of interconnected linked servers.

Production stored procedure runs fine while test one fails with

  Login failed for user 'user_test'

I can get no further details from error_procedure() and error_line().

Complete output:

  (1 row affected)

  (1 row affected)

  ...skipped

  ErrorNumber=[18456]; ErrorSeverity=[14]; ErrorState=[1]; ErrorProcedure=[]; ErrorLine=[1]; ErrorMessage=[Login failed for user 'user_test'.]

  Msg 18456, Level 14, State 1, Line 1
  Login failed for user 'user_test'.

Apparently some of the linked servers are not properly setup on the test environment. So I would need to either set them up or mock/comment external calls. I cannot debug the stored procedure on a test server.

How to find exact line in the code which throws this error without adding traces after each line of SQL?

System runs in SQL Server 2016 on Windows Server 2012 R2.


texts: Efficient Value Update with Large Database

texts:
I have a table as large as 8GB and with 20M records. There is an int field called mth. The mth field stores date information in form of YYYYMM, I want to transform the mth field to consecutive integers. So I use a formula to get the year and month from field mth and calculate the month order, specifically, I use the following code:

create function mth_to_num(@month int)
returns int
as
begin
   return(round(@month/100,0)*12+@month-100*round(@month/100,0))
end

Then I use the following code to update the value in the huge table

update full_orig_month_Q1_1999
set mth_order = dbo.mth_to_num(period)
Go

However, the code took quite long time to execute, roughly 2-3 minutes. My system is windows 10 64 bit with SQL server 2016. Is there any way I can speed it up?

Another problem is that I found SQL server occupying as much as 8GB in the database after executing the above query. Does it need so much memory? How can I release them?

Thanks for your help in advance!

Jason


texts: order by case with multiple order criteria

texts:
I would like to know how to use multiple order columnes with case. For example case 1, order by FirstName, GivenName case 2 order by GivenName, FirstName.

I tried this, but it looks like the colon introduce an error

Order by case
     when 1 then FirstName, GivenName
     else GivenName, FirstName end


texts:
I want to backup a postgres table with pgAdmin and have the dump ordered by ID (or any other column). How do I do that?

[EDIT] The dump should contain INSERT queries, not the pure data! [/EDIT]

What I tested so far:

1) I checked pg_dump too and did not find any solution.

2) Another solution would be to export the results of a SELECT statement which is not possible too with pgAdmin.

3) With psql the only solution I found was psql -h localhost -U myuser -W -c 'copy (SELECT * FROM mytable ORDER BY id) to stdout' which only prints the data as csv.

Can anyone help me out? Thanks!


texts:
I have 3 db servers on docker containers, and i used to connect like this from my python app:
psycopg2.connect(host="db-0", user="user", password="password", database="data") This works,but if i add more then 1 host it fails.

psycopg2.connect(host="db-0, db-1", user="user", password="password", database="data")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python3.7/site-packages/psycopg2/__init__.py", line 126, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: could not translate host name "db-0, db-1" to address: Temporary failure in name resolution

my package is:

psycopg2-2.8.4

python3.7

psql 11.5 on docker containers db-0 and db-1
I used pip install psycopg2 to install.


texts: NOSQL Manager for MongoDB throws command failed collection already exists

texts:
While copying collections from one MongoDB server hosted on A to another MongoDB server hosted on B using Copy collection to another database tool of NOSQL Manager we got an error "Command create failed collection already exists code :48"

The option we specified in NOSQL Manager for copy collections is Replace existing collections.

https://www.mongodbmanager.com/clone-mongodb-collection#copy


texts: MySQL SELECT n-th row between dates (time interval)

texts:
I have a server which records various sensor data each second and inserts it into a MySQL table.
To view the sensor data on a dashboard I have a chart which also gets updated with live data each second. The database is growing at a rate of more than 250,000rows / 24h (3 sensors+).

When loading the dashboard I want to display history data. At least 24h, more would be better (72+ ideal); my testings are all done with 24h.

The amount of data is huge (86400 rows), so I though of selecting only a subset of data for the history, which can be refined on zoom. I chose an interval of 5 minutes, or 300 seconds:

SELECT * FROM `data` WHERE `sensorId` = 0
 AND `unixDate` >= (1458829800 - 86400)
 AND `unixDate` < 1458829800
 AND NOT `unixDate` % 300 -- left out for all data
ORDER BY `id` DESC

This query is of course amazingly slow, on the damn small machine I'm running this on (a raspberry pi) it takes more than 10 seconds!

Querying all the 86400 rows takes roughly below one second.

I might be able to install a separate database server on a heavy machine as well, but I'd like to get this project done before my server environment is up and running, also I don't want to send so much data around my network each second...

I wonder two things: is there a quicker way of querying data by time intervals and might there be a completely better solution (time series db, which seemed a bit overkill to me) for this kind of work?


texts: What's a good way to get SQL Server to release memory to the operating system on a dev machine?

texts:
On my dev box I have my SQL Server configured with a high maximum amount of memory (8GB of 16GB). This is required for many development tasks.

Sometimes I want my memory back, though. I don't want to leave 8GB consumed permanently.

What's a good way to get SQL Server to suddenly release all it's memory to the operating system?

DBCC DROPCLEANBUFFERS does not do that. It merely marks clean buffers as available.

Restarting SQL Server is a problem because this causes a 1 minute database recovery to be run due to this bug/feature: Stopping the SQL Server service causes databases to not shut down cleanly (please vote for that connect item; seems like an egregious bug).


texts: Extend iso currency data with user currency

texts:
I have a simple currency table with iso 4217 data:
currencies
----------
currency_code char(3) pk
name          nvarchar

And I want users to be able to add their own currencies (like cryptocurrencies) in a table like:
users_currencies
----------------
user_id int     pk (fk to users table)
code    char(3) pk
composite primary key (user_id, code)

So that they can use to price a product:
users_products
--------------
user_id       int pk
product_id    int pk
currency_code char(3) (fk to users_currencies)
price         decimal

Then I would copy the currencies table into users_currencies when creating new users.
Is this a good design? What are other possibilities?
I would like to avoid this kind of copy if possible.


texts: SQL Server: Multiple Availability Groups, one cluster, how do I keep the primary cluster node as primary?

texts:
I have a database server setup where there are three SQL Server database instances set up on three nodes of a Windows Server Failover Cluster (WSFC).

The sysadmin, who is responsible for the Windows Cluster, is accusing me of manually setting the primary cluster node as something other than what it is.

Can failing Availability Groups over to secondary replicas, to include during reboots when they automatically take over, also cause the underlying Windows Server Failover Cluster to switch the primary node and not switch it back?

This has the potential to become a distracting issue, keeping me from getting work done.  I'm hoping to be pointed in the right direction to keep this from happening again.


texts: SQL Server 2016 SP1 CU2 AlwaysOn AG Secondary always disconnected

texts:
I'm trying to setup an AlwaysOn AG with T-SQL commands but my secondary is always in a disconnected state (when I failover, my old primary becomes disconnected).
This is not an firewall/network issue, I'm able to send messages from SQL1 to SQL2 with a TCP-sender/receiver.

The option New Availability Group... in SMSS also generates the same problem.
However when I'm creating an AG with New Availability Group Wizard... it works perfect.

This is the T-SQL statement I'm using:

--on primary
CREATE AVAILABILITY GROUP [AG_SQL]
FOR
DATABASE SetupAG
REPLICA ON 'sql1'
    WITH (ENDPOINT_URL = N'TCP://sql1.mydomain.com:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     --   BACKUP_PRIORITY = 50,
      -- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
        SEEDING_MODE = AUTOMATIC),
    N'sql2' WITH (ENDPOINT_URL = N'TCP://sql2.mydomain.com:5022',
        FAILOVER_MODE = AUTOMATIC,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
     --   BACKUP_PRIORITY = 50,
       -- SECONDARY_ROLE(ALLOW_CONNECTIONS = NO),
        SEEDING_MODE = AUTOMATIC);
GO
--on secondary
ALTER AVAILABILITY GROUP [AG_SQL] JOIN
GO
ALTER AVAILABILITY GROUP [AG_SQL] GRANT CREATE ANY DATABASE
GO

This is the output from different DMV's:

dm_hadr_availability_group_states:

dm_hadr_availability_replica_states:

dm_hadr_database_replica_states

On the secondary dm_hadr_database_replica_states is empty and the database doesn't show up in sys.databases. When I look in SSMS the database is shown in 'Availability Databases'

Is this a bug in SQL Server 2016 SP1 Standard CU2 or is there something wrong with my T-SQL statement?


texts:
We have around 15 linked servers in each of 2 servers and application is querying the views and stored procedures using these linked servers.

I would like to know

Is there a way to find from SQL(2008) Side, how many linked servers are being used and how many are not.. as application team want us to remove the unnecessary linked servers.
Is there any post/document which gives us more information on how to troubleshoot Linked server issues.
are there any DMV's or any Views where the historical information of Linked servers is maintained.


texts: Mongodb: too many namespaces raise on setProfiling

texts:
I have a mongodb database with a single (relatively big, around 130GB) collection, with 3 indexes, on which I'd like to do some profiling.
However when I run the db.setProfilinStatus(1, 1000) command, mongodb returns the following error:

{
    "was" : 0,
    "slowms" : 100,
    "errmsg" : "exception: too many namespaces/collections",
    "code" : 10081,
    "ok" : 0
}

I have verified the nssize setting (I have nssize = 2047 in mongod.conf and I have checked the result of db.adminCommand( "getCmdLineOpts" )

Any idea what the issue might be here, and how I could enable profiling?
Thanks!


texts: SQL Server 2008 R2 - how to repair System Views

texts:
I have SQL Server 2008 R2 database, which is corrupted. Dbcc checkdb command gives result:

  Msg 8921, Level 16, State 1, Line 1
  Check terminated. A failure was detected while collecting facts.
  Possibly tempdb out of space or a
  system table is inconsistent. Check
  previous errors.

Then I tried run dbcc checktable, with this command:

sp_MSforeachtable @command1="print '?' dbcc checktable ('?')"

Result of this command is:

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.  The results, if any,
  should be discarded.

Then I reviewed System Views and found, that some System Views are corrupted, in example when I do

select * from [sys].[all_parameters]

or from [sys].[all_columns] I got some result rows and then I got this error:

  Msg 0, Level 11, State 0, Line 0
  A severe error occurred on the current command.  The results, if any,
  should be discarded.

Is there any ways to repair that System Views?

P.S. I already tried to do repair with data loss but it was unsuccessful...


texts:
First off, I'm not a DBA at all, I'm more of a front-end guy, but I'm currently hitting a brick wall and can't figure out how to tackle it. A database I am working on has a table with several columns. The table represents completed tasks, the columns are:

TaskID             -  -  -  1. On-site
DateStart         |         2. Return-to-base
DateEnd           |         3. Chargeable
Type -  -  -  -  -          4. Complaint
Reference                   5. Rebook
Requester
Approver
Cost
...

Reference is used when the customer provides their own ID for the task, typically this is for example "THR#000123123" or "IX#01212". It is always in the format "ABC#123".

Now, I need to categorise these into multiple arbitrary categories and subcategories based on only the type and reference fields:

Chargeable (type = 3)

By company IX (reference like "IX#%")
All other Chargeable

Non-chargeable (type <> 3)

By company THR (reference like "THR#%")

Return-to-base (type = 2)
On-site (type = 1)

Return to base (type = 2)
All other non-Chargeable

A task can only belong to one category, so if a task for company THR was "return-to-base", it must only be under category 2.1.1, not 2.2. I realise these categories seem very arbitrary but this structure represents the most useful break-down of almost any selection of tasks from the table.

My question is, is it possible to create a view that contains the TaskID and its category? Or should I just implement this logic in the front-end after selecting every TaskID, Type and Reference?


texts: SQL Developer - trying to import an excel file into a table

texts:
I'm trying to import an excel file into a table. The excel file has dates in the following format: 20/08/2008  12:00:00 AM
and I am telling SQLDEV the following: DD/MM/YYYY H24:MI:SS AM

To import the file, I am right clicking on the table in SQL Developer, hitting import data and then selecting the file, I map the columns and set the format to above and get the above error. Here's the table:

--------------------------------------------------------
--  DDL for Table DRUG_INFO_NOS
--------------------------------------------------------

  CREATE TABLE "DANIEL"."DRUG_INFO_NOS"
   (    "DIN" VARCHAR2(20 BYTE),
    "TRADE_NAME" VARCHAR2(50 BYTE),
    "FORM" VARCHAR2(40 BYTE),
    "GENERIC_NAME" VARCHAR2(60 BYTE),
    "MANUFACTURER" VARCHAR2(100 BYTE),
    "CR_DATE" DATE,
    "NOC_DATE" DATE,
    "FIRST_CLAIM_DATE" DATE,
    "SEQUENTIAL_ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DATAL03" ;
--------------------------------------------------------
--  Constraints for Table DRUG_INFO_NOS
--------------------------------------------------------

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("DIN" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("TRADE_NAME" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("GENERIC_NAME" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("MANUFACTURER" NOT NULL ENABLE);

  ALTER TABLE "DANIEL"."DRUG_INFO_NOS" MODIFY ("SEQUENTIAL_ID" NOT NULL ENABLE);

but I am getting the above error. What's up?


texts: How can I display 9 rows of results in a 3x3 grid?

texts:
I have a report that gathers data to be printed on labels, we want to print up to 9 labels per page, on a 3x3 grid.

Am I able to use something like groupings to get this layout generated automatically, or will I need to hard-code each of the 9 labels individually? I would like to be able to do it automatically so that I can re-purpose it for other label sizes.

I haven't tried anything yet, as I really can't get my head around how to do it.
I am using SSRS 2012, I am relatively new to it and SE, so I apologize if I am lacking any crucial information.

Example of the input data, regarding the favorite things of totally real people:

The result I would like:


texts: Splitting Snapshot files with MaxBCPThreads for Transactional Replication

texts:
I've just set up a publication, and I'm attempting to get the snapshot to apply faster. So far the Distribution Agent is respecting the MaxBCPThreads settings, but the Snapshot Agent is not. I'm expecting it to split the files so the threads on the Distribution Agent would go and grab the data. But it doesn't appear to be able to do that any time I snapshot.
Some possible solutions that I've seen online where to update the agent profile (I originally just edited the agent step with the flag, and that worked for the dist agent but not for snapshot).
I tried updating the agent profiles and that hasn't made any difference. I also found people saying that you should have sync_method set to native so I checked my script and I had already created the publication with native mode specified.
I'm wondering if I'm missing a specific setting that MaxBCPThreads needs in order to split all the bcp files into different files each.
I thought I had solved my own issue: It looks like you have to have a clustered index with a distinct set of ranges to get SQL Server to split the files into partitions. But right now my index seems to have 0's for all ranges.
DBCC SHOW_STATISTICS

After additional testing, I've found that this seems to only work on replicated tables. If you were to replicate based on (indexed) views then it seems that you only get the 1 bcp file instead of the partitioned stuff you'd get from normal tables.
The question is: Why doesn't SQL replication partition bcp files for Indexed Views like it does with normal tables?
I'm replicating the indexed view itself without the table ("Indexed View as a Table"). The reason is I have to join identifying information for the database for the subscriber to use for other things.  The only way I've found to do it so far is to manually split my views using BETWEEN, which isn't particularly efficient. I'm hoping I can get SQL Server to do what I'd expect when replicating a normal table.


texts: What is the default size of text field in Postgres?

texts:
In the official documentation (http://www.postgresql.org/docs/9.1/static/datatype-character.html), it states text field is variable unlimited length.

I wonder what is the default size of this field (or minimum size of the field)?


texts: Why does querying a PostgreSQL database have an overhead of a few dozens of milliseconds?

texts:
Everything is in the same place, same local computer.
Whenever my Go application as well as pgAdmin 4 queries the PostgreSQL database, it takes at least a few dozens of milliseconds, however short the actual execution time is. Where does this overhead originate from? What causes the delay? It’s the same without the EXPLAIN ANALYSE.
pgAdmin 4

The problem is with “Total query runtime: 68 msec.” for EXPLAIN ANALYZE SELECT 1; taking 5 microseconds. There’s no way to reduce the client overhead other than querying less?


texts: Ubuntu postgresql 2 databas in two hard disks

texts:
I have two postgresql database in single 1tb hard disk. Each data base sizes more than 300gb. So now I faced a disk space problem and I bought another 1tb hard disk recently. So I need to move one database into this new hard disk. How can I do that in Ubuntu ????

If I move one database to another drive, postgresql can communicate both harddisks at same time? Can I access both databases in postgresql on master hard.

Sorry for my bad English.


texts:
I had asked for database(Oracle 11g) back-up files (for a particular date)from a third party vendor. The files I received  were with the following extensions :   .dbf  , .svd ,    .arc ,   .ctl  (PFA showing the files).
Now my concern is to load the data from this file in ‘Toad for Oracle , Version 10.5.1.3‘ software – I usually use the same for  working on this particular database .
The purpose of loading this database is to run some select queries(along with lot of joins) so as to extract the data from the files which can be shared  with the requestor .
        I am not very sure which file will have the complete data as there are files with various extensions . Also , not sure if ‘Toad for Oracle’ has the facility to open /load dbf or other aforementioned files . I don’t have DBA rights for this database as it is maintained by 3rd party vendor .  Please let us know if Toad can be used for the same or a new software   needs to be installed for this purpose . Also , what steps are required to be followed to do the above mentioned task .I do have the details related to schema name , username/schema and password to the database !Quick help on this is  much appreciated


texts: Postgres numeric type overflow after 1023 bisection of 1

texts:
i am testing the numeric type in postgres according the precision and scale i declared the numeric type with (1000,900) which gives me 900 digits decimal points but when i test it by inserting and halving 1 it stops after the 1023 halving and throws the error: ERROR:  value out of range: overflow
SQL state: 22003.

I use this statement to insert:

 insert into numeric_test
  select   ((1 / power(2, i))::numeric)+10000000, pg_column_size(( 1/power(2, i)::numeric)+10000000)
from generate_series(1, 1024) as i;

And that is my table:

create table numeric_test (
  pos numeric(1000,900),
  sz integer
);

alter table numeric_test add column index_x integer;

Is there a limit how big and numeric type entry could get? The size of the 102 3iteration is 127 bytes and the 1024 iteration throws an error of overflow.


texts:
I'm setting up an Azure D11 SQL Server to be used for a data warehouse. I can only attach 4 data disks and have done so, now I need to determine how to configure them and split the DB files. I'm trying to adapt MS guidance to my specific situation.

Traditional Design:

OS Disk/Azure Temp Disk
1 data disk - TempDB
1 data disk - Log
2 data disk - Data

I'm expecting their guidelines are for general/potentially OLTP servers. I have a data warehouse in simple recovery mode with a single nightly ETL process. I'm not particularly fluent in log file management, but my inclination is the log and TempDB can go on a single data disk, leaving three disks available to stripe and maximize IO for the nightly ETL and, more importantly, day time reporting.

Is it reasonable to put the log and tempdb on a single disk and stripe three for the data or does the log file really need to be on a separate disk?


texts:
I have an assignment in my introduction to database course. I chose to try and do a database model to represent basic geometric figures and mathematical objects (functions, polynomes, etc). For example, my db should be able to store (partially) what you can make with a software like GeoGebra (https://www.geogebra.org/classic).

I am doing an ERD, but I have some problems representing relations and entities... The base of my model is the entity Point with three attributes label, x and y. I am having troubles representing figures that are solely composed of Point, but that can contain more Point, e.g.. polygons (composed of N > 3 points, and there can be points INSIDE the polygon area), ellipses (characterised by 3 points, but can contain more points on the edge), etc.

For example, how do I represent a polygon? Is it a weak entity? Can it have two relations (one for the characterisation and one for the content) ?

Here's an extract what I have right now :

I added the id identifiers because... Else there would be nothing inside my tables (which is bad I think).

So I am wondering if these entities should be weak, as they are identified by a set of identifiers of another entity? Can they have no attributes? And is it okay to have those two relations like so for each entity?

I guess what I would like to do is something like Ellipse(focalPoint1, focalPoint2, characterPoint) (3-tuple as compound key) and Has (label, focalPoint1, focalPoint2, characterPoint), but I do not know how to represent this.


texts:
I run multiple pool leagues and have been keeping the information in excel. I'm working on moving this to the web and using MySQL for the data. The largest amount of data is in the matches table, with information about every match played.

I have multiple leagues based on different games, straight pool, one pocket, 9 Ball & 8 Ball. There are a few fields specific to each game, like high run in straight pool but no others, break & run in 8 Ball and 9 Ball but not the other two, 8 on the break in 8 Ball but no others, etc.

I could put all of these fields in one table and have a field called 'game' which flags which game this record is about. Or, I could have a table for each game, with all and only the fields needed for that game.

The single table, if I go that route, will be small to start, but if I'm able to expand the number of places where I have these leagues the number of records could grow to 20,000 or more in a couple years.

Which way would be best?

P.S. I do not envision running queries that look at records of more than one game in the same query.


texts: Create INSERT INTO from SELECT in PostgreSQL

texts: How to create INSERT INTO statements, using SELECT statement? I need it because I want to delete 10k of records from a table, but if someday some of that record need to be restored I want to do that easily. I don't want to backup the whole table, because I just need to restore some of deleted rows.


texts: Unable to delete a SQL Server 2012 publication

texts:
I've set up a transactional replication for two databases, let's call them A & B.

Replication for the database A worked just fine and I was able to delete it afterwards without any issues.

Replication for the database B would error out and after checking the logs I found (Could not obtain information about Windows NT group/user 'DOMAIN\someaccount', error code 0x5.).

I was going to start fresh but now I am not even able to delete it. I am trying to remove a publication from the Publisher/Distributor (both using code and via Management Studio) and getting the same error.

What concerns me is database A had no issues whatsoever, and also the fact that DOMAIN\someaccount does not appear to own any objects or be referenced within the database at all.

Please help!


texts: Excluding clustered key columns from non-clustered indexes definitions

texts:
As I've read the bookmark or the reference of the non-clustered index to the table clustered index is the clustered index key itself. On the SQL Saturday event I've attended, one of the lecturers said it's always good practice to exclude the clustered key columns from the non-clustered index key definition and include clause, too.

I am wondering are there any negatives of doing so, for the following scenario:

Table A has the following columns: RecordID, QuestionID, Pts, PtsOf and many other.
I have created a non-clustered index like this:

CREATE NONCLUSTERED INDEX [IX_TableA_RecordID_QuestionID] ON [dbo].[TableA]
(
    [RecordID] ASC
   ,[QuestionID] ASC
)INCLUDE ([Pts],[PtsOf]);

in order to skip reading the clustered index when score is calculated per records and questions. The [RecordID][QuestionID] pair is the clustered index key.

If I change the index like this:

CREATE NONCLUSTERED INDEX [IX_TableA_RecordID_QuestionID] ON [dbo].[TableA]
(
    [Pts] ASC
   ,[PtsOf] ASC
);

The indexes is again used as before (it satisfies the search criteria) even for queries that are not referring the points columns:

SELECT [RecordID]
      ,[QuestionID]
FROM [dbo].[TableA];

SELECT [RecordID]
FROM [dbo].[TableA];

SELECT [QuestionID]
FROM [dbo].[TableA];

So, the engine is smart enough to see using the non-clustered index will reduce the reads and I know leaving it as the original definition is working, too, but some of the indexes are quite big and I think removing some of the keys can reduce their size.

What I am concerned about is that the first index rows were ordered by the bookmark and now they are not. I am wondering if and how this could harm the performance?


texts: Always being logged in as SA and not Windows account

texts:
First off i would like to appologise if i sound stupid as i do not have much knowladge of SQL Server.

I have recently started a project working with SQL Server 2008 R2. I have access to the database using Windows Authentication. Once logged in i can access all of the databases etc... My problem is, for my user DOMAIN/Glen i would like to add a default schema for one of the databases. I have changed the Default schema for 'DOMAIN\Glen' within the users section under the database in question however whenever i run a script without the schema prefix on the table it will not run.

I have been looking around and trying some things out and to my knowladge i am for some reason using the SA account and not the DOMAIN\Glen login.

If anyone could help me change my Loged in user from SA to DOMAIN\Glen that would be a great help.

Thanks

Boldonglen

EDIT

When i am accessing the database directly on the server it is logging me in as DOMAIN\Glen but when i am accessing it from my computer it is logging me in as SA.

EDIT 2

Also when i connect to SQL Server on the server normally i am getting the default schema of the one i have added however if i run SQL server on the server as an administrator i am getting the default schema of dbo.

Any Ideas?


texts: What can be done, if anything, to tune a SharePoint database?

texts:
SharePoint uses a single table (i.e. dbo.AllUserData) with columns like nvarchar1, nvarchar2, ..., nvarchar64 or int1, int2, ..., int16 (193 columns altogether) to store all items from all lists in all site collections.

Can creating new indexes, recomputing statistics, etc. help?
Let's forget for now that it very likely to lose Microsoft support for SharePoint installation modified like this.
Am I right in thinking that such a design makes all classic databases tuning techniques useless?
Has anyone ever tried to solve performance problems of SharePoint in any other way than adding more/faster hardware?


texts: SQL Server 2016 - ColumnStore Clustered Index vs. Non-ColumnStore Clustered Index

texts:
I just read the SQL 2016 Columnstore Index guide located here. We're going to have some rather large tables (hundreds of millions to billions of rows) in a SQL 2016 database that will be used for both OLTP and analytics.

These tables will be queried, primarily, one of two ways: 1) Users will retrieve rather small result sets based on specific discrete values in a Where clause (e.g., Where SubId = 'ABC'); 2) Users will retrieve larger results sets based on a date/time value ranges (e.g., Where ReadTime Between '2/1/2017' And '2/5/2017').

Since a columnstore index is better suited for scenerio #2 (I think), I'm considering making the clustered index non-columnstore (e.g., on SubId) for scenerio #1 and creating a non-clustered columnstore index (e.g., on ReadTime) for scenerio #2.

However, I'm not sure this is really any better than if I created a columnstore clustered index on ReadTime and a non-columnstore index on SubId.

I'm not sure how to make this decision.


texts: MySQL Plugin failed to install due to "undefined symbol"

texts:
For several years now I have used the MariaDB Audit Plugin on my MySQL databases.

I am now looking to start Upgrading my databases to MySQL 8, and so have just tried installing it onto MySQL 8.0.19, but am getting the following error:

Error Code: 1126. Can't open shared library '/usr/lib/mysql/plugin/server_audit.so' (errno: 2 /usr/lib/mysql/plugin/server_audit.so: undefined symbol: fn_format)

I tried downloading the latest version (audit 1.4.7 from the MariaDB 10.4.7 binaries) but it made no difference.
Downloading older versions of the plugin also made no difference.

Is this likely to be a MySQL problem or a Mariadb problem? And is there a way to get round this. Most of my databases are installed on Debian 9 or 10 servers.


texts:
I have a database with hundreds of queries created by many people over the years. Now I can investigate for each query if any SSRS report, application for the database or other queries are using each query manually, but as everyone can imagine this is penal labor...

So I found some articles in the web about unused tables or procedures.
So as list all queries with

SELECT * FROM sys.all_objects WHERE type = 'V' AND is_ms_shipped = 0

Can I somehow find out if this query is ever been called in a period of time?

E.g. since start which I can get via

SELECT sqlserver_start_time  AS LastSQLServiceRestart FROM sys.dm_os_sys_info

then I would take this date and list all views that aren't used in this time until now.


texts: SQL query to count with subtraction from count of other sql table

texts:
I have 2 tables as below I column is common to both. Table 2 has FK from Table 1.

I need to get the count of ID_Tag per BoxNo as result. Condition

List all BoxNo and subtract the count if the ID_Tag appears in table 2.
If BoxNo has 0 tags after subtraction do not display in the result

SQL Query I have tried

 Select tb1.BoxNo,
        Count(tb1.ID_Tag) - Count(tb2.DelID_Tag) As NumberOfTags
 From Table1 tb1
   Left Outer Join Table2 tb2 on tb1.ID_Tag = Tb2.DelId_Tag
 Group By tb1.BoxNo

I get the list of all the BoxNo with the count from Table1.


texts: pg_stat_statements not found even with "shared_preload_libraries = 'pg_stat_statements'

texts:
I have followed the instructions given below:

http://www.postgresql.org/docs/9.3/static/pgstatstatements.html

... to the effect of adding the following lines:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

In file postgresql.conf and then restarting the server but the table pg_stat_statements is still not visible:

$ cat /usr/share/postgresql/9.3/postgresql.conf | grep -A3 ^shared_preload_libraries
shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
pg_stat_statements.max = 10000
pg_stat_statements.track = all

$ sudo /etc/init.d/postgresql restart
* Restarting PostgreSQL 9.3 database server          [ OK ]

$ psql -U postgres
psql (9.3.10)
Type "help" for help.

postgres=# select count(*) from pg_stat_activity;
count
-------
      1
(1 row)

postgres=# select count(*) from pg_stat_statements;
ERROR:  relation "pg_stat_statements" does not exist

update

After executing:

sudo apt-get install postgresql-contrib-9.3

And then doing:

$ psql -U postgres
psql (9.4.5, server 9.3.10)
Type "help" for help.

postgres=# create extension pg_stat_statements;
CREATE EXTENSION
postgres=# \dx
                                     List of installed extensions
         Name        | Version |   Schema   |                        Description
--------------------+---------+------------+-----------------------------------------------------------
 pg_stat_statements | 1.1     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 (2 rows)

 postgres=# \quit
$ sudo /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.3 database server  [OK]

... I now get:

postgres=# select * from pg_stat_statements ;
ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

system details

I am running on Ubuntu 14.04.03 LTS. PostgreSQL was installed with apt-get install.

log trace of PostgreSQL during a restart

While doing a sudo /etc/init.d/postgresql restart I get the following log trace:

$ tail -f /var/log/postgresql/postgresql-9.3-main.log
2015-12-21 11:11:31 EET [23790-2] LOG:  received fast shutdown request
2015-12-21 11:11:31 EET [23790-3] LOG:  aborting any active transactions
2015-12-21 11:11:31 EET [7231-1] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23903-5] postgres@postgres FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23822-7] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23795-2] LOG:  autovacuum launcher shutting down
2015-12-21 11:11:31 EET [23815-1] esavo-user@RegTAP FATAL:  terminating connection due to administrator command
2015-12-21 11:11:31 EET [23792-1] LOG:  shutting down
2015-12-21 11:11:31 EET [23792-2] LOG:  database system is shut down
2015-12-21 11:11:32 EET [16886-1] LOG:  database system was shut down  at 2015-12-21 11:11:31 EET
2015-12-21 11:11:32 EET [16886-2] LOG:  MultiXact member wraparound  protections are now enabled
2015-12-21 11:11:32 EET [16885-1] LOG:  database system is ready to accept connections
2015-12-21 11:11:32 EET [16890-1] LOG:  autovacuum launcher started
2015-12-21 11:11:33 EET [16892-1] [unknown]@[unknown] LOG:  incomplete startup packet


texts: Why use multiple full & differential backups when I can use transaction log backups?

texts:
I'm new to DB backup. I just learned about backup types from this question(Link). After I read the differential backup is cumulative since the last full, I got curious if multiple full and differential backups are not mandatory.

For example, I have backups like this:

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
diff_backup_2018_05_09_001500.bak
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
diff_backup_2018_05_09_003000.bak
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
diff_backup_2018_05_09_004500.bak
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
full_backup_2018_05_09_010000.bak
...

What's the difference between this and using one full backup and transaction log backups like this?

full_backup_2018_05_09_000000.bak
tran_backup_2018_05_09_000500.trn
tran_backup_2018_05_09_001000.trn
tran_backup_2018_05_09_001500.trn
tran_backup_2018_05_09_002000.trn
tran_backup_2018_05_09_002500.trn
tran_backup_2018_05_09_003000.trn
tran_backup_2018_05_09_003500.trn
tran_backup_2018_05_09_004000.trn
tran_backup_2018_05_09_004500.trn
tran_backup_2018_05_09_005000.trn
tran_backup_2018_05_09_005500.trn
tran_backup_2018_05_09_010000.trn
...


texts: Datediff SECOND with DATETIME type and rounding returns questionable results

texts:
I am puzzled by the following behaviour in SQL Server (tested on SQL Server 2017).

The goal is to return the number of seconds between two different datetime values. The two datetimes typically lie pretty close to another (differences measured between 0.4 and 2 seconds).

I started with the simplistic expression DATEDIFF(SECOND, startDateTime, endDateTime) and assumed that it might return 0 seconds for up to 499 (in fact due to precision 497) milliseconds and round up to 1 second for values starting at 500 milliseconds.

I read the MSDN Documentation but didn't get any wiser. It mentions something about "datepart boundaries" stating that "Those dates are adjacent and they differ in time by a hundred nanoseconds (.0000001 second). " I have milliseconds in difference. Does that also qualify as "being adjacent"?

However this is not how it works. It seams that if a boundary to the next minute is crossed the value of DATEDIFF(SECONDS...) always yields 1 while for the same interval all within one minute the seconds yield 0. Please have a look at rows with id 8 and 9 as well as 10 and 11 in the following result set:

Here's the code for the example:

WITH cteValues AS
(
    SELECT
        id,
        CAST(startDateTime AS DATETIME) AS startDateTime,
        CAST(endDateTime AS DATETIME) AS endDateTime
    FROM
    (
        VALUES
        (   1,'20200202 23:56:19.737',  '20200202 23:56:21.723'),
        (   2,'20200202 23:56:25.690',  '20200202 23:56:27.297'),
        (   3,'20200202 23:56:31.453',  '20200202 23:56:32.753'),
        (   4,'20200202 23:56:36.940',  '20200202 23:56:37.533'),
        (   5,'20200202 23:56:37.353',  '20200202 23:56:37.927'),
        (   6,'20200202 23:56:38.653',  '20200202 23:56:39.213'),
        (   7,'20200202 23:56:42.630',  '20200202 23:56:43.163'),
        (   10,'20200203 06:34:50.900', '20200203 06:34:51.363'),
        (   8,'20200203 06:34:50.100',  '20200203 06:34:50.600'),
        (   9,'20200203 06:34:50.800',  '20200203 06:34:51.300'),
        (   11,'20200203 06:34:50.100', '20200203 06:34:50.563')
    ) t (id,startDateTime, endDateTime)
)
SELECT
    id,
    startDateTime,
    endDateTime,
    CAST(endDateTime-startDateTime AS TIME) AS resultCast,
    DATEDIFF(SECOND, startDateTime, endDateTime) AS resultDatediffSeconds,
    ROUND(DATEDIFF(MILLISECOND, startDateTime, endDateTime) / 1000.0, 0) AS resultSecondsExtractedFromMilliseconds,
    DATEDIFF(MILLISECOND, startDateTime, endDateTime) AS resultDatediffMilliseconds
FROM
    cteValues
ORDER BY
    resultDatediffMilliseconds DESC

Play for yourself in this SQL Fiddle.

Why is that so? It is astonishing that the same command with the MILLISECOND option returns a precise amount of seconds but with the SECOND option it does not.

Which way would you recommend to write the second calculation if one cares about accurately rounded results and is open for the best performing solution?


texts: LSN numbers query regarding Differential backups

texts:
We have the following backup regime in place which I am not 100% sure about.

Full backup each night at 18:00 taking approx. 6 hours
Transaction log backup upon completion of full generally about 10 mins.
Differential backups each hour taking approx. 20 mins.

When querying the msdb.backupset table, I understand that the first_lsn will only be reset by a transaction log backup, but am slightly confused that the last_lsn entry is incremented each time the differential backup runs.

This seems to imply that the differential backup also contains transaction log entries (which I don't believe it does).

Is this entry in msdb.backupset just to give information to a restore to which transaction log backup to use to preserve the chain of transactions ?

Finally, if we only have a transaction log backup once a night and differentials throughout the day, we are still looking at potentially a maximum of 24 hours data loss despite hourly differentials.

Apologies if this seems like a lot of questions but trying to get this clear in my head.

Thanks for listening.


texts: Most efficient method to import bulk JSON data from different sources in postgresql?

texts:
I need to import data from thousands of URLs, here is an example of the data:

[{"date":"20201006T120000Z","uri":"secret","val":"1765.756"},{"date":"20201006T120500Z","uri":"secret","val":"2015.09258"},{"date":"20201006T121000Z","uri":"secret","val":"2283.0885"}]

Since COPY doesn't support JSON format, i've been using this to import the data from some of the URLs:
CREATE TEMP TABLE stage(x jsonb);

COPY stage FROM PROGRAM 'curl https://.....';

insert into test_table select f.* from stage,
   jsonb_populate_recordset(null::test_table, x) f;

But it is inefficient since it creates a table for every import and it imports a single url at a time.
I would like to know if it is possible (through a tool, script or command) to read a file with all the URLs and copy their data into the database.


texts: Is the "load data infile" command in MySQL unsafe in general, or just when using statement based replication?

texts: I'm also trying to understand why MySQL considers "load data infile" unsafe and switches to RBR if running in mixed mode, does that mean it is only unsafe when using SBR?


texts: MYSQL Server shows it is master server in replication. How to turn off this?

texts:
I'm using Ubuntu 20.04 version. I have installed MySQL server using apt-get install mysql-server and used mysql_secure_installation for security. Then I have installed phpMyAdmin using apt-get install phpmyadmin.
Opening phpMyAdmin's status tab shows This MySQL server works as master in replication process

Can anyone help me to stop this replication process?
I found nothing about this replication in my.cnf configuration file.
my.cnf file
The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/


texts: Will frequent small select queries slow down frequent write queries in InnoDB table in MySQL?

texts:
I'm running a MySQL database with tens of tables in remote server. To monitor the data, I write a small program to query to database via VPN connection every 10 seconds. Each query is a union of 20+ select sub-queries from 20 different tables with the same structure. A server program receives data from external sources and writes thousands of records each second to a table I don't query.

What's the impact of my queries to the database? What if I query every second? Do they slow down the whole database notably or slow down the writing of the table I don't query?

Since the remote database is working in a production system, it is not allowed to stop working. Do my queries create any risk that might shut down the whole database?