texts: sstableloader returns `java.lang.NumberFormatException: For input string: "Filter.db"`

texts:
I've been using the Java API to create an SSTable by following the instructions on this site.

The program compiles and works fine; no errors.

However when I try to load the table using the following command sstableloader -v -d 127.0.0.1 /path/to/sstable/keyspace/tablename it returns an error. Here is the stack trace it outputs:

For input string: "Filter.db"
java.lang.NumberFormatException: For input string: "Filter.db"
        at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
        at java.lang.Integer.parseInt(Integer.java:580)
        at java.lang.Integer.parseInt(Integer.java:615)
        at org.apache.cassandra.io.sstable.Descriptor.fromFilename(Descriptor.java:276)
        at org.apache.cassandra.io.sstable.Descriptor.fromFilename(Descriptor.java:235)
        at org.apache.cassandra.io.sstable.Component.fromFilename(Component.java:120)
        at org.apache.cassandra.io.sstable.SSTable.tryComponentFromFilename(SSTable.java:160)
        at org.apache.cassandra.io.sstable.SSTableLoader$1.accept(SSTableLoader.java:84)
        at java.io.File.list(File.java:1161)
        at org.apache.cassandra.io.sstable.SSTableLoader.openSSTables(SSTableLoader.java:78)
        at org.apache.cassandra.io.sstable.SSTableLoader.stream(SSTableLoader.java:162)
        at org.apache.cassandra.tools.BulkLoader.main(BulkLoader.java:109)

NOTES

Tried to google it but there doesn't seem to be anything about this error.

The table that im loading into only has three columns: ( id uuid PRIMARY KEY, datablob text, tablename text ).

I've also tried loading empty data and just auto generating a uuid to check that it's not the data I'm loading but it still doesn't work.


texts:
I have a 830mb .sql file which is a scripted DB.

I wish to execute this SQL so it will create and insert data into a database on a hosted server. I cannot get it to open in SQL Server Management Studio or EMS SQL Manager.

How can I execute it?


texts:
I am trying to understand where to draw a fine line for Indirect Checkpoint that was introduced in SQL Server 2012.

Based on my understanding

SQL Server does checkpoint to flush dirty pages out to the disk, but frequency or time interval between checkpoints depends on many factors. It is controlled by server level configuration called recovery interval and default value for this settings is zero which means in a fairly busy system SQL server can perform checkpoint every minute and can result in database recovery time of less than a minute.

SQL Server 2012 introduced indirect checkpoint – which in turn can allow you to control recovery interval for individual databases.

At a high level this setting appears to a good thing, it allows to even out disk IO by performing more frequent checkpoints instead of doing a periodic checkpoint which can result in flooding the underlying IO subsystem

Now my questions are

a) Does indirect checkpoint takes into considerations number of dirty buffers instead of number of transaction which usually automatic checkpoint was using?

b) I keep finding in many blogs that indirect checkpoint setting is very dangerous as
It can keep your IO system very busy. Does below statement is true from above blog?

  Checkpoint normally writes large blocks of data to disk optimally in
  single write operation, up to 256KB, depending on the number of
  contiguous dirty pages in cache that needs to be flushed to disk. Once
  you turn on the indirect checkpoint feature by setting target recovery
  time to a non-zero value, the checkpoint writes will turn into single
  page writes, 8KB writes.

c) Does indirect checkpoint setting is more suitable for Data Warehouse vs OLTP  type workload? What scenarios you consider for SQL database in question before you start leveraging indirect checkpoint?


texts: What are the default fixed server role members for SQL Server 2012

texts:
What are the default fixed server role members for newly created SQL Server 2012 instance using all default options.

As given by

EXEC sp_helpsrvrolemember


texts: Is it necessary to run apply-log immediately after innobackupexec has run?

texts:
i have been trying to read other questions, but i did not find a answer to suit me. So excuse me if i ask something that has been already asked.

I'm trying to organize my self and backup all my mysql servers with innobackupexec (since i only use Percona Mysql). I have read percona documentation and some other blog/posts but i do not understand if i need to run --apply-log immediately after full backup has been run.
If i need to schedule a script as a daily basis backup, should i only script this?

innobackupex /data/backups

and should i only use

innobackupex --use-memory=4G --apply-log /data/backups/2010-03-13_02-42-44/

only when i need to restore the database?

i'm pretty confuse, hope to get here some clarification.

thank you


texts: Disable password policy for partially-contained database

texts:
I'm stuck in a situation where I need to create a new user for a partially-contained database (SQL Server 2016). The password is short, so I get an error:

Password validation failed. The password does not meet Windows policy requirements because it is too short.

When creating a login at the instance level, there is an option to untick 'Enforce password policy', 'Enforce password expiration', and 'User must change password at next login'. There is no such option when creating a user for a partially-contained DB.
Is there a way to get around this?


texts: Would it be possible/wise to execute 2 parallel restores from a single backup?

texts:
Every night I create a backup (to disk) of a particular database. Subsequently that backup has to be restored to 2 different servers. These restores now happen sequentially and take about half an hour per restore.

Would it be possible/wise/faster to execute the 2 restores in parallel?


texts: Oracle 12c - Can not login to Enterprise Manager - XDB Login prompt

texts:
I have installed Oracle 12c Standard Edition on one of my Windows 7 LAN desktops. Installation went fine. I created ORCL Container and ORCLPDB pluggable database during install. I can log in to Oracle SQLPLUS using SYS & SYSTEM passwords.

I can as well login to Oracle Web Enterprise Manager (It runs on https://(hostname):5500/em ) using SYS & SYSTEM passwords without specifying any database. However in case of Web Enterprise Manager the moment I choose a database container (See the screenshot, left side login prompt) despite entering correct userid/password it prompts for yet another weblogin prompt. I am clueless what to enter here.

I tried SYS & SYSTEM passwords but it did not help. During installation, it asked for an additional admin password that too does not work here. I also tried unlocking XDB & Anonymous accounts and restarted Database & listener but it did not help. tnsnames.ora has entries for both ORCL & ORCLPDB.

Does anyone have any idea what this login is for and how to proceed further?

Thanks.


texts: How to make MySQL query performant WHERE one column uses greater than and one column uses less than?

texts:
I'd like to make this query perfomant--
SELECT *
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'
AND end_time > '2020-09-07T07:00:00.000Z'

This is tricky because I need less than on start_time and greater than on end_time.  If there is a composite index on foo_id, enabled, start_time, end_time the index is good at filtering down all the rows with foo_id, enabled, and start_time matching the criteria--but then does a full scan to find the rows that match end_time.
My best efforts at optimizing so far are--
SELECT *
FROM my_table
WHERE foo_id = 8
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'
AND id IN (
  SELECT id
  FROM my_table
  WHERE end_time > '2020-09-07T07:00:00.000Z'
)

and
SELECT *
FROM my_table
WHERE foo_id = 8
JOIN my_table AS t ON t.id = my_table.id
  WHERE t.end_time > '2020-09-07T07:00:00.000Z'
AND enabled = true
AND start_time < '2020-09-12T06:59:59.999Z'

Both of these efforts try to use two indexes separately: the one on foo_id, enabled, start_time and the other on end_time.  They are both faster.  But I'd like to avoid a query with a large IN list.  And doing the JOIN just to sneak in a WHERE doesn't feel right.
Maybe this is good enough?  INTERSECTION would serve well here.  This seems like it would be common problem, so curious if I'm missing an obvious/better solution.


texts:
I have backuped sql database from my server and tried restore on my machine. Restoring window disappeared by saying successfully restored. In the database list my database is not accessible.

myDatabase(Restoring...)

This is how it looks like. When I try to access it gives this window. username/passwords were different on each machine.
From googling I found from microsoft msdn: sp_change_users_login (Transact-SQL)
What I tried is:

USE myDatabase;
GO
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, '12';
GO

In the messages panel:

  Msg 927, Level 14, State 2, Line 2
  Database 'myDatabase' cannot be opened. It is in the middle of a restore.
  The number of orphaned users fixed by updating users was 0.
  The number of orphaned users fixed by adding new logins and then updating users was 0.

How can I fix it?
UPDATE
I restored the database on another machine it worked properly.


texts: A significant part of sql server process memory has been paged out. Lots of free memory though

texts:
Having a problem where my SQL Server 2012 SP2 Enterprise server says that most of SQL Server's memory has been paged out to disk but there's over 100GB free (of 256GB).

From there system performance drops to the point where it starts dropping network connections.

Looking in Resource Monitor I see a large amount of Modified memory and lots of disk activity to the page file.

I just don't understand why it's paging if there is a lot of free memory available.

I've tried the Lock Pages in Memory setting which didn't seem to work (about to try again).

Googling the problem only seems to return hits talking about Lock Pages in Memory or situations where there actually was a memory pressure problem.

Any help or suggestions would be appreciated. Thank you!


texts: Unable to run union in parallel on SQL Server

texts:
I am unable to get this fairly simple query to parallelize the union operation:

select va.ObjectId, 0 as IsFlag
  from Oav.ValueArray va
 where va.PropertyId = @pPropertyId
   and va.value in (select value from #MatchValues)
 group by va.ObjectId
having count(distinct va.Value) = (select count(*) from #MatchValues)

union all

select odv.ObjectId, 1 as IsFlag
  from Pub.OtherTable codv
 where PropertyId = 2551
   and Id in (select value from #Ids)
   and Flag = @pFlag
   and Value in (select value from #MatchValues)
 group by codv.ObjectId
having count(distinct codv.Value) = (select count(*) from #MatchValues)

Running with MAXDOP 1 gives an expected .8s (.5 + .3).  I was hoping that increasing MAXDOP to 2 would optimize for the biggest gain by using one processor for each side but that is not the case.  Maxdop zero on a lightly loaded 12 Cpu machine all ~4% only results in parallel execution about 10% of the time.

Is there a way to weight the hints so that parallelization at the union point is the most important?  Does the syntax support separate MAXDOP's for each side?

I have tried (concat/hash/merge union) with little change.

Match values is usually a small table (~10 rows).


texts: Always On - Does the read replica need the same SQL license as the primary?

texts: We have a SQL Server 2014 Enterprise Edition (30 cores) production database. We're using Always ON to provision a read only replica. Does the read replica instance also need to be Enterprise Edition with support for 30 cores?


texts: Documenting Databases with Extended Properties - value is being overwritten

texts:
I have a query that views some information about an entire instance of databases. Using Extended Properties, I have also added information on database level to what customer the database belongs to, among other things.

To view all the information collectively, my query looks like this:

SELECT d.database_id                     AS [ID],
       d.NAME                            AS [Database Name],
       Round(Sum(mf.size) * 8 / 1024, 0) AS [Size (MB)],
       d.state_desc                      AS [State],
       d.is_read_only                    AS [Read Only],
       d.create_date                     AS [Created Date],
       Max(CASE
             WHEN SEP.NAME = 'Shared' THEN value
           END)                          AS [Shared],
       Max(CASE
             WHEN SEP.NAME = 'Product' THEN value
           END)                          AS [Product],
       Max(CASE
             WHEN SEP.NAME = 'Customer' THEN value
           END)                          AS [Customer]
FROM   sys.master_files mf
       INNER JOIN sys.databases d
               ON d.database_id = mf.database_id,
       sys.extended_properties SEP
WHERE  d.database_id > 4 -- Skip system databases
GROUP  BY d.database_id,
          d.NAME,
          d.state_desc,
          d.is_read_only,
          d.create_date
ORDER  BY d.NAME

And the results become this:

As you can see, all Extended Properties "Shared", "Product", and "Customer" will be overwritten by the first database's Extended Properties. In reality, if the query worked as intended, it would be something like this instead:

Notice the emphasis on the last three columns. I have tried to group the query on SEP.name and SEP.value, but the result have not been to any help. How does one fix this?


texts: How did my colleague get his own schema?

texts:
I have a stored procedure that:

checks if a table exists and if so, drops it.
creates that table again
then fills that table with about 30 queries.

When I (DB Owner) run this proc, everything works as intended. When my co-worker does so, who has DROP/CREATE rights on this database through a role in the Active Directory, several things go wrong. What boggles my mind is this:

The creation of the table did not have the dbo schema explicitly specified before the name. This resulted in a table called domain\cowork_id.table_name_here table to be created. Besides that table being created in his personal schema, he now also has that schema on the database (it didn't exsist prior to running the proc).

What happened? Why does SQL Server create tables in user's schema's instead of in dbo when that isn't specified?


texts: I accidentally set password to blank for a PostgreSQL database, how do I change it?

texts: When I set the password to blank, it didn't give any warnings. Now when I try to log in with a blank password, it gives me an error "fe_sendauth: no password supplied". So now I can't log in - any ideas how I can get access to the db again?


texts: Postgres get rows where a field stopps growing

texts:
I have a table which has the following structure

id SERIAL PRIMARY KEY,
device_id INT REFERENCES(another_table),
uptime TIMESTAMP

Every device is pinged every N minutes.
The application logic and db is (Sadly) made so that for each ping instead of an update to one row there is a new INSERT.

How can I get the rows where the next row's timestamp is smaller than this row's in the context where they are grouped by their id's in ascending order (you basically start from the smallest one and finish with the biggest one) ?

Can it be done with some sort of group by or should it be written in pl/SQL or something along those lines ?


texts: mysql - import only schema from full dump?

texts:
I have a full database export (single file, schama + data), but I would like to import only the database schema. Is there any magic option or trick to achieve this?
I'm NOT able to perform a "mysql_dump --no-data" at the original database at this moment. Is removing all inserts and updates manual (awk/grep/vim) from the dump file the only option?


texts: Get list of all tables and columns "touched" by application

texts:
I need to find out where in the database changes are made, originating from an application. In other words: I'm not interested in the actual value changes, I'm interested in where these changes are taking place. Can this be done and if so, how?
Context
I'm tasked with managing a gigantic application that interacts with a very large database. Even though I have the source code available it is proving quite difficult to track which changes in the database the different parts of the application cause. More specifically which tables and columns get "touched" by the actions invoked from the application.
I have a test system set up (a clone of production) with it's own database. I am the only user on the test system so I can safely say that triggered changes are caused by my own actions.
I've been reading up on CDC, but as far as I can tell if you don't know which columns are being touched you have to query ALL the columns by name. This is problematic considering the database holds hundreds of tables with hundreds of columns.
Is there any other way to get a list of changes in the database per action?


texts: MySQL (Percona): Multiple simultaneous long-running connections

texts:
I'm running a distributed app in the cloud where clients keep open multiple long-running connections to a MySQL (Percona) server.

After updating to the latest Percona release, the maximum number of simultaneously connected clients has dropped significantly. In the past, with the previous Percona version, it was used successfully with up to 8K connections, now it struggles to get above 3K.

By "struggles" I mean that when the connection bottleneck has been reached, connecting to MySQL times out even in the command line. When I do manage to connect, show processlist doesn't show any hanging queries or locks. So the connections that are established work just fine. The Apache webserver serving the PHP webapp used to monitor the project also hangs indefinitely.

Here's the my.cnf I use, patched together from various guides.

[mysqld]
open_files_limit               = 16384
table_open_cache               = 16384
character_set_server           = utf8mb4
max_connections                = 16384
expire_logs_days               = 10
max_binlog_size                = 100M
innodb_open_files              = 16384
innodb_file_per_table          = 1
innodb_read_io_threads         = 4
innodb_write_io_threads        = 4
innodb_thread_concurrency      = 0
innodb_log_file_size           = 128M
innodb_open_files              = 4000
innodb_flush_method            = O_DIRECT
innodb_buffer_pool_instances   = 1
thread_pool_size               = 16
local_infile                   = 1
skip-name-resolve
thread_cache_size              = 16384
thread_handling                = pool-of-threads
innodb_buffer_pool_size        = 512M
innodb_buffer_pool_instances   = 1
innodb_log_buffer_size         = 64M

And the Percona version I'm running:

mysqld --version
Ver 8.0.15-6 for debian-linux-gnu on x86_64 (Percona Server (GPL), Release '6', Revision '63abd08')

OS: Ubuntu 18.04.2 LTS

Hardware:

CPU: Intel(R) Xeon(R) Platinum 8175M CPU @ 2.50GHz
RAM: 32G

The queries I'm running are all based on unique indexes (no joins), tables are all InnoDB. The same configuration worked fine until the update to Percona 8.

The my.cnf settings I had to remove after the upgrade as they were no longer supported are:

innodb_locks_unsafe_for_binlog = 0
query_cache_size               = 0
query_cache_type               = 0

Things I tried:

I ran mysqltuner.pl but didn't get any relevant advice from it.
ulimit -n and ulimit -s show values significantly higher than what I'm using (1048576 and 16384 specifically).

Any other suggestions?


texts: Is it possible to Increase storage performance in azure vm using disk striping?

texts:
Given the following Azure VM:

E8s_v3 (8 cpu, 64 GB RAM, 12800 IOPS, 192 MBps)
I added 2 SSD Premium drives, one for data (1024 GBs / 200 MBps / Host Caching enabled read-only) and one for Logs (256 GBs / 125 MBps / No Cache enabled)

When I perform a simple test with CrystalDiskMark I get 134 MBps for the data disk and 127 MBps for the log file. I assume that the data disk is not getting closed to 192 MBps (Azure VM limit) because of the enabled host caching.

My question is: if I use disk striping, let's say, instead of 1 TB disk, I add 2 512 GBs (150 MBps); would that give me around ~300 MBps?? Or simply there's no way that one volume (stripe or not), inside the azure VM, runs faster than the limit set by the VM size, 192 MBps for our example VM model: E8s_v3?

Any idea on how to improve storage performance? The only way I see would be to increase the VM size and the disk size but of course, that would increase our cost a lot. In our local servers, we have really fast SANs that are around thousands of MBps and it's impossible to get those speed in the cloud at a rational cost, if ever.

Thanks!


texts: What is purpose of `ALLOW_CONNECTIONS` being set so no one can connect to the new Postgres database?

texts:
In Postgres 11, the CREATE DATABASE command that makes a new catalog has an option ALLOW_CONNECTIONS. The documentation says this option can be used to block all connections to the database:

[ ALLOW_CONNECTIONS [=] allowconn ]
allowconn
If false then no one can connect to this database. The default is true, allowing connections (except as restricted by other mechanisms, such as GRANT/REVOKE CONNECT).

➥ Why would anyone want to create a database that nobody can connect to?
➥ If you cannot connect, how can you ever change this setting to true to allow connections?


texts:
I have a table with ~40 million entries and I have an index for most of my fields. All my queries are optimized, except for one, which gets called a couple times per second and uses most of the fields. Now I'm investigating on how to optimize this query, do I:

create an index over all fields?
create an index over all fields and remove the individual indexes?

If I have an index over all fields, would I even need the individual indexes?

This is the query:

SELECT count(*) as rang_pos
FROM results
WHERE results.created >= DATE_SUB(NOW(),INTERVAL 24 HOUR)
AND results.speedtest_id = 1
AND results.checked = 1
AND results.typing_mode = 1
AND results.keystrokes <= 500

Indexes


texts: Grant Select on some columns for specific user

texts:
I have a windows group with 20 people and all 20 people in the group were revoked access for some columns in the table. Now I need to add the select permissions for 2 of the users in this windows group. How can I do this?
Can I simply grant select on these columns directly to these 2 users instead of windows group?

Thank you


texts:
I just got talking to DBA as he asked me: What's best, memory speed or memory amount, e.g. 512GB @ 1866MHz or 768GB @ 1333MHz, when running the database in-memory?

We've both been looking for stats, benchmarks and experiences all over the internet, but with no luck, so I thought I'd try my luck here.


texts: How to free space after I drop a column of a table in SQL Server?

texts:
The background is that there is a column Fn in a table DataProcessed in SQL Server. Plus, the property of Fn is varchar(max), NOT NULL.
The problem is that I hope to drop Fn from DataProcessed, and free the space which is occupied by Fn in the NDF files of DataProcessed.
How can I finish that? Or, is this doable?


texts:
I recently inherited an environment with around 4000+ databases distributed between 21 instances on 3 huge physical servers. Databases are distributed between different instances based on the name, so some instances are a few times bigger than others. Schema is identical for all databases, but data is not. Some databases are just few MB and other are 400+GB.

Management is considering whether we should try to keep as many databses as possible on each instance and have fewer huge instances. Another idea is to keep adding small instances to existing environment or even reduce the number of databases in each existing instance and increase the number of instances.

Administration wise its probably easier to have fewer big instances than many small instances. On the other hand if something happens to big instance then all databases on it will be affected, so it may be safer to have many small instances.

What would be better from the performance point of view? What other factors should I take into account? How many databases should I aim to keep on each instance? I know that it depends, but do you have some rough estimates? For example is 500 databases on one instance too many? If you need any more information please let me know.

I forgot to mention that all these databses are production databses of different clients and so all of them are equally important. Development, testing etc are on different servers.


texts: Simple recursive CTE stuck in infinite loop


texts: Strange Garbage Character in SQL server database like ÂÃÂÃÂÃÂÃÂÃÂÃÂÃ

texts:
I am using HTMLBOX editor for getting Notes/RichText Data input from User. I found there are lot of garbage characters in my database and they keep on doubling on each time user save/update notes.

Below is code sample for my PHP to SQL server connection.

<?php
class Model {
    public static $db;

    public static function initialize() {
        self::$db = new PDO('odbc:Driver={SQL Server};Server='.DBCONNECTION_SERVER.';Database='.DBCONNECTION_DATABASE.'; Uid='.DBCONNECTION_USER.';Pwd='.DBCONNECTION_PASS.';', null, null,array(PDO::ATTR_PERSISTENT => true));
        self::$db->setAttribute(PDO::ATTR_CASE, PDO::CASE_LOWER);
        self::$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        self::$db->setAttribute(PDO::ATTR_EMULATE_PREPARES , false);
    }
}
?>

I search for same problem and most of solution suggest to use UTF8 encoding. I set below attribute but no success. :(

self::$db->setAttribute(PDO::SQLSRV_ATTR_ENCODING , PDO::SQLSRV_ENCODING_UTF8);

Please see below screenshot.

Update:

Column Type : NVARCHAR(MAX)

User Input : User can enter or Copy/paste anything. Those garbage character appears when user copy paste from Outlook or other Rich Text Source. The single quote(') from outlook get converted to (Ã¢ÂÂ) garbage character.


texts: Authenticating to Oracle REST data services as admin

texts:
i'm working on an android app connected to a database, the database has more data than any tablet can handle, so after some research i found many solutions, most of them consisted of implementing a webservice between the app and the database itself, i found this and this, i just installed the latest version of ords, had a couple issues to get the web interface to work but now that it's working, i cannot login, i did find a couple solutions but none of them seems to work , any ideas ? what are the default identifiers supposed to be ? i'm lost here with like 50 tabs

thank you

https://imagizer.imageshack.us/v2/867x679q90/571/22qo.png

https://imagizer.imageshack.us/v2/753x796q90/855/u9b3.png

ps : i checked the apex_public_user and the apex_040200 users and they were 'expired' is that normal ?

if you need any more information please let me know


texts: Best way to add Foreign Key to massive table?

texts:
Say I have 4 tables: arrival (~100 million rows), departure (~100 million rows), airport (~30 million rows), airplane (small).

arrival and departure each have 2 ForeignKeys: one to airport, one to airplane.

Currently, to figure out which planes are at an airport at a certain time, I have to do a massive join between arrival, departure, and airport. So, I'd like to add a (possibly NULL) ForeignKey to the departure table that points to the arrival of the same airplane that's departing.

Given that these tables are so big, it seems like a bad idea to do this all as a single transaction. So, what's the best way to go about it?


texts: Best mysql db design to solve timezone issues

texts:
I faced problem with timezone on my last two projects. So What is best way to sort out problems with timezone on db design.

I mean, What format should i added date time stamp.

On of my problem. My client asked me to expire particular event on users timezone. How can i solve this.

Thanks in advance.


texts: Unable to attach database to Amazon RDS using SQL Server Management Studio 2016

texts:
I'm attempting to attach an mdf file to an instance of SQL Server running on Amazon's RDS service.  Using Microsoft SQL Server Management Studio 2016, I follow the following steps:

After connecting to the amazon instance, I right click on the Databases directory and select Attach.
Up comes the Attach Databases dialogue
I click the Add button to select the local .mdf
I get the following error message:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

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

The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'.
The EXECUTE permission was denied on the object 'xp_fixeddrives', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229)

I've seen this error message on several forums but always when trying to modify the database (at this point I believe I'm only bringing up a file browser to locate the mdf).  I've also tried running it as admin but no luck.  I'm quite new to SQL Server and am much more at home in a Linux environment, so I wouldn't rule out something obvious.  Any help would be appreciated.


texts: Why does the PG manual state to modify the "pg_hba.conf" file before starting the server the first time?

texts:
The following is from https://www.postgresql.org/docs/current/static/creating-cluster.html with original emphasis on before:

  or modify the generated pg_hba.conf file after running initdb, but before you start the server for the first time.

Is the manual saying this to prevent unauthorized users from accessing a poorly secured PG server? Or is there some other reason why the pg_hba.conf file should be edited before starting the server for the first time?


texts:
I have the below fields and would like to add the contract_term field to the date field:

Contract_Term: Years
Date: YYYY/MM/DD

For example: 3(contract_term) + 2019/01/01(date) = 2022/01/01

What would be the best way for me to achieve the above result?

Thanks for any help.


texts: PgBouncer on separate server than PostgreSQL

texts:
Right now I am hosting PostgreSQLand PgBouncer on the same Linode with 48GB of RAM and 12 Cores.

The server gets many short lived connections per second. The server load will increase with time and I want to reduce the server load as much as possible.

When I look at the 'top' command on the server, I see that PgBouncer is taking about 30% CPU usage. Would I benefit by creating another Linode and running PgBouncer on that server by itself?


texts: MySQL Group Replication - Offline Node with cluster Metadata 1.0.1

texts:
I have a production MySQL Group Replication setup out of three Nodes. The cluster has Metadata Version 1.0.1. Today, one node went down an during rejoin phase, it says, it cannot connect to the cluster.
When trying to dba.rejoinInstance('');, the MySQL Shell says, it won't do it, because it will only make changes to a cluster with Metadata 2.0.0.
ERROR: Unable to start Group Replication for instance 'O18-SQL-NBG:3306'. Please check the MySQL server error log for more information. Cluster.rejoinInstance: Group Replication failed to start: MySQL Error 3092 (HY000): The server is not configured properly to be an active member of the group. Please see more details on error log. (RuntimeError)
The logfile says:
[MY-011521] [Repl] Plugin group_replication reported: 'Member version is incompatible with the group.'
When trying to upgrade Metadata on the cluster to 2.0.0, it says, it won't do it either, because one Member of the cluster is not available.
Dba.upgradeMetadata: This operation requires all the cluster members to be ONLINE (RuntimeError)
When trying to rescan() the cluster, it says, it won't it, because my metadata is out of date and it decided to only do read-only operations on this cluster:
Cluster.rescan: Operation not allowed. No cluster change operations can be executed because the installed metadata version 1.0.1 is lower than the version required by Shell which is version 2.0.0. Upgrade the metadata to remove this restriction. See \? dba.upgradeMetadata for additional details. (RuntimeError)
I have several versions of MySQL Shell available (8.0.17, 8.0.19 and 8.0.20). My primary objective is - guess what - to get the faulty node back up into the cluster. What are my options?


texts:
I use MariaDB galera cluster 10.0.31 with 3 nodes.

In one node when I tail -f err log to monitor server, Every 20 seconds MySQL dumping Show InnoDB Status to err log, and the log are getting bigger.

I don't know Which variable cause to dump output Innodb status to Err log file!


texts:
For scheduling a daily back up on a postgres database we execute the following command:

0 2 * * * /usr/local/pgsql/bin/pg_dump -Fp -b -U database_name \
          > $HOME/db_backups/database_name-`date +\%Y\%m\%d`.sql \
          2>> $HOME/db_backups/cron.log

After the code executes, the log says "invalid password". Is there a way to specify a password or avoid the requirement when creating a dump?

Also we  would like to retrieve the dump and pop it on separate Windows server  using pgadmin3. When restoring using pgadmin dbase -> database restore the 'OK' button is disabled and we are unable to restore the database.


texts: Shut Down MySQL during InnoDB rollback after crash

texts:
I am puzzled once again.

We had a server crash last night during some huge queries. Upon restart in the morning MySQL went into recovery mode. I needed to shut down the MySQL service and it is waiting for... I assume the background rollback for uncommitted transactions as the log says but it's been at it on a HOT server for over 9 hours with no end in sight.

Can the process be safely interrupted and resumed in background while service restarts? Or do we gotta wait it out gasp.

Any input or ideas (or even info about what's going on) would be super helpful. In advance, thank you.

We are running MySQL 5.5.

Here's the log output:

140930  9:04:30  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 41217466, file name .\mysql-bin.001584
InnoDB: Starting in background the rollback of uncommitted transactions
140930  9:26:14  InnoDB: Rolling back trx with id B214637, 15850890 rows to undo

InnoDB: Progress in percents: 1140930  9:26:14  InnoDB: Waiting for the background threads to start
140930  9:26:15 InnoDB: 1.1.8 started; log sequence number 1661733560991
140930  9:26:15 [Note] Server hostname (bind-address): '(null)'; port: 3306
140930  9:26:16 [Note]   - '(null)' resolves to '0.0.0.0';
140930  9:26:16 [Note] Server socket created on IP: '0.0.0.0'.
140930  9:26:18 [Note] Event Scheduler: Loaded 0 events
140930  9:26:18 [Note] wampmysqld: ready for connections.
Version: '5.5.24-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
140930 12:19:36 [Note] wampmysqld: Normal shutdown

140930 12:19:36 [Note] Event Scheduler: Purging the queue. 0 events
140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 20  user: 'root'

140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 19  user: 'root'

140930 12:19:38 [Warning] wampmysqld: Forcing close of thread 17  user: 'root'

140930 12:19:38  InnoDB: Starting shutdown...
140930 12:20:44  InnoDB: Waiting for 1 active transactions to finish
140930 12:21:47  InnoDB: Waiting for 200 pages to be flushed
140930 12:21:49  InnoDB: Waiting for 1 active transactions to finish
140930 12:22:55  InnoDB: Waiting for 1 active transactions to finish
140930 12:24:01  InnoDB: Waiting for 1 active transactions to finish
140930 12:25:07  InnoDB: Waiting for 1 active transactions to finish
140930 12:26:12  InnoDB: Waiting for 1 active transactions to finish
 ... for 9 hours


texts: How to Search a Database of Package Deals for Specific Items with the Fewest Unwanted Extra Items

texts:
Short Description

I have a database of special offers that bundle several items together.  I want to be able to search for a list of specific items and get a combination of bundles that includes all those items with the fewest unwanted extras.

Example

My database has bundles like these:

Bundle 1: Item A X2, Item B X1, Item D X4
Bundle 2: Item A X5, Item C X1, Item E X3
Bundle 3: Item B X2, Item C X3, Item D X3
Bundle 4: Item C X10, Item D X1, Item F X4

I want 10 of Item A, 4 of Item C, and 1 of Item E.  I want to search for a combination of bundles that yields the items I want with the fewest extra items.  The items I want can vary and should be easily editable in the search.


texts: Equivalent of SQL’s "INNER JOIN USING" in relational algebra

texts:
I'm following a course in databases (Standford introduction to databases on YT) and, at some point, it's mentioned that t1 INNER JOIN t2 USING attr1, attr2 is equivalent to the NATURAL JOIN symbolized by a bow tie1 in relational algebra.

The INNER JOIN … USING … expression, however, can point out what columns to use for the JOIN, while the bow tie2 operator in relational algebra does not seem to be capable of expressing that.

What is the exact representation of an SQL INNER JOIN … USING … in relational algebra? Can someone give me an example to nudge me in the right direction?

Notes

1, 2 The bowtie with no subscript is condisered the NATURAL JOIN, while the bowtie with the condition as a subscript is considered the THETA JOIN. Here I was refering to the bowtie with no subscript.


texts:
I'm migrating a SQL 2008 database from a corporate data center to an RDS intance on AWS.

So far I have performed a native backup which gave me a .bak file

Is it possible to simply upload this to S3 and 'restore' the .bak to a new RDS instance?

What's the best approach to take?


texts: Problems with connecting to a remote Oracle 12c database

texts:
tl;dr
RMAN failing to connect to standby database with correct credentials.

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Password file is correct, password is correct.

The long version.

I'm trying to set up a dataguard pair of servers at the moment and I'm running into problems with passwords.

I start with a fresh install of Oracle 12c. Global database name is orcl.domain and SID=orclprmy

I've checked the ORACLE_SID set in .bash_profile and it matches with orclprmy

I then go in and do all my settings to prepare it as my dataguard primary.

From that instance I create a password file using the following:-

rm $ORACLE_HOME/dbs/orapworclprmy
orapwd file=$ORACLE_HOME/dbs/orapworclprmy password=<password>

I've checked on on the primary REMOTE_LOGIN_PASSWORDFILE = 'EXCLUSIVE' so it should be using that password file.

I also sort out the TNSNAMES and LISTENER configuration files to recognise both servers.

I then install oracle in the same way on a separate standby server, the only difference being the database SID being set to orclstby. Again, I've check the ORACLE_SID on that server and it'a also correct.

I scp the password file over from primary to standby (orclstby) and rename it to orapworclstby. I also copy over the TNDNAMES and LISTENER files from primary to standby and edit them as required.

So far, so good. Everything appears to be running properly.

Then comes the part where I prepare the standby to be recovered with RMAN to be the standby database.

vi $ORACLE_HOME/dbs/initorclstby.ora
   db_name=orclstby
slqplus / as sysdba
   SHUTDOWN IMMEDIATE
   STARTUP NOMOUNT pfile='/u01/app/oracle/product/12.0.1/db_home1/dbs/initorclstby.ora'
   QUIT

That should put the standby database into a state where it's ready to take the rman connection.

I then go back to the primary server and start rman

rman
CONNECT TARGET sys/<password>@orclprmy
CONNECT TARGET sys/<password>@orclstby

And then I'm in trouble. RMAN comes back with

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

I've checked an on standby REMOTE_LOGIN_PASSWORD='EXCLUSIVE', so as far as I can tell it should be using the same password file as primary, and I'm using the same password for everything anyway (it's a proof of concept piece).

Trying sqlplus sys/password@orclstby  from primary fails as well:-

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86-64 Error: 2: No such file or directory

What's really annoying is that I did have this working a while ago, but I must have changed something and I can't work out what it is.

Where should I be looking to see why the standby isn't using the password file (or if it is, why I can't use the password from primary).

I've been working on this for a couple of days now, so it could be I'm missing something obvious.

Many thanks,

David


texts: Incremental Backup. Base backup creation frequency?

texts:
So I'm trying to understand how Continuous Archiving and Point-In-Time-Recovery works.

From what i've read there are some steps involved for the incremental backup and some steps for the recovery:

Summary of PostgreSQL Backup Steps

Modify postgresql.conf to support archive log
Make a base backup (full database backup)
Copy base backup to remote storage.
Backup WAL (archive log files) to remote storage (continuous process)

Summary of PostgreSQL Point-in-time Recovery Steps

Extract files from base backup
Copy files from pg_xlog folder
Create recovery.conf file
Start Recover

So my questions is: Is it safe to stick with this base backup forever (plus all the wal files) until an error appears, and then apply the PITR steps?  Or should I re-generate the base backup periodically (each week, month?) in order to minimize any type of error when trying to recover the database?

Thanks in advance.


texts: How do I generate the default collections from a fresh Django installation inside a Mlab database?

texts:
When I use Djongo to install the default Django installation migrations onto my local computer server's MongoDB Compass sever, the collections such as 'auth_group', 'auth_group_permissions', 'auth_permission', 'auth_user', 'auth_user_groups', 'auth_user_user_permissions', 'django_admin_log', 'django_content_type', 'django_migrations', 'django_session' show up. However, when I do the same installation on my Mlab server database, only 'schema' and 'django_migrations' show up.
Am I doing something wrong for my Mlab Django database installation or is that the way it is supposed to work on a fresh Django database installation on Mlab?


texts: How to use temporary variable in Update query?

texts:
How can I set a variable while doing an Update statement?

I can't seem to figure out the syntax. I am using this @tp variable for holding calculated result and it will be used in when condition.

I want something like this:

UPDATE users IU
INNER JOIN activity_stats DAS
    ON (IU.user_id=DAS.for_user_id) SET
    @tp:= IU.total_subscription_time - (unix_timestamp()- IU.start_date),
    IU.time_period_left = CASE WHEN @tp > 0 THEN @tp ELSE 0 END,
    IU.used_time = (unix_timestamp() - IU.start_date),
    IU.status = CASE WHEN @tp > 0 THEN 'A' ELSE 'I' END,
    DAS.total_time_used = DAS.total_time_used + IU.used_time
WHERE IU.time_period_left > 0
  AND IU.status='A'

but it's saying the syntax is wrong:


texts:
I have a database that's about 3TB in size and want to upgrade to SSDs. The SSDs I found that are reasonably priced are only 512GB so I'll need to purchase multiple drives.

What are some ways to split the database file so that it can be accessed from multiple drives without much code changes or needing a RAID controller.

The database software in question in sql server 2014 enterprise edition, but it would be best to find a solution without requiring database engine specific features.


texts: A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSDTC service

texts:
Experiencing this intermittent issue on a few servers running MSDTC.

19/09/2018 10:54:38 a.m. -- A timeout (30000 milliseconds) was reached while waiting for a transaction response from the MSDTC$cde1a4e7-dc9d-4ae3-89b9-8114147ae420 service.

19/09/2018 10:54:38 a.m. -- Cluster resource 'MSDTC-SQLServer' of type 'Distributed Transaction Coordinator' in clustered role 'SQL Server' failed.

19/09/2018 10:54:57 a.m. -- The Distributed Transaction Coordinator (cde1a4e7-dc9d-4ae3-89b9-8114147ae420) service terminated unexpectedly.  It has done this 12 time(s).

All these servers run trend micro deep security agent ( Anti Malware protection and Intrusion Prevention ). The issue completely goes away when AV is turned off. But having the AV turned off for long is not an option.

Have configured the following AV exclusions for MSDTC.

MSDTC Service
MSDTC Log file
MSDTC Log Folder

are there any more exclusions that I should get added ?


texts: How to know how full the MySQL redo log is?

texts:
Setting innodb_log_file_size to a higher value improves write performance, but increases crash recovery time (or even restart time, if I understand correctly).
Is there a way to know, at any time, how full the redo log is? To get an idea of how long the crash recovery would take.
Subsidiary question: is there a way to force flushing the redo log, while MySQL is running?


texts: SQL Server 2016 high idle CPU and queries extremly slow

texts:
I have a ~10 day old installation of WinServer2012R2 and SQL Server Express 2016 for testing. I am the only user on this machine. A database with a .bak from SQL Server 2005 of ~250MB is restored without any issues. After a reboot of the machine the process "SQL Server NT - 64 Bit" uses 0% CPU.

After a couple of minutes or hours and some simple queries (no updates/inserts!) from SSMS CPU usage of "SQL Server NT - 64 Bit" suddenly jumps to ~15% and stays there, even when idle. From that point on queries which usually take less than a second suddenly take 2 minutes. During an actual query the CPU usage does NOT increase. The server becomes virtually unusable in this state.

Only connecting SQL Server Profiler then takes >30sec. Beside my own queries I see only very few queries from SQLServerCEIP / SQLTELEMETRY, ~3 per minute.

Restarting SQL-Server does not solve it. CPU usage jumps right back to ~15%. Even after hours SQL-Server does not recover. Only rebooting the entire machine solves the issue.

As this is an "out of the box" installation, there is only a small database, virtually no queries, only me as a user and probably no locks, the many articles on regular SQL-Server performance issues talk about lot of things that don't really apply here. It seems as SQL-Server exclusively wants to concentrate on some internal task.

This is a virtual machine with 2GB RAM and dual Xeon at 2GHz. I also have VS2016 on it and it's really fast. No antivirus, not even Windows Defender. Already late here. I will try the sp_whoisactive tomorrow. I really wonder WHAT SQL-Server IS doing there... On the previous machine with 1 GB the same DB ran under SQLServer2005 for 10 years without issues...

I am not an SQL-Profiler expert. Where should I start looking?


texts: With PostgreSQL, I want to replicate a subset of all tables from Database A to B, but only once a day. Since I do not know what to search for: is there a technical term for such a scenario? Does e.g. Slony-I support this?


texts: Postgresql streaming replication - choose databases to sync

texts:
I have been reading on the Postgresql replication streaming (as described here) and was wondering if it is possible to choose which databases are synced, or if it has to be the entire DB server.  E.g. if I have database1, database2, and database3 in my master server, can I have my slave server sync only database1 and database2?  And further, could I have a second slave server sync a different subset of databases, like only database3?

I am using postgresql version 9.6.


texts:
The following query takes over an hour on the table to insert.

There are no indexes. There was one which was deleted but still takes an hour after deletion.

Any pointers on improving this appreciated.

INSERT  INTO SAPECC_Full.dbo.[vbep]
    ( [MANDT] ,
      [VBELN] ,
      [VBELN_oco] ,
      [POSNR] ,
      [POSNR_oco] ,
      [ETENR] ,
      [ETTYP] ,
      [LFREL] ,
      [EDATU] ,
      [EZEIT] ,
      [WMENG] ,
      [WMENG_oco] ,
      [BMENG] ,
      [BMENG_oco] ,
      [VRKME] ,
      [LMENG] ,
      [MEINS] ,
      [BDDAT] ,
      [BDART] ,
      [PLART] ,
      [VBELE] ,
      [POSNE] ,
      [ETENE] ,
      [RSDAT] ,
      [IDNNR] ,
      [BANFN] ,
      [BANFN_oco] ,
      [BSART] ,
      [BSTYP] ,
      [WEPOS] ,
      [REPOS] ,
      [LRGDT] ,
      [PRGRS] ,
      [TDDAT] ,
      [MBDAT] ,
      [LDDAT] ,
      [LDDAT_oco] ,
      [WADAT] ,
      [CMENG] ,
      [LIFSP] ,
      [GRSTR] ,
      [ABART] ,
      [ABRUF] ,
      [ROMS1] ,
      [ROMS2] ,
      [ROMS3] ,
      [ROMEI] ,
      [RFORM] ,
      [UMVKZ] ,
      [UMVKN] ,
      [VERFP] ,
      [BWART] ,
      [BNFPO] ,
      [ETART] ,
      [AUFNR] ,
      [PLNUM] ,
      [SERNR] ,
      [AESKD] ,
      [ABGES] ,
      [MBUHR] ,
      [TDUHR] ,
      [LDUHR] ,
      [WAUHR] ,
      [AULWE] ,
      OcoDateChanged
    )
    SELECT  RTRIM([MANDT]) ,
            RTRIM([VBELN]) ,
            dbo.SAPLeading0s(VBELN) ,
            RTRIM([POSNR]) ,
            dbo.SAPLeading0s(POSNR) ,
            RTRIM([ETENR]) ,
            RTRIM([ETTYP]) ,
            RTRIM([LFREL]) ,
            RTRIM([EDATU]) ,
            RTRIM([EZEIT]) ,
            RTRIM([WMENG]) ,
            dbo.SAPNumeric(WMENG) ,
            RTRIM([BMENG]) ,
            dbo.SAPNumeric(BMENG) ,
            RTRIM([VRKME]) ,
            RTRIM([LMENG]) ,
            RTRIM([MEINS]) ,
            RTRIM([BDDAT]) ,
            RTRIM([BDART]) ,
            RTRIM([PLART]) ,
            RTRIM([VBELE]) ,
            RTRIM([POSNE]) ,
            RTRIM([ETENE]) ,
            RTRIM([RSDAT]) ,
            RTRIM([IDNNR]) ,
            RTRIM([BANFN]) ,
            dbo.SAPLeading0s(BANFN) ,
            RTRIM([BSART]) ,
            RTRIM([BSTYP]) ,
            RTRIM([WEPOS]) ,
            RTRIM([REPOS]) ,
            RTRIM([LRGDT]) ,
            RTRIM([PRGRS]) ,
            RTRIM([TDDAT]) ,
            RTRIM([MBDAT]) ,
            RTRIM([LDDAT]) ,
            dbo.SAPDate(LDDAT) ,
            RTRIM([WADAT]) ,
            RTRIM([CMENG]) ,
            RTRIM([LIFSP]) ,
            RTRIM([GRSTR]) ,
            RTRIM([ABART]) ,
            RTRIM([ABRUF]) ,
            RTRIM([ROMS1]) ,
            RTRIM([ROMS2]) ,
            RTRIM([ROMS3]) ,
            RTRIM([ROMEI]) ,
            RTRIM([RFORM]) ,
            RTRIM([UMVKZ]) ,
            RTRIM([UMVKN]) ,
            RTRIM([VERFP]) ,
            RTRIM([BWART]) ,
            RTRIM([BNFPO]) ,
            RTRIM([ETART]) ,
            RTRIM([AUFNR]) ,
            RTRIM([PLNUM]) ,
            RTRIM([SERNR]) ,
            RTRIM([AESKD]) ,
            RTRIM([ABGES]) ,
            RTRIM([MBUHR]) ,
            RTRIM([TDUHR]) ,
            RTRIM([LDUHR]) ,
            RTRIM([WAUHR]) ,
            RTRIM([AULWE]) ,
            GETDATE()
    FROM    vbep_full


texts: Transpose two-dimensional array from n rows to 2 columns

texts:
Background

Using PostgreSQL 9.1, so WITH ORDINAL (a 9.4 feature) is not available.

Problem

Looking to simplify the code that pivots a two-dimensional array.

Code

A working, overly-verbose example that illustrates the problem is:

SELECT
  u.aspect,
  u.preference
FROM (
  SELECT
    t.aspect_preference AS aspect,
    -- Skip every second row
    seq % 2 AS seq,
    lead( aspect_preference, 1 ) OVER (ORDER BY t.seq) AS preference
  FROM (
    SELECT
      unnest( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] ) aspect_preference,
      -- Maintain array order after unnesting to a result set
      generate_series( 1,
       (array_ndims( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[] ) *
        array_length( '{ {"COLOUR_SCHEME", "RASPBERRY_BLISS"}, {"FONT", "TERMES_HEROS"}, {"LIST_LAYOUT", "BULLET_SNOWFLAKE"} }'::text[], 1 ) )
      ) seq
  ) t
  ORDER BY
    t.seq
) u
WHERE
  u.seq = 1

This produces the correct results with the unnest'ed array rows pivoted into the desired columns:

aspect          | preference
----------------+--------------------
COLOUR_SCHEME   | RASPBERRY_BLISS
FONT            | TERMES_HEROS
LIST_LAYOUT     | BULLET_SNOWFLAKE

Question

What is a simpler way to write this query (e.g., fewer lines of code, fewer nested SELECTs, or fewer references to the array)?


texts: schema shows through standard client connection but not through ODBC connection?

texts:
Hoping you can help!
The Gist
When I log into an Oracle 11g DB using SQL Developer or DBVisualizer, I can see schemas and execute queries against them.
However, when I use the same credentials to connect via an ODBC connection in Crystal Reports (2008), those schemas do not show up as being available to me.
Supporting Information

The ODBC connection will ultimately be created on Windows Server 2008
I'm currently testing the ODBC connection / setup on Win7 Enterprise x64
We are connecting to the DB via LDAP, not tnsnames (no port information specified -- only the SID and user/pass)
I can login to the database just fine in DBVisualizer and run a query against it
The ODBC connection tests successfully after logging in with the username and password
The connection to ODBC is successfully made by Crystal Reports; it enumerates certain system users, etc. -- just not the users I need access to (and that I have access to via a standard client)
Our DB is managed separately by a team of DBAs, who aren't the easiest folks to reach. I can, however, get questions answered most likely.

Help?
Any thoughts on where I could start or what the issue might be? Thanks!


texts: Query JSON in Azure SQL DB to get one array element per row

texts:
I have a stored procedure in a SQL Azure DB instance, and the input parameter is a JSON string formatted like this:

{
    "OrderNumber":["OD12034"],
    "Site":["New York"],
    "LineNumber":[1000,2000],
    "ItemNumber":["BX12345","BX12349"],
    "Quantity":[23,15]
}

I would like to parse this so that I can insert records into a table like this:

ArrayKey    | ArrayElement
------------|-----------
OrderNumber | OD12034
Site        | New York
LineNumber  | 1000
LineNumber  | 2000
ItemNumber  | BX12345
ItemNumber  | BX12349
Quantity    | 23
Quantity    | 15

So far I have been able to get the data out of the JSON, but not in the format I want. Here is one simplistic example.

SELECT * FROM OPENJSON(@JsonParameter)

key              |value                      |type
-----------------|---------------------------|----
OrderNumber      |["OD12034"]                |4
Site             |["New York"]               |4
LineNumber       |[1000,2000]                |4
ItemNumber       |["BX12345","BX12349"]      |4
Quantity         |[23,15]                    |4

And here is another (opposite of simplistic) example.

SELECT
       [ON].OrderNumber
     , [S].Site
     , [LN].LineNumber
     , [IN].ItemNumber
     , [Q].Quantity
FROM OPENJSON(@JsonParameter)
    WITH (
         [OrderNumber]      NVARCHAR(MAX)   '$.OrderNumber'     AS JSON
        ,[Site]             NVARCHAR(MAX)   '$.Site'            AS JSON
        ,[LineNumber]       NVARCHAR(MAX)   '$.LineNumber'      AS JSON
        ,[ItemNumber]       NVARCHAR(MAX)   '$.ItemNumber'      AS JSON
        ,[Quantity]         NVARCHAR(MAX)   '$.Quantity'        AS JSON
    ) AS jsonValues
CROSS APPLY OPENJSON(jsonValues.OrderNumber)
    WITH (OrderNumber NVARCHAR(25) '$') AS [ON]
CROSS APPLY OPENJSON(jsonValues.Site)
    WITH (Site NVARCHAR(25) '$') AS [S]
CROSS APPLY OPENJSON(jsonValues.LineNumber)
    WITH (LineNumber INT '$') AS [LN]
CROSS APPLY OPENJSON(jsonValues.ItemNumber)
    WITH (ItemNumber NVARCHAR(31) '$') AS [IN]
CROSS APPLY OPENJSON(jsonValues.Quantity)
    WITH (Quantity INT '$') AS [Q]

OrderNumber  |Site        |LineNumber |ItemNumber    |Quantity
-------------|------------|-----------|--------------|-----------
OD12034      |New York    |1000       |BX12345       |23
OD12034      |New York    |1000       |BX12345       |15
OD12034      |New York    |1000       |BX12349       |23
OD12034      |New York    |1000       |BX12349       |15
OD12034      |New York    |2000       |BX12345       |23
OD12034      |New York    |2000       |BX12345       |15
OD12034      |New York    |2000       |BX12349       |23
OD12034      |New York    |2000       |BX12349       |15

Is there way to get one row per array element through the use of JSON tools in Azure SQL DB?


texts: Will AlwaysOn work with hundreds of databases in 1 group/multiple groups?

texts:
I have inherited a SQL 2017 system that had 114 databases that were synched to a second instance via an Always-On AG, with another 13 databases needing to be added. It was supposed to be providing High Availability.
Long story short, it ended in tears as I started to add in the additional ones.
I know that Microsoft has tested Always-On with up to 100 databases. What I have not been certain about is whether that limit of 100 is a SQL instance limit or per AG.
What I would like to know is if anyone has had experience/success with Always-On with a large number of databases, say 2 or 300? If so was this in a single or multiple groups.
This installation failed due to insufficient memory - I found out the secondary has half the memory of the primary so it is no surprise it came a cropper. There were HADR waits indicating throttling of requests being sent from the primary.
There is a great blog article by Bob Dorr about Always-On and worker pool usage ( https://docs.microsoft.com/en-gb/archive/blogs/psssql/alwayson-hadron-learning-series-worker-pool-usage-for-hadron-enabled-databases )
If the secondary server is beefed up memory-wise, and possibly the primary, is it worth attempting to get this to work using the info from Bob Dorr's article? Or do I cut my losses and go with a SQL cluster, and then log shipping when management realise the costs involved?
Thanks for any thoughts
Simon


texts:
I am thinking about scaling issues with many-to-many join tables.

I have a constructed example as following:

Many users have many wishlists.
On every wishlist are many items.

If I scale this table to million of users the wishlist_items table
will get very big:

1000000 users
100 wishlists for every user
around 1000-10000 items on every wishlist (bad example, but you get the point)

Therefore table size for the join table will be:

users * wishlist_per_user * items_per_wishlist

I think this is a very common situation in RDBM design. I am afraid that this users * wishlist_per_user * items_per_wishlist relation will grow out of bound.

Are there any similar experiences with such big tables and how to query them fast? How would I design my tables to prepare for better scaling?


texts: How does to_tsquery and to_tsvector decide which characters to omit?

texts:
I was quite surprised to find that TO_TSVECTOR('english', '平仮名ひらがな'); results in a vector of '平仮名ひらがな':1. For a system that omitted all non-alphanumeric characters and emojis I threw at it, I expected it to also omit all foreign characters; i.e. the desired behavior is to only index alphanumeric characters.

How does it decide which characters to omit (is it a whitelist/blacklist, a range of character codes, etc)? I don't really mind it automatically converting accented characters to their normal alphanumeric counterpart, but I would like to efficiently omit all characters that don't have an alphanumeric representation.

edit - based from jjane's answer, this is the final solution I'm going with:

CREATE TEXT SEARCH CONFIGURATION basic (parser = pg_catalog.default);
ALTER TEXT SEARCH CONFIGURATION basic ADD MAPPING FOR asciiword, asciihword, hword_asciipart WITH english_stem;
--optionally ALTER TEXT SEARCH CONFIGURATION basic ADD MAPPING FOR email, host WITH english_stem;


texts:
I would like to setup alerts from within the maintenance plans.
When you setup a notification from the Agent Job it sends out the Job name, duration, status, message.

If I add a Notify Operator Task it want's a subject and body. Will it still include the above information?

If not how can I have this information included?


texts: Several Foreign Key and Cascade Delete SQL Server

texts:
In SQL Server 2014, I'm trying to add CASCADE DELETING (I want to set field to null actually, but is the same ) on 3 FK. If I add a Cascade Delete in one relationship, it works fine. If I add more Cascade Deletes, it doesn't work (Cycle detected error message).

In the above diagram, you can see the Users table, and a Tasks table ("Tareas" in spanish). So, what I need to acomplish is when the user is deleted, I need to set the marked field in Tasks to NULL.

This is something common in a database, so I thought there is a way to handle this.

In my case, most of my tables have a pair of fields holding the UserId of the user that Created or Modified the record. So, I need to solve this pattern to apply it several places.


texts: Improve speed of COPY COMMAND in postgres

texts:
I would like to understand if increasing work_mem does help improving the speed of the COPY command.

Does COPY use work_mem or maint_work_mem extensively?


texts: comparing two time values with 2 column respectively after converting in 24 hour format

texts:
Hi I have two varchar column with time values store in 12 hour format. I want to get the output like rows that which don't fall in 10:00 am to 12:00 pm

here

id  |time1     | time2
____|__________|__________
1   |01:00 pm  | 10:00 pm
2   |11:00 am  | 01:00 pm
3   |09:00 am  | 11:00 am
4   |05:00 pm  | 11:59 pm

output will be

1   |01:00 pm  | 10:00 pm
4   |05:00 pm  | 11:59 pm

here is the link of sqlfiddle to test
http://sqlfiddle.com/#!9/b6bf48/2


texts: Database design for a table with both bulk insert and single row insert

texts:
I have to design a table where records will be inserted in two ways.

Bulk insert from a batch process from a delimited text file between 2 - 3 million rows in a short burst. Later the same records will also need an bulk update for the status. e.g. from 'Pending' to 'Completed'.
Single row insert from a LOB application, via stored procedure, say one thousand rows per day. Some of these records will not require the status update and some will.

Process 1 is already in place (The text file is used as an external table. insert/update via a stored procedure) but we are willing to re-design this as currently we have about 2 million rows but we are expecting major volume growth of say 30 million records per year from the batch plus say 3.5 million from the application. Data for 7 years needs to be stored. Data will be queried using a Id, type and date range (say current year) with pagination.

The preferred DB is Oracle 11G. But I am also open to SQL Server 2012 enterprise edition. In this case process 1 will perhaps be replaced by SSIS.

My questions are:

What are the considerations required for designing a table like this? Should the bulk and single row insert be in different tables with identical structure? I wouldn't like the bulk insert to affect the performance of single row insert and query when happening at the same time.
Is there any significant difference between using Oracle vs SQL Server for this scenario specially for performance?
Is SSIS appropriate to use for process 1 if SQL Server is chosen?


texts: creating 2 database at the same time in db2

texts:
Do you know whether it is safe to have in one shell user db2inst1 creating a database A  and having another shell the same user creating at the same time database B?

create database A # shell 1
create database B # shell 2

I am using Linux/ Cent os with DB2 V 9.7 installed.


texts: Handle Duplicate entries in Fact table in Data Cube

texts:
We have been constructing data cube for our project in which we are facing a problem like 1 to many relationship in the dimension tables and making entries in fact table (duplication).

EmployeePersonalInfo Table:

EmployeePersonalID  Name
E1                  Steve(row1)
E2                  Mark(row2)

EmployeeDiscipline IncidentInfo Table:

EmployeePersonalID  EmployeeDisciplineIncidentID
E1                  Dis1(row1)
E2                  Dis5(row2)

EmployeeDepartmentInfo Table:

EmployeeDepInfoID   EmployeePersonalID
Dep1                E1(row1)
Dep2                E1(row2)
Dep3                E1(row3)
Dep1                E2(row4)
Dep2                E2(row5)
Dep3                E2(row6)

While creating a Fact table with the above entries comes as,

FactDiscipline Table:

EmployeePersonalD   EmployeeDepInfoID   EmployeeDisciplineIncidentID
E1                  Dep1                Dis1(row1)
E1                  Dep2                Dis1(row2)
E1                  Dep3                Dis1(row3)
E2                  Dep1                Dis5(row4)
E2                  Dep2                Dis5(row5)
E2                  Dep3                Dis5(row6)

Actually Employee E1 in associated with discipline incident Dis1. Since EmployeeDisciplineInfo is joined with it , 3 entries will be there for the same Employee E1.  But Employee E1 involved with only one Incident Dis1. In Fact table, it measures the incident count as 3 for Employee E1.

Your help is appreciated!!


texts: Sync two MySQL servers data, while changing the Engine in the slave

texts:
I'm running a MySQL server 5.5.44, and I'd need to sync its data to a new server running MySQL 5.7.13 using master-slave. The idea is to bring both DBs synced until we migrate to the second, newer server, achieving a close to zero downtime.

The issue is not about how to prepare the master-slave. The issue comes when I want to sync the data, but change the engine of many tables in our slave server. Specifically, moving them from MyISAM to InnoDB.

These tables are larger than 1Gb, one being 14Gb, which would lead in a lock for more than 8 hours. My idea was to do any structural optimisation in the slave server, but I'm afraid this might compromise the whole master-slave thing.

Do you know if this is possible?


texts: I have 20000 records in a table. I want to fetch 200 records at a time and do either update or delete operation. The next time I fetch 200 records, these previously processed/non-deleted records shouldn't come into picture.I though of using pagination, but could get to the logic of implementing it.Is there any way to achieve it?


texts:
I'm trying to prove to myself that the order of inner joins doesn't matter, but, in an abstract sense, I'm coming up with nothing.

How would one go about proving that the order of a set of INNER JOINs that are executed to turn many tables into a single table does not affect the result set (i.e., prove commutativity and associativity of the INNER JOIN operation)?


texts: where is memory going in In-Memory OLTP?

texts:
I have only memory optimized table in my database. When I am running below query, I am getting pages_mb for that DB as 710 MB.
SELECT type
 , name
 , memory_node_id
 , pages_kb/1024 AS pages_MB
FROM sys.dm_os_memory_clerks WHERE type LIKE '%xtp%'

But when I run below query, I am getting mem_alloc_total_mb for that table as 2 and others as 0.
   SELECT
   object_name(object_id) AS table_name,
   memory_allocated_for_indexes_kb / 1024 as mem_alloc_index_mb,
   memory_allocated_for_table_kb / 1024 as mem_alloc_table_mb,
   memory_used_by_indexes_kb / 1024 as mem_used_index_mb,
   memory_used_by_table_kb / 1024 as mem_used_table_mb,
   (memory_allocated_for_table_kb + memory_allocated_for_indexes_kb) / 1024 as mem_alloc_total_mb,
   (memory_used_by_table_kb + memory_used_by_indexes_kb) /1024 as mem_used_total_mb
   FROM sys.dm_db_xtp_table_memory_stats
   where object_id = object_id('<table_name>');
   go

Data from both the above queries are contradicting each other. Am I missing anything?
NOTE: Durability setting for the memory optimized tabe is "SCHEMA_AND_DATA".


texts: using SQLCMD to export/extract data withot trailing spaces

texts:
I am trying to extract data from some of my tables using sqlcmd utility as SSMS does not generate appropriate csv-s for me.
My issue: all csv extract includes trailing spaces according to table definitions (I mean field length) regardless of using char, varchar, nvarchar, etc...
When using sqlcmd utility eg with following options:
sqlcmd -S . -d /*databasename*/ -E -s, -W -Q "SELECT * FROM /*tablename*/" > C:\Test.csv

it is still contains trailing spaces, eg:
,"273                 ",

any hints or tips to do this in SSMS and/or correcting my parameters for SQLCMD?


texts: Efficiently computing aggregate functions over subqueries with incremental data

texts:
I have a PostgreSQL database (version 9.3.6) containing a large number of orders. As the orders are processed, scan_events are triggered and stored, with multiple events per order. Scan events have a boolean indicating whether an order can be marked as completed after that event, but multiple complete scans can occur. Generally speaking however, the only scans I actually care about are the first scan, and the first completed scan.

I want to know the average and standard deviation of the percentage of orders with a given creation date that have received their first scan within x days of being created.

Schema

CREATE TABLE orders (
    id character varying(40) NOT NULL,
    date_created timestamp with time zone NOT NULL
);

ALTER TABLE ONLY orders
    ADD CONSTRAINT orders_pkey PRIMARY KEY (id);

CREATE TABLE scan_events (
    id character varying(100) NOT NULL,
    order_id character varying(40) NOT NULL,
    "time" timestamp with time zone NOT NULL
);

CREATE INDEX scan_events_order_id_idx ON scan_events USING btree (order_id);

Desired Computation

For days_elapsed values ranging from 1 to 14 days, I want the means and standard deviations of:

the set of percentages of orders that received any scans within days_elapsed days of their orders.date_createdfrom each of the past 30 days (aka grouped by DATE(orders.date_created))
the set of percentage of orders that received a scan with completed = TRUE within days_elapsed days of their orders.date_created from each of the past 30 days (aka grouped by DATE(orders.date_created))

Ideally, the output would look something like this, but honestly, anything performant is fine.

output
----------------
days_elapsed
mean_scanned
stddev_scanned
mean_completed
stddev_completed

Current Progress

I have a query that will get me the counts per day (optionally with WHERE scan_events.completed IS TRUE to get the completed scan results):

SELECT DATE(orders.date_created),
    COUNT(DISTINCT orders.id) AS total,
    COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS scanned,
    COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND scan_events.completed AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS completed
FROM orders
LEFT JOIN scan_events ON orders.id = scan_events.order_id
WHERE orders.date_created BETWEEN '2015-07-01' AND '2015-07-31'
GROUP BY DATE(orders.date_created)
ORDER BY DATE(orders.date_created) ASC;

For days_elapsed = 1, this query is roughly what I imagine should work:

SELECT AVG(counts.scanned * 1.0 / counts.total) AS mean_scanned,
    STDDEV(counts.scanned * 1.0 / counts.total) AS stddev_scanned,
    AVG(counts.completed * 1.0 / counts.total) AS mean_completed,
    STDDEV(counts.completed * 1.0 / counts.total) AS stddev_completed
FROM (
    SELECT DATE(orders.date_created),
        COUNT(DISTINCT orders.id) AS total,
        COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS scanned,
        COUNT(DISTINCT CASE WHEN scan_events.id IS NOT NULL AND scan_events.completed AND DATE_PART('day', scan_events.time - orders.date_created) <= 1 THEN orders.id ELSE NULL END) AS completed
    FROM orders
    LEFT JOIN scan_events ON orders.id = scan_events.order_id
    WHERE orders.date_created BETWEEN '2015-07-01' AND '2015-07-31'
    GROUP BY DATE(orders.date_created)
) counts

The problem with this is that it's most certainly redoing work that it doesn't have to...

Things that I imagine we can take advantage of:

AVG and STDDEV ignore null values so we can do some CASE WHEN ... THEN ... END trickery
The sets that AVG and STDDEV are calculated over could maybe be built up incrementally as we increase days_elapsed by one

Any help would be much appreciated -- my SQL-fu is not up to snuff :(


texts: Will SSDT publish always fail with "Data Loss May Occur" when table is rebuilt?

texts:
Consider the following scenario:

We have a SSDT database project.
We add a new column to the middle of a table.
This forces the database project to rebuild the table in order to deploy the change.
We publish this SSDT database project  with the setting “Block incremental deployment if data loss might occur” enabled.
Because we have the above setting enabled, and (also, I think) the table is being rebuilt the deployment (correctly) throws an error saying data loss may occur.

My question is this: Can we rely on the fact that whenever the table needs to be rebuilt as part of a SSDT publish this error will be thrown?


texts: How can I optimize this MySql Select query?

texts:
I have the following query executed every 1 minute on a cron job

SELECT COUNT(*)
  FROM `Orders` AS `o`
  WHERE (`o`.`OrderStatus` = 0) AND (`o`.`CreatedAt` > DATE_ADD(UTC_TIMESTAMP(), INTERVAL -1.0 minute))

Should I add index on the OrderStatus and CreatedAt columns, and if yes, what kind of indexes ?


texts: Running a CTE query in a loop using PL/pgSQL

texts:
I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude coordinates of grids, I pass the top left and bottom right latitude / longitude values into my CTE in order to display the amount of requests (hourly) made within those coordinates for given two timestamps-. However, I cannot display the results of my CTE and I get the following error message:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "inline_code_block" line 6 at SQL statement

What should I change here in order to make the entire query work as needed? My code is as below:

DO $$
<<outer_scope>> DECLARE
  coords RECORD;
BEGIN
    FOR coords IN SELECT topleftlat, topleftlon, bottomrightlat, bottomrightlon FROM coordinates LOOP
        WITH cal AS (
        SELECT generate_series('2011-02-02 00:00:00'::timestamp ,
                   '2012-04-01 05:00:00'::timestamp ,
                   '1 hour'::interval) AS stamp
    ),
    qqq AS (
      SELECT date_trunc('hour', calltime) AS stamp, count(*) AS zcount
      FROM mytable
      WHERE calltime >= '2011-02-13 11:55:11'
        AND calltime <= '2012-02-13 01:02:21'
        AND (calltime::time >= '11:55:11'
        OR calltime::time <= '01:02:21')
        AND lat BETWEEN coords.bottomrightlat AND coords.topleftlat
        AND lon BETWEEN coords.topleftlon AND coords.bottomrightlon
     GROUP BY date_trunc('hour', calltime)
    )
    SELECT cal.stamp, COALESCE (qqq.zcount, 0) AS zcount
    FROM cal
    LEFT JOIN qqq ON cal.stamp = qqq.stamp
    WHERE cal.stamp >= '2011-02-13 11:00:00'
      AND cal.stamp <= '2012-02-13 01:02:21'
      AND (
        extract ('hour' from cal.stamp) >= extract ('hour' from '2011-02-13 11:00:00'::timestamp) or
        extract ('hour' from cal.stamp) <= extract ('hour' from '2012-02-13 01:02:21'::timestamp)
      )
    ORDER BY stamp ASC;

    END LOOP;
END;
$$;


texts: Extract one MySQL database from a FULL MYSQL Server backup file

texts:
I have a small shared windows server on which i'have installed MySQL 5.1 engine for hosting my user's mysql databases.

I backup the complete MySQL databases each day by running this script as batch file in SCHEDULED TASKS:

"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqldump.exe" -uroot -p"**********" --force --flush-logs --all-databases > c:\dbbackup\alldatabases.sql

So alldatabases.sql will be a SQL file which contains the complete data of MySQL server which can be restored on cases that all of the databases are lost or migrating to another server.

I have never extracted one specific mysql database from this "alldatabases.sql" backup file and i don't have any clue on this.

**The must idiot solution is to restore this big backup file in another server and then simply create an individual backup from a specific database.

Could you help me on extracting one specific MySQL database from this big backup file?
Lets say that MySQL database name is  JOOMLAdb

** Also do you have any idea on changing my backup script, so instead of having a big backup file i may have individual backups for each database (Databases will increase/decrease through the time and we don't have a fix list of available databases)
Thank you for your care

Best Regards


texts: Performance of BINARY vs CAST AS BINARY in MySQL

texts:
Using MySQL 5.6.23, I recently changed a prepared statement from:

SELECT * FROM table WHERE BINARY column = :boundValue

to:

SELECT * FROM TABLE WHERE CAST(column AS BINARY) = :boundValue

According to the MySQL docs on BINARY:

  BINARY str is shorthand for CAST(str AS BINARY)

So I expected these two prepared statements to have the same run-time performance. However, the CAST version was significantly slower. I mean orders of magnitude difference.

The query plans are identical:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where

CREATE TABLE `session_detail_binary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `column` varchar(64) NOT NULL DEFAULT '',
  `foo` varchar(64) DEFAULT NULL,
  `bar` varbinary(4096) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_column` (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

There are about 260k rows in this table.

What's going on here?

UPDATE: I believe this was a red herring. At the same time this SELECT .. WHERE x runs, there were a lot of UPDATE .. WHERE x on the same table and under the same where condition, where "a lot" means 100x more. This suggests to me the row locks were handicapping the selection.


texts: Postgres - using aggregate functions to return a different column?

texts:
I currently have a table containing raw temperature data that looks like this:

I have written a query to find the min, max and avg of this data grouped by the date of the timestamp:

drop table solardb.weatherinfodaily;

select date_trunc('day',ts) as ts,
min("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Min Temp (F)",
avg("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Avg Temp (F)",
max("Air Temperature (Actual) (Degrees C)")*9/5 + 32 as "Daily Max Temp (F)"
into solardb.weatherinfodaily
from solardb.weatherdata
group by date_trunc('day',ts)
order by date_trunc('day',ts);

select * from solardb.weatherinfodaily;

I would like to return the timestamp that is associated with the aggregate function and I have not figured out a way to do this yet.

i.e. the query would return:

ts, Daily Min Temp, timestamp from raw data table when the min temp happened, daily avg temp, daily max temp, timestamp from raw data when max temp happened.

How can I write such a query?


texts:
Using SQL Server 2008 R2, the main transactional table in a vendor database is massive compared to all others and has about 14 indexes. Some of these indexes don't get used in our environment, but we can't remove them. That's not a problem, it's just something we have to live with.

The question I have is about why some of these low-or-no-read indexes seem to be using memory - more than other indexes on the same large table that get used often. I would have thought that most of the buffer cache would go towards objects that are read from frequently. The only thing happening on these indexes is write overhead.

For example, one of these low-read indexes is being allocated about 2 GB of memory (58% of the index's total size) and another has 1.7 GB of memory (27% of its size). Meanwhile, the monster-sized and well-used clustered index itself only has 4 gigs (2% of its size). A different NC index with lots of reads only has 100 MB in the buffer cache (5% of its size).

Looking at the physical stats, I can see the fragmentation is pretty bad. That's understandable from all the writes on this table and the non-sequential inserts. I'm not sure if it could be related to memory usage, though.

Looking at the operational stats for these indexes is also interesting.

The leaf_ghost_count reminds me that there are just about as many deletes taking place on this table as there are inserts (from a regular cleaning process).
One of these low-read indexes has some of the highest page_lock_wait values in the database. Perhaps that's only because of the writes?
2 others have some of the highest page_io_latch_wait values. I understand that latch usage would be related to memory usage, so that makes sense.

I realize this is an abstract question and that I'm not providing many actual stats. I'm just curious about how SQL Server comes to these buffer cache usage decisions and wonder if anyone out there understands it.


texts:
Quick and simple... why is this sp failing when I attempt to retrieve statistics for one of the columns on the temp table?

CREATE PROCEDURE dbo.Demo
AS
BEGIN
SET NOCOUNT ON
-- Declare table variable
CREATE TABLE #temp_table (ID INT)
DECLARE @I INT = 0

-- Insert 10K rows
    WHILE @I < 100
    BEGIN
        INSERT INTO #temp_table VALUES (@I)

        SET @I=@I+1
    END

-- Display all rows and output execution plan (now the EstimateRow is just fine!)
SELECT * FROM #temp_table

-- Is the object there
SELECT OBJECT_ID('tempdb..#temp_table')

-- How about statistics
DBCC SHOW_STATISTICS ('tempdb..#temp_table', 'id')
END;

I don't understand, I get a message saying that there is no statistics created on column id

Could not locate statistics 'id' in the system catalogs.

Having said that, I have seen an article by Paul White where this technique is used and does indeed work.

https://sqlkiwi.blogspot.com/2012/08/temporary-tables-in-stored-procedures.html

Any ideas?


texts: How to increase innodb_log_file_size in Digital Ocean? Ubuntu!

texts: Please do help me on how to increaase my innodb_log_file_size


texts:
I have just started on researching the feasibility of using MariaDB's columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with 8GB RAM. This is the table, a trigger and the timings:
MariaDB [test]> show create table analytics_test\G
*************************** 1. row ***************************
       Table: analytics_test
Create Table: CREATE TABLE `analytics_test` (
  `id` int(11) DEFAULT NULL,
  `str` varchar(50) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

MariaDB [test]> show create trigger test_trg\G
*************************** 1. row ***************************
               Trigger: test_trg
              sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg before insert on analytics_test for each row
begin
  set new.str=concat('Value: ',new.id);
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_general_ci
               Created: 2021-01-07 11:14:28.81
1 row in set (0.000 sec)

MariaDB [test]> insert into analytics_test set id=1;
Query OK, 1 row affected (0.817 sec)

MariaDB [test]> insert into analytics_test set id=2;
Query OK, 1 row affected (0.560 sec)

MariaDB [test]> insert into analytics_test set id=3;
Query OK, 1 row affected (0.611 sec)

MariaDB [test]> select * from analytics_test;
+------+----------+
| id   | str      |
+------+----------+
|    1 | Value: 1 |
|    2 | Value: 2 |
|    3 | Value: 3 |
+------+----------+
3 rows in set (0.085 sec)

I think .5 sec for a simple insert is very slow - compare to the same table in innodb:
MariaDB [test]> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `str` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.000 sec)

MariaDB [test]> show create trigger test_trg1\G
*************************** 1. row ***************************
               Trigger: test_trg1
              sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` trigger test_trg1 before insert on test for each row
begin
  set new.str=concat('Value: ',new.id);
end
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8mb4_general_ci
               Created: 2021-01-07 11:45:07.85
1 row in set (0.000 sec)

MariaDB [test]> insert into test set id=1;
Query OK, 1 row affected (0.010 sec)

Is there anything I need to do in order to make inserts perform better?


texts:
I understand that MongoDB pre-allocates disk space, but the disk allocation I'm seeing is growing much faster than I'm anticipating.

For example, I had a database using up 8GB of actual disk space. Today a new 2GB file was pre-allocated, bringing it up to 10GB on disk. I didn't expect this, because as it looked like my data (including wasted space) would comfortably fit.

Doing db.stats() I can see the dataSize for this database is ~2G, indexSize ~500M. The storageSize is ~4GB, so even with a lot of fragmentation it's still nowhere near the disk space now allocated.

What I'm trying to understand is why the 4GB wasn't happily contained in the first 6GB of files. Why would data have been written to the last 2GB of empty space, causing a further 2GB to be allocated?

Using the output from db.stats is it possible to predict with any certainty what data size will result in new disk allocation?

For detail: I'm running MongoDB 2.6 as a replica set, not sharded. My database contains thousands of collections, but each is not very large.


texts: Get updated rows without triggers or timestamps

texts:
I'm working on a project with a client. The client has a MySQL Database with a few large tables (~100 million records each).
I've pulled the initial data.
I am required to sync the client's database to my database.
Now whenever the client inserts a row in any of the tables, I should sync it to my table. However, there are some (very strict) restrictions.
I cannot create triggers on client's tables.
The tables may not have a dateTime column for me to recognise the creation time of a row.
I do not wish to run a comparison between client's table vs mine(which will obviously take days just to identify a few 100 new insertions)

Is there any other way I can extract the newly inserted rows from client's tables?


texts: Connection problem different errors .net application

texts:
We have a SQL Server with some relational databases. We have another webserver with applications which communicate with the databases.
We have an issue with one of our customers. We get some errors every night, and we have a problem identifying what the problem is. There is a web/wcf service.
We have traced the database with SQL Server Profiler and identified a lot of Connections with the following:
Audit login
RPC:Completed
Audit logout

Same SPID more than ten times. Is that normal behavior?
Errors

Timeout expired.The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. The statement has been terminated.

Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21033; handshake=0;
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
The wait operation timed out

A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
The specified network name is no longer available

Exception has been thrown by the target of an invocation.The wait operation timed out

An error occurred on the server, but the error message and default error messages is missing, or could not be retrieved.   System.UnauthorizedAccessException: UnauthorizedAccess

We need some advice how to do to identify the cause of the problem. We think it is several: network, queries performance etc.


texts: Is it benefit to create a index key if unique key already exists on the same columns?

texts:
The table schema for example:

CREATE TABLE `foos` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `bar1` bigint(20) unsigned NOT NULL,
  `bar2` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uqx_bar1_bar2` (`bar1`,`bar2`),
  KEY `idx_bar1` (`bar1`),
  KEY `idx_bar2` (`bar2`),
  CONSTRAINT `fk_bar1` FOREIGN KEY (`bar1`) REFERENCES `bars1` (`id`),
  CONSTRAINT `fk_bar2` FOREIGN KEY (`bar2`) REFERENCES `bars2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

This table is created for m:n relations, index key is created separately for bar1 and bar2, and unique key is created for both columns bar1 and bar2.

In most cases, this query will be executed:

SELECT * FROM foos WHERE bar1=? AND bar2=?

The question is: Will query optimizer get benefit if index key created for both columns bar1 and bar2? That is:

ALTER TABLE foos ADD INDEX `idx_bar1_bar2` (`bar`,`bar2`)


texts: Best practice around non-production databases refreshes and TDE

texts:
Say we have a production system with several databases encrypted using TDE and a self-generated certificate.

We regularly need to take this database and refresh our non-production systems. This process involves backing up the source database, restoring it to pre-production, obfuscating personal data and a number of other items.

The key fact here is that the pre-production system has a copy of the encryption certificate from production

I am looking at ways to make this process more secure - I'm not happy with the certificate sat on the pre-production environment. Is there another way that will:

Never have an unencrypted backup saved anywhere
Not allow propagation of personal data from production into non-production systems?


texts:
I need to query a MSSQL 2008 db for 4 columns of data where one column (an ID) is used to join a number of tables from a Sybase ASE 15 database then output all 4 columns from the SQL query with a number of columns from 5 other tables in Sybase.

Currently we query SQL and output to a csv, then load that data to an Access db.
 Then use that to Access table to query the Sybase tables using a join on the ID from the SQL query.  I need to take Access out of the equation and have all this run either in MSSQL or Sybase and have that triggered by a SSRS report to display the final Sybase query in a SSRS report.

I am able to make a linked server from SQL to Sybase or vise versa, and am able to use openquery from SQL to query Sybase.  How do I use my results from the SQL side to then query the Sybase side, I need the small subset of IDs from the SQL query to pull from the Sybase tables but cannot seem to get the SQL results to the Sybase side in a temp table or anything to then query from Sybase with the openquery call.

Is my only option to use a SQL bcp command to output the query to a csv file then use a separate bcp (Sybase version) to load the SQL csv to Sybase then return the results?  If so how can that be done from SSRS? What are my options here, am I missing an opportunity?


texts: mysql innodb_buffer_pool_size let me understand

texts:
I couldn't find answer for my situation.

I have a windows server 2008 with 2 cores and 7 GB RAM (Azure D1 VM).

I'm running mariadb 10.0.14 on it. I have 2 databases (1.5GB and 9GB in size).

Statistics are in general:

  AVG 1192590 SELECTs per hour (70% of all)
  AVG  163555 UPDATEs per hour (10% of all)
  AVG   87728 INSERTs per hour (5% of all)
  AVG    5920 DELETEs per hour (0.3% of all)

I had innodb_buffer_pool_size set=4G and the whole server was running terribly slow.
CPU was utilized avg 10-15% but queries was executed very slowly.

Now I have set innodb_buffer_pool_size=1G and it's running much much faster.
The interesting this is that I have commited 1,5 GB RAM by mysqld.exe
So 5 GB RAM are still free.

Why setting innodb_buffer_pool_size larger means slower queries?
What should I configure more to use this free RAM to gain more performance on mysql (mariadb)?


texts: How to Restrict Schema Access to Specific IP Address & user?

texts:
I want to lock down access to specific schemas. Restrict the access to specific users when the connection is made from the specific  IP addresses.
Eg Only Access to USER1 and USER2


texts: Join tables but keep matches in a single row

texts:
I have the following tables structure in a PostgreSQL DB:

These tables have this content:

So if I want to retrieve the SB associated to each server I'll need to join these 3 tables:

Server with Map_ServerSB on server.Id = Map_ServerSB.IdObj1
Map_ServerSB with SB on Map_ServerSB.IdObj1 = SB.Id

The thing is that, this way, I'll get 2 records.

However, I would need to get a single record; something like this:

Note: We won't have more than 2 records in SB for each server (there could be zero, one or two records for each server)

Is there any way to accomplish that?

Thanks!


texts:
We use Mixed replication in RDS mysql 5.6 and we are noticing replication lag every few minutes or so, the lag appears momentarily and then disappears. On slaves when we do show processlist we saw update and long insert queries (sometimes).

Previously in MYSQL 5.5 we  used statement based replication and never encountered insert and update queries appearing on slaves, there are no other systems writing to slaves so this is ruled out.

Can any one suggests what could be the problem of this occasional lag.


texts: How to generate an SQL file with DBMS_DATAPUMP

texts:
I am trying to generate an SQL file with the DBMS_DATAPUMP api. This tasks is specifically adressed in Oracle support Note 1519981.1 (How To Generate A SQL File Using The DBMS_DATAPUMP API).

When I copy/paste the code found in the mentioned document, it fails on line 5 with a rather obscure

ERROR at line 1:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3444
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3693
ORA-06512: at line 5

The line in question, that is line 5, is DBMS_DATAPUMP.add_file(...) with the parameter filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE.

I have run the script with a user that has DBA privileges.

I have also created an Oracle directory named MYDIR. The OS directory it points to exists and is writeable.

The complete code that fails is (copied from the note):

DECLARE
  h1 NUMBER;
BEGIN
  h1 := DBMS_DATAPUMP.open(operation => 'SQL_FILE', job_mode => 'SCHEMA', job_name => 'j2');
 DBMS_DATAPUMP.add_file(handle => h1,
   filename => 'scott.dmp',
   directory => 'MYDIR',
   filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);
 DBMS_DATAPUMP.add_file(handle => h1,
   filename => 'scott.sql',
   directory => 'MYDIR',
   filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_SQL_FILE );
 DBMS_DATAPUMP.start_job(handle => h1, skip_current => 0, abort_step => 0);
 DBMS_DATAPUMP.detach(handle => h1);
END;
/

The version is 11.2.0.1.


texts: Is it possible to convert Postgresql server to master-master cluster on the fly?

texts:
There is an Postgresql server that should never be stopped.
I want to upgrade it.
Is it possible to:

configure new empty server,
convert existing server to the first node of master-master cluster (without stopping),
attach new server to cluster,
assign DNS name to IP of second server,
and shutdown old server?


texts: Does using PK and FK effect performance in negative way?

texts: Few companies I noticed do not use PKs and FKs. I don't understand the logic of not using Clustered Indexes and I hear some say it can cause issues because FK couples records together and takes up more memory. Any insight?