texts: How do I make PostgreSQL log the file path and line number of the errors? (If it's even possible at all.)

texts:
My postgresql.conf contains this:
log_destination = 'csvlog'
logging_collector = on
log_directory = 'C:\\Users\\John Doe\\Documents\\PostgreSQL logs'
log_filename = 'PG_%Y-%m-%d_%H;%M;%S'
log_rotation_age = 1min
log_rotation_size = 0
log_truncate_on_rotation = on
log_min_error_statement = 'info'

This makes PG log its errors to CSV files in my custom dir.
Then, I have a constantly running script which looks for new files in that dir, and if it finds any, COPYies it into a custom database table (as described/recommended in the manual) and deletes the file that was successfully added. It doesn't touch the currently active log file.
My table only has one custom column, a boolean called "unimportant", which I set depending on whether I think the error is important or not, so that I can hide the noise in various views and statistics.
Since PostgreSQL does not provide any fields such as "file path" or "line number", I'm at a total loss as to what caused the various logged errors. The only thing I have to go by is the application_name, which is uselessly limited due to being 64 characters maximum and no Unicode, even preventing me from abusing this field to, for example, set it to the relevant file path. But even if it were possible, I wouldn't want to do that, since application_name is supposed to be the application name and nothing else. (But again, it doesn't matter since the path would be too long and contain non-ANSI characters anyway.)
I've thought long and hard about this, but I just can't figure out a way to make me able to know where exactly the error occurred.
Application (PHP) errors will include the relevant location, but these errors are not always logged (I'm unsure why). Here are some examples of errors which don't trigger PHP errors but are logged by PostgreSQL:

there is no transaction in progress for the query COMMIT in application_name whatever123. Okay? Which script did it? And which line?
there is already a transaction in progress for the query BEGIN in application_name whatever123. Okay? Which script did it? And which line?
current transaction is aborted, commands ignored until end of transaction block and deadlock detected ones at least specify what query caused it, but again, I'd have to hunt it down manually by searching for parts of the query. Usually, I don't bother, or am unable to because the query was constructed in code in an unsearchable way.

Is there really no way to solve this? Any idea why the above errors aren't considered "real" errors to PHP (or rather, the pg_*/pglib functions)? And can I control that somehow?


texts: SQL Server 2008 Express backup and restore to test

texts:
We have a database system currently in SQL Server 2008 Express that we want to copy to a SQL Server 2012 server for testing. Having never moved a SQL Server database I have a few questions.

Can I take a full backup without taking the database offline?
Our database is 6Gb, however our .LDF file is over 200Gb. Can I take just a backup of the data and not bring over the transaction log?
Is the right click Backup in SSMS better/worse than doing BACKUP DATABASE in T-SQL?

Just making sure I have my ducks in a row since I don't have a current test DB with any real data to test the process. Thanks!


texts: Postgres - Trying to backup Functions only

texts:
I'm trying to follow Craig Ringer's answer to this question:
https://stackoverflow.com/questions/13758003/how-to-take-backup-of-functions-only-in-postgres

My command is:

psql -U username -AT db_name -c "
    SELECT pg_get_functiondef(f.oid)
    FROM pg_catalog.pg_proc f
    INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
    WHERE n.nspname = 'public'
" > /dump/file/functions.sql

But I'm getting the following error:

Error: "st_extent" is an aggregate function

Why doesn't it like aggregate functions? And what can I do to get around this problem?


texts:
From this question I was able to make my  init_connect =  "CALL mydb.Warnings()", but a user login in does not triggers the SP.

the SP is like:

CREATE DEFINER=`root`@`localhost` PROCEDURE `Warnings`()
BEGIN
select * from mydb.log;
END

The user 'Tk3' is able to call the SP with success, but it ain't allowed to do Selects.
I did check with show global variables like 'init_%'; and the correct value is set.

I'm missing the reason for this failure, could you help?

1st Edit:
After the @danblack and  @Rick-James suggestions I altered the SECURITY to:
CREATE DEFINER='tk3'@'localhost' PROCEDURE 'warnings'()
but the behavior is the same.
:( I'm lost ):

2nd Edit:

    MariaDB [(none)]> show create procedure logdatabase.warnings;
    +-----------+--------------------------------------------+----------------------+
    | Procedure | sql_mode
    |Create Procedure
    |character_set_client | collation_connection | Database Collation |
    +-----------+--------------------------------------------+----------------------+
    | warnings  | NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

    CREATE DEFINER=`tk3`@`localhost` PROCEDURE `warnings`()
    BEGIN

    SELECT * FROM logdatabase.log_activity;
    END
    | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
    +-----------+--------------------------------------------+----------------------+
    1 row in set (0.00 sec)

MariaDB [(none)]> show grants for 'tk3'@'localhost';
+------------------------------------------------------------------------------+
| Grants for tk3@localhost                                                     |
+------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'tk3'@'localhost'                                      |
| GRANT SELECT, CREATE, EXECUTE, CREATE ROUTINE, ALTER ROUTINE ON `logdatabase`.* TO 'tk3'@'localhost' |
+------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

I'm not showing the SHOW CREATE USER because it's returning an error:

MariaDB [(none)]> CREATE USER 'mytest'@'localhost';
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show CREATE USER 'mytest'@'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'USER 'mytest'@'localhost'' at line 1

(this print was made with 'root' - I will deal with this strange thing later)


texts: How to normalize a table that contains a CK not included in the PK?

texts:
I have the following table. The PK is model_ID.
Assuming that the modelName field is unique(that is, no two brands can have an identical modelName), there is a transitive dependency between modelName and brand.

What is the optimal solution for the table to comply with 3NF, without having the modelName field as part of the PK?


texts:
What happen with the active transaction log when I stop SQL Server? Under the stop process all transactions will write to the db? Or will put when the db will starting...

Is it possible when I stop the SQL Server that not all transactions will be written into the db? Uncommitted transaction will stay in the transaction log and / or transaction log backup only?


texts: What is the strict difference between key and constraint in SQL Server?

texts:
In SQL Server, the 'keys' folder includes primary, foreign and unique keys, while the 'constraints' folder, for example, check and default. But unique can also be considered a constraint, so why it is kept under the 'keys' folder in SQL Server? Is it because it represents alternate keys in logical modeling?

I want to know the strict definition of constraint and key which I cannot find. Thanks.


texts:
There are structures:

CREATE TABLE `invoices` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `invoices` VALUES (1,'2018-09-22');

CREATE TABLE `products` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`quantity` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `products` VALUES (1,1,150.00,2),(2,1,60.00,3),(3,1,50.00,1);

CREATE TABLE `payments` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`invoice_id` int(10) unsigned NOT NULL,
`amount` decimal(10,2) unsigned NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `payments` VALUES (1,1,400.00,'2018-09-23'),(2,1,80.00,'2018-09-23');

I have this query:

select i.id, sum(pr.amount * pr.quantity) as productAmount,
sum(pm.amount) as paymentAmount
from invoices as i
left join products as pr on pr.invoice_id=i.id
left join payments as pm on pm.invoice_id=i.id
group by i.id

and have this result:

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |       1060.00 |       1440.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

However, I want to get the following result:

+----+---------------+---------------+
| id | productAmount | paymentAmount |
+----+---------------+---------------+
|  1 |        530.00 |        480.00 |
+----+---------------+---------------+
1 row in set (0,00 sec)

I want sum amount of products and sum amount of payments grouped by invoice.id.
What should be the query in this case?


texts:
I'm looking to create a database (using Django) where:

A user uploads photos.
A user upvotes/downvotes photos of other users.

I came up with this ER diagram:

My questions are:

Is a vote better as an entity or as an attribute of the photo?
If a vote should remain an entity, how do I show in its attributes that the primary key of the vote is the combined foreign keys of the user and the photo?

Any constructive comments/recommendations are also welcome.


texts: Stream every row coming from a Postgres function

texts:
This is the how the function definition looks:
CREATE OR REPLACE FUNCTION my_function(foo NUMERIC)
RETURNS SETOF my_type AS $$
DECLARE rec record;
BEGIN
    FOR rec IN SELECT * FROM my_table WHERE my_table.bar > foo
    LOOP
        RETURN QUERY SELECT
            my_scalar_function(rec.bar1),
            my_scalar_function(rec.bar2),
            my_scalar_function(rec.bar3);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

On the client i am trying to use a QueryStream from the pg-query-stream package:
pool.connect((err, client, done) => {
  if (err) throw err
  const query = new QueryStream('SELECT * FROM my_function($1);', [123])
  const stream = client.query(query)
  stream.on('end', ()=>{
    console.log('finished')
    done()
    process.exit(0)
  })
  stream.pipe(JSONStream.stringify()).pipe(process.stdout)
});

But still i am getting all rows at the end of the function and no intermediate rows. I would like to stream every row when it is calculated in the my_function.


texts: SSIS export to flat file - how to configure all DataTypes for columns w/o manual entry?

texts:
In my OLE DB Source to Flat File Destination Data Flow, I have quite a few columns and it's excruciating to manually set each one to decimal with a scale of 4.

I tried adjusting my SQL code in the OLE DB Source to CAST to decimal(14,4) to see if SSIS would pick that up in Suggest Types, but it likes single-byte signed integer.  I don't see any option to set default column DataTypes.  Is there another way - either by adjusting my SQL code, or within SSIS options - to get all of my columns to decimal, scale 4?


texts: MySQL in Ubuntu Linux - I cannot connect to the server

texts:
I am new in MySQL. I installed Workbench and server. When i try to connect to the database server i receive a message: Your connection attempt failed for user 'root' to the MySQL server. Ann AppArmor policy prevents this sender from sending this message to this recepient; type .
enter image description here
enter image description here


texts: What is the correct way of filtering a property by multiple features?

texts:
Having the following tables:

Property
Feature
PropertyHasFeature (property_id, feature_id)

What is the right way to search properties by specifying multiple features? I want to list all properties that have both Feature A and Feature B.

Right now my approach is to join the Property table two times and to add two conditions, like this:

INNER JOIN Feature as feature_1
ON feature_1.property_id=Property.id

INNER JOIN Feature as feature_2
ON feature_2.property_id=Property.id

WHERE feature_1.id = <ID of first feature> AND feature_2.id = <ID of second feature>

Is this the right way? So if I the users selects 10 features I will have to do 10 joins with the Feature table? Or is there a better way?


texts: MySQL Master 5.5 - Slave 5.6 - unknown system variable 'binlog_checksum', Error_code: 1193

texts:
I see the below warning on the my slave server:

2016-11-18 22:48:03 12808 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2016-11-18 22:48:03 12808 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.018098' at position 920330293, relay log '/mnt/data/mysql/mysqld-relay-bin.318142' position: 473446279
2016-11-18 22:48:03 12808 [Note] Slave I/O thread: connected to master 'repl@xxx:3306',replication started in log 'mysql-bin.018103' at position 271452699
2016-11-18 22:48:03 12808 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Error_code: 1193
2016-11-18 22:48:03 12808 [Warning] Slave I/O: Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.42-log), even though it is on the slave (version: 5.6.31-log), Error_code: 1193

From what I understand it is happening when master is not compatible with the 5.6 slave, is it correct ?
Should I ignore these warning ?
if not How do I fix this without damage anything the MYSQL MASTER ?

Master version:

Variable_name            Value
-----------------------  ------------------------------
innodb_version           5.5.42
protocol_version         10
slave_type_conversions
version                  5.5.42-log
version_comment          MySQL Community Server (GPL)
version_compile_machine  x86_64
version_compile_os       Linux

Slave version:

Variable_name            Value
-----------------------  ------------------------------
innodb_version           5.6.31
protocol_version         10
slave_type_conversions
version                  5.6.31-log
version_comment          MySQL Community Server (GPL)
version_compile_machine  x86_64
version_compile_os       Linux

Thanks !


texts: Preventative measures to avoid wrong cardinality estimate statistics

texts:
This is a follow-up to Queries randomly getting slow, good preventive practices?, attempting to ask a more specific question about one of the ideas stated there.
A common cause of queries suddenly becoming slow after a long history of being fast is outdated cardinality estimates. SQL Server takes advantage of cardinality estimates to figure out a query plan that will be the fastest to answer a given query. If a query that usually takes few minutes suddenly bursts to tens of hours because of outdated cardinality estimates, it might negatively affect business uses before support staff can respond.
Are there any measures that could be applied preemptively to find and fix outdated cardinality estimates before a query is executed? Are there any specific tools that could be periodically executed to go over estimates and fix them, or maybe some metrics to be observed that would indicate how good are the estimates in a database?


texts:
As I'm new in using RMAN and archivelog, I've a few doubts regarding to this. My database is Oracle 11g(windows platform). It was previously in noarchivelog mode and recently I've changed it to archivelog mode to backup my data and it started generating archived redo logs in the ARCHIVELOG directory. Each day almost 7 or 9 GB archived redo logs are generating.

Also, I have set my recovery destination and size as 50G and recovery window parameter as 4 days. I know that archive redo log files help me for the point on recovery.

But I am not getting a clear idea about below.

Do I need to delete archivelog mode or will it automatically get cleared?
If I move these archivelog files to other location or disk as backup, should it help me to recover data till that date?
If I need to restore a test database using the production database, is it possible with these archive files? Does it need any other processing?

Thanks in advance.


texts: Trouble migrating a MySQL database to MS SQL 2012 using the migration assistant

texts:
I am trying to migrate a MySQL server database to Microsoft SQL Server 2012. I converted the schema and synchronized the databases, but when I try to do the migration, it doesn't work and I get this when it's done:

I don't think it even tried to migrate anything. I have no idea what I could have missed. Has anyone seen this behavior before?

Thanks!


texts: What does MariaDB use for an encryption initialisation vector?

texts:
I have been using MySQL 5.6 in development environment and MariaDB 5.6 in production until I needed to use encryption.  MySQL 5.6 has multiple encryption modes and allows AES-256-CBC. MariaDB 5.6 only uses ECB.
So, I was working towards an upgrade to MariaDB 10 and noticed that the AES_ENCRYPT() and AES_DECRYPT() functions don't use an initialization vector (IV).  This is strange since CBC mode requires an IV.  In MySQL 5.6 and up these functions use an IV (see chart lower down in MySQL documentation).  Why doesn't MariaDB use an IV?  What does MariaDB 10 use as an IV for CBC mode?
I just upgraded to MariaDB 10 and comparing the AES_ENCRYPT() function to MySQL 5.6. I noticed that it does not use an initialization vector in the function.  Why?  CBC mode requires an IV and so where does the IV come from for MariaDB?


texts:
I'm running a select query. On certain days it's using an incorrect execution plan. Other days it's using the correct execution plan. I have verified with (nolock) option, index stats, blocking, CPU, and memory load; everything is normal. Are there any other ways to check why it is using the wrong execution plan?

The query:

DECLARE @ProjectName as nvarchar
DECLARE @vehicleName as nvarchar
DECLARE @fromDate as datetime2
DECLARE @toDate as datetime2

select
    d.DDSEventId, d.PrjName, d.VehicleName, d.StartTime, d.EndTime,
    e.Name, s.Value, es.Name, s.DDSSignalId, e.ProcessId, e.EventId,
    d.VehiclePos, e.DefaultDescription, es.DefaultDescription,
    d.CarName, d.ExclusionState
from
    DDSEvents d
inner join
    Events e on d.DiagnosticConfigurationId = e.DiagnosticConfigurationId
             and d.EventId = e.EventId
             and d.ProcessId = e.ProcessId
left join
    DDSSignals s on d.DDSEventId = s.DDSEventId
                 and s.StartTime = d.StartTime
left join
    EnvironmentSignals es on s.EnvironmentSignalId = es.EnvironmentSignalId
where
    d.PrjName = @projectName and
    d.VehicleName = @vehicleName and
    d.StartTime >= @fromDate and
    d.StartTime < @toDate

Correct plan

Wrong plan

I have declared the same variables for both execution plans.


texts: How to design a table that each rows have 5K boolean attributes?

texts:
I have about 2M rows and each row looks like the following.

244 true false ... true

-> One integer column(V) and about 5K boolean columns(B1, B2, ..., B5K) associated to the integer.

Due to the limitation of the maximum number of columns that I can have for a row, I have separated the boolean columns(attributes) in a separate table.

Table V:

idx_V value_V
--------------
1     244

...

Table B:

idx_V idx_B value_B
--------------------
1     1     true
1     2     false
...
1     5K    true
...

This design works alright when I try to find V's that match one boolean column. For example, finding V's where the 2nd boolean attribute is true:

select value_V
where VT.idx_A = BT.idx_A
    and idx_B = 2
    and value_B = true
from V_Table as VT
    and B_Table as BT

But the query becomes awful when I have to find V's that match a multiple boolean columns, sometimes even for all 5K columns, like finding V's with B1=true, B2=false, B3=true, ... and B5K=false.

My primary use of the tables would be the following 2:

Find V's that x1th, x2th and xnth boolean columns are false/true (n can be anything between 1 and 5K)
Sublists:

Find the sequence of the boolean columns for a specific V: T F T T F F ...
Find other V's that match the sequence found in 2-A

I'm thinking about constructing a varchar[5K] field to store the boolean sequence to do 2 but it seems like there's just too much waste in space since each boolean only requires just 1 bit but I'm allocating a byte.

What would be the best way to go about this?


texts: unknown variable after upgrading from mySQL 5.7 to 8

texts:
After upgrading from mySQL 5.7 to 8.0 on freebsd, I keep getting the follow error, which prevents mysql to start:
[ERROR] [MY-000067] [Server] unknown variable 'innodb_large_prefix=true'.

The thing is that setting is not on my my.cnf file. I understand it is deprecated on mySQL 8, but it is not on my configuration.
Is there a way to fix it?
Best,
Francis


texts:
How can i get my out parameters from my procedure.
My  stl_mbc_cdr table has over 100 records and i cant see my results.

declare
   call_charge VARCHAR(10);
   MSG_OUT     VARCHAR(50);
BEGIN
   FOR REC IN (SELECT *FROM dulguunbaatar.stl_mbc_cdr_1062) LOOP
   pk_test_duba.recharge_idd(REC.Called_Number,
                               rec.call_date||rec.call_time,
                                                         rec.duration,
                                                         CALL_CHARGE,
                                                         MSG_OUT);
    END LOOP;
end;


texts: Sending email from SQL Server without using DBMail

texts:
Is there a way to send email from SQL server stored procedures without using DBMail? DBMail is hard coded by default to use base64 encoding for email messages. As far as I can tell there's no way to override this.

We need to parse/process messages on our mail server and base64 encoded messages can't easily be parsed. Rather than trying decode them we'd like to send email using some other method.

Preferably one that doesn't use CDOSYS or CDONTS either? Why? Because we're migrating to Windows Server Core and Server Core doesn't support CDOSYS or CDONTS.

See here: https://technet.microsoft.com/en-us/library/hh922965.aspx

Specifically This:

However, before you attempt to host a Web site on Server Core, it’s worth noting
code that makes use of System.Web.Mail will fail because of the dependency on
CDOSYS (which does not exist in Server Core).

Microsoft recommends modifying Web application code to use System.Mail.Net
instead of System.Web.Mail.

This is refering asp/.net code on a web server but presumably the same restriction applies to SQL server installed on a Core server.

So with those two limitations in mind (no dbmail, and no cdonts/cdosys) how can we send mail from stored procedures? Any ideas? :)

Thanks!
Brad


texts:
I've been having a big mysql performance problem for a while now after a migration from a dedicated server to a cloud VM.

The issue ultimately requires production load to test, so it has been difficult to control all the variables. However, I have not been able to find any recommendations that have had a significant positive effect on our performance.

Azure support recommended creating a RAID array, a handful of innodb config changes, and a change to process limits and disk scheduling algorithm. This was done on test but it was difficult to measure any difference in speed.  At some point while trying to develop a fully fleshed-out process for deploying these changes to production, the test server started exhibiting similar behavior. As a temporary fix, a feature which depended on a slow query was removed.  This brought the test server back to normal speed, but only got the production server about 1/2way there.

Since nothing is improving, I'm tempted to just assume that Azure is inherently slower, but that doesn't make sense with the discrete drop in performance the test server exhibited... as if it "caught" whatever was wrong with production.  Unfortunately I can't remember all the details of the state of the server before that happened.

Another issue is that we don't have a test server that's the same size as production, so we can only make a 4-disk raid array, and it's hard to tell how a performance difference on the 4-disk array would compare to a 16-disk array.  However since most of our DB should be in memory at any time, I don't really know that IO is the issue.  So I don't necessarily see this being a very productive line of investigation.

The way we are using our server is sort of queue-like which I know is frowned upon, but it will be difficult to rebuild this system in any other way in the immediate future.  I've got an idea that maybe the Microsoft MySQL optimization recommendations do not take this sort of usage into account .

Here's the relevant parts of the DB config:

key_buffer_size         = 64M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_stack            = 512K
thread_cache_size       = 16
myisam_recover_options  = BACKUP
max_connections        = 200
table_open_cache        = 4096
table_definition_cache  = 4096

query_cache_limit       = 1M
query_cache_size        = 64M

slow_query_log = 1
slow_query_log_file  = /var/log/mysql/slow-log

log_slow_slave_statements

long_query_time = 10

server-id               = 732807

log_bin                 = /var/log/mysql/bin-log
relay_log_space_limit = 16G
expire_logs_days        = 10
max_binlog_size         = 100M

binlog_format                    = MIXED

skip_name_resolve
sql_mode=NO_ENGINE_SUBSTITUTION

sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M

tmp_table_size = 64M
max_heap_table_size = 16M
max_heap_table_size = 64M

back_log = 100
max_connect_errors              = 10000
interactive-timeout             = 3600
wait-timeout                    = 600
bind-address                    = 127.0.0.1

### Storage Engines
default_storage_engine         = InnoDB
#innodb                          = FORCE

## MyISAM

myisam_sort_buffer_size         = 128M

## InnoDB

innodb_buffer_pool_size        = 5G

innodb_log_file_size           = 100M
innodb_file_per_table          = 1
## Replication

## Logging

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

no-auto-rehash  # faster start of mysql but no tab completition

[isamchk]

key_buffer_size         = 16M

Any advice on config, or more tips for debugging would be appreciated.


texts: MariaDB Encryption - unknown variable 'plugin-load-add=file_key_management.dll'

texts:
I'm really new to MariaDB and I need to set up encryption at rest. I'm running MariaDB 10.3 on Windows Svr 2012R2.

I have created a key and added the following to my config file (my.ini)

plugin-load-add=file_key_management.dll
file-key-management
file-key-management-filename = "C:/MariaDB/data/keys.txt"
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4

The service starts fine. However when I go to open the MySQL Client I get the following error:

  C:\Program Files\MariaDB 10.3\bin\mysql.exe: unknown variable 'plugin-load-add=file_key_management.dll'

The dll is present and in the plugin-dir location specified in the config file.

Can anyone help me please?


texts: How to upgrade PostgreSQL 9.6 to 10 in Ubuntu 16.04?

texts:
Is there any simple apt-get version?

Some people here said the long process was OK for them.
 (https://gist.github.com/delameko/bd3aa2a54a15c50c723f0eef8f583a44)


texts: Find order that has deliveries in multiple weeks

texts:
I thought I had a simple run of the mill problem, but I can't manage to figure out how to slice it.  Assume I have a table of Deliveries that has the OrderNumber, the DeliveryNumber and the DeliveryDate.  The question I want to ask is "Which OrderNumbers have DeliverDates that all aren't in the same week"

Here is a test script.

DECLARE @DeliveryTable Table
(
    OrderNumber INT,
    DeliveryNumber INT,
    DeliveryDate Date
)
;

INSERT INTO @DeliveryTable
(OrderNumber, DeliveryNumber, DeliveryDate)
VALUES
(1,300, '2020-04-27'),
(1,301, '2020-04-28'),
(1,302, '2020-04-30'),
(4,730, '2020-04-26'),
(4,731, '2020-04-28'),
(4,732, '2020-05-05')
;

My query would return OrderNumber={4} because the '2020-05-05' is not in the same week as '2020-04-28' and '2020-04-26'.  OrderNumber 1 would not be returned because all the deliveries are in the same week.

I've thought about using a LEAD/LAG function, but there can be an arbitrary number of Deliveries so I wouldn't know how far to LEAD/LAG.  I was thinking about using some type of join, but I don't know what I would join on other than ( ..DATEPART(WK, first.DeliveryDate) <> DATEPART(WK, second.DeliveryDate)..) but that seems hamfisted to do over 1.4 million rows.


texts: Number of times a table is read, written to and updated

texts:
Is there a way I can find out the number of times a table is read, updated, added to or has had rows deleted during a trace?

I am creating the trace in SQL using sp_trace_setstatus, not using the Sql Profiler UI.

I am using sp_trace_setevent and ::fn_trace_gettable to get my current stats, but cannot find out how to get the stats regarding the table.

I would like to run this trace on both SQL 2000 and SQL 2008 R2.


texts: MySQL reusable results as table with parameters

texts:
I used to use MySQL but nothing more complicated than JOINs, then I worked for a year at a company that uses MSSQL, where I learnt how to program stored procedures. My current company uses MySQL.

In MSSQL, you can use the result of a stored procedure as a table in a query, e.g.

SELECT *
FROM Table t
JOIN Proc(NOW()) p
  ON p.value = t.value

I've been trying to do the same thing in MySQL, but so far to no avail. MySQL doesn't let you use the result of a stored procedure in a query, and views don't take parameters, nor are they programmable.

The MySQL documentation says that stored functions only return scalar values, so that's out too.

Is there a way to get the same effect in MySQL?

I think one way might be to create a temporary table in the stored procedure. Call the stored procedure before running the query, then JOIN to the temporary table.


texts:
Morning all,
I'm trying to quickly compare multiple queries that do similar tasks and I'm wondering if there's a quicker way of lining up the EXPLAIN outputs. To me, this example query should work:

EXPLAIN SELECT count(*) AS thetotal, date(datetimefield) AS theday, key_id FROM dbname.tablename WHERE datetimefield >= "2015-12-12 00:00:00" AND datetimefield <= "2015-12-12 23:59:59" GROUP BY key_id UNION
EXPLAIN SELECT count(*) AS thetotal, date(datetimefield) AS theday, key_id FROM dbname.tablename WHERE DATE(datetimefield) = "2015-12-12" GROUP BY key_id, theday;

and output something like the following:

but as you probably know it gives a "Syntax error, unexpected describe" message. Is there any reason why it shouldn't, or a way to achieve a similar result? At the moment I'm running EXPLAIN on the two similar queries, exporting them to csv, opening both in a spreadsheet editor and pasting them one row after the other.

Thanks in advance for any assistance you can give me.


texts: Is there a Successor or an Alternative to mk-parallel-dump?

texts:
Just read about mk-parallel-dump in a possibly-outdated version of High Performance MySQL. Sounded great, but when I went to download it I found out that it's deprecated with no equivalent command in the Percona toolkit.

Are there other tools that do something similar-- ie fast database backup?


texts:
We are developing an application that loads data from clients and then processes it in some predictive analytics tasks. Each client has different data structure: some clients will for instance have very detailed website activity logs, others will not. We try our best to map their data model to ours.

Currently, we modeled our problem by defining "domains". A domain can be "Transactions", "Customers", "Events"... Each domain has a minimum set of columns that is required to link it to other domains or simply to make sense (each transaction always must have a timestamp and a customer ID, for instance). It then has "tags", types of which can be dynamically added, that represent extra columns that that specific client has.

We load data from the clients in chunks of a few gigabytes. Because an extract can have overlapping data on what has been loaded before, or even updates of previously loaded data, we need to be able to quickly link a record to the existing record (upsert). Would not be a big problem if each client had a defined ID for each entity of each of our domains. But this is not the case: most clients are not capable of providing a sound ID in 1 value.

On these cases, however, multiple columns are enough for a primary key. What I do then is that I take a hash of those columns and use it as a primary key. The fact that keys are randomly distributed creates a performance problem in a clustering key.

What kind of storage engine could I use to makes this efficient, while maintaining my capability of nicely querying it? With our current solution (innoDB), it takes hours to load a million rows on a computer with a classical hard disk. As a startup, we would be interested in a free or cheap solution.

The options we are considering right now:

Simply have a very slow data ingestion process. Once historical data is loaded, the DB only needs to be fast enough to absorb the incoming data each night.
Use a noSQL solution that would absorb the data faster and that would be able to run on commodity hardware. This would require a lot of work because the data must be queryable in a lot of different ways.


texts:
I want to shutdown one node in RAC. I did the following steps:

Shutdown database instance

export ORACLE_SID=mydb1
sqlplus / as sysdba
shutdown immediate;
shutdown ASM instance

. oraenv
+ASM1
sqlplus / as sysasm
shutdown immediate;

But shows the following error: ORA-15097: cannot SHUTDOWN ASM instance with connected client

Please, tell me what steps should I do to shutdown all services and instances on the server which is the part of the RAC?


texts:
I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start testing, though 5 seconds maximum.

If I do a single entry for each sensor at each second, I can get away with just 5 columns, entry_id, dim_id, dim_second, dim_date, sensorValue. However, that equates to (86,400 * 1000) * 31 or 2,678,400,000 rows in a month. That is a whole lot of rows.

If I had 1004 columns, I could do 86,400 * 31 or 2,678,400. That is a ton of columns.

Most of what I've read online tells me it is always undesirable to use columns in this way for RDS. However, if I were to do an entry per sensor, do I dump in a single fact table? Of course I wouldn't make 1000 tables, but should sensors be grouped together in fact tables, maybe by unit (flow, amperage, etc) to make the data set smaller for queries?

Or maybe RDS is not the proper choice for this application, and I should consider NoSQL? I have never worked with it.

I'm using a 16GB, Intel Core i7-4650u machine with a 1TB SSD for the development environment.

EDIT:
I should note that my data pipeline is summarizing this data and dumping it in other tables. The issue is that this has to be an on-prem solution, and I want to be able to bring back big batches of raw data for analysis. Hence the long period and small grain.


texts: MYSQL database how to check if database is active and on which port (Linux)?

texts:
I got the server and on it is some mysql database TEST.
I tried connected on it with my root account by using DBVisualizer but I keep getting could not connect check host name and port.

I tried with root account and also with TEST user account.
Their passwords are OK.

with command

mysql> show databases;

I found my TEST database.
I do not know any other parameters how to connect to database?
I suppose either root or test can connect to it.
How to check on which port is the database or does it have to be active?

I tried with netstat and this is my response? what  does it mean?

netstat -an | grep -i mysql
unix  2      [ ACC ]     STREAM     LISTENING     8596     /var/run/mysqld/mysqld.sock

Thanks


texts:
I am facing an issue that Even after creating a GIN INDEX execution plan is taking much time.

select email from customer where lower(email) like '%gmail';
CREATE INDEX idx1  ON customer USING gin  (lower(email) gin_trgm_ops);.

Index created as  "idx1" gin (lower(email::text) gin_trgm_ops)

explain plan gives as below

###
 Bitmap Heap Scan on customer  (cost=168.51..367.36 rows=194 width=22) (actual time=381.844..1807.673 rows=931488 loops=1)
   Recheck Cond: (lower((email)::text) ~~ '%gmail.com'::text)
   Rows Removed by Index Recheck: 556
   Heap Blocks: exact=53254
   ->  Bitmap Index Scan on idx1  (cost=0.00..168.46 rows=194 width=0) (actual time=372.263..372.263 rows=933383 loops=1)
         Index Cond: (lower((email)::text) ~~ '%gmail.com'::text)
 Planning time: 0.741 ms
 Execution time: 1988.646 ms
(8 rows)

Please let me know if this can be fine-tuned further


texts: How do cardinality estimates affect CPU and Reads in SQL Server?

texts:
Trying to understand a concept, not sure where to look for answers.  I have been researching cardinality estimates and their affects on memory.  In the process of optimizing a query to get better cardinality estimates for a stored procedure I discovered some interesting data.

There was a stored procedure that the team lead of our DBAs asked that I optimize.  This particular proc was causing significant issues with our server the DBA was not sure why, he had attempted to optimize it but without any success.  The issue was the simplicity of the procedure.  It was JOINing 4 tables and returning about 20 columns.

He provided me a spreadsheet that had the data of over 13,000 executions from a particular day.  This data included the parameters for each execution, the duration, CPU, and number of reads.  I optimized it and it went live 3 days ago.  Yesterday I asked for similar data for the day after it went into production so I could see if the optimization was working.  The real issue was the original proc ran really great in test, even using the parameters of the worst runs from the spreadsheet, it only had the issue when it was in production and running so many executions in a day along side all the other procedures that were running.  This procedure and the mystery (for us) was why I starting looking into the cardinality as all the JOINs were giving me some huge discrepancies on estimated and actual rows.  This lead to me asking this question to try understand more about how statistics work and yesterday I asked this question about memory grant.

My question today stems from the data on the executions from before and after optimization.  I have tested the old procedure on the test server and if 3 of the 4 tables' indices had bigger sample sizes then the procedure would not have needed more optimization.  I spent a lot of time manipulating JOINs with additional tables and creating several temp tables and indices on temp tables in order to get better cardinality estimates.

The new optimized procedure ran great.  It ran over 54k times in an 11 hour time span for the day in question.  The average duration of each run was much better but 'only' 34% faster.  I say 'only' because the CPU was 98% less usage and the reads were 95.5% fewer.  This is why I put in the previous story. Like I mentioned, the original proc ran fine in test for a one-off execution and when compared to the new procedure I was getting 15-20% improvement on CPU and reads for some of the better executions, several executions had the same CPU and a few had worse CPU, reads or duration.

The question is why, in a production environment with heavy executions, would cardinality estimates affect CPU and reads so much?  I am looking for technical reasons and this is going to be included, if there is a correlation, in a presentation I am doing next week to try and promote better sample sizes on our indices. If there is no correlation I can accept that as an answer, but it would be nice to still know what could possible cause such a change when all that was really improved was the cardinality.


texts: Comparing two different ODBC system-DNS entries. Which is faster? PS script

texts:
i want to find out which ODBC Connection is faster on my VM with Windows Server 2008 R2. I have two different ODBC providers. The first is "Oracle Instant Client ODBC" in v11. The other "Microsoft ODBC for Oracle". I thought, that i can use Powershell to test how long two simmilar querys run with each provider. (Later i also want to test if the MS Provider for Oracle is faster on Win08R2 or on Win12R2)

I found a nice script here: LINK

i modified it only a bit. So now i have tihs litte script:

 #SQL Authentication
 $t_start = Get-Date

 $connectstring = "DSN=instant;Uid=USER;Pwd=PASSWORD;"
 #Windows Authentication
 #$connectstring = "DSN=myDsn;"
 $sql = "SELECT * FROM TABLE1 WHERE ROWNUM <= 900;"

 $conn = New-Object System.Data.Odbc.OdbcConnection($connectstring)
 $conn.open()
 $cmd = New-Object system.Data.Odbc.OdbcCommand($sql,$conn)
 $da = New-Object system.Data.Odbc.OdbcDataAdapter($cmd)
 $dt = New-Object system.Data.datatable
 $null = $da.fill($dt)
 $conn.close()
 $dt | measure

 $t_end = Get-Date
 Write-Host ($t_end - $t_start)

It works fine for litte querys but the problem is that if i want to select more than 840 rows i get an error:

Exception calling "Fill" with 1 argument (s): "Input string was not in a correct format."
Line: 14 Sign: 2
    In Zeile:14 Zeichen:2
    +  $null = $da.fill($dt)
    +  ~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : FormatException

The result of the skript is:

Count    : 840
Average  :
Sum      :
Maximum  :
Minimum  :
Property :

00:00:01.4530713

If i change the SQL-Query to "ROWNUM <= 839" it works. If i change to "ROWNUM <= 841" i get this error.

My aim was to really make a big query like "ROWNUM <= 1.000.000"

The table i query has about 1.200.000 rows...

Is there a posibility to change something, so that i am able to query large tables? Or has anyone a nice freeware tool i can use to query ODBC-SYSTEM-DSN entries and get a usefull querytime so that i can compare my different ODBC's ?


texts: Can PostgreSQL UPDATEs in autocommit mode deadlock?

texts: Is it possible for UPDATEs in PostgreSQL to deadlock when in autocommit mode? If so, how, and why? (Or, why not?)


texts: Best option to back up mysql innodb partition

texts: We have a number of tables which we plan to partition based on months. The issue now before we can drop those partition we would like to perform backup. One option we found is this link http://www.percona.com/doc/percona-xtrabackup/howtos/recipes_ibkx_partition.html. Can any one suggest is the best mechanism or there other option such as mysqldump etc?


texts:
I'm trying to grant proper privileges for using DBMS_CRYPTO.

Whenever I call it from package it gives me error:
PLS-00201: identifier 'DBMS_CRYPTO' must be declared

User already has privileges:
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
SELECT ANY DICTIONARY

and EXECUTE on DBMS_CRYPTO to package_owner.

any ideas what am i doing wrong?


texts: How to select all rows from column including two specific numbers within a set of digits, but not in beginning or end?

texts:
I'm attempting to retrieve all rows that include two specific characters within a set of characters corresponding to the attributable column of data. However, the specified characters can't be included within the beginning or the end of that series of characters.

My attempt currently displays the specified characters but includes beginning and ending characters. However I only want to display those characters contained in between the sequence.

I tried this query:

SELECT table_id,
       table_number
FROM TABLE
WHERE table_number LIKE '%99%';


texts: Modelling Relationships in an E-R Diagram, Table or Foreign Key?

texts:
Should a relationship in an E-R diagram be created in the database as a table or a foreign key?

For example:

  a tutor lectures a class

I see it as three possibilities:

there is a "lectures" table showing this relationship, containing
tutor_id and class_id, both as foreign keys
Or "tutor_id" is a foreign key in the class table
Or "class_id" is a foreign key in the tutor table

I say no to #3, because a lecturer can take multiple classes, and then there would be multiple entries in the lecturer table for the same lecturer (breaking 3NF?).

But I can see an argument for both #1 and #2. I imagine that both might be allowed, but that #1 would be over-normalised (is there such a thing?), and that #2 is most practical. #1 has two joins to get the lecturers details, using a potentially unnecessary table.

If when creating an E-R diagram you put in the relationships, then you have the table structure for modelling that relationship.

For what it's worth, I would use approach #2, but the idea of going a step further with #3 came to me today while drawing a diagram, and I wonder if it is worth the extra effort.


texts:
I am making an app than has an exam center... I am aware of the database normalization concepts...but in my case it looks like I need to store non atomic values in a column. Actually the app will have 2 screens -

Exam packs (that contains a set of questions) e.g. Kids Science, Modern Physics, History etc.
Custom Exams...where one can pick subjects and then conduct an exam.

So... ofcourse there should be a table called questions with

Now I have to fetch some out of these questions if user selects an Exam pack or set of subjects. It was clean till the Exam Pack was into picture. Now I have two options:

Create a columns called questions in exam_packs table that will contain the comma separated ques_ids or an array.
Create separate tables for each exam_pack with questions in them... by doing so..these questions could not be available for users taking exam by subject.

Any suggestions are welcome.


texts: Does interrupting ALTER INDEX COALESCE CLEANUP lose the work so far?

texts:
In Oracle 12c (12.1) we have a huge partitioned table with interval partitioning.
Beside local indexes, there are two global indexes on it.
Dropping old partitions with UPDATE INDEXES clause helps the global indexes keeping valid.

Next night, the feature called Asynchronous Global Index Maintenance background job has started an
ALTER INDEX xxx COALESCE CLEANUP
command, which runs since several days and cleans up the global indexes from orphan entries of dropped partitions.

Unfortunately, we need to create another index on this table with different parameters, but it's not possible due to resource busy error because of a shared DML lock (Row-X (SX)) caused by the background job.
Since we need to deploy a new release, we need definitely the DDL on another index.
For me it looks strange, that alter index ... coalesce cleanup doesn't allow to create another index in parallel. Why should there be any relation?

Question: If we kill the session, will the work of past 2 days be lost and job need to restart again from beginning?


texts:
I have an attributes table, where I want a unique name for each attribute in order to prevent confusion between potentially similar attributes.

In terms of the table design, the way I see it, there are at least two realistic options.

Have an autoincrement int PK, with an additional unique-constraint placed on the column that holds the name data.
Drop the integer autoincrementing PK, and just use the name as the primary key, it will by definition then have a unique constraint.

This attributes table will be joined all round the place, like say many thousands of products each having one or more attributes associated with them.

I am wondering if there is any disadvantage to #2 over #1?, like the indexing costs of adding new attributes down the path, or, are joins slower. What is the best practice?

I suspect #1 is the best option from performance, but #2 has advantages for readability of the data (ie pk:product_weight is more intuitive than pk:2)

Database is MySql, and, I am writing code using a SqlAlchemy/Python ORM abstraction layer, with migrations etc...


texts:
I have a table of users:

CREATE TABLE users
(
  userId INT PRIMARY KEY,
  userName VARCHAR(100),
  /* plus other user columns */
);

And a table of groups:

CREATE TABLE groups
(
  groupId INT PRIMARY KEY,
  groupName VARCHAR(100),
  /* plus other group columns */
);

And I have a table of resources; and I want each resource to be owned (i.e. to have a parent), either owned by a user or owned by a group.

Is the following the right way to model that:

CREATE TABLE resources
(
  resourceId INT PRIMARY KEY,
  userId INT NULL,
  groupId INT NULL,
  /* plus other resource columns */

  FOREIGN KEY(userId) REFERENCES users(userId),
  FOREIGN KEY(groupId) REFERENCES groups(groupId),

  CHECK ((userId IS NULL) ^ (groupId IS NULL))
);

In other words I model it as two nullable columns, each with foreign key constraint, and a check constraint to ensure that exactly one of them is null.

Is this a good way to model that? It looks plausible to me but I don't remember reading it in a book, so I thought I should ask.

Is there a different way to model it? I guess I don't want to have two resources tables (e.g. userResources and groupResources), because they're the same kind of resource and because resources is itself a parent table of further tables (so I don't want to multiply the number of resources tables).


texts: Backup and restore SQL Server database with encrypted columns: what should I backup along with database?

texts:
Assume I've backed up a SQL Server database with Management Studio, and that database have some encrypted columns. Now I want to restore this database into another fresh-enrolled MSSQL server. To use encrypted columns I had to:

Create database master key;
Create certificate;
Create symmetric key.

Which of those are stored along with backup, and which I should backup manually and restore on the other server manually as well? I see there is SQL statements backup master key, restore master key, backup certificate, create certificate ... from file. So, along with database itself, there are:

Master key;
Master key password;
Certificate;
Certificate private key;
Symmetric key.

So what (and how) should I deal with when I restore my database on a fresh server? Thank you!


texts: Restoring a database to a point in time: Database I want not showing up in dropdown list

texts:
I want to restore to a point in time from a database that is already attached.  When I try to do this using SMSS I cannot find the database I want in the dropdown list, but oddly it has a couple databases in the list that have been deleted.

I tried closing/reopening SMSS in hopes that it would refresh but no such luck.

Anyone know why the database is not showing up in the "From database:" drop down list?

EDIT:

If I try to restore a backup to a point in time then no backup sets appear for me to select.  However, if I restore the backup to the most recent point in time and then restore the transaction log then I'm able to select a point in time when restoring the transaction log.  But then when I query the database I find that it's still restored to the most recent point in time from the database backup rather than the time I selected for the transaction log.

How do I restore to a point in time?  The backups are using the full backup model, but I'm not sure if I should be doing anything else as part of the backup to be able to restore to a point in time.  Any advice would be greatly appreciated!


texts: Ignore the joins without any result in SQL Server

texts:
Situation
I'm making a SQL server query to filter data from the projects table (tblProjecten). This table got a lot of many to many relationships to other tables like region (tblProjectenRegio), organisation (tblProjectenOrganisatie), etc.
All the parameters I declare are optional. I've found to use coalesce to do this. It works fine if I don't use the joins inside my query here you got my query I've use first:
declare @themaid int             = 1        ; -- themeID
declare @trefwoord nvarchar(max) = ''       ; -- search query

select distinct p.*
from tblProjecten p left join tblProjectenThema pt                 on p.projectId = pt.projectId
where pt.themaId        = coalesce(@themaid, pt.themaId)                 and
      p.naam like '%' + @trefwoord + '%'                                    ;

Below my results for the different declarations (see also data below):

@themaid
@trefwoord
results
correct

1

166
OK

1
airco
166
OK

null
airco
166
OK

null

166, 185 , 415
OK

This works fine but If I add other conditional parameters like query below. I got totally other results.
declare @themaid int             = 1        ;
declare @studiegebiedid int      = null     ;
declare @opleidingdtypeid int    = null     ;
declare @doelgroepid int         = null     ;
declare @organisatorid int       = null     ;
declare @regioid int             = null     ;
declare @trefwoord nvarchar(max) = ''       ;

select distinct p.*
from tblProjecten p left join tblProjectenThema pt                 on p.projectId = pt.projectId
                    left join tblProjectenStudiegebieden ps        on p.projectId = ps.projectid
                    left join tblProjectenOpleidingsType pot       on p.projectId = pot.projectID
                    left join tblProjectendoelgroep pd             on p.projectId = pd.projectId
                    left join tblProjectenOrganisator po           on p.projectId = po.projectId
                    left join tblProjectenRegio pr                 on p.projectId = pr.projectId
where pt.themaId        = coalesce(@themaid, pt.themaId)                 and
      ps.studiegebiedid = coalesce(@studiegebiedid, ps.studiegebiedid)   and
      pot.opleidingsID  = coalesce(@opleidingdtypeid, pot.opleidingsID)  and
      pd.doelgroepId    = coalesce(@doelgroepid, pd.doelgroepid)         and
      po.organisatorId  = coalesce(@organisatorid, po.organisatorId)     and
      pr.regioId        = coalesce(@regioid, pr.regioId)                 and
      p.naam like '%' + @trefwoord + '%'                                    ;

Here are the results (the other declarations are null):

@themaid
@trefwoord
results
correct
must be

1

NOT OK
166

1
airco

NOT OK
166

null
airco

NOT OK
166

null

NOT OK
166, 185, 415

This comes because the other tables haven't any data inside it. (see data below)

Question
My question is now can I ignore the joins without any result to make the last query working?
I've also tries to use inner and right joins but give the same results.

Data
Here you got some data:
tblProjecten:

projectId
naam

166
Attestering AIRCO PROJECT

185
Autoweb E-LEARNING

415
Bouw en Hout

tblProjectenThema:

themaId
projectId

1
166

2
166

2
415

3
415

6
185

tblProjectendoelgroep:

doelgroepId
projectId


texts: Granting minimum select permission on system "sys" tables in every database

texts:
Our development team has created some SSRS reports that retrieve metadata only (no 'real' data) from every database. I am trying to lock down the service account running these reports to the minimum privileges required.

The queries only select from the system tables in each database such as:

 sys.tables
 sys.extended_properties
 SYS.objects
 SYS.schemas
 sys.dm_db_partition_stats
 sys.indexes

I thought the following would do it, but no such luck.

 GRANT VIEW ANY DATABASE TO [DOMAIN\LOGIN]
 GRANT VIEW SERVER STATE TO [DOMAIN\LOGIN]
 GRANT VIEW DATABASE STATE TO [DOMAIN\LOGIN]

Querying system tables gives the error:

 The server principal "DOMAIN\LOGIN" is not able to access the database "DATABASENAME" under the current security context.

I can't seem to find a way around this other than creating a user for the login for every single database and then granting permissions to the system tables explicitly. This presents a new set of challenges for all future databases. Am I missing something or is there no simple way to grant this access across the board to all databases without granting the "sysadmin" server-level role?


texts:
I have a table that has the following column definitions:

ID (INT, PK)
Name (VarChar)
Active (Bit)
Bunch_of (Other_columns)

Question: I want to have a Constraint on Name/Active such that we can only have 1 record with a given Name that is Active (Active = 1), but we could have many records that have the same Name that are Inactive (Active = 0). Is this possible?

I've tried the normal Unique constraints, but they limit the dataset to only allow 1 record with a given Name to be Inactive.


texts: How to use SUM window function on a column that requires data conversion (and filtering away non convertible numbers)?

texts:
I am trying to aggregate a subset of values from colB (nvarchar) by filtering out all non-convertible values from this column first. I'm using a CTE along the lines of:

;with cte as (
  select
    colA,
    colB
  from Tbl
)

select
  cte.colA,
  sum(convert(int, cte.colB))
from cte
where cte.colA in (...)
group by cte.colA

The filtering on colA guarantees that all the values in colB are convertible to ints. But the aggregation results in an error...

I get this message:

  Conversion failed when converting the nvarchar value 'Yes' to data
  type int.

Without the grouping there is no 'Yes' value in the result set. All of the result set are numbers.

So I guess that the aggregation is happening before the where filter is being applied. How can I get around this?

A link to a gist with the actual query is available here: https://gist.githubusercontent.com/zachsa/f38b08357ad4841a8e776db9226ef66d/raw/071f17ab0079d370e1aec2b70e1f976dc74fd1ea/Aggregating%2520and%2520filtering


texts: Slow query / Indexes creation (PostgreSQL 9.2)

texts:
I have the following Query:

explain analyze
SELECT split_part(full_path, '/', 4)::INT AS account_id,
       split_part(full_path, '/', 6)::INT AS note_id,
       split_part(full_path, '/', 9)::TEXT AS variation,
       st_size,
       segment_index,
       reverse(split_part(reverse(full_path), '/', 1)) as file_name,
       i.st_ino,
       full_path,
       (i.st_size / 1000000::FLOAT)::NUMERIC(5,2) || 'MB' AS size_mb
FROM gorfs.inodes i
JOIN gorfs.inode_segments s
  ON i.st_ino = s.st_ino_target
WHERE
      i.checksum_md5 IS NOT NULL
  AND s.full_path ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'
  AND i.st_size > 0;
  split_part(s.full_path, '/', 4)::INT IN (

SELECT account.id
        FROM public.ja_clients AS account
        WHERE
        NOT (
                ((account.last_sub_pay > EXTRACT('epoch' FROM (transaction_timestamp() - CAST('4 Months' AS INTERVAL)))) AND (account.price_model > 0)) OR
                (account.regdate > EXTRACT('epoch' FROM (transaction_timestamp() - CAST('3 Month' AS INTERVAL)))) OR
                (((account.price_model = 0) AND (account.jobcredits > 0)) AND (account.last_login > EXTRACT('epoch' FROM (transaction_timestamp() - CAST('4 Month' AS INTERVAL)))))
        ) LIMIT 100
);

Explain analyze link: http://explain.depesz.com/s/Oc6

The query is taking ages, and I can't get the problem solved.

These are the index I've already created on the inode_segments table:

Indexes:
    "ix_account_id_from_full_path" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "full_path"::"text" ~ '^/userfiles/account/[0-9]+/[a-z]+/[0-9]+'::"text"
    "ix_inode_segments_ja_files_lookup" "btree" ((
CASE
    WHEN "full_path"::"text" ~ '/[^/]*\.[^/]*$'::"text" THEN "upper"("regexp_replace"("full_path"::"text", '.*\.'::"text", ''::"text", 'g'::"text"))
    ELSE NULL::"text"
END)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
    "ix_inode_segments_notes_clientids" "btree" (("split_part"("full_path"::"text", '/'::"text", 4)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
    "ix_inode_segments_notes_clientids2" "btree" ("full_path")
    "ix_inode_segments_notes_fileids" "btree" (("split_part"("full_path"::"text", '/'::"text", 8)::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")
    "ix_inode_segments_notes_noteids" "btree" ((NULLIF("split_part"("full_path"::"text", '/'::"text", 6), 'unassigned'::"text")::integer)) WHERE "gorfs"."is_kaminski_note_path"("full_path"::"text")

These are the index I've already created on the inodes table:

 Indexes:
    "ix_inodes_checksum_st_size" "btree" ("checksum_md5", "st_size") WHERE "checksum_md5" IS NOT NULL

Question:

What else can I do to improve the Performance of the Query?

This is related to my previous question; index creation - Slow Query - PostgreSQL 9.2

UPDATE 1:

Explain analyze: http://explain.depesz.com/s/UBr

The index and function have been created as mentioned on the answer below.

UPDATE 2:

Explain analyze: http://explain.depesz.com/s/LHS

Using the Query provided on the answer below


texts: Update DATE field when another field in the same table changes value

texts:
I hava a table, say:

 - id (autoinc pk)
 - name (varchar)
 - description (varchar)
 - yearofarrival (int) <- (should contain either 78, or 1998, that's why it is not a date field)
 - date_updated <- auto-update field

What I need is to set a trigger (I guess this is how it should be done) that, after update, if the field "yearofarrival" has been changed, it should update the "date updated" with the current date.

This should not happen with any other field change.


texts: If function behave differently in MySQL 5.5.49

texts:
I'm running MySQL 5.5.49 on my Windows 7 32-bit

I'm using a stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_testing`(
IN in_param VARCHAR(50)
, OUT out_1 VARCHAR(50)
)
BEGIN

    DECLARE v_no_por_cabang VARCHAR(50);
    DECLARE v_no_por_cabang1 VARCHAR(50);
    DECLARE v_no_por_cabang2 VARCHAR(50);

    SET v_no_por_cabang = in_param;

    SET v_no_por_cabang1 = IF(v_no_por_cabang = 0, NULL, v_no_por_cabang);

    SET v_no_por_cabang2 = IF(v_no_por_cabang = '0', NULL, v_no_por_cabang);

    SELECT v_no_por_cabang1;
    SELECT v_no_por_cabang2;

END$$

and I'm calling it like this:

CALL sp_testing('POR1',@out1);

The output for v_no_por_cabang1 is NULL. The output for v_no_por_cabang2 is POR1.

Why does the IF function behave like that? In my previous test in MySQL 5.5.xx on Ubuntu 12 the same stored procedure didn't behave like that. It returned POR1 for v_no_por_cabang1.

Anybody know why?


texts:
Our products' database contains an audit trail table:

CREATE TABLE gn_AuditTable(
  gn_ObjectId int NULL,
  gn_Action smallint NULL,
  gn_Time datetime NULL,
  gn_UserId int NULL,
  gn_Login varbinary(16) NULL,
  gn_ExtraObjectId int NULL,
  gn_ExtraInfo int NULL
) ON [PRIMARY]

We add rows to this table with a simple insert statement - no stored procedures:

insert into gn_AuditTable (
  gn_ObjectId,gn_Time,gn_UserId,gn_Login,gn_Action,gn_ExtraObjectId,gn_ExtraInfo
) values (
  ?,?,?,?,?,?,?
)

passing the values of the columns as parameters to the query; there are also no triggers.

The problem is that sometimes this insert is super-slow - taking in some cases more than 30 seconds and timing-out.

We have multiple installations of the same product, and we are experienceing the problem in some - but not all - of them.

The audit table can be quite big - up to some million of rows. The very slow insert happens with databases that have big tables (quite naturally) - but we have other databases with simliar number of rows that run fine, and even the databases experiencing the problem have it intermittently, in most cases the insert is reasonably fast (sub-second).

The table has 6 non-clustered, non-unique indexes:

gn_Login;
gn_ObjectId;
gn_Action and gn_ExtraInfo;
gn_ExtraObjectId;
gn_Time;
gn_UserId.

Having all these indexes could cause the problem, but again, we have the same indexes everywhere and the problem only somewhere / sometimes.

We had the insert timing-out at least once on a test database with only one user, so the problem does not appear to be related to the overall load but to the insert and the table themselves.

We used to have a clustered index on gn_ObjectId, but we got rid of it as an attempt to solve this problem. gn_ObjectId is not sequential and so we thought that it is a poor choice for a clustered index, forcing SQL Server to re-order rows on inserts. Probably gn_Time - that is always increasing - would be a better choice.

There is no primary key because there is no column or combination of columns that  is 'naturally' unique - we would have to add an id column, and it seems just overhead.

Does anyone have any suggestion at what we could look at to diagnose the problem? What is an insert doing that can take so much time - intermittently?


texts: Django Auth user as well as custom users table?

texts:
So we've been using djangos database authentication for a while now and have recently decided we want to implement a completely new login system for our website.

We are keeping the django ORM as we rely heavily on it for the database modelling tool.

Anyway, the reason I'm asking this question is because we want to keep the Auth_user table so users can still use the admin panel!

So basically at the moment this is what's happen when a user signs up:

Signs up with FB, we grab their details
We insert their fb details to our NEW user database table
I'm trying to find the best solution for syncing this user table with djangos Auth_user table.

Any ideas?


texts: Postgres Aurora RDS password auth fails except with master user

texts:
I can successfully connect to a Postgres Aurora server using the master password thru the SSH tunnel on the bastion host I have setup.

However, when I create any other users, I can't connect with them even tho all other things are the same configuration wise. I get a DB level auth error.

password authentication failed for user "SomeUser"

I've added the users using these queries.

CREATE USER SomeUser LOGIN ENCRYPTED PASSWORD 'SoMePaSsWoRd';
GRANT CONNECT ON DATABASE postgres TO SomeUser;
ALTER USER SomeUser CREATEDB;
GRANT ALL PRIVILEGES ON DATABASE postgres TO SomeUser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO SomeUser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO SomeUser;

What am I missing here?


texts: Oracle alternative edition for Windows Server 2012

texts:
Does Oracle 11.2 support Windows Server 2012? Any alternative for Windows Server 2012 with respect to Oracle editions?

I checked in internet but their forums, and this question says its not currently released for Windows Server editions.

Any alternative Oracle editions for Windows Server 2012 64bit?


texts: Execute SQL conditionally based on database Compatibility Level

texts:
The Problem
I have a script that I need to execute on both SQL Server 2016 and SQL Server 2019. If executing on Server 2019+, I want to use syntax that is only supported on that version (specifically, Data Classification).
What I want is something like this, where we simply skip Data Classification if running on a version before 2019:
DECLARE @compatibility_level TINYINT = 0;
SELECT @compatibility_level = compatibility_level FROM sys.databases WHERE name = 'MyDatabase';
PRINT CONCAT('Database compatibility level is: ', @compatibility_level);

IF @compatibility_level >= 150
BEGIN
    ADD SENSITIVITY CLASSIFICATION TO
        MyTable.Forename,
        MyTable.Surname
        WITH (LABEL='Confidential - GDPR', INFORMATION_TYPE='Name', RANK=MEDIUM);

    -- More classifications would be here
END
ELSE
BEGIN
    PRINT 'Data Classification will not be performed, as the compatibility level is not high enough';
END

If I run this on SQL Server 2019+, it works as expected. But if I run it on older version, it fails with Incorrect syntax near 'SENSITIVITY', because it's parsing the whole script before executing it.
Restrictions
I should add that this is part of a set of database migration scripts that are being executed automatically, so I can't simply execute different scripts for different versions (not without a lot of work).
Potential Solutions
One solution I thought of is to build the SQL dynamically, inside the IF block. Something like:
DECLARE @sql NVARCHAR(MAX) = '';
@sql += 'ADD SENSITIVITY CLASSIFICATION TO MyTable1.Column1 ... ;';
@sql += 'ADD SENSITIVITY CLASSIFICATION TO MyTable2.Column1 ... ;';
EXEC sp_executesql @sql;

I haven't tried this, but presume it would work. It feels pretty ugly though - are there any other ways to achieve this in a single script?


texts: Quoting columns with spaces in PostgreSQL?

texts:
I migrated a database from SQL Server to PostgreSQL.

Most column names contain double words, for example:

SELECT [Column Name] FROM table;

...which does not work in PostgreSQL.

What is the correct syntax for PostgreSQL?


texts: Packaging SQL Server Express 2012 install won't install due to user rights

texts: Is there a way force the above permission window to elevate the user permissions?


texts: Trying to filter msdb..sysjobs by job_id

texts:
I'm attempting to determine the name of the SQL Server Agent Job that is currently running by comparing the output of master.sys.dm_exec_sessions.program_name to msdb..sysjobs.job_id

I'm using the following T-SQL to try to diagnose why I cannot get the results I want:

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
--  INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE
    program_name like 'SQLAgent%' ;

SELECT CAST(job_id as varbinary(max)) as JobStepID, CAST(step_id as varbinary(max)) as StepID, *
FROM msdb..sysjobsteps;

SELECT program_name, '|' + SUBSTRING(program_name,30,34) + '|' AS JobIDSubstring, t.text
FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_requests r on s.session_id = r.session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
    INNER JOIN msdb..sysjobsteps sj on SUBSTRING(program_name,30,34) = cast(cast(sj.job_id as varbinary(max)) as varchar(max))
WHERE
    program_name like 'SQLAgent%' ;

Results:

The last query should return results, however it returns zero rows since the JOIN clause is clearly not working.  How should I construct this so the JOIN from sys.dm_exec_sessions to msdb..sysjobsteps works?


texts: Find all records with the same first 3 characters

texts:
Does anyone know how to return all records with the same first three characters in MS Access (I'm guessing this has to be done in the SQL view)?

For example

andria -doesn't match

Angie -match

Angela -match

Angel -match

Ann -doesn't match


texts: How do I add and/or modify $jsonSchema for a MongoDB collection from within the NodeJS driver?

texts:
I would like to use the MongoDB native schema functionality in a Node application using the MongoDB NodeJS driver.
I do not want to use Mongoose for this. Mongoose has it's own implementation of Schemas, but has different issues which makes me want to use MongoDB directly.
So far, I have only been able to find how to create a in (official) documentation using the MongoDB via the command line only. I cannot find or figure out how to do this from within a NodeJS app using the NodeJS MongoDB driver.
Also, I cannot find any documentation on how to alter the Schema if one has already been set on a collection.
Anybody know how to do this?
The official docs that I have seen about this topic can be found here:
https://docs.mongodb.com/manual/core/schema-validation/
The answer's here: https://stackoverflow.com/questions/55025621/mongodb-add-schema-for-existing-collection


texts: complex mysql constraints over foreign keys

texts:
It seems rational to me to stop users or bad codes from inserting invalid data, but I don't remember to see this anywhere!

Consider the following tables

How I can make sure an order is always referencing an address that is created by the same user?
Is this kind of constraint usual and recommended? I mean, Do I even have to care about it in the design?


texts: unable to connect between SQL Developer and Oracle VM Virtualbox

texts:
I have setup an Oracle DB within Oracle VirtualBox and I am trying to connect to it using SQL Developer installed on the same desktop. It worked initially but has since stopped connecting. It seems like there is an issue with my listener I have been unable to figure out. My listener is showing no services supported now when I pull status.
'''# listener.ora Network Configuration File: /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ol7-122.localdomain)(PORT = 1539))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

~
~
~
~'''

'''[oracle@ol7-122 admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 08-FEB-2021 12:57:58

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ol7-122.localdomain)(PORT=1539)))
TNS-12535: TNS:operation timed out
 TNS-12560: TNS:protocol adapter error
  TNS-00505: Operation timed out

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                08-FEB-2021 10:14:20
Uptime                    0 days 2 hr. 44 min. 37 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol7-122/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol7-122.localdomain)(PORT=1539)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully'''


texts: mysql client auto reconnect in case of ERROR 2013

texts:
I'm searching for a way how to instruct mysql client to reconnect in case of ERROR 2013. I'm testing PXC with ProxySQL and want to keep sql statements flowing from the client in case when the writer node get killed and new one is promoted. Is it possible for mysql client to reconnect when server goes down during the query? Can mysql client rerun the sql query (insert, update, ...)? With Sysbench it is possible if setting the --mysql-ignore-errors=all parameter.
(from Comment)  I'll be calling that SP from a custom lua script, where I'll test the 'error 2013' condition and in that case I'll rerun that query. Does this make sense, or the value of @err set by the error handler can't be passed to the script, because the session will just die, when the mysqld will get killed?
DELIMITER //
CREATE  PROCEDURE sbtest.InsertIntoTable (
                IN trnid INT, IN unixtime INT,
                OUT err INT)
BEGIN DECLARE CONTINUE HANDLER FOR 2013 SET @err = 1;
INSERT
     INTO  sbtest.failover_test ( node, trn_id, unix_time )
         VALUES  (@@hostname, trnid, unixtime);
END//

my table:
failover_test | CREATE TABLE `failover_test` (
  `id` int NOT NULL AUTO_INCREMENT,
  `node` varchar(255) DEFAULT NULL,
  `trn_id` int DEFAULT NULL,
  `unix_time` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=116837 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

--
CALL sbtest.InsertIntoTable(1, 1599207191, @err);SELECT @err


texts: Updating open orders from a price list within a range of dates


texts: DROP vs DELETE vs TRUNCATE for staging data

texts:
I need to import data (>> 1 GB) into MS-SQL-Server. For several reasons I need to import this data into a staging table (not directly into the target). From staging the data is loaded into the target table. The data in the staging table is never used again. I have several ways to do:

Load the data into staging stable, mark this data as new, load only marked data into target, unmark data.
Delete all data in staging, load the data into staging table, load all data into target.
Truncate staging table, load into staging, load into target
Drop staging table, create staging table, laod into staging table, load into target.
Drop staging database, create staging table, load into staging table, load into target.

If the solution is not restricted  by your server role: What would be the recomended solution? Which is most robust?

Solution 1 is most likely nonsense if you don't need the data anymore. Solution 3 performances much better compared to 1 and 2. But what between solution 4 and 3? Actually I would prefer solution 5 as I can change nealy every parameter within the staging process within my load procedure but you need rights to drop&create database. This is quite a lot for a load process. So in most of the cases I use drop and recreate the staging table. But is this the recommended way?


texts: Do I have to filter the percona.checksums table in replication for use with pt-table-checksum?

texts:
I've set up MySQL replication, one master and one slave. Now I just starting to check the data for integrity on both sides by using Percona's pt-table-checksum. Wonderful tool to detect any changes or drift on the slave.

From what I understand from its documentation, it runs on the master, writes checksums to the percona.checksums table and runs similarly on the slaves connected (automatically detected) using a regular remote MySQL connection. However, the percona.checksums table is actually also replicated! If data differs on the slave, the checksums from the master may be overwritten on the slave and not being detected.

Now, I am worried this might be causing my observed false negatives in detecting changes.

Does pt-table-checksum have safeguards put into place for preventing replication on the percona.checksums table?

If so, how does this work? (as far as I can see, one cannot change replication filters like this) This may help me to pinpoint some other reason for my observations (see below).
If not, then how can I be sure the contents of that table reflect checksums of that specific server? Should I put a replication filter in place? This is actually discouraged by Percona's toolkit documentation.

Actual observations

I have seen one occurrence in my test setup in which somehow running pt-table-checksum did not detect the change deliberately made on the slave. Inspecting the checksum table contents showed the master's checksums. Running the tool on the slave itself then showed the checksums should have been different! This makes me believe there's a race condition going on and the reason why this is hard to reproduce.

Another hypothesis

Suppose the slave has drifted and the network connection is down for a short while the moment running pt-table-checksum. It will show it is unable to connect to the slave in the stderr output, but not fail. Short time later, networking is restored, and replication resumes, copying over the master's contents of the table percona.checksums. Then running pt-table-checksum --replicate-check-only, will provide false negatives. Am I right here? If so, how can I prevent this in this case?

This is a regular MySQL server/client using Percona's toolkit with it.

Versions:

pt-table-checksum 2.1.4
MySQL client: 5.5.24
MySQL server: 5.5.24-0ubuntu0.12.04.1-log (Ubuntu 12.04)


texts: When mirroring accounts for Sql Server Windows Authentication, do both have to be local?

texts:
I understand that when using windows authentication to connect to a remote SQL Server instance, two local accounts with the same username and password can be used (i.e. "mirrored" local accounts).

My question is: if the username and password are the same, can the client account be a domain account and the SQL Server account be a local account?


texts:
I am creating a database for bus timetable in android app. I have created four tables: stops, route, detail_route and schedule. columns in each table are as follows.

Database structure

route (id, name)
stops (id, route_id, name)
detail_route (route_id, stop_id, time, stop_order) here time means: time require to reach to that stop
schedule (source_stop_id, destination_stop_id, route_id, start_time)

Now I have a problem here.  User can select any source and any destination, but in the database I am only storing data of bus about source, destination and route.

Ex:
Here Bus route is from Point A to E :

A > B > C > D > E

I will store this entry in schedule table as

source_stop_id : A

destination_stop_id : E
route_id : 1
start_time : 9:15 AM

now if user select source as B and Destination as D. There is no bus available from B to D according to my database. Here same bus should be shown because it is on same route. I don't know how to do it. please suggest.


texts: Replication Monitor Missing Subscriptions in Watch List

texts:
In Replication Monitor, for one of our publications (transactional replication), none of the subscriptions are showing up in the Subscription Watch List. This is new behavior that just started recently.

The other publications associated with this distributor don't exhibit this behavior, the subscriptions are visible under the Subscription Watch List tab.

I can reproduce similar behavior using the Sp_replmonitorhelpsubscription SP. When executing this using the @publisher that is not returning results in the Replication Monitor, no results are returned. But when using the @publisher that do show subscriptions, results are returned.

I've confirmed in the subscription database for this affected publisher that transactional replication is functioning as expected, commands are being delivered, row counts are in sync.

Is there anything that can be done to get the subscriptions to appear again for the affected publisher? The only thing I can think of doing is deleting the subscription and publication and starting over, since some metadata must be missing, but that requires a downtime and seems unnecessary.

Version: Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0


texts:
I have a simply query:

SELECT DISTINCT images.*
FROM   `images`
WHERE  `images`.`user_id` IN (SELECT following_id
                                FROM   `follows`
                                WHERE  `follows`.`follower_id` = 9
                                       AND `follows`.`accepted` = 1)
LIMIT  15 offset 0

This executes in less than 1 ms.

Adding an ORDER BY on column that is indexed:

    SELECT DISTINCT images.*
    FROM   `images`
    WHERE  `images`.`user_id` IN (SELECT following_id
                                    FROM   `follows`
                                    WHERE  `follows`.`follower_id` = 9
                                           AND `follows`.`accepted` = 1)
ORDER  BY id DESC
LIMIT  15 offset 0

takes 15 ms to execute. There are a mere 7,000 images in the table. Should it really take 15 fold time to order an indexed column? What can I do to decrease such a performance hit?

Running on:

Server version: 5.6.11-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

EXPLAIN:

+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+
| id | select_type  | table       | type        | possible_keys                     | key                  | key_len | ref                      | rows | Extra                                                        |
+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+
|  1 | SIMPLE       | <subquery2> | ALL         | NULL                              | NULL                 | NULL    | NULL                     | NULL | Using where; Using temporary; Using filesort                 |
|  1 | SIMPLE       | images    | ref         | user_id                           | user_id              | 5       | <subquery2>.following_id |    3 | NULL                                                         |
|  2 | MATERIALIZED | follows     | index_merge | accepted,follower_id,following_id | follower_id,accepted | 5,2     | NULL                     |   19 | Using intersect(follower_id,accepted); Using where; Distinct |
+----+--------------+-------------+-------------+-----------------------------------+----------------------+---------+--------------------------+------+--------------------------------------------------------------+

Thanks


texts: Oracle database created whats next? Create Schema or Create Tablespace?

texts:
I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here.

I'm supposed to create a database for an application. The server where this will be running previously had a database for a pretty similar app. I don't know much about Oracle so I reused the ZFS filesystems and left them how they were created (because honestly, I didn't knew why they were created that way, but I'm pretty sure it was for a good reason).

app/oradata_smart_ora1    858M  12.2G   858M  /oradata/SMART/ora1
app/oradata_smart_ora2   7.18M  18.0G  7.18M  /oradata/SMART/ora2
app/oradata_smart_ora3   7.18M  36.0G  7.18M  /oradata/SMART/ora3
app/oradata_smart_ora4   60.6K   400G  60.6K  /oradata/SMART/ora4
app/oradata_smart_redo1   400M  2.61G   400M  /oradata/SMART/redo1
app/oradata_smart_redo2   200M  2.80G   200M  /oradata/SMART/redo2
app/oradata_smart_redo3   200M  2.80G   200M  /oradata/SMART/redo3

Since I reused the filesystems I created my database and placed the controlfiles in the same places where the old database files were (/oradata/SMART/ora1,/oradata/SMART/ora2,/oradata/SMART/ora3).
Thinking like MySQL works I created app/oradata_smart_ora4   60.6K   400G  60.6K  /oradata/SMART/ora4 specifically to store the database there.

The databases startups and mounts no problem. Now is where I'm stuck. I've read this, this,this,this,this and much more but I still have doubts.

Note that this server will manage with millions/billions records throughout its lifetime.

Now that my Database is created, whats the next step? Create the Schema or Tablespace?
Tablespace Questions: Tablespace datafile(s) is where actual data from tables is stored? how many are needed? Default or Temporary? How much space will I need for it? Autoextend?

I'm really sorry for all these questions but I really want to do this right while following the best practices for Oracle. If you need more information for your answer let me know.


texts:
We receive an error Login failed for user 'x' sometimes from our application. We are 100 % sure the login is working because we checked it by manual login from SSMS as well as from the application. The error happens sometimes only. How can we find the exact reason for the failure? Why does it fail only sometimes? Below is the whole stack trace:

  Exception Type : System.Data.SqlClient.SqlException
  Message : Login failed for user 'x'.
  Source : .Net SqlClient Data Provider


texts: How do you change the location of the data files in SQL Server 2000?

texts:
I have an old SQL Server 2000 machine in a clustered environment. The data files are on Disk S: which is running out of space.  I have another Disk B: in the same disk array which has lot of space.  I want SQL Server to use the Disk B: instead of Disk S:

How do I setup SQL Server to store the data files on Disk B?

Any help would be greatly appreciated.

Thanks.


texts: Avoid joining to the same table multiple times

texts:
I have two tables:

CREATE TABLE one (
  id int4 primary key,
  p_id int4,
  k_id int4,
  c_id int4
);

CREATE TABLE two(
 id int4 primary key,
 p_id int4,
 k_id int4,
 t_id int4,
 pos int4
);

Table one: is a join table, I'm using it in this report only because of the c_id and to limit the number of row.

Table two: contains my data, a p_id, k_id and t_id can have many pos, so I'm grouping them and using min(), to get the min pos. I've tried some ways to get all my data for a k_id and p_id here is a way (with this solution I was not able to order by pos), so I end up doing this (see fiddle):

SELECT
  o.p_id,
  o.k_id,
  min(t.pos) as t_pos,
  min(t1.pos) as t1_pos
FROM one o
  LEFT JOIN two t ON t.p_id = o.p_id
    AND t.k_id = o.k_id
    AND t.t_id = 1
  LEFT JOIN two t1 ON t1.p_id = o.p_id
    AND t1.k_id = o.k_id
    AND t1.t_id = 2
WHERE o.p_id = 1 AND o.c_id = 1
GROUP BY 1, 2
LIMIT 1

This solution is 2-3 times slower then @Erwin Brandstetter's solution from the other post, but sorting t_pos, t1_pos is possible and also getting all data where type_id = 1 and pos = 1 for example.

Is there a better way of achieving the same output and improve the speed and be able to sort my data (t_pos and t1_pos columns)?


texts: Is it possible to force an adaptive join with a query hint?

texts:
Is it possible to force a query to use an adaptive join? The query in question produces an adaptive join when run as a SELECT, but when run as SELECT INTO instead, it doesn't.

I have a parameterised query which has a sub-query that typically selects almost no rows (0 to 4), but occasionally picks 100k.

I believe that this is exactly the type of scenario that an adaptive join is designed for, but SQL Server doesn't pick this as a query plan.

A query plan of always-hash-join is server-destroying, so my only recourse at the moment is to force the join to be nested loops, and accept that individual worst-case requests will take a bunch of extra seconds to run.

I have an Azure SQL database at compatability 150, and BATCH_MODE_ON_ROWSTORE=1

Further details

For some reason paste-the-plan doesn't think the XML for this query is legit.

The query at issue is:

with
hashes as (select h.[ContactHash]
    FROM HashTable h
        join someTable p on h.id=p.id
    group by [ContactHash]
    having count(distinct key) >1
        and count(distinct p.thing) between 1 and 20),
dupeContacts as
    (select distinct c.key,c.ContactHash from someTable c
    where c.[ContactHash] in (select t.[ContactHash] from hashes t ) and c.key =  @id
    )
    insert  into @MyTableVar
select * from dupeContacts

The two tables involved are similar to:

HashTable (
    int id ,
    ContactHash varbinary(16)
) -- millions, typically 4:1 to someTable by ID

and

SomeTable (
    int id Primary Key ,
    varchar(100) thing
)


texts: Storing undetermined amounts of user information (MySQL)

texts:
I've been looking into the best way of storing an undetermined amount of information submitted by a user. A friend of mine suggested using nested tables, however these don't appear to be a thing in MySQL.

The application will allow users to store pieces of information per day (each day is a blank slate so to speak)

What I have currently is

-Users
--ID
--email
--password

-Things
--UID (made from date and user ID)
--Thing1
--Thing2

This works fine. The UID is the users ID and the date combined (i.e 71420150404) as each day will be different but I'm open to changing this. The application checks to see if there are any entries for that UID and if there isn't, creates a new row.

The problem I have is I'd like the user to be able to select how many pieces of information they would like to add per day. So instead of the static 'Thing1, Thing2' the user could theoretically have this go up to 'Thing100', and I'm fairly sure adding these as columns isn't the best way to go about this.

I looked into if its possible to store an array in a cell and I'd access it like that through PHP but the research I came across all suggests I shouldn't do this. Creating a new table per user also seems very inefficient.

What is the best way to go about this?

EDIT - Including original PHP insertion code in response to an answer

Insert

$sql = "INSERT INTO things (uid, thing1, thing2)
VALUES (:uid, :thing1,:thing2)
ON DUPLICATE KEY UPDATE thing1 = :thing1, thing2 = :thing2";

Retrieve

$sql = "SELECT * FROM things WHERE uid=$uid";


texts: Suboptimal query plan when updating partitioned table

texts:
Background

I have a simple CTE used to update a declaratively-partitioned table.
The subquery runs quickly (1.7sec per EXPLAIN ANALYZE) and returns 3,769 records (CTE y in the query below).
The UPDATE seeks to update non-index columns of a declaratively-partitioned table. A few characteristics of the table:

Contains 21 million records
184 partitions -- yes, too many -- each child partition has primary/ foreign keys and indices
fillfactor=100 (plan to reduce to potentially use HOT on same pages, but not certain that query plan affected by lack of page space)

Problem arises upon UPDATE, per the incremental query plan shown (EXPLAIN)  after the subquery (via CTE) runs (several nested loops of hash joins):

-> Hash Join /* Run for each child partition, based on CTE PK = child table PK */
     -> Nested Loop
          -> CTE Scan
          -> Append
               -> Index Scan /* for index on each child partition */
                  ... /* Index scans (for each child partition) */
     -> Hash
          -> Seq Scan /* on child table */
   ... /* Hash Joins (for each child partition) */

Query

The following query is the UPDATE statement causing the issue. Basically, the query performs a couple functions using a value from parent_table that cannot be nested into a single SQL statement (so two CTEs used), then UPDATE the same parent_table for the result (the functions are expensive, so the result stored in the table itself).

WITH x AS (
  SELECT t."p1", t."p2", f(t."b1") OVER "win_x" AS "c1"
  FROM parent_table AS "t"
  WHERE t."p1" IN ('val1','val2')
  WINDOW "win_x" AS (PARTITION BY "p1" ORDER BY "p1","p2")
), y AS (
  SELECT x."p1", x."p2", f(x."c1") OVER "win_y" AS "c2"
  FROM x
  WINDOW "win_y" AS (PARTITION BY "p1" ORDER BY "p1","p2")
)
UPDATE parent_table AS "t2"
SET ("a1")=(t.”b2”*y."c2")
FROM y INNER JOIN parent_table AS "t" USING ("p1","p2")
WHERE t2."p1"=y."p1" AND t2."p2"=y."p2";

Table definition

Table defined as following:

CREATE TABLE IF NOT EXISTS parent_table (
   p1   integer,
   p2  timestamp without time zone,
   a1  numeric,
   b1  numeric,
   b2  numeric,
   c1  numeric,
   c2 numeric,
   ... /* additional 8 columns of numeric type */
   z1 numeric,
   z2 numeric,
CONSTRAINT lbound_z1 CHECK ( “z1”::numeric >= 0),
CONSTRAINT lbound_z2 CHECK ( “z2”::numeric >= 0)
PARTITION BY RANGE (EXTRACT(YEAR FROM p2), EXTRACT(MONTH FROM p2))
WITH (OIDS=‘false’)
TABLESPACE ts_ssd_raid10;

CREATE TABLE IF NOT EXISTS child_table_yyyy_mm PARTITION OF parent_table (
  CONSTRAINT child_pk PRIMARY KEY (p1, p2) WITH (FILLFACTOR=‘90’) USING INDEX TABLESPACE ts_idx_m2ssd
CONSTRAINT child_fk_other_child_yyyy_mm FOREIGN KEY (p1, p2) REFERENCES other_child_yyyy_mm (p1,p2) MATCH FULL WITH (FILLFACTOR=‘90’) USING INDEX TABLESPACE ts_idx_m2ssd )
FOR VALUES FROM (yyyy, mm) TO (yyyy, mm)
WITH (FILLFACTOR=‘90’, OIDS=‘false’)
TABLESPACE ts_ssd_raid10;
ALTER TABLE child_table CLUSTER ON “child_pk”;

Question

How can I perform the UPDATE without the nested loop hash joins for each of the 184 child tables?

System Info

Postgres version 10.3


texts: Fastest query to filter product by countries

texts:
I have a large Product table:

ProductID
ProductName

1
Product 1

2
Product 2

...
...

n
Product n

whereas n is around 2,000,000.
To limit product availability to a certain country, I have a second ProductCountryRestriction table:

ProductID
CountryCode
IncludeExclude

1
USA
0

1
UK
0

2
AUS
1

2
NZ
1

If a product is restricted to one or more countries, I will then add product- included countries to this table with IncludeExclude = 0. For example Product 1 is available for USA and UK only.
If a product is valid for all countries except some countries, I will then add product - excluded countries to this table with IncludeExclude = 1. For example Product 2 is available for all countries except AUS and NZ.
All country codes corresponding to a product must either be set as IncludeExclude = 0 or as IncludeExclude = 1. The mixing of 0 and 1 is not allowed. For example, there are records with IncludeExclude = 0 for Product 1, I can't add a record with ProductID = 1 and IncludeExclude = 1 to this table.
An user of the system can select several countries to work with. The user preference is stored in UserCountry table:

UserID
CountryCode

1
USA

1
NZ

So the User 1 can see Product 1 because it is available for USA. He can also see Product 2 because the Product 2 is also available for USA (although it is excluded from NZ).
Here is my query to get all available products for an user:
DECLARE @UserID int = 1;

SELECT P.*
FROM Product P
WHERE
    EXISTS
    (
        SELECT * FROM ProductCountryRestriction PCR
        WHERE
            PCR.ProductId = P.ProductId
            AND PCR.IncludeExclude = 0
            AND PCR.CountryCode IN (SELECT CountryCode FROM UserCountry WHERE UserID = @UserID)
    )
    OR EXISTS
    (
        SELECT * FROM UserCountry UC
        WHERE UserID = @UserID
            AND UC.CountryCode NOT IN (SELECT CountryCode FROM ProductCountryRestriction PCR WHERE PCR.ProductId = P.ProductId AND PCR.IncludeExclude = 1)
    )

This query works as expected but it introduces poor performance. What can I do to improve it? I don't mind to change the DB design.
Thank you for reading my question! I will be grateful for any help you can provide.
Edited 12/31/2020 - Added execution plan as suggested by @J.D.
Please check this link for the plan: https://www.brentozar.com/pastetheplan/?id=BywbFaqaw