texts: Optimize very simple Postgresql query (and understand Query Plan)

texts:
I have a DB with many tables created using the following structure :

CREATE TABLE table1 (id serial primary key, col1 real, col2 real, ..., coln real)

with about 50 columns and 400k rows. The tables are read-only but are fully dropped and recreated once a day (with new values) and are hosted on a HDD.

When I run a simple query based on the PK:

EXPLAIN ANALYZE SELECT col1, col12, col22, col17, col41, col27 FROM table1 WHERE table1.id=461 OR table1.id=7618 OR table1.id=45581 OR table1.id=5238;

I get the following QUERY PLAN :

Bitmap Heap Scan on table1  (cost=9.72..17.76 rows=4 width=24) (actual time=28.290..45.165 rows=4 loops=1)
   Recheck Cond: ((id = 461) OR (id = 7618) OR (id = 45581) OR (id = 5238))
   Heap Blocks: exact=4
   ->  BitmapOr  (cost=9.72..9.72 rows=4 width=0) (actual time=16.748..16.748 rows=0 loops=1)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=11.230..11.230 rows=1 loops=1)
               Index Cond: (id = 461)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.832..0.832 rows=1 loops=1)
               Index Cond: (id = 7618)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=4.476..4.476 rows=1 loops=1)
               Index Cond: (id = 45581)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.208..0.208 rows=1 loops=1)
               Index Cond: (id = 5238)
 Planning time: 0.074 ms
 Execution time: 45.197 ms
(14 rows)

If a run the exact same query again, the QUERY PLAN becomes:

Bitmap Heap Scan on table1  (cost=9.72..17.76 rows=4 width=24) (actual time=0.031..0.036 rows=4 loops=1)
   Recheck Cond: ((id = 461) OR (id = 7618) OR (id = 45581) OR (id = 5238))
   Heap Blocks: exact=4
   ->  BitmapOr  (cost=9.72..9.72 rows=4 width=0) (actual time=0.024..0.024 rows=0 loops=1)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.012..0.012 rows=1 loops=1)
               Index Cond: (id = 461)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
               Index Cond: (id = 7618)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
               Index Cond: (id = 45581)
         ->  Bitmap Index Scan on table1_pkey  (cost=0.00..2.43 rows=1 width=0) (actual time=0.003..0.003 rows=1 loops=1)
               Index Cond: (id = 5238)
 Planning time: 0.078 ms
 Execution time: 0.074 ms
(14 rows)

The first question is why in the first query the actual time of the Beatmap Heap Scan is higher than the expected cost ?

Also, is there a configuration that would improve the query performance ?

Many thanks in advance.

PS :due to RAM limitation I can not use pg-warm tool


texts: Status fields: ALTER TYPE vs. INSERT INTO?

texts:
I have a simple table with a status column:

create type status as enum (
  'approved',
  'pending_approval',
  'rejected'
);

create table document (
  id          serial primary key,
  uuid        uuid unique not null default uuid_generate_v4(),
  title       text not null,
  content     text,
  status      status not null,
  created_at  timestamptz not null default now(),
  updated_at  timestamptz not null default now(),
  deleted_at  timestamptz
);

This is the beginning of a small workflow-esque application I'm doing. Statuses, however, can be defined by the users of the application. What are the cons of using ALTER TYPE instead of creating a new table and using JOIN? I really don't want to add a JOIN to my queries just because of a status field.


texts: In SQL Server, can user X be granted ability to change permissions for all users except Y?

texts:
...and if so, how?  Sorry for such a newbie question.

Thanks


texts: Implementing a degenerate dimension with an alphanumeric varchar(30) at the transaction level?

texts:
I am currently working on designing my first data model and I am confused about this particular implementation of degenerate dimensions. According to Kimball's design tip 46, a degenerate dimension that's unwieldy (alphanumeric) can be implemented using a surrogate key. I have a two column degenerate key requirement, char(6) and varchar(30) on a fact table that has a grain of one row per transaction line. Accordingly, the DD would consume a large amount of space when compared to a simple integer; however, there is no additional context to add here. Would it be best to leave it as is and afford it the extra space,
or is it worth putting this into a separate dimension while knowing that it would grow proportionally to the fact table (roughly 25%, as there's an average of 4 lines per transaction).


texts: Can an encrypted MySQL restart without the keyring file on the box?

texts: I am running Mysql 5.7 with a few tables in the DB encrypted, if I move the keyring file off of the box to secure it, can I still restart the DB without issue?


texts: SQL Server 2014 installation taking long time

texts:
Normally our SQL Server installation takes 5 - 10 minutes, but on several of our recent server builds on vmWare, the SQL Server 2014 Enterprise Edition setup has been taking a very long time. I have intermittently had the problem with 2014 with SP1 and without SP1 (RTM). With SP1 it was stuck for about an hour
on "Install_SqlSupport_KatmaiRTM_Cpu64_Action: Create Shortcuts. Creating shortcuts" and the CPUs were at about 20% while it was stuck running the installer service. This time it has been stuck for 1.5 hours on Install_sql_engine_core_shared_Cpu64_Action: RemoveSupportFiles_64. Removing temporary files and the CPUs are at 0%.

The Summary.txt file states the following, but I didn't cancel the installation:

Setup completed with required actions for features.
Troubleshooting information for those features:
  Next step for SQLEngine:       SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for Adv_SSMS:        SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for SSMS:            SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for ComponentUpdate: SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for SNAC:            SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for SNAC_SDK:        SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for Writer:          SQL Server Setup was canceled before completing the operation. Try the setup process again.
  Next step for Browser:         SQL Server Setup was canceled before completing the operation. Try the setup process again.

Last time the same thing happened but it finished and the Summary.txt file showed successful installation.

I am using a .config file to run the installations and have:

FEATURES="SQLEngine, ADV_SSMS"

Has anyone else experienced similar problems and do you have any suggestions?


texts: Mongodb - Best Practice for TTL index on huge dataset

texts:
I want to create a TTL index for a table.

Table size 1.2TB

I have a shard infra. Each shard is having 3 node replica set.

For creating this index, can I go with the rolling index creation method? Or any other suggestion?


texts: How can I tell WHY an insert on a certain table is slow?

texts:
I know that an INSERT on a SQL table can be slow for any number of reasons:

Existence of INSERT TRIGGERs on the table
Lots of enforced constraints that have to be checked (usually foreign keys)
Page splits in the clustered index when a row is inserted in the middle of the table
Updating all the related non-clustered indexes
Blocking from other activity on the table
Poor IO write response time
... anything I missed?

How can I tell which is responsible in my specific case? How can I measure the impact of page splits vs non-clustered index updates vs everything else?

I have a stored proc that inserts about 10,000 rows at a time (from a temp table), which takes about 90 seconds per 10k rows. That's unacceptably slow, as it causes other spids to time out.

I've looked at the execution plan, and I see the INSERT CLUSTERED INDEX task and all the INDEX SEEKS from the FK lookups, but it still doesn't tell me for sure why it takes so long. No triggers, but the table does have a handful of FKeys (that appear to be properly indexed).

This is a SQL 2000 database.


texts: Need a parallel database for reporting purpose

texts:
As our team is planning to implement BI reporting in our application, they requested a separate but a parallel db for reporting purpose in which data should be as in our production db. I read about standby database and data guard. Which is the best way for this purpose? What should I prefer? Do it cause any performance degradation to my current db?

I am using oracle 11g DB in windows environment which is in archivelog mode.


texts: Max Degree of Parallelism for SQL Server 2008

texts:
Microsoft have recommendations around setting MAXDOP on various versions of SQL Server, but this (old) article raises some issues and recommends setting MAXDOP = 1 generally for SQL Server 2005 and indicates that some of the issues aren't fixed in SQL Server 2008.

Six Reasons You Should Be Nervous About Parallelism

Is there any indication that here are still problems with MAXDOP > 0 and are any DBAs out there not using 0 to reduce risks?


texts: How to insert all the dates in a date range into Oracle database?

texts:
For instance, The date range is from 26th Jul to 1st Aug, that is

  26th Jul, 27th Jul, 28th Jul, 29th Jul, 30th Jul, 31th Jul and 1st Aug

How can I construct a function (of course, I need to implement this function using SQL or PL/SQL) like this:

// insert all the dates from "from" to "to" ("from" and "to" are included)
// into Oracle datebase
bool InsertDateRange(date from, date to) {
    bool state = true;

    for (date d in [from, to]) {
        // insert
    }

    return state;
}

If given a different date range, say [27th Feb, 1st Mar], I also need to know this year is a leap year or a common year.


texts: Combine rows to remove nulls while using latest value based on time

texts:
I have a table that has multiple rows associated with each ID but sometimes containing differing info:

Key, ID,  Name,  Title,  Time
4,   AA,  Abe,   NULL,   2018-01-05
3,   BB,  NULL,  Mrs,    2018-01-04
2,   AA,  NULL,  Mr,     2018-01-03
1,   BB,  Cat,   Ms,     2018-01-02

Some of the fields are Nulls, some of the row have differing info for the same columns and ID but there will always be an ID and a timestamp.

I want a query where I can squash Nulls but when there are multiple differing fields choose the latest one. Something like this:

Result =
ID,  Name,  Title,  Time
AA,  Abe,   Mr,     2018-01-05
BB,  Cat,   Mrs,    2018-01-04

I have something using "SELECT DISTINCT ON (id, name, title)" but I'm missing the next step so it's not working.

SQL Fiddle: http://sqlfiddle.com/#!17/72ba3/1


texts: What does the DBMS do exactly when i send it a SELECT query regarding the .frm files and database pages? (MySQL)

texts:
so i was reading about database pages and DBMS in mysql and it got me really confused

so based on their documentation database pages have a part where it points to rows in the page, which have tuples of the database, but how does the DBMS find the correct page when i type something like SELECT name from mytable where id>1? there is no info on this

I Suppose the DBMS looks for the corresponding .frm, but how does it find the correct pages that should read inside that?

i read this article : https://ieeexplore.ieee.org/document/5474822/

which showed the header content of .frm files, but there was nothing in the header that helps find the correct page, then how does the DBMS find it?!


texts: Passing comma separated values to parameters in stored procedure

texts:
I have a requirement to retrieve data from a table based on the comma separated values sent in the parameters for stored procedure. As of now I have made the code work for one single value not sure how to make it work for multiple values.

Sample table:

CREATE TABLE [dbo].FinalStatus
(
    [ID] [int] Primary key IDENTITY(1,1) NOT NULL,
    [Col1] [varchar](15) NULL,
    [Col2] [varchar](15) NULL,
    [Col3] [varchar](100) NOT NULL,
    [LastUpdatedDate] [datetime] NOT NULL DEFAULT (getdate())
)

Test data:

Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('10','ABC21','Msg2')
Insert into FinalStatus (Col1, Col2, Col3) values ('11','C21','Some Msg1')
Insert into FinalStatus (Col1, Col2, Col3) values ('12','BC21','Some Msg2')

Stored procedure:

CREATE PROCEDURE [dbo].[FindResult]
    (@col1  VARCHAR(15) = NULL,
     @col2 VARCHAR(15) = NULL)
AS
    SET NOCOUNT ON
BEGIN
    DECLARE @c1 VARCHAR(15)
    DECLARE @c2 VARCHAR(15)

    SET @c1 = @col1
    SET @c2 = @col2

    SELECT
        Col2, Col1,
        LastUpdatedDate, Col3
    FROM
        dbo.FinalStatus
    WHERE
        (Col1 = @c1 OR @c1 IS NULL)
        AND (Col2 = @c2 OR @c2 IS NULL)
    ORDER BY
        LastUpdatedDate DESC
END

Execution script for single value (it works till here):

--To get all data
EXEC [dbo].[FindResult]

--passing first parameter alone
EXEC [dbo].[FindResult] @col1 = '10', @col2 = NULL

--passing second parameter alone
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'c21'

Now how to make it return appropriate result even when we pass multiple values to the parameter?

Something like this:

EXEC [dbo].[FindResult] @col1 = '10,12', @col2 = NULL
EXEC [dbo].[FindResult] @col1 = null , @col2 = 'ABC21, c21'

The underlying table would minimum have 100000 records at any given point in time.

Did tried reading "Arrays and Lists in SQL Server 2008" by Erland Sommarskog but its way over my head. So looking for some help in modifying this stored procedure.


texts: How to create and process queue of data (FIFO) from MS SQL Trigger

texts:
I would like to create a table and fill it with data and have it act as a queue that works in a first-in-first-out fashion. New items can be added at any time while processing or not.

My inserts would look like this

INSERT INTO MyQueue
SELECT EmployeeId FROM OtherTable
WHERE EmployeeId NOT IN
   ( SELECT EmployeeId FROM MyQueue )

At the same time there needs to be some mechanism that dequeues the next item and processes a stored procedure passing the latest value. I have a sql trigger that populates the queue.

What would be a good strategy for this mechanism?

The problem I am facing Is that I have the necessity to update some static data when a trigger (parent trigger) is fired. Because, of the nature of the update all the employees in a company need to to be recalculated from the trigger. At the same time the trigger needs to fill in some missing courses that my employees need to be trained in.

When a new course is created it also needs the values to be recalculated but only on a one-to-one basis for that employee. Courses can be created from with in my app or in the parent trigger. So, when the company has to process everything fires twice and with large companies the process can take 10 seconds or more.


texts:
We will be modifying a SQL Deployment in our Hosted Environment. We have a solution which is currently configured to use one Application (and not SQL) System Database (which holds Security and System Level Configurations for our Application) and numerous independent Company Databases, each used by different users (originating from independent Organizations hosted in our Environment). We will be changing this deployment, after which each Organization will have its own System Database (instead of using a unique one for all Organizations). Thus, each Company Database will now have an associated Application specific System Database.

To summarize, before the deployment let us suppose that a Server had 100 Company Databases and one System Database, it will now have 100 Company Databases, plus 100 System Databases. Thus, the number of Databases will have doubled. Meanwhile, the number of transactions on the SQL Server will be identical. As an example, upon logging into a Company Database, a user will log into its associated System Database first in order to verify its Application specific Security Role for instance, instead of accessing the shared System Database as before. Hence, the Number of transactions launched against the System Databases will remain the same (as when there were only one of them), but the number of Databases will double.

Considering that the Number of Transactions will remain the same, but that the Number of Databases on the SQL Server Instance will double, how should we determine how much more Memory should we be adding (if any) to the Server?

Thank you.


texts: Using CONVERT TO CHARACTER SET for changing only collation?

texts:
I think the answer to this is an obvious yes, but I'm having trouble finding confirmation on it. All the questions related to charsets seems to be about changing the charset and not only collations.

So I have tables in utf8mb4_general_ci that I want to convert to utf8mb4_unicode_ci. Can I safely run the following query to get the task done?

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

I would run that on all tables that have a table collation in information_schema set to utf8mb4_general_ci. Anything I need to be aware of before beginning this task? Should I double check that the columns in the table also have the same charset set? I've received a warning that even just changing the collation can cause charset issues to pop up afterwards, but not sure how much truth there is to that...

I also have latin1-tables and utf8-tables in these databases to convert. But I think I'll just try to tackle one challenge at a time.. Since I don't want to have the column types changed, I guess I'll have to do it in a more complicated way than using that command.

(I'm trying to fix charset inconsistencies between tables in numerous databases we have)

So I would like to start by unifying the collations on those tables already utf8mb4 and then just continue gradually from there.

Server version: 5.7.16-10-log Percona Server (GPL), Release '10', Revision 'a0c7d0d'


texts: Storing parent-child with n amount of depth

texts:
I am recording information in a MySQL database about the components web page. For example, page A has 2 tabs, each of which has 5 links inside it. Some of those links may actually expand to be a group of 3 additional links.

Example:

-> Page A
-> -> Tab 1
-> -> -> Link 1
-> -> -> Link 2
-> -> -> Link 3 (Clicking this expand a section with 2 sub-links)
-> -> -> -> Link 4
-> -> -> -> Link 5

-> -> Tab 2
-> -> -> Link 6
-> -> -> Link 7

I am unable to figure out the best possible architecture to store this information. For example, How would I store the relationship between Page A and Link 5? I only see two options:

A.) Create 4 tables all linked with each other by an ID. This is not a valid option because the depth level (number of components between the page and the final link) will change based on which page I am on.

B.) Create 1 table that has an ID, Parent ID and item name inside it:

ID | Parent ID | Name
1  | -1        | Page A
2  | 1         | Tab 1
3  | 2         | Link 3
4  | 3         | Link 4
5  | 3         | Link 5

However, I am not sure if this is the best way to store this. For example, I will have to make as many SELECT calls as there are items to get all the children and sub-children of a given component.

Is there a better way to store this type of data?


texts: sp_WhoIsActive: Are Stats Cumulative for Session or Batch?

texts:
In the output of sp_WhoIsActive, are columns such as reads, writes, CPU, and wait_info cumulative for the session, or just cumulative for the batch?  We have a query that is blocking a lot and showing millions of reads, but when I run the eight queries in the batch from SSMS, the reads are in the single digits, so I'm trying to understand whether something different is occurring with the way the application is running it to cause it to read so much more.

Or could it be something different?  I notice that the session has 2 open transactions.  Could those stats represent all of the transactions that session currently has active?


texts: If the maximum current value of an ID generated by a sequence is N, does that guarantee that all future rows will have index > N?

texts:
I'm debugging some legacy code and have found what might be a flaw in how it interacts with the database. A simplified version of it is as follows:
TableA is acting as a queue. It has data inserted into it from multiple users/sessions at pretty high velocity. There are never any updates or deletes except the one I am about to describe. This table has an integer ID which is populated by a 'before insert' trigger on the table that draws values from a sequence.
A consumer application repeatedly selects the whole table, processes the data, and stores the maximum index value of the resultset each time. It then executes
DELETE FROM TableA WHERE id<=:maxSelectedID

My question is, is this a reliable way to delete all the data that was previously selected (and only that data)? I feel like with the volume of inserts on this table that it is quite possible that a higher ID might get committed (and thus be available to select) before a lower one. In this case the lower one could potentially be deleted without ever having been selected (or if we are lucky it would also miss the delete get caught in the next select).


texts: Problem with (not) Locking Tables during Restore

texts:
First time here in DBA. I'm working to create a simple and strong database backup plan. My solution that is already working to create a gzipped, encrypted backup is:

$string = "mysqldump -u$db_user -p$db_password $database $table | gzip | openssl enc -aes-256-cbc -salt -pass pass:$ePass > " . $path . $table . '_' . $d . "_en.sql.gz";
$result = shell_exec ( $string );

I am trying to test the dump and my programming (head to toe) by importing it into another another table. The table User_test_import below doesn't exist in the operational and live site.
The dump is creating as an example (after modification to test import):

-- MySQL dump 10.11
--
-- Host: localhost    Database: accoun3_flifo
<omitted>
--
-- Table structure for table `User`
--

DROP TABLE IF EXISTS `User_test_import`;
CREATE TABLE `User_test_import` (
   <omitted>
);
--
-- Dumping data for table `User`
--

<Line in question>
LOCK TABLES `User_test_import` WRITE;               /*  Line 59  */
/*!40000 ALTER TABLE `User` DISABLE KEYS */;
INSERT INTO `User_test_import` VALUES ( ... <omitted> ...
);

The third line up had to be 'commented' in order for the import to work using:

$import = 'User_5.sql.gz';
$string = "gunzip < $path$import 2>&1 | mysql -u$db_user -p$db_password $database 2>&1";

From the command line the above reports ERROR 1100 (HY000) at line 60: Table 'User' was not locked with LOCK TABLES User is an existing table. Line 59 is the

The question then is, how do I use the dump with it's LOCK TABLES statement? Thanks.

Edit Question:
Why is TABLE User was not locked with LOCK TABLES occurring? How can it be prevented?


texts: Set Constraint To Display Date In MMDDYYYY Format

texts:
I am using SQL Server 2008 R2 - and used this syntax to add a Constraint to my table

ALTER TABLE ProdData ADD CONSTRAINT Cst_TodaysDate DEFAULT GETDATE() FOR InputOn

ProdData is my table name
Cst_TodaysDate is my Constraint Name
GetDate() is the default value
InputOn is the field I want the GetDate() added to

Now the problem I have is that the Constraint when viewed is in this format:
Aug 15 2016  2:20PM

And I want it to be in
2016-08-15 14:20:34.633

Did I set up the constraint incorrect?  What must I alter in order to get the date to display MM/DD/YYYY format?

If I run Select GetDate() from within my SSMS I get the proper output of 2016-08-15 14:20:34.633 - so why does a Constraint field show this differently?


texts: Aerospike schema: which one is the better design?

texts:
We have database requirements as follows:-

Huge number of records: 10-100 millions per set.
Huge number of bins: around 100 bins
Some point queries need to be run within milliseconds.

Now which one of the following two schema design philosophy will be better for aerospike?

Have one set of each type with all possible bins (in hundreds). But would it degrade Aerospike performance?
Categorize bins and have multiple sets with each set have around 10 bins max. But this means redundant keys in each set. High space complexity and hard to combine data for same key from different bins.


texts: Temporary Resolving State on Synchronous HA, is there a way around this?

texts:
So we're having an issue whereby any loss of communication between our 2 HA nodes causes the primary node to temporarily go into a 'resolving' state. This is for all of 1-3 seconds however we serve a DB to an application which doesn't retry connection and requires users to close and reopen meaning they lose work in progress.

This cluster is comprised of 2 nodes, one active and one non-readable secondary with a fileshare witness. All of our other applications don't mind this temporary blip however as mentioned this has a huge visible impact on other users. Is this a given when running HA in synchronous commit?

My concern is also when patching the secondary, I will be kicking users off when the secondary goes down. Would switching to async remove this constraint or is this an issue with how it has been configured?


texts: Does putting single quotation marks around numeric constants really protect from SQL injection in MySQL?

texts:
The manual seems to suggest that using quotes around numbers is sufficient to protect from SQL injection.

According to section 5.3.1. General Security Guidelines of the MySQL 5.1 Reference Manual:

  If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it.

Does that mean that the user is protected if the user enters the value 234' OR 1=1 # ? (i.e. to generate the query SELECT * FROM table WHERE ID='234' OR 1=1 #')


texts: Can I query stored procs in all databases with one query without dynamic sql

texts:
If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT
     'select ' + '''' + name + '''' + ', name from [' + name
   + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI '
FROM sys.databases
-- I get a collation error from the following
WHERE name NOT IN ('ReportServer', 'ReportServerTempDb');

Can I do the same thing without dynamic sql and without the cursor?


texts: Tail log backup or use log backup after putting DB into single user mode?

texts:
I plan to migrate existing SQL Server 2008 DB to new server (has 2017 engine) with minimum down time.  I will do full restore, then log restores (all with norecovery) on the new server up until the time of migration.  I have tested the restore process using my plan which is this:

For the final log backup, my plan now is to set the database into single user mode, then do a "regular" log backup. Do I need to do the "tail" log backup (add NORECOVERY option) while taking this last log backup? if so, then why? If the DB is set into single user mode which means that no one except me can access database, can I omit using NORECOVERY?  I use Ola's scripts to do log backups, and would rather just run my "regular" log backup job after the DB is in single user mode, copy that backup to the new server, and do the final restore.

Bottom line - is there a difference between log backup in single user mode and a tail log backup?


texts: What do spikes in InnoDB lock structures indicate?

texts:
I co-maintain a high traffic site similar to StackExchange. It runs MySQL 5.1 (working on replacing it with MySQL 5.5 or MariaDB). All our tables are InnoDB. MySQL goes down a couple of times a day. By that I mean that queries start timing out and the site goes down accordingly.

When this happens, and I have 'mtop' open, I can see queries queueing up trying to do INSERTs on tables with only 60.000 or so rows. There's only a few indexes on such tables, all integers.

In our MonitorIX MySQL graphs, supplied by a Percona package, I see spikes in a 'InnoDB lock structures' that correspond with the downtimes:

Another corresponding phenomenon is a spike in queries that reside in state Statistics:

What exactly is happening when a database shows spikes in amount of lock structures and queries in state Statistics? Where do I need to start looking to overcome these outages?

Aside from these incidental outages, our site performs fine.


texts: What's the maximum number of listeners an Oracle database can have?

texts: Can an Oracle database have more than one listener? If so, what's the maximum number of listeners a database can have?


texts:
I have an Oracle database that has one user that has access to everything. This was fine when we only had one user working on the database. But now that we have more users we want to tighten up security and have different users.

I created each user, gave them DBA access, and they are part of the same tablespace. When the user logs in, there are no tables visible to the users. I've tried to GRANT SELECT to a test table and still nothing. I tired to set up a role for the user and still nothing. Am I missing something or if the user is not the owner of the table other users cannot see the tables or run queries?


texts: SELECT ROWS WITH SIMILAR ID AND MERGE THE FILED VALUES IN ONE

texts:
My situation is as follows. I have the query below and the query is doing its job pretty well. However I have excluded one able from it which is field_data_field_luogo and the rows inside this can have column entity_id and with different values in column field_luogo_tid , so what i want to do is , if entity_id is similar for 5 rows then merge the column of field_luogo_tid 's values in one column instead of having multiple rows.

SELECT a.nid,a.type,a.title,a.status,b.body_value,c.field_bici_value,
d.field_classifiche_url,d.field_classifiche_title,e.field_circuiti_tid,
f.field_corto_value,g.field_dcorto_value,h.field_medio_value,
i.field_dmedio_value,j.field_lungo_value,k.field_dlungo_value,
l.field_cronometraggio_tid,m.field_dalzeroit_value,n.field_data_value,
o.field_edizione_value,p.field_email_email,q.field_recapiti_value,
r.field_note_value,s.field_partenza_value,t.field_organizzatore_value,
u.field_star_value,v.field_superstar_value,w.field_tipomanifestazione_tid,
x.field_verifica_percorsi_value,tex.name as c_name ,
tex_t.name as cr_name,tex_n.name as m_name
FROM node a
LEFT JOIN field_data_body b ON b.entity_id = a.nid
LEFT JOIN field_data_field_bici c ON c.entity_id = a.nid
LEFT JOIN field_data_field_classifiche d ON d.entity_id = a.nid
LEFT JOIN field_data_field_circuiti e ON e.entity_id = a.nid
LEFT JOIN field_data_field_corto f ON f.entity_id = a.nid
LEFT JOIN field_data_field_dcorto g ON g.entity_id = a.nid
LEFT JOIN field_data_field_medio h ON h.entity_id = a.nid
LEFT JOIN field_data_field_dmedio i ON i.entity_id = a.nid
LEFT JOIN field_data_field_lungo j ON d.entity_id = a.nid
LEFT JOIN field_data_field_dlungo k ON k.entity_id = a.nid
LEFT JOIN field_data_field_cronometraggio l ON l.entity_id = a.nid
LEFT JOIN field_data_field_dalzeroit m ON m.entity_id = a.nid
LEFT JOIN field_data_field_data n ON n.entity_id = a.nid
LEFT JOIN field_data_field_edizione o ON o.entity_id = a.nid
LEFT JOIN field_data_field_email p ON p.entity_id = a.nid
LEFT JOIN field_data_field_recapiti q ON q.entity_id = a.nid
LEFT JOIN field_data_field_note r ON r.entity_id = a.nid
LEFT JOIN field_data_field_partenza s ON s.entity_id = a.nid
LEFT JOIN field_data_field_organizzatore t ON t.entity_id = a.nid
LEFT JOIN field_data_field_star u ON u.entity_id = a.nid
LEFT JOIN field_data_field_superstar v ON v.entity_id = a.nid
LEFT JOIN field_data_field_tipomanifestazione w ON w.entity_id = a.nid
LEFT JOIN field_data_field_verifica_percorsi x ON x.entity_id = a.nid
LEFT JOIN taxonomy_term_data tex ON tex.tid = e.field_circuiti_tid
LEFT JOIN taxonomy_term_data tex_t ON tex_t.tid = l.field_cronometraggio_tid LEFT JOIN taxonomy_term_data tex_n ON tex_n.tid = w.field_tipomanifestazione_tid
WHERE a.type = 'manifestazione'
AND a.status = 1
GROUP BY a.nid

like if I have multiple rows with entity_id = 5 with field_luogo_id different in every row like :

2345
9877
9863
2374

by merging it will become :

2345,9877,9863,2374


texts: How to -create- performance issues? - oracle

texts:
I want to dedicate some time to learn more about performance and tuning issues.
I assign for that a clean DB. I want to know how can I load into it some data and performance-problem queries/DML/DDL?
Do you know some scripts that can cause/generate that (purposely or not)?

The idea is to learn the basic(and maybe more) of em/awr/addm etc - tuning and performance wise. I dont really know what specific to ask, but I'm guessing index problems, selects issues, wrong way to access the data, etc..

Books are great but I have the knowledge I need some actual work on that.

Maybe its not a Q&A at all, but for me any answer (as long as it is an answer to my question) will do.

EDIT
I dont want just a server load script - because thats has no point, plus thats not what I want.


texts: How to get SQL Server CPU and Memory read and write usage within two weeks?

texts: Is there any a quick way or quick script on how to get the CPU and Memory usage within a time range (at least two weeks)? I did my research earlier and it seems that the procedures available seems complicated. Any help will be appreciated.


texts: MySQL replication to slave having VPN connection to Master

texts:
I am setting up an environment in which the Master and Slave are on different networks at different physical locations (both run on MySQL 5.5.20). I have set up a VPN connection from slave's network to Master's network to accomplish replication.

But I observed over a few days that sometimes the VPN connection is lost. I was wondering, if the slave loses connection to the master, will it result in lost data for replication?

And when the connection is re-established, will replication start from the point it stopped or from the point where the Master's pointer is at that moment ?


texts:
Before, I asked a question in here how to linked the SQL to MYSQL. It was a success and I can also query from it. The problem is upon searching why "I can't find SQL SERVER AGENT" in SQL SERVER MANAGEMENT STUDIO EXPRESS EDITION is because it is "EXPRESS". All the ideas that I will be working using SQL Scheduler will unable to continue because the management studio that I have is not capable of it.

Can I have alternative way where in I can create scheduler or scheduled time for Transferring the SQL to MYSQL.

Thank you.


texts: Backup database and clean old backup question

texts:
Right now we have a maintenance plan which looks like this:

backup all databases
clean old backups (> 24 hours)

This job runs every day.
Every now and then this script crashes during backup step so we are left with all old backup files.

We want to rewrite this maintenance plan so it runs like this:

backup database 1
clean old backup for database 1
backup database 2
clean old backup for database 2
etc...

For the first we can use built in actions.
Is there any simple approach for the last method described? Or do we have to script it completly?

We don't want to add all databases manually because then we have extra maintenance work everytime somebody adds a new database which also results in potenitially unbackuped databases.


texts: Synchronising batch delete from multiple applications with advisory lock in Postgres

texts:
We are moving our application from one-instance to multiple instances (all connected to the same Postgres database) in order to increase resiliency of one of our services, and I am now facing a synch issue between multiple instances of the app all trying to delete "old" data once a day (basically, "delete all rows since 30 days ago or more").

I am considering using an advisory lock in Postgres as a synchronisation token so that only one instance actually performs the deletion.

So this is how I envision it (assuming all instances run the exact same code): At, say, 4 AM each morning all 3 instances will try to acquire the advisory lock; only one will succeed initially, and it will perform the deletion of old data, while the other app instances will wait on the lock.

Once the first instance is done, the other instances will, in turn, acquire the lock but will find that there a no more eligible rows and return.

Please let me know if you see any issue with the above approach.


texts:
Is there a functional difference between the tables built in the following two ways?
CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test_uq] ADD UNIQUE NONCLUSTERED(f1)

vs.
CREATE TABLE [dbo].[test_uq](
    [ID] [int] NOT NULL,
    [f1] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[test_uq] ADD CONSTRAINT UQ_f1 UNIQUE (f1);

I believe they're both implementing a unique constraint with a corresponding non-clustered index on the [f1] column but given the different syntax (both in creating and if you script the builds afterwards from SSMS) I'm wondering if one is different (or better?) somehow.  Thanks for any feedback.


texts: SSMS / SQL-Server delays console output (RAISEERROR WITH NOWAIT)

texts:
It is well known, that SSMS (or maybe the SQL Server, but I'd guess more the SSMS) delays PRINT messages until several lines (40) are gathered in the buffer, before they are really printed to the message window in SSMS (exception: something else is printed out, e.g. the number of rows affected by an UPDATE or the batch is done).
So I usually use RAISEERROR(<text>, 0, 0) WITH NOWAIT to print it out immeadiately. This works well for the first 500 rows, after this SSMS seems to start to buffer them again (50 lines, starting at 1000 lines the buffer seems to increase to 1000).
Does anybody knows, how I can prevent this "buffer feature" (e.g. if I manually run the statistic updates by using Ola Hallengreens maintenance solution, I'd prefer to know, what it really does at the moment without having to use sp_whoisactive etc.).
PS: You can simulate this behavior by using the following "script"
DECLARE @i INT = 0

WHILE @i < 10000
BEGIN
    SET @i += 1
    RAISERROR('Step %i', 0, 0, @i) WITH NOWAIT
    --PRINT @i
    WAITFOR DELAY '00:00:01.0' -- wait 1 second, feel free to decrease
END


texts:
I installed Oracle client and Toad and have worked fine.

But When I install Oracle server also in the same system.

The server and the client seem to conflict on the path something.

I decided to delete both and then install a server only not client because I realized that a server includes a client component.

After installing Oracle server, I run Toad.

But Session -> new connection shows there is no client installed.

I think I need settings for that.

How can I make Toad detect oracle client in a server?


texts:
I have back slashes stored in mysql.

I want to remove backslash '\'. So, I run query like

UPDATE table_name set col_name = replace(col_name, "Equipe d\'Olivier", "Equipe d'Olivier");

More intrestingly, even if I query something like

Select * from table_name where column_name = "Equipe d\'Olivier"

It returns row even with "Equipe d'Olivier" as well

I could not get any idea on this. How can I replace or remove that backslash


texts: Does Mysql locks table while insert operation

texts:
I am using Mysql(INNODB) and does Mysql locks table when I do some insert operation?

I have a huge table and it has lots of indexes. I do some research and Mysql doc says INNODB only locks rows not the entire table but I have indexes and does it still only locks rows? My insert queries nearly takes 36 seconds.

Query_time: 35.829970


texts: MySQL. Capture Cis program makes incorrect query: SELECT DISTINCT "PartType" FROM "Capacitor" ORDER BY "PartType"

texts:
I am having problems to get one database running. I have a MySQL in my server, the server is running "5.5.48-MariaDB - MariaDB Server" with "UTF-8 Unicode (utf8)"

I have a MySQL ODBC 64 bits 3.51 Driver to be able to connect to this database. The ODBC is configure to use utf8 character set.

If I use for example MySQL workbench to connect to this database for example:

SELECT * FROM leomaker_prueba_01.Capacitor;

All is perfect, I get the data. Also work with:

SELECT DISTINCT PartType FROM Capacitor ORDER BY PartType

I think that this is the correct form to make query of this kind.

The problem start with my program: "OrCAD Capture Cis". This program make a connection to a Database to fetch data and use it in the program.

In the console of the program I get this error:

  ODBC Error Description: State:S0001,Native:1050,Origin:[MySQL][ODBC 3.51 Driver][mysqld-5.5.48-MariaDB] SELECT DISTINCT "PartType" FROM "Capacitor" ORDER BY "PartType" ERROR(ORCIS-6245): Database Operation
  Failed  Please Check Session For More Details ERROR(ORCIS-6250): ODBC
  Error Code: -1 Description: You have an error in your SQL syntax;
  check the manual that corresponds to your MariaDB server version for
  the right syntax to use near '"Capacitor" ORDER BY "PartType"' at line
  1

As we see the query that it makes is

SELECT DISTINCT "PartType" FROM "Capacitor" ORDER BY "PartType"

Which is incorrect. I also try it in MySQL Workbench and i get the same kind of errors. I am sure that is problems of the " symbols.

The default database connect to a local SQL server. (but i can't see the querys)

Which could be the problem? I try to use MariaDB ODBC and i get the same error. Also if I try to change of character set.

It could be any form to change the querys to the database?

Thanks


texts:
It seems that whenever I restart my Windows 7 64bit box MySQL gets corrupted.

I just wanted to make this bug known as it's a serious issue. Are there any special settings I need to setup (task scheduler) to prevent this from happening?

Is there a way to inform Windows to shutdown MySQL?


texts: How we can recover MySQL database (Innodb,myisam)

texts:
I have found some recovery tool innodb-recovery but it works only on linux machine, I would like to know how to recover innodb tables on windows.

Transaction ID based recovery
Cancel based, Change based recovery
Point in time based recovery

Let me know if any of the blogs are available for this, and looking for good information.


texts: Passing value of datatype interval in parametrized query

texts:
The context is connecting to a Postgres db from a rest server.

To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is older/newer than an arbitrary value.

In the example query below, the table structure is simple - name is of type text, and creation_date is of type timestamp. So when I do something like

server_pg_module:query("select name from new_table where
current_timestamp - creation_date < '6 days'")

it works nicely. But what I really want to do is get that value of 6 days from the server. So I try something like

server_pg_module:query("select name from new_table where
current_timestamp - timestamp < $1", ["6 days"]

it throws an error. I tried '6 days', "'6 days'" and a few other concoctions, all throw errors. So to check I added a new column interval of type interval and tried a query like

server_pg_module:query("insert into new_table (name, interval) values ($1, '3 day')", ["fooo"]).

which works, but

server_pg_module:query("insert into new_table (name, interval) values ($1, $2)", ["fooo", "3 days"]).

breaks. For good measure, in addition to the concoctions like "'3 days'" mentioned above I also tried $2::interval (which I am not sure is legit), but it doesn't work.

Thus I believe it might either have something to do with expressing an interval in a param query, or something peculiar about the module I am using. Any ideas on what causes the trouble and how to do this sort of thing would be appreciated. Or may be it can be narrowed down that the problem is not with pg but with the module, then I have to address it elsewhere.

Postgres version: 10.x

The module I am using is pgo (for the Erlang programming language) https://github.com/SpaceTime-IoT/pgo. The error message I get (when I pass "2 days" or "'2 days'" as the query parameter) looks like:

{error,{pgsql_error,#{code => <<"08P01">>,file => <<"pqformat.c">>,
                          line => <<"575">>,
                          message => <<"insufficient data left in message">>,
                          routine => <<"pq_copymsgbytes">>,severity => <<"ERROR">>,
                          {unknown,86} => <<"ERROR">>}}}

And when I pass '2 days' as the parameter, it throws a badarg error.


texts: Slow query when having 'contains' and '=' together in where clause

texts:
The following query takes about 10 seconds to finish on a table with 12k records

select top (5) *
from "Physician"
where "id" = 1 or contains("lastName", '"a*"')

But if I change the where clause to either

where "id" = 1

or

where contains("lastName", '"a*"')

It'll return instantly.

Both columns are indexed and the lastName column is also full-text indexed.

CREATE TABLE Physician
(
   id         int identity    NOT NULL,
   firstName  nvarchar(100)   NOT NULL,
   lastName   nvarchar(100)   NOT NULL
);

ALTER TABLE Physician
  ADD CONSTRAINT Physician_PK
  PRIMARY KEY CLUSTERED (id);

CREATE NONCLUSTERED INDEX Physician_IX2
   ON Physician (firstName ASC);

CREATE NONCLUSTERED INDEX Physician_IX3
   ON Physician (lastName ASC);

CREATE FULLTEXT INDEX
    ON "Physician" ("firstName" LANGUAGE 0x0, "lastName" LANGUAGE 0x0)
    KEY INDEX "Physician_PK"
    ON "the_catalog"
    WITH stoplist = off;

Here is the Execution Plan

What could be the problem?


texts:
I am trying  to design the schema for an app that I am creating, but I am having difficulty figuring out how to do one-to-many relationships.

This is a school app where students will register into a course and tutorial.

var CourseSchema = new mongoose.Schema({
    name: { type: String, required: true },
    code: { type: String,  required: true, unique: true },
    tutorials: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Tutorial' }]
});

var TutorialSchema = new mongoose.Schema({
    number: { type: String, required: true },
    students: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Student' }]
});

var StudentSchema = new mongoose.Schema({
    firstName: { type: String, required: true },
    lastName: { type: String, required: true },
    email: { type: String, required: true },
    password: { type: String, required: true }
});

Simple enough.

Now, what if I want to add a feature that will allow students within the same tutorial to be part of groups. Some tutorials will have group-based work and some not.

One way I can think of doing it is to add a groups field as an array of references to groups.

var TutorialSchema = new mongoose.Schema({
    number: { type: String, required: true },
    students: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Student' }],
    groups: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Group' }]
});

var GroupSchema = new mongoose.Schema({
    number: { type: Int, required: true },
    students: [{ type: mongoose.Schema.Types.ObjectId, ref: 'Student' }]
});

Should I worry about data duplication? Is there a better approach?


texts: Create mysql user which is also a domain account

texts:
I am from MSSQL background and trying to learn MYSQL on a windows server 2012 VM. I want to create a user in MYSQL and the user is also a domain account. Please give me the script to get it done or is it a limitation of MYSQL.

CREATE USER 'domain\abc'@'%' IDENTIFIED BY 'password';

If I try the above command then it's creating a user by name 'domainabc' in mysql


texts: PostgreSQL vacuum starting too late (every 100k instead of 10k dead rows)

texts:
I have a large table (50M) and autovacuum seems to work properly in general. However I started monitoring it with Datadog and I have noticed something strange:

select relname, reloptions from pg_class where relname='subscriptions';

{
  autovacuum_vacuum_scale_factor=0,
  autovacuum_analyze_scale_factor=0,
  autovacuum_vacuum_threshold=10000,
  autovacuum_analyze_threshold=10000
}

So you expect the table to be cleaned every time it reaches 10k dead rows.

However the graph of dead rows shows that vacuum starts much later, at about 100k or even 200k dead rows:

Why? How can I make the vacuum run more frequently (every 10k dead rows)?


texts: concatenating columns from multiple unrelated 1-row resultsets

texts:
Assume I have these two queries in SQL Server 2014, both of which return one row from unrelated tables:

SELECT SUM(A) A, SUM(B) B FROM X
SELECT SUM(C) C, SUM(D) D FROM Y

I'd like to combine these queries into a single resultset that contains A, B, C, D.

What's a good way to do this within a single query, as opposed to multi-query solutions like selecting the results into and out of scalar variables?


texts: How to export import content from inside a table using mysql commands

texts:
I'm transferring a WordPress site to a new server, but I'm trying to do the process as clean as possible. However, since I have to transfer a lot of posts to the new database, and the export feature on WP doesn't work well, I exported only the essentials tables, and then I'm configuring everything again from scratch.

The only problems is that I'm using the TablePress plugin which stores its data in the wp_posts and wp_postmeta tables, and the settings in the wp_options table.

Since I already the restored the posts using the wp_posts and wp_postmeta, the entries from TablePress plugins are already in the database, but the content doesn't show up in the plugin, because the index and option are located in the wp_options, which I'm configuring again from scratch.

The plugin developers has suggested that I can export the tablepress_tables and tablepress_options entries from the wp_options, but neither him or I knows the syntax to export and import the entries from a tablet, in this case from the wp_options table in mysql without using phpmyadmin, since it's not available in the server.

So I wanted to ask if anyone could help figuring out the how to export tablepress_tables and tablepress_options entries from inside the wp_options table inside a database called wordpress using command line on mysql 5.7?

Thanks,


texts: FileStream DB SQL Server - merging DB data

texts:
We recently upgraded a server from 2014 to 2017. Since down time was a concern, we migrated some DB's a couple days before.
Migration complete Sunday afternoon, we've now come to find out the Filestream DB was used between restore and cutover. So we have a datagap to mitigate. I restored the old DB to a backup server, and did a basic 'not in' to find 12 documents in the old DB.
Finally, the question: how do we get those 12 documents into the current DB? Is it as simple as an insert from old to new?
If it helps, this query is what shows the 12 record gap:
SELECT *
INTO #tmpFileStore
FROM OPENQUERY (OldDBSever_LinkedServer, 'SELECT * FROM [FSDB].[dbo].[DocumentFileStore]');

SELECT stream_id
  FROM #tmpFileStore
  WHERE stream_id not in (Select [stream_id] FROM [dbo].[DocumentFileStore])
GO


texts:
I have a database called LIVE and I want to create a scenario were I copy the LIVE database every night at 2:30 am. I want the copied database to be called REPORT. So every night I want the REPORT database to be overwritten by the content of the LIVE database. I found a way to do this by running a cron job with mysqldbcopy.

30 2 * * * mysqldbcopy --source=user:pwd@localhost --destination=user:pwd@LIVE:REPORT

I need to know if the code above when executed by cron will overwrite REPORT with the LIVE database every night at 2.30 because I am confused by the copy statement. Copy seems like a one time thing. So will the code above :

Create a database called REPORT
Copy LIVE database into REPORT
Overwrite REPORT the next day with the latest copy of LIVE and continuously do so.
If my code above does not do that can someone adjust it to do that please.


texts: Rebuild an index while it is being referenced in Oracle

texts:
I have come across many articles on this site and on Tom Kyte's blog on the how, whys and whens of Re-building indexes. Note that this question is about whether I can rebuild when there are concurrent sessions referencing those indexes; and are there any recommendations for the same?

I have a couple of SELECT queries running in an OLAP DB on a bunch of tables. Should I stop these sessions before the rebuild? I am assuming there might be problems with locks.

UPDATE:
Note that, our DBAs had advised against having active sessions during the rebuild. Also the size of the tables are in the range of 15-20GB and rebuilds can typically take 2-3hrs.


texts: Processor affinity for SQL Server on vmware

texts:
A while ago a consultant said that I should set processor affinity when I'm running SQL Server on VMWare. The advice was to disable CPU0 so it was free for the OS.

When I read the "Architecting Microsoft SQL Server on VMware vSphere"-PDF on the site of VMWare I find following:

  VMware recommends not using CPU affinity in production because it
  limits the hypervisor’s ability to efficiently schedule vCPUs on the
  physical server.

To me it looks like overkill to set processor affinity so a core would be free for Windows.
What is your advice about this?


texts: Messaging:- Need to find last child of its parent with in the same table

texts:
I am making a messaging module.

I need to find the all parents with their last child message.

Like:

+--------------------+----------------------+---------+
| pkUserDiscussionID | fkDiscussionParentID | message |
+--------------------+----------------------+---------+
|                  1 |                    0 | a       |
|                  2 |                    1 | b       |
|                  3 |                    1 | c       |
|                  4 |                    0 | a       |
+--------------------+----------------------+---------+

I need to find the last child of parent node (fkDiscussionParentID=0) with last child (pkUserDiscussionID=1 last child pkUserDiscussionID=3) with message.

Below I found a link close to my answer but not completely:

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query


texts: How to show two tables in one query? part and the whole


texts: Difference between changed compatibility level database and new database

texts:
What is the difference between SQL Server databases A & B where:

A. Database restored from lower level compatibility(100) to higher compatibility (130)

B. New Database created in higher compatibility (130)


texts: How can I disconnect clients from MySQL?

texts:
I need an efficient way to disconnect all clients with a given username from MySQL.  I thought about changing the users password but I think that is only checked when the connection is made.

Ideas?


texts: Silence Sqlplus Login Message but Retain Echo

texts:
Running a series of automated checks on our databases for the security folks checking our STIG compliance. Mostly simple stuff like certain parameters turned off, etc. We're in the stone ages, so this is being done with Sqlplus scripting to populate the reports.
Using sqlplus -S to filter out that unnecessary login message, but the security folks would also like the command with the finding details instead of just the output. With sqlplus -S, the output is
VALUE
------
FALSE

whereas we want
Select blah blah;

VALUE
------
FALSE

Set echo on doesn't fix this, even when nesting the query in a sql script and run with @. I'm assuming the S option overrides the echo setting. Wondering if there's an option I'm not familiar with or if I just need to work around it.


texts:
I want to daily rotate MySQL log files on Windows. I want to keep the files, I don't want to use tables instead.

There are 3 log files: general.log slow-queries.log and error.log

I can do it easily with a scheduled task. I saw that it recommended stopping the logs before I copy them to the backup directory.

One problem is that 'error.log' cannot be stopped.
Another problem is that stopping the logs may cause some loss of events, and I want to avoid it.

Is there a safer way (or built-in way) for logs rotation? (on a daily base or according to the file size)?


texts: Return type of JOINing stored procedure - PostgreSQL

texts:
Is it possible to express return type of stored procedure that returns joined tables using table type of one of joined tables?

My stored procedure looks like this:

CREATE or REPLACE FUNCTION selectJoin()
RETURNS SETOF ???
AS $$
BEGIN
    RETURN QUERY SELECT t1.*, t2.name
                 FROM t1, t2
                 WHERE t1.t2_id = t2.id;
END; $$
LANGUAGE plpgsql;

Table t1 has many (approx 70) columns so I'd like to somehow reuse the t1 type in RETURNS clause. Something like (t1, varchar(30)) or composite type inheritace. Is is possible?

PostgreSQL version 9.3


texts: Best way to capture historical changes between levels in a dimensional hierarchy

texts:
What is the cleanest way to capture changes between levels in a dimensional hierarchy?

I have the dimensional hierarchy [Area] > [Region] > [Location], where Area is the parent to Region and Region is the parent to Location. Location is lowest level of the hierarchy, and is associated with the fact table.

The business rules are: Locations are able to change the Region they are assigned to based on business need. Furthermore, Regions are able to change the Area they are assigned to.

We want to capture those changes so that when we do historical analysis, we can compare business metrics before and after a Location changed its Region, and a before and after a Region changed its Area.

What is the best way to do this?

The four ways that I have come up with all seem to have major drawbacks:

Using a bridge table between hierarchy levels requires that you use time bounds (effective start/end dates) to correctly identify which parent a child belongs to as you walk up the hierarchy. That makes queries prone to error, especially with hierarchies with many levels.
Versioning a child record whenever its relationship to its parent changes (e.g., versioning Region A when it moves from Area A to Area B) and then cascading that change all the way down the hierarchy (e.g., creating new versions of Locations tied to Region A so that they are tied to the new Region A).
Updating the child record's reference to the parent record, making you use time bounds to reassemble the correct history (prone to error).
Always use time bounds when querying up the hierarchy to make sure Location A is associated with the correct Region (likewise, Region A is associated with the correct Area) for the moment of the fact being analyzed.

None of these seems clean to me.

Is there a better way?

Thanks a lot


texts: how to write queries properly inside of a postgres RLS policy

texts:
I'm trying to do a single implicit JOIN inside of an RLS expression. I get a syntax error and have a "hacky" solution using a subquery, but would love to know if there is a better way.

So assuming two tables, projects and objects:

CREATE TABLE app_pub.projects (
  id uuid DEFAULT public.uuid_generate_v4 () NOT NULL,
  owner_id uuid
);

CREATE TABLE app_pub.objects (
  id uuid DEFAULT public.uuid_generate_v4 () NOT NULL,
  project_id uuid
);

The ownership is expressed on the project.owner_id, but the policy of interest will check against objects.project_id. The procedure app_pub.get_current_role_ids () returns an array of role ids for access.

Specifically this query's boolean result tells us if the user has access:

SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
    FROM app_pub.projects p, objects o
    WHERE (p.id = o.project_id)

I'd like to write a policy like so:

CREATE POLICY authenticated_can_select_on_objects
ON app_pub.objects
FOR SELECT
TO authenticated
USING (
SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
    FROM app_pub.projects p
    WHERE (p.id = project_id)
);

But this obviously fails due to syntax error, hence my question...

So I do have my "hacky" solution is to use a subquery and do TRUE IN (subquery):

CREATE POLICY authenticated_can_select_on_objects
ON app_pub.objects
FOR SELECT
TO authenticated
USING (
    (TRUE IN (
        SELECT (p.owner_id = ANY (app_pub.get_current_role_ids ()))
            FROM app_pub.projects p
            WHERE (p.id = project_id)
        )
    )
);

And YaY "it works!", with this TRUE IN (subquery) which feels wrong. I know I could write a procedure and use that, but was hoping to do something inline in hopes the query planner would be happier.

So, with all that context:

is this "hacky" solution the only/best way?
can I do a clean select or join like my first attempt was trying to accomplish?


texts:
I have setup Oracle TDE for my 11.2.0.4 database. I created the wallet. I created the autologin wallet and everything looked good.
{ORACLE_HOME}/bin/orapki wallet create -wallet cwallet.sso -auto_login -pwd password
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

The encryption wallet itself was open:
SQL> select STATUS  FROM V$ENCRYPTION_WALLET;

STATUS
------------------
OPEN

But after I restarted the database the wallet status showed closed and I had to manually open it. So my autologin did not work.
SQL> select STATUS  FROM V$ENCRYPTION_WALLET;

STATUS
------------------
CLOSED

Can anyone explain what could be the problem or what am I missing here? Thanks.


texts: What is the best way to design a database for this specific type of 'offer' that this restaurant chain offers?

texts:
I am working on adding a few features in a mobile application of a restaurant chain and the tech stack used is Java for Android and PHP + SQL for backend.
Until now I have always used NoSQL, but since this app is already in production & uses SQL they require me to continue using the same tech stack.
This chain has 9 outlets in the district and in each of their outlets they have a 'Five in a box' meal where you can choose any five things from their menu and order them under the title of 'Five in a box' with a discounted price than if you order them separately.
Now the task is to keep a track of what 5 things each customer ordered and from which outlet did they order. Each night a Cron job will determine which combination of 5 things is the least ordered that day and then the users who ordered that particular combination get another discount coupon.
(They also need to keep all of the old records as well for analysis.)
What I have in mind is:
A restaurant1_offerorder table which will contain the following columns -
userid - ID of the user who made that order,
date - date of order,
item1, item2, item3, item4, item5 (five columns) which will contain the ids of the food item that the user bought.
And since each outlet is projected to have at least 700+ 'Five in a box' meal orders everyday a restaurant1_offerorder_old table with the same schema as restaurant1_offerorder which will contain all the old order data.
Every night the Cron Job will insert all records from restaurant1_offerorder to restaurant1_offerorder_old
This way the database will have 2 tables for each restaurant (18 tables in total which I feel is the wrong approach)
Now,
Question 1:
Is this the right database structure for this scenario? And should there be a separate database for each restaurant to store the old order data since soon there will be a lot of orders?
Or will it be better to keep all orders in one table and SELECT * with today's date? (Consider the volume of orders say a year from now)
Question 2:
(Not to be implemented. Just curious and want to future proof the db structure and keep scalability in consideration)
Say this system needs to be scaled up and turned into a food ordering application where any restaurant can list themselves on the application and can opt in to the 'Five in a box' meal deal (from their own menu), would creating a new table for each restaurant's 'Five in a box' meal orders and a table for their old orders be the right approach? (Seems like a bad idea considering a rise in the number of restaurants and number of users)


texts: Publication shows up in secondary server

texts: I have created  log shipping and the primary server is also used for transactional replication. Now after creating log shipping I have noticed the publisher shows up on the secondary and it wont let me delete the database on the secondary as it is being used for replication. How do I delete the database from the secondary?


texts:
I am using AWS RDS DB in my web application. The problem is a table which has millions of rows in it and the number of reads/writes per second on the table are huge. This happens even though I have DB replication, caching and queueing (to write data) mechanism in place.

Becuase of the indexes and the size of the table, the inserts are becoming slower. Sharding/partitioning would also not help as there is not a single column with which table can be partitioned. Is there any other way I can improve write frequency per second to the table?


texts:
If I have an instance at SQL Server 2016 SP1-CU7-GDR (KB4057119) and want to jump over to the latest SP2 path, which patch or patches do I have to run?

SP2 = KB4052908
SP2-CU2 = KB4340355
SP2-CU2-GDR = KB4458621

Do I run all 3 of these in order, do I run just #1 and #3, or can I just run #3 all by itself?

It’s a bit confusing, but would appreciate the help.

Thanks,

Robert


texts: Oracle data dictionary, tell built-in roles apart from custom roles

texts:
In Oracle, if you describe the DBA_ROLES view, you only get three columns:

SQL> describe dba_roles;
 Name                      Null     Type
------------------------------------------------
 ROLE                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                  VARCHAR2(8)
 AUTHENTICATION_TYPE                VARCHAR2(11)

There is no column that could tell me whether or not a given role is one of the built-in roles the RDBMS comes with, like SET_CATALOG_ROLE or a user-created role like LEMONADE_STAND_MANAGER.

How can I get a list of all user-created roles (excluding all built-in roles)?


texts: Performance impact of Extended Events filter [like_i_sql_unicode_string]

texts:
I have created an extended events session in which I wanted to see all queries using nolock hint. I have decided to use it instead of sql trace in order to minimaze possible performance impact. Session was created like with following statement:

    CREATE EVENT SESSION [SCOMDB_debug] ON SERVER
    ADD EVENT sqlserver.deprecation_announcement(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)),
    ADD EVENT sqlserver.rpc_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.rpc_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sp_statement_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sp_statement_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_batch_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_batch_starting(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_statement_completed(
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%'))),
    ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
        ACTION(sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.session_id,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)
        WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%NOLOCK%')))
    ADD TARGET package0.event_file(SET filename=N'M:\MSSQL11.SCOMDB\MSSQL\Log\SCOMDB_debug.xel',max_file_size=(500))
    WITH (MAX_MEMORY=40960 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
    GO

The result was that database instance (used for SCOM databases) became very slow and application was almost unusable.

Wait stats were full of SOS_SCHEDULER_YIELD and PREEMPTIVE_XE_DISPATCHER and I could see many long running queries in sys.dm_exec_requests waiting mostly on CPU as well.

Is it expecated bahaviour? I can imagine that [like_i_sql_unicode_string] operator can be quite performance consuming but still I would expect much better performance.

Is there any other filter in ex events which could be used to catch queries which contain 'sometext'?


texts: PostgreSQL 9.5 database showing signs of corruption in logs, though clients work normally

texts:
We have a database server running PostgreSQL 9.5 on a Ubuntu 16.04 LTS box, with the total data size just under 30 GB.
We've set archive_mode to on, and archive_command is rsyncing WAL files to another server that is running Barman. To prepare for an eventual PostgreSQL upgrade using pg_upgrade, we've set up a disposable server instance also running PostgreSQL 9.5 that we're using as the target for barman recover. The idea is to get a snapshot of the DB there that we can test pg_upgrade on, to a PostgreSQL 12 instance running on the same server.
It is in this process, doing barman recover to the disposable server, that I discovered an issue. The target DB was stopped, and after Barman had finished rsyncing the contents of the data directory there, I restarted the server. It came online just fine and accepted queries as usual, but this error started appearing in the log file:
2020-12-08 12:05:20 EET ERROR:  could not access status of transaction 79509466
2020-12-08 12:05:20 EET DETAIL:  Could not open file "pg_clog/004B": No such file or directory.
2020-12-08 12:05:20 EET CONTEXT:  automatic vacuum of table "template0.pg_catalog.pg_statistic"

It got printed four times on server startup, and then again every 60 seconds.
I first assumed that this was an issue with Barman or the data on its server, but since no operational problems could be found there, I turned my eye to the production server itself. Lo and behold, ever since the Oct 21, that error has appeared on the production server. So this was never about something being wrong about the backup or restoration process, it was the actual production data that was wrong!
This has gone unnoticed. Nothing happened on the server (a VPS on Upcloud) on Oct 21 that we can discern: all of our web applications–for which we have robust error alerts–relying on the server have kept working normally throughout. Nobody can remember any manual operations having been run there on the day. It's possible that this was an issue with the VPS provider, who on Oct 28 reported having detected a storage backend problem, which was quickly resolved afterwards. This was a week after our problem first appeared in the logs, but I suppose that the symptoms may have started earlier.
Alphabetically the first file inside pg_clog is 004C. Based on some googled advice, I tried creating a 256k file of all zeroes called 004B. After I did, the error changed to:
2020-12-08 13:35:25 EET CONTEXT:  automatic vacuum of table "template0.pg_catalog.pg_statistic"
2020-12-08 13:35:40 EET ERROR:  found xmax 79509466 from before relfrozenxid 80163082

(This was on the disposable server that was the barman recover target, I haven't dared touch production.) This gets printed at 15 second intervals.
I have no idea how to further analyze, let alone fix this issue. Everything is working normally as far as our database clients are concerned, but this needs to be corrected. Any help is appreciated. Note that my knowledge of PostgreSQL data storage internals is close to zero.


texts: date not inserting - shows 0000-00-00 after insert

texts:
I am trying to insert date to a mysql table. The SQL query I used to create the table is as follows:

CREATE TABLE IF NOT EXISTS DDD(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    datecolumn DATE NOT NULL ,
    open FLOAT,
    high FLOAT,
    low FLOAT,
    close FLOAT,
    volume INT)

I am inserting the data with the following SQL statement:

INSERT INTO DDD(datecolumn, open, high, low, close, volume)
VALUES(2011-05-26, 12.09, 13.31, 12.05, 13.09, 1293441)

After that when I select and view the data the entries in the datecolumn is

  0000-00-00

I have tried the solution from

importing CSVs into MySQL table leaves date fields as 0000-00-00

...but it resulted in the following error:

ERROR 1064 (42000): You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version
for the right syntax to use near 'datecolumn DATE' at line 1

after the following query,

ALTER TABLE db.DDD MODIFY datecolumn datecolumn DATE;

I am a novice in SQL and learning now. Could anyone shed some light, what it is that I am doing wrong?


texts: SQL CLR very slow on Amazon EC2 Instance (SQL Server 2014 CU6)

texts:
I am running a SQL Server 2014 Standard Edition (Cumulative Updates 6) instance on an r3.large Amazon EC2 box.
I noticed that the CPU usage of SQL Server skyrockets to 100% while executing pretty much no queries at all.

Looking at the SQL Performance Tool, I can see that most of the time is lost in SQL CLR functions.

Here's a screenshot of one of such operations:
https://dl.dropboxusercontent.com/u/3098924/Schermata%202015-03-04%20alle%2016.46.17.png

Most of those CLR functions are doing basic things. For example the IsNullOrEmpty function is returning the result of String.IsNullOrEmpty() but even those are taking up between 1500 to 7000 ms to execute.

If I take the same job and run it on a physical box that we use for development, those calls can't almost be seen as they're way too quick.

Is there anyone who faced similar issues? I suppose it's due to EC2 or VMs but I can't really tell why. Thanks!


texts: prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

texts:
I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index.

MySQL accepts this query:

CREATE TABLE IF NOT EXISTS `poll_option_i18ns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `poll_option_id` int(11) NOT NULL,
  `lang` char(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `title` varchar(127) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`poll_option_id`,`lang`),
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

ALTER TABLE `poll_option_i18ns`
  ADD CONSTRAINT `poll_option_id` FOREIGN KEY (`poll_option_id`) REFERENCES `poll_options` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;

But in MySQL workbench I couldn't add foreignkey without index!
It is MySQL Workbench bug or I do it wrong way?


texts:
From blog.sqlauthority.com:

  Protecting ISV database access: in custom ISV (Independent Software Vendor) applications, the database schemas are usually complex, and in the case of applications such as Siebel and SAP, they are tuned for specific application access paths by using many customized indexes. Ad hoc access or alteration to the underlying base tables of these applications can severely impact performance of queries and the application itself. Using schemas, a developer can logically group objects and even create custom objects without running the risk of messing up the underlying ISV database

I have a vendor MS SQL Server 2008 R2 database that I need to add tables to in order to track timestamps. However, I am not allowed to modify the vendor's schema in any way, which is the "dbo" schema.

Would creating a schema new schema in the same database sufficiently isolate my proprietary objects from the vendor's so as not to interfere with things like vendor updates?


texts: Do higher normalized forms always meet the criteria of lower ones?

texts: For example, a database that is 3NF is always 1NF and 2NF; does this hold for 4, 5, and 6?


texts:
RAM usage is increasing in database server with time. When we restart the SQL services it take around 4 GB but after day or two it increases to 11-12 GB and even further.
Application is hosted on separate server. Please suggest what could be the reason.


texts: SSIS package blocks itself if uses TRUNCATE

texts:
There is an SSIS package with Required transaction on the package level and Supported on the tasks level. The main part of the package is deleting the data and inserting new data:

Here the Delete block deletes everything from six tables, and the Parse block loads six files to the six tables.

If the Delete uses delete from dbo.table, then everything works fine, but is slow.
But if Delete uses truncate table dbo.table instead, the deletion takes no time, but the package becomes blocked when it comes to the Parse task. Would just sit around doing nothing.

At this moment on the server side I can see an SSIS spid being blocked by spid -2, which is "Orphaned distributed transaction." It would appear that used to represent the truncating connection. This connection hangs around, and the package is blocked by the locks it placed.

If I switch the only used SQL Server Connection manager to RetainSameConnection = True, then the package fails as soon as it enters the Parse task. The task asks the manager for a connection, and the manager responds with

  [SQL Server Destination [471]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "Connection Manager" failed with error code 0xC001A004.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Is there a way to set up the package so that it uses truncate and does not block itself?


texts: What exactly do database passwords protect?

texts: I would like to know what exactly I am password protecting on my server when I set a database password. I am unclear as to what people are referring to when saying to password protect a database. Are they referring to a single specific database or do they mean the complete mysql database structure located on the server. The reason I ask is because, when I set a password, it is only one password for all my databases that I'm managing in phpMyAdmin. So when I'm required to enter the password in my config file in codeigniter, it is always the same one for all my different databases that I'm using. Is the password that I'm required to enter in order to even get phpMyAdmin started the Database Password which people refer to, or can I (and should I) set different passwords for my seperate Databases?


texts: Does disk errors on a mongo primary instance trigger election process?

texts: On the MongoDB site here states that a elections occurs when primary becomes unavailable. What happens if there is a disk read/write error on the primary node? Does heartbeat check covers disk health, system load or other checks or just covers connectivity?


texts: Why is MySQL MariaDB GREATEST(timestamp,NOW()) padding appending results with zeros?

texts:
Everything works as expected on my development machine, but when deploying to quality assurance environment, tests are failing because MariaDB is padding the result set with extra precision zeros after a decimal.

For example, when I run this query:

SELECT GREATEST(  '2016-04-14 15:06:30', NOW() )

On dev, running Ubuntu 14.04.4 LTS (Trusty Tahr) mysql Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3, I get this expected result:

  2016-04-18 09:07:42

On test, running Red Hat Enterprise Linux Server 7.2 (Maipo) mysql  Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1, I get this unexpected result:

  2016-04-18 09:07:42.000000

I don't see anything in the GREATEST() documentation that would explain this behavior, so I think it must be a server configuration setting in my.cnf but I couldn't find anything in online search results.

Even more curiously, when running the following queries, I get the same unpadded results on both machines:

SELECT GREATEST(  '2016-04-14 15:06:30', '2016-04-14 16:06:30' )

  2016-04-14 16:06:30

SELECT GREATEST(  NOW(), NOW() )

  2016-04-18 09:24:22

SELECT GREATEST(  5, 10 )

  10

Why is MariaDB GREATEST() padding/appending my result set with zeros on timestamp comparison with NOW()?


texts: What is the best permissions to set for only creating and managing own databases?

texts:
I want to create a login for a new user who could only create and manage their own databases. Other databases on the server should be read-only to that user. What would be a good set of roles/permissions to use to implement this?

Thank you for your help!

p.s. I am using SQL Server 2008 r2


texts: How do I create a database+tablespace+user in Oracle 11gR2?

texts:
Good evening,

I'm trying to figure out how to create a database+tablespace+user in Oracle 11g.

I've tried some tutorials, but nothing seems to work.

I cannot find Database Configuration Assistant either.

I'm running Windows 8 x64 with Oracle 11g R2, please tell me how I can do this task.

Thanks for all suggestions,

Alec Taylor


texts:
I have devices table like this:

traccar=# \d devices;
                                          Table "public.devices"
     Column     |            Type             | Collation | Nullable |               Default
----------------+-----------------------------+-----------+----------+-------------------------------------
 id             | integer                     |           | not null | nextval('devices_id_seq'::regclass)
 name           | character varying(128)      |           | not null |
 uniqueid       | character varying(128)      |           | not null |
 lastupdate     | timestamp without time zone |           |          |
 positionid     | integer                     |           |          |
 groupid        | integer                     |           |          |
 attributes     | character varying(4000)     |           |          |
 phone          | character varying(128)      |           |          |
 model          | character varying(128)      |           |          |
 contact        | character varying(512)      |           |          |
 category       | character varying(128)      |           |          |
 created_at     | timestamp without time zone |           |          |
 updated_at     | timestamp without time zone |           |          |
 driveruniqueid | character varying(50)       |           |          |
 protocol       | character varying(128)      |           |          |
 disabled       | boolean                     |           |          | false
Indexes:
    "pk_devices" PRIMARY KEY, btree (id)
    "uk_device_uniqueid" UNIQUE CONSTRAINT, btree (uniqueid)
Foreign-key constraints:
    "fk_devices_groupid" FOREIGN KEY (groupid) REFERENCES groups(id) ON UPDATE RESTRICT ON DELETE SET NULL
Referenced by:
    TABLE "device_attribute" CONSTRAINT "fk_device_attribute_deviceid" FOREIGN KEY (deviceid) REFERENCES devices(id) ON DELETE CASCADE
    TABLE "device_command" CONSTRAINT "fk_device_command_deviceid" FOREIGN KEY (deviceid) REFERENCES devices(id) ON DELETE CASCADE
    TABLE "device_driver" CONSTRAINT "fk_device_driver_deviceid" FOREIGN KEY (deviceid) REFERENCES devices(id) ON DELETE CASCADE
    TABLE "device_geofence" CONSTRAINT "fk_device_geofence_deviceid" FOREIGN KEY (deviceid) REFERENCES devices(id) ON DELETE CASCADE
    TABLE "device_maintenance" CONSTRAINT "fk_device_maintenance_deviceid" FOREIGN KEY (deviceid) REFERENCES devices(id) ON DELETE CASCADE
    TABLE "device_notification" CONSTRAINT "fk_device_notification_deviceid" FOREIGN KEY (deviceid) REFERENCES devices(id) ON DELETE CASCADE
    TABLE "user_device" CONSTRAINT "fk_user_device_deviceid" FOREIGN KEY (deviceid) REFERENCES devices(id) ON DELETE CASCADE

For some reason two very similar UPDATE queries vary drastically in execution time. Statistics from pg_stat_statements:

                    short_query                     | total_time | calls |   mean   | percentage_cpu
----------------------------------------------------+------------+-------+----------+----------------
 UPDATE devices SET positionId = $1 WHERE id = $2   |  325376.29 | 21053 |    15.46 |          89.70
 UPDATE devices SET lastUpdate = $1 WHERE id = $2   |     471.75 |  6491 |     0.07 |           0.13

What could be the issue and how to fix it? I have checked execution plans and they are exactly the same for both queries.

Database version info:

PostgreSQL 10.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit


texts: Problem passing rows results to columns names

texts:
I have a table with following data:

ID  IDALUMNO    ALUMNO                          IDTIPOEVALUACION    TIPOEVALUACION  NOTA
1   682         ALVITEZ MIRANDA, KAREN LIZET    1                   PR1             13
2   682         ALVITEZ MIRANDA, KAREN LIZET    1                   PR2             14
3   682         ALVITEZ MIRANDA, KAREN LIZET    1                   PR3             NULL
4   682         ALVITEZ MIRANDA, KAREN LIZET    4                   TR1             11
5   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA1             14
6   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA2             18
7   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA3             17
8   682         ALVITEZ MIRANDA, KAREN LIZET    6                   PA4             16

And using PIVOT

SELECT [ALUMNO], [PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4]
FROM
( SELECT * FROM ALUMNOSEVALUACIONES ) AS [SOURCE]
PIVOT
(
    AVG(NOTA)
    FOR [TIPOEVALUACION] IN ([PR1], [PR2], [PR3], [TR1], [PA1], [PA2], [PA3], [PA4])
) AS PIVOTTABLE

I get the following result:

ALUMNO                          PR1     PR2     PR3     TR1     PA1     PA2     PA3     PA4
ALVITEZ MIRANDA, KAREN LIZET    13      NULL    NULL    NULL    NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    14      NULL    NULL    NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    11      NULL    NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    14      NULL    NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    18      NULL    NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    NULL    17      NULL
ALVITEZ MIRANDA, KAREN LIZET    NULL    NULL    NULL    NULL    NULL    NULL    NULL    16

But I need this result:

ALUMNO                          PR1     PR2     PR3     TR1     PA1     PA2     PA3     PA4
ALVITEZ MIRANDA, KAREN LIZET    13      14      NULL    11      14      18     17       16

My question is whether you can get the results as they wish using PIVOT.

Please look at my demo


texts: How can I restart Database Mirroring after intentionally breaking it?

texts:
I have setup database mirroring using SQL server on two servers. I have Enterprise Edition on the primary server and Standard Edition on the secondary server.

In order to verify the data on the secondary server I manually broke the mirroring, so I could inspect data on the mirror. Now after the verification process, I want to enable mirroring again.

The database is larger than 200 GB so it is not feasible to take a backup of the primary, send it over the network, and restore it on the secondary server because of the long distance between the two nodes, and the network configuration.

What options are available so I can restart mirroring?


texts: Allowing non-privileged users to cause privileged code to run

texts:
Context:

Our SQL server is used for teaching, and we need to be able to automatically create databases for the students on demand and in real-time.  We also need the students to be able to delete databases that they created.  The students cannot be given administrator privilege, or any other level of access that would potentially allow them to break the server.

We had a hacky but working solution on our old server, based on an extended stored procedure: there was a "management database" whose job was to track and manipulate the student's databases, and an SQL Server Agent job that was constantly running.  The extended stored procedure allowed the job to go to sleep, and to be woken up, at which point it created or deleted databases as indicated by entries in a table, and subject to various business rules, then went back to sleep again.  (I can expand on this if necessary, but hopefully you get the idea.)

We have decommissioned the old server because it was running SQL Server 2008 R2, and will be building a new one for the upcoming trimester running SQL Server 2019.  From the documentation I've found, extended stored procedures are deprecated but still supported, so in principle I could just reuse the existing solution.  However, I would like to do something saner if possible.

Question:

What I think I need to ask is how I can best allow a non-privileged user to cause a stored procedure, which runs in a sufficiently privileged context to dynamically create and delete databases, to run, or failing that, to wake up from sleep.

However, I would be happy to receive suggestions of other possible solutions to my underlying problem of allowing a non-privileged user to create and delete databases on demand, subject to business rules.  (For example, we require the databases to be prefixed with the student's username.  We also need to configure the database security when it is created so that the student has access to it.  And of course the student should only be able to delete their own databases, not other people's.)

If I remember correctly (and based on some quick research) triggers and the like won't work because they run in the context of the user that triggered them, e.g., see this post.  But perhaps a signed stored procedure would work?  I'm looking for the simplest, sanest options here, thinking forwards to whoever winds up looking after this server after me and looking for solutions that will be understandable and maintainable.

Solutions whose server-end can be implemented entirely in SQL are preferred, but this is not an absolute requirement.

Additional notes, in response to comments:

Students are authenticated via Windows logon, and access is granted based on membership in a Windows group.
On the old server, we did not give the students dbo access to their own databases, because we were not sure of the security implications.  We did give them the db_ddladmin, db_datareader, and db_datawriter roles.


texts:
This will be in MySQL. Table consists of following fields:

Date, ID, blah, blahblah, blahblahblah

I believe the primary key needs to be Date & ID.

The query I want to run is for every Date, count all the IDs but only if this is the last date the ID shows up.

So if the ID shows up in the database on 9/9/16 but NOT on 9/10/16 then we would say that it disappeared and therefore add it to the total count for date 9/9/16.

If that same ID later reappears on 9/11/16, when we run the query again that ID will no longer be added to the count as of 9/9/16.  If on 9/12/16 that ID disappears again then it would be added to the count as of 9/11/16.

Here's some sample data.  I did not put any data in for the "blahs" since it is not relevant for the query (I believe):

Date,   ID, blah,   blahblah,   blahblahblah
9/8/2016,   1
9/8/2016,   2
9/8/2016,   3
9/8/2016,   4
9/8/2016,   5
9/8/2016,   6
9/8/2016,   7
9/8/2016,   8
9/9/2016,   1
9/9/2016,   2
9/9/2016,   3
9/9/2016,   4
9/9/2016,   5
9/9/2016,   9
9/9/2016,   10
9/9/2016,   11
9/10/2016,  2
9/10/2016,  4
9/10/2016,  6
9/10/2016,  11
9/10/2016,  12
9/10/2016,  13

The expected output run on 9/9/16 would be:

Date, Total IDs Last Day
9/8/16,3
9/9/16,8

The expected output run on 9/10/16 would be:

Date, Total IDs Last Day
9/8/16,2
9/9/16,4
9/10/16,6

Note that when the query is run on 9/10/16, the total for 9/8/16 is different from when it was run on 9/9/16 because an ID (number 6) disappeared but then re-appeared.


texts: Could you point me to a good book about setting up a PostgreSQL cluster? The goal is to set up an active-passive cluster spread across two different data centers to achieve high availability.


texts: How do I best measure the query performance?

texts:
I have 2 stored procedures, where the second stored procedure is an improvement of the first one.
I'm trying to measure by exactly how much that is an improvement.

Measuring clock time doesn't seem to be an option as I get different execution times. Even worse, sometimes (rarely, but it happens) the execution time of the second stored procedure is bigger than the execution time of the first procedure (I guess due to the server workload at that moment).

Include client statistics also provides different results.

DBCC DROPCLEANBUFFERS, DBCC FREEPROCCACHE are good, but the same story...

SET STATISTICS IO ON could be an option, but how could I get an overall score as I have many tables involved in my stored procedures?

Include actual execution plan could be an option also. I get an estimated subtreecost of 0.3253 for the first stored procedure, and 0.3079 for the second one. Can I say the second stored procedure is 6% faster (=0.3253/0.3079) ?

Using "Reads" field from SQL Server Profiler?

So how can I say that the second stored procedure is x% faster than the first procedure, no matter the execution conditions (the workload of the server, the server where these stored procedures are executed, etc)?
If it is not possible, how can I prove the second stored procedure has a better execution time than the first stored procedure?


texts: Trying to setup Postgresql 9.5 with different data directory

texts:
Ultimately I want Postgresql 9.5 running in a docker container using a mounted volume for the data so it can be persistent. As part of that I am experimenting with setting up the Postgresql server to use an alternative data directory.

I read that it isn't possible to use a FAT32 or VFAT directory, so I have, for the time being, abandoned using the Samba mount from our backed up NAS and am trying something simpler.

I am working on Ubuntu 16.04.3 LTS. The commands I ran (as root (sudo su -)) were:

service postgresql stop
mkdir -p /blah/data
chown postgres /blah/data
sudo -u postgres /usr/lib/postgresql/9.5/bin/initdb -D /blah/data
sed -i 's#.*data_directory = .*#data_directory = "/blah/data"#' /etc/postgresql/9.5/main/postgresql.conf
service postgresql start

The initdb command completed perfectly, unlike when I ran it on my samba mount where I got fsync errors.

If I now type sudo -u postgres psql I get told:

Error: Invalid line 41 in /etc/postgresql/9.5/main/postgresql.conf: »data_directory = "/blah/data"«

I have followed everything I can find on the internet about this, especially the official documentation. It should just work. It doesn't. Apparently the documentation needs some improvement.

Can anyone help me get this to work, please?

If anyone can help me to then extend it to work in a docker container using a volume mounted from a directory on the host which is in turn a samba mount, I would be very grateful.


texts: MySQL Backup with innobackupex error: Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374

texts:
Getting following error message:

Unrecognized character \x01; marked by <-- HERE after <-- HERE near column 1 at - line 1374.
170414 19:01:33 Connecting to MySQL server host: localhost, user: XXXXXXXX, password: set, port: not set, socket: not set
Error: Unsupported server version: '5.7.17-0ubuntu0.16.04.2'. Please report a bug at https://bugs.launchpad.net/percona-xtrabackup

after running following command:

innobackupex --user=XXXXXXXX  --password=YYYYYYYYY --no-timestamp /data/backups/new_backup