texts: How to use HTTP Connection Manager in Script Component? (Not Script Task)

texts:
I'm trying to perform the same scenario as in the following link; Create a SSIS Script Component as a Data Source that uses a pre-existing HTTP Connection Manager to retreive a page with GET and emit rows into the Data Flow pipeline.

http://www.sqlis.com/sqlis/post/Downloading-a-file-over-HTTP-the-SSIS-way.aspx

My target platform is SQL Server 2008 and therefore C#. The MSDN documentation gives examples of File and SQL Connection Managers but not HTTP ones.

https://msdn.microsoft.com/en-us/library/ms136060%28v=sql.100%29.aspx

The specific problem is that I can NOT figure out why there's no HttpClientConnection constructor in my current context. The MSDN documentation of that class does not seem to apply in the case of Script Components and translating this to something useful is apparently beyond me.

https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.httpclientconnection.httpclientconnection%28v=sql.100%29.aspx

My non-working code looks like this -

using System;
using System.Data;
using System.Text;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void AcquireConnections()
    {
        IDTSConnectionManager100 connMgr = this.Connections.MyWebServer;
    }
    public override void PreExecute()
    {
       base.PreExecute();

       HttpClientConnection clientConn = new HttpClientConnection(connMgr));

       Byte[] buffer = clientConn.DownloadData();
       String Document = Encoding.ASCII.GetString(buffer);
    }

What am I missing?


texts: I don't know how to read many to many relationships

texts:
I'm reading this tutorial, and I have question about it.

Look at this picture:

Looking at Physical Many-to-Many Implementation I read the relationship this way: 'A user can be in zero or more projects'.

But I don't know how to read it in the other way from ProjectEmployee to Employee. How do you read it?

And another question, Why the relationship is one and only one on Employee side?


texts: Using a trigger function to constrain values according to those already in the table (postgresql)

texts:
I'm just learning SQL and I can't seem to find out how to do this.

The table is (id, subject, year, grade). I'm supposed to reject any inserts or updates for a student if he has scored an F before for the same subject i.e. students are not allowed to retake a subject if they have failed in it before.

I'm supposed to use a trigger function but I'm confused about how to get started.
A nudge to the right direction would be appreciated.


texts: Cassandra querying without full primary key

texts:
As far as I understand there are no secondary indices with multiple columns, only single columns.

So how do I solve the index problem I'm having? This is my table:

CREATE TABLE IF NOT EXISTS logstv.messages (
    timestamp timestamp,
    channelid bigint,
    userid bigint,
    message text,
    PRIMARY KEY ((channelid, timestamp), userid)
) WITH CLUSTERING ORDER BY (timestamp DESC);

But this is the error currently:
Missing CLUSTERING ORDER for column userid

I want these things:

SELECT WHERE with channelid, userid, timestamp with an order by timestamp and limit
SELECT WHERE with channelid, timestamp with an order by timestamp and limit

Is this possible?

The only option I currently see is setting timestamp as the primary key and then sorting through the rest in my software, which seems very expensive. Or having 2 tables for my data which i want to very much avoid because the data will grow very fast and get big.

Example queries:

SELECT message, timestamp
        FROM logstv.messages
        WHERE userid = ?
        AND channelid = ?
        AND timestamp >= ?
        AND timestamp <= ?
        ORDER BY timestamp DESC
        LIMIT 100;

SELECT message, timestamp
        FROM logstv.messages
        WHERE channelid = ?
        AND timestamp >= ?
        AND timestamp <= ?
        ORDER BY timestamp DESC
        LIMIT 100;


texts: Select max value from many to many tables

texts:
i have next tables in may data base:

I want to select the book that has the max number of pages for each author. I did this :

select auteur.nomauteur, auteur.prenomauteur ,livre.Title, livre.nombrepages from auteur, livre, livreauteur
  where (livre.idlivre = livreauteur.idlivre and livreauteur.idauteur = auteur.idauteur and
            livre.NOMBREPAGES in (select max(livre.nombrepages) from auteur, livre, livreauteur
  where (livre.idlivre = livreauteur.idlivre and livreauteur.idauteur = auteur.idauteur)
            group by auteur.nomauteur, auteur.prenomauteur)) group by auteur.nomauteur, auteur.prenomauteur, livre.title, livre.NOMBREPAGES;

Correct answer:

But the expression is too long, and i need to do this select with JOIN. I tried to do like this:

select auteur.NOMAUTEUR, auteur.PRENOMAUTEUR, livre.TITLE, max(livre.nombrepages) from auteur inner join livreauteur on auteur.idauteur = livreauteur.idauteur inner join livre on livre.idLivre = livreauteur.IDLIVRE group by  auteur.NOMAUTEUR, auteur.PRENOMAUTEUR, livre.TITLE;

Wrong answer, 2 extra rows for the same author:

also I tried with:

select
        auteur.idauteur,
          (select top 1
            livre.idlivre
            from livreauteur
            inner join livre
            on livre.idlivre = livreauteur.idlivre
            where livreauteur.idauteur = auteur.idauteur
            order by livre.nombrepages desc )
            from auteur;

But it is wrong. How can I do the first select with JOIN ?


texts:
I am new to SQL Azure and need to plan on moving some of our SQL Server Databases.

We have a SQL Server Azure Pool which can take up to 50 databases.

I need to restrict certain users to specific databases inside the pool.

E.g. User1 can only Access Database Northwind -thus when he connects with SSMS to the Azure Pool, this is the only database he can see (he can't see the other 49)

Is setting up this possible - or is it a case that if you have access to the Instance (the whole pool) - then you will have access to all the DBs inside that pool?

This would mean that we would need to move each database to its own instance based on security?
It will also have the implication that whatever SQL username/password is used in the applications web.config, that user in the config will have access to all the databases in the SQL instance (servername)

I am looking at the SQL Azure training on Pluralsight and seeing that there are database level firewalls based on IP - in this case we have multiple devs - each with same IP - but specific devs must only have access to specific databases. Thus one IP rule per database on the instance will not work since username is not evaluated.


texts: ORA-01034: ORACLE not available in oracle 11g

texts:
I installed oracle 11g on ubuntu 14.04 and i started lister and the message is as below.

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 10-OCT-2014 14:20:02

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

Starting /u01/app/oracle/product/11.2.0/xe/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.2.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/TSDC/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TSDC)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                10-OCT-2014 14:20:02
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/TSDC/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TSDC)(PORT=1521)))
Services Summary...
Service "XE" has 1 instance(s).
  Instance "XE", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Then I started the server by oracle-xe start and it was started successfully.

When I was trying to connect to sql*plus following error was getting..

ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory
Process ID: 0
Session ID: 0 Serial number: 0

Please suggest me what to do with this?


texts: installing plpython3u for postgresql 9.6 under windows 7

texts:
I've used EnterpriseDB stackbuilder to install postgres9.6 and the language-pack. Postgres9.6 is installed under C:\Program Files folder while the edb lanaguge pack is installed under C:\edb.

However, it seems that I cannot create the plpython3u extension because it cannot find some necessary modules. How do I get postgres to find my python language pack installation?

When I type CREATE EXTENSION plpython3u is says:

ERROR:  could not load library "C:/Program Files/PostgreSQL/9.6/lib/plpython3.dll": The specified module could not be found.

NOTE: I already use Python 3.6 for development, so I'd rather not change the entire installation, I just want to point postgres to the proper runtime so plpython3.dll can load up.

Do I have to change some environment variables?


texts:
I'm working on a testing server to upgrade a large database,  700GB with around 20000 tables.

The upgrade was fast enough using the --link option, but the analyze only script takes 8 hours, which is more downtime than I can justify. (9.3 to 9.4)

Is there anyway to speed up the analysis, or just run it in the background on the live production database after the upgrade?

What performance impact might this have?


texts: Select all rows with same ID when 1 column meets criteria

texts:
I need help with a SQL query.
My problem is better explained with some examples.

Say I have my data like this pulling from two tables, ORDR and RDRO. The first table (ORDR) contains all the data about the order except for the items. The second table (RDRO) contains all the item information for an order. Both tables can be joined using [docNum].

T0.docNum | T1.itemCode
123         EDS100
123         DDS200
123         DDS500

124         LLS220
124         LDS254

125         FGD450
125         LDS552
125         DDS200
125         EDD200

I want to return all rows when 1 item number meets the criteria.

Say in this case I want all the rows of the order when one row is equal to DDS200 would give this result:

T0.docNum | T1.itemCode
123         EDS100
123         DDS200    -- match
123         DDS500

125         FGD450
125         LDS552
125         DDS200    -- match
125         EDD200

I'm sure this is trivial, I have tried the following:

SELECT T0.docNum, T1.itemCode
FROM ordr T0  INNER JOIN rdro T1 ON T0.docNum = T1.docNum
WHERE  T1.itemCode like 'DDS200';

this only returns the rows that meet the criteria, not all the rows of the order as shown above.

Also, please note I'm on SQL server.

Thanks for your help


texts: What are the side effects of changing MySQL `character_set_server`?

texts:
I am having some strange issues with character sets on my system. After digging through all kinds of settings, I found a discrepancy in my character set settings.

When I run show variables like 'char%'; I get:

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+

Are there any side effects to changing the the character_set_server variable?

Could it possibly affect what character set a connection will use when talking with the database?
Could it possibly alter the existing data in any way?

From my understanding, it looks like all it does is set the character set for you when creating a new database if you don't specify a charset. Is that really all it does?

Source: https://www.blueboxcloud.com/insight/blog-article/getting-out-of-mysql-character-set-hell


texts: Do indexes get transferred with pg_restore

texts:
I see other questions that have been answered in the negative (i.e. indexes do not get transferred over with the standard pg_restore). However, it looks like it did in my recent dump/restore and I am not sure if it really did transfer over or not.

I migrated my database over from PostgreSQL 9.3 to 9.4.5 and used dump/restore in order to do so.

Below are the commands used:

sudo -u postgres pg_dump -h localhost -p 5432 -d nominatim -F d -f dump/postgres/backup -j 20
sudo -u postgres pg_restore --create --dbname=nominatim --exit-on-error -h localhost -p 5432 -F d -j 7 dump/postgres/backup

The dump and restore was successful (no errors).

I set autovacuum to off while doing the restore and so I ran analyze (no parameters) from within psql and while connected to the database that was restored.

The first thing I noticed is that under 9.3, the data directory took up roughly 860GB, whereas under 9.4, it's taking up about 680GB.  Is 9.4 THAT much more efficient?

Second thing I noticed is that I see the indices in the database:

nominatim=# \d country_name
                   Table "public.country_name"
            Column             |         Type         | Modifiers
-------------------------------+---------------------------------
country_code                  | character varying(2) |
name                          | hstore               |
country_default_language_code | character varying(2) |
partition                     | integer              |

Indexes:
        "idx_country_name_country_code" btree (country_code)

nominatim=# \d idx_country_name_country_code
    Index "public.idx_country_name_country_code"
    Column    |         Type         |  Definition
--------------+----------------------+--------------
 country_code | character varying(2) | country_code

btree, for table "public.country_name"

nominatim=# select * from pg_indexes where tablename = 'country_name';
 schemaname |  tablename   |           indexname           | tablespace |                                       indexdef
------------+--------------+-------------------------------+------------+---------------------------------------------------------------------------------------
 public     | country_name | idx_country_name_country_code |            | CREATE INDEX idx_country_name_country_code ON country_name USING btree (country_code)
(1 row)

Do I still need to reindex or did my indexes somehow or other make it over or is what I am seeing simply the index definition?  Any further tips on better managing the dump/restore, checking my indexes, etc. would definitely be appreciated.


texts: Tempdb growing, however it has a lot of free space

texts:
On a sql server dedicated to a Dynamics CRM database, I've been seeing some odd behavior.

While monitoring growth using the following query  as basis, I've noticed tempdb has grown quite frequently in the last few hours:

DECLARE @path NVARCHAR(1000)
SELECT @path = Substring(PATH, 1, Len(PATH) - Charindex('\', Reverse(PATH))) +
                      '\log.trc'
FROM   sys.traces
WHERE  id = 1
SELECT databasename,
       e.name   AS eventname,
       cat.name AS [CategoryName],
       starttime,
       e.category_id,
       loginname,
       loginsid,
       spid,
       hostname,
       applicationname,
       servername,
       textdata,
       objectname,
       eventclass,
       eventsubclass
FROM   ::fn_trace_gettable(@path, 0)
       INNER JOIN sys.trace_events e
         ON eventclass = trace_event_id
       INNER JOIN sys.trace_categories AS cat
         ON e.category_id = cat.category_id
WHERE  e.name IN( 'Data File Auto Grow', 'Log File Auto Grow' )
ORDER  BY starttime DESC

Gives several lines of growth of the data files:

tempdb  Data File Auto Grow Database    2015-03-02 09:50:33.187 2

However, when I look at the space occupied by tempdb I'm not seeing where the space crunch is:

USE tempdb
GO
SELECT DB_NAME() AS DbName,
name AS FileName,
size/128.0 AS CurrentSizeMB,
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files;

Gives the following output:

DbName  FileName    CurrentSizeMB   FreeSpaceMB
tempdb  tempdev     7500.000000     7492.625000
tempdb  templog     156.132812      93.820312
tempdb  tempdev2    7250.000000     7245.625000
tempdb  tempdev3    7250.000000     7245.312500
tempdb  tempdev4    7366.500000     7360.875000

The person maintaining the CRM has asked for us to shrink the tempdb.
My colleague has, in the past, obliged. However I'm unwilling to do so without an explanation. Especially considering that the shrinking has become an almost weekly occurence.

Can anyone give me an indication as to why the tempdb has so many growth events, and how to handle this properly?
I'm currently considering asking for more storage, and increasing the tempdb drive by 50%.
However this feels like treating the symptom, not the cause.


texts: How to do the complex nested select SQL?

texts:
I have two tables, table1 is as following, id is primary key, table2_id  is foreign key

id date                 aid table2_id message
1  2014-03-19 16:21:02  121   1       login from xxxx
2  2014-03-20 14:00:32  123   2       decrease budget
3  2014-03-20 18:00:32  121   2       increase budget
4  2014-03-21 16:21:02  121   3       login from xxxx
5  2014-03-21 16:21:02  121   3       login from xxxx
6  2014-03-22 10:21:02  121   2       increase budget

table2 is a type table

id type
1  login
2  change_budget
3  search

I need to analyse the relationship between activity change_budget and other activity. I am not sure how could I deal with the big data solution, but, anyway, following is an idea of mine:

I try to write SQL for each time one aid change_budget, count the all other actions of this aid within one day. if one aid have a change_budget at 2014-03-14 16:21:02, I need all the action of this aid from 2014-03-13 16:21:02 to 2014-03-14 16:21:02. Then, group by the action and count.and selected table will be look as following:

aid login search change_budget
121 1     0      1
123 0     0      -1
121 0     2      1

-1 means change_budget is decrease,1 means increase.
Other number is how many time this action of this aid has happen within oneday before this aid change_budget happens.

Anyone can teach me how to write this?


texts: Multiple disks vs single disk on virtualized environment

texts:
We're planning to install a SQL Server on this physical architecture:

A hyper-v cluster based on windows server 2019
A cluster shared volume where all VMs live
A full-flash SAN that contains the CSV

I've always read that is a common best practice to have multiple disks for data files, log files, temp db files ect...
But that best practice is referred to a bare-bone installation.
So, what I'm asking is: what are the actual benefits of doing so on the above described architecture (if any)?


texts: Postgresql - Sybase concepts correspondence

texts:
The following diagram depicts the main PostgreSQL concepts pretty nicely:

I find myself having to work with Sybase ASE 15 and I was wondering what the similar concepts are in Sybase. E.g. from what I've understood so far Sybase lacks the concept of schemas. So some questions are:

does Sybase ASE 15 have the concept of schemas?
what is the boundary for referential integrity constraints? E.g. in PostgreSQL you can have foreign keys across schemas (but not across databases). What is the situation in Sybase ASE 15?


texts: Pros and cons of master status table vs private status table?

texts:
I am the only DBA at our company and am relatively new in the profession (having 2.5 years of experience now). The developers and I are currently having a discussion about how to properly design the database for a new piece of software being developed. They develop their applications in modules and believe that having a private status table for each module is best, where as I believe having one table to hold all internally generated states (and then possibly additional tables to give those states some meaning) would be best. The sample code below is written in postgresql, since that's what we use.

My approach:

CREATE TABLE status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);

CREATE TABLE member_status (
  status_id INT PRIMARY KEY REFERENCES status (status_id)
);

Their approach:

CREATE TABLE member_status (
  status_id SERIAL PRIMARY KEY,
  status_code VARCHAR(3) UNIQUE NOT NULL, -- short description
  status_description VARCHAR(50) UNIQUE NOT NULL -- long description
);
-- rinse and repeat for every object that may need a status

They only see "problems" with my approach (which have yet to be clearly articulated) and, while I don't see problems with their approach, I do believe it is not the best design.

Any input into which design is better and WHY would be appreciated.


texts: Do link servers cache remote database connections?

texts:
I have set up a link server from a database server (SQL2008) to my local SQLEXPRESS db, using the actual database as its Failover Partner.  I then stopped the SQLEXPRESS service and I could still query the remote database.

However, I then deleted this link server altogether and re-created it without the FailoverPartner in the connection string, but with the same default table in the @catalog option.  I did not expect this to work as there is no reference to the actual database, but I can still happily run different queries.

Therefore, I can only assume that the original connection settings to the original remote database are remembered somehow?!  Can anyone explain what is happening here?

Also, if the default table cannot be found in either the failover or primary database, would it then look in other link servers?


texts: Possible benefits of storing multiple values in one field of one row instead of as separate rows

texts:
During our last weekly meeting, a person that has no background experience in Database Administration brought up this question:

  "Would there be a scenario that justifies storing data in-line (string) instead of several lines?"

Let us assume a table called countryStates where we want to store the states of a country; I'll use USA for this example and will not list all the States for the sake of laziness.

There we would have two columns; one called Country and the other called States. As discussed here, and proposed by @srutzky's answer, the PK will be the code defined by ISO 3166-1 alpha-3.

Our table would look like this:

+---------+-----------------------+-------------------------------------------------------+
| Country | States                | StateName                                             |
+---------+-----------------------+-------------------------------------------------------+
| USA     | AL, CA, FL,OH, NY, WY | Alabama, California, Florida, Ohio, New York, Wyoming |
+---------+-----------------------+-------------------------------------------------------+

When asking this same question to a friend developer, he said that from the data traffic size point of view, this might be useful, but not if we need to manipulate this data. In this case there would have to be an intelligence on the application code which could transform this string in a list (let's say that the software that has access to this table needs to create a combo box).

We concluded that this model is not very useful, but I got suspicious that there might be a way to make this useful.

What I'd like to ask is if any of you already saw, heard or done something like this in a way that really works.


texts: Verbose Oracle RDBMS error log output examples?

texts:
I'm working on a project and my boss has asked that I find an actual example of the "verbose" output of an Oracle RDBMS query error. We want to see what they actually look like. I know that there are, of course, a number of different queries that can be ran against a database, so my request more pertains to error log messages that can be used to reveal information about a database's data structures.

Any information on where to find examples of such errors would be greatly appreciated!


texts: Minimum Permission to Restart SQL Services

texts: Occasionally, our developers need to restart SQL services.  We do not want said developers to be local admins on each server if not necessary.  What is the minimum permission required so that the developers can restart the SQL Services (database engine and agent) remotely using SSMS?  We use SQL Server 2008 R2 on Windows Server 2008 R2.


texts: How to recompile all stored procedures and table valued functions in SQL Server database?

texts:
In a SQL Server database sp_recompile can be run on a stored procedure to update the execution plan.  I would like to run this on all stored procedures in a database.  Also, I would like to run its equivalent on all table-valued functions but I do not know which sys procedure to run.

Is it possible to do this without manually typing out a sp_recompile line for all of the stored procedure names in SQL Server Management Studio?  Likewise for the table valued functions?

I believe I need to do this because the VM SQL Server has had its memory significantly increased but I am only seeing a marginal increase in execution time. The execute plans show 80+ percent of the run time is on a clustered index seek so I don't think there is much more I can do to optimise the stored procedures.


texts: Replication SQL Server to a Cluster with error

texts:
I have a source DB MSSQL Server and deploy transactional-replication
 to a cluster with 2 Node(A&B).
The subscriber was added using SQL IP of Cluster and using "sa" account.
It run fine when server A active and server B passive.
When i shutdow the server A the replication stop and show error:

The process could not connect to Subscriber 'AHTDWHSQL'. (Source:
 MSSQL_REPL, Error number: MSSQL_REPL0)
Get help: http://help/MSSQL_REPL0
Named Pipes Provider: Could not open a connection to SQL Server [1326].
(Source: MSSQLServer, Error number: 1326)
Get help: http://help/1326
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. (Source: MSSQLServer, Error number: 1326)
Get help: http://help/1326
Login timeout expired (Source: MSSQLServer, Error number: HYT00)
Get help: http://help/HYT00


texts: Creating indexes vs Rebuilding indexes (larger db/log file)

texts:
I am a bit lost on what it the right way to do this.
I am migrating data from one db to another (corrupt errors) so i did the following:

Create a new blank db
Generate a object script from my source db (not including indexes because I want my data copy to be fast), run on the new db
Generate a script for all my indexes from source
Copy all my data from source to dest using tsql, all is well
Run my create index on the dest, all is well, my new db size is approx 1.76GB with approx 760MB being the log file
Shrink the database (doing this for testing only at this point)

Now if I run the following command (to rebuild all the indexes)

EXECUTE sp_msForEachTable 'SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD;

The new sizes are 3600mb for the db and 1608MB for log

My question is doesn't creating indexes on a fresh db same thing as rebuilding or is there more to it than that. Also should I be using rebuild all indexes?

I guess I am trying to find out what I should be doing in the future, drop/recreate indexes every once in a while, or just run the rebuild command.

My database size is getting too large for the space I have left at my hosting.

I am trying to keep my db sizes down as much as I can, because I have 10 dbs and limited space on my VPS and recently I was getting errors that my db is corrupted (I do not have the backups) so this is why I am creating new dbs, moving data and starting "fresh" so I want to make sure everything is as optimized as possible going forward.


texts: MySQL Query doesn't work but works some time, whats wrong with it?

texts:
Below Query doesn't work most of the time and giving error Getting error #1048 - Column 'StateStarttime' cannot be null but work sometimes, what am I doing wrong here

I think DATE_FORMAT( NOW()-60 , '%H:%i' ) causing the issue if it is what will be the right syntax?

INSERT INTO time (
EmpID,
Date,
Username,
Computername,
State,
MinutesatState,
StateStarttime,
StateEndtime
) VALUES (
'303016',
CURDATE(),
'yatendra',
'NIPL885',
'Active',
'1',
DATE_FORMAT( NOW()-60 , '%H:%i' ),
DATE_FORMAT( NOW() , '%H:%i' )
)


texts:
I am using below command to get SQL Server last restart time :

Select min(Login_time) from master.sys.sysprocesses

select getdate()

Can anyone help me with a query if possible : If the SQL Server last start time is within last 24 Hours from current select getdate() it should be written to another column that SQL Server got restarted last XX hours back along with SQL Server Last restart time.


texts: CHECKDB with default settings and parallelism involved

texts:
This would be my follow-up question because I am trying to understand the CHECKDB behaviors
While running CHECKDB without any MAXDOP specified on below 2 servers:-

Server with 10 Vcores, MAXDOP at instance level 0, DB size 40 GB --> almost hitting 90% CPU spike

Server with 10 Vcores, MAXDOP at instance level 0, same like above DB in terms of schema, but data size over 150 GB --> almost hitting 30% CPU spikes

Can someone help me understand with server with same settings of instance level MAXDOP why one server will get all cores used and while other very few, probably why lower CPU usage.
Just curious how is SQL server making the conclusion of how many schedulers to allocate based on server level MAXDOP setting when running DBCC CHECKDB without specifying MAXDOP.
Thank you


texts: How to do a fast simple query on a large table of postcodes

texts:
I have a table with 2.5 Millions of Postcodes.

I have to populate a textbox while the user is searching for the Postcode so the query has to be very fast.

The table is composed by:

 ID (Primary key), Postcode (Unique), Population INT(11) NOT NULL

The query is:

SELECT * FROM `postcodes` WHERE substr(Postcode,1,LENGTH_OF_TERM) ='TERM'  order by population desc limit 0,10

TERM is what the user has typed and LENGTH_OF_TERM is the lenght of the TERM .

Despite i have created also an index on  Postcode the query is very slow (3-4 seconds).

How i can improve the speed of the query?


texts: Translating the Relational Algebra output

texts:
I have the following relation Jobs(name, company, salary).

P1(salary) = πsalary(σcompany='pwc'(Jobs))
P2(salary) = πsalary(ρT1(s)(P1) thetaJoin(s>salary) P1)
P3(salary) = P1 − P2

I know that P1 just selects the salary of all people works in pwc.

I am having a hard time figuring out what P2 indicates.

I know that ρT1(s)(P1) renames P1 into T1 and the salary attribute to s. But then I am confused with the thetaJoin(s>salary) part because I thought s and salary would be the same.

P3 would just be the difference between P1 and P2

The final relation

Final(name) = πname(Jobs thetaJoin(salary>s) ρT2(s)(P3))

will depend on what P2 means.

Please help me doing this translation.


texts:
USE [RF_WORLD]
GO
/****** Object:  Trigger [dbo].[lastdeathtrigg]    Script Date: 08/20/2012 10:52:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      Mauro Tamm
-- =============================================
ALTER TRIGGER [dbo].[lastdeathtrigg]
   ON  [dbo].[tbl_pvporderview]
   AFTER UPDATE
AS
IF (UPDATE(Death))
BEGIN
    SET NOCOUNT ON;
DECLARE @userserial int;
SELECT @userserial ='serial' WHERE UPDATE(Death)
Insert INTO [RF_WORLD].[dbo].[lastdeath]
(serial)
VALUES (@userserial)
END

The where part is suspicious, woudl it work like that? essentially i need it to only select the row serial value if it actually updated.


texts: User in multiple windows groups mapped to sql logins

texts:
I have two C# apps with Windows Auth to Microsoft SQL Server 2008 R2.

For every app:

SQL login based on Windows Group is created
SQL server login is mapped to SQL database user
user is member of a role
role has granted permissions to tables, views, procedures, etc.

Question is:

If I add a single windows user as a member to both windows groups (with are "mapped" to different SQL logins, which are mapped to different SQL users, which belongs to different roles and so on..) - could user run both apps without any problems or only one of them will work properly?


texts: Postgres equivalent of SQL Server profiler

texts:
In SQL Server when we have applications for which we do not have source code we could use the SQL Server profiler to analyze which queries or procedures are called when we do something in the application. Now I have similar situation with a .NET third party library and a Postgres database when it would be very useful to track queries called by that library. I found something similar in pgAdmin, but so far it appears to be useful to track only SQL queries that are executed in pgAdmin, not queries from external applications.

So to sum up: Is there a tool that allows me to track queries executed on a Postgres database from external applications?

EDIT: I tried to select from pg_stat_activity, but when I expect to see which stored procedures or statement is executed it prints only "COMMIT" string in the "query" column.


texts: MariaDB master-slave replication failover and failback

texts:
I'm trying to configure a DR instance of MariaDB (10.1.13) using master-slave replication.

I was able to establish the master-slave relationship with GTID by locking the tables on master, backing up the database, restoring the database on the slave and running this on the slave:

CHANGE MASTER TO MASTER_HOST='master.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='masterlogfile', MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;

I was able to confirm that the replication is working is expected. However, I'm struggling with figuring out the process of converting the current slave to a master and master to the slave (reversing the replication flow).

When I run the following on the ex-master I get errors in the SHOW SLAVE STATUS \G; (Slave_SQL_Running is No and the error is Error 'Operation CREATE USER failed for 'replication_user'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replication_user'@'%' IDENTIFIED BY 'somepassword'', I'm not sure why it's trying to create this user after running the query below):

CHANGE MASTER TO MASTER_HOST='slave.domain.com', MASTER_USER='replication_user', MASTER_PASSWORD='replication_password', MASTER_PORT=3306, MASTER_LOG_FILE='slavelogfile', MASTER_CONNECT_RETRY=10, MASTER_USE_GTID = slave_pos;

I have a feeling I'm doing something wrong and that my scenario is not supported. If it's not supported, what's the best way of implementing asynchronous kind of replication (analog of AlwaysOn in MS SQL) for MariaDB to support failing over to the slave and failing back?


texts:
Can somebody please tell me how to encrypt an existing tablespace in 11g? The wallet has been setup and the sqlnet.ora points to it.
SQL> select STATUS  FROM V$ENCRYPTION_WALLET;

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

I alter the tablespace offline ...
alter tablespace USERS offline normal;

But I can only find the 12n and above command for the next part:
ALTER TABLESPACE users ENCRYPTION OFFLINE ENCRYPT;

Many thanks in advance for the 11g command.


texts: Is SQL Server's FILETABLE suitable for storing large files (bigger than 10 GB)?

texts:
There is a project for lab.  The requirement to save the lab data (raw data and related infomation ) for research for years.

So the data must be saved for years, but the data is so big, every raw data is more than 10 GB.  If we store the raw data in SQL Server's FILETABLE and store related information in a standard SQL Server table, after months or years, the database size will be so big that we must move some history data out of the database.

Maybe we could use adding files (on a different hard disk) to the FILESTREAM filegroup for filetable (to store raw data ), but I feel that is not suitable for maintaining the SQL Server.  We must keep the raw data and related information's consistency.

We have considered using tape to save the historical raw data.  We want to use hard disk to save latest raw data and use tape to save the historical raw data.  When we move the history data to tape we record the move in a log table, so we can know where the history data is moved and pick up it as soon as possible.

Is there good advice for:

How to store the raw data and related information?
Is filetable suitable for this scenario?
Is there a better solution for this scenario?


texts: MySql - view out params of stored procedure when it selects from table

texts:
I have stored procedure:

DELIMITER //
CREATE PROCEDURE prcGetFemaleUsers()
begin
    select firstName, lastName
    from tblUser
    where gender = 'female';
end //
DELIMITER ;

How can I get types that this procedure returns?
I'd expect to get something like:

firstname varchar(50)
lastname varchar(50)

I know there is this:

SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_SCHEMA = 'yourdb'
  AND ROUTINE_TYPE = 'PROCEDURE'
  AND ROUTINE_NAME = "procedurename";

... and a better version of this:

SELECT param_list, returns
FROM mysql.proc
WHERE db='yourdb'
  AND type='PROCEDURE'
  AND name='procedurename';

... but none of them give me desired results.

Any help is appreciated!


texts: pt-query-digest --type binlog (percona-toolkit) gives empty output with MariaDB/Galera 10.1

texts:
My installed versions of MariaDB and Percona Toolkit on MacBook:

brew info percona-toolkit
percona-toolkit: stable 3.0.10 (bottled), HEAD
Percona Toolkit for MySQL
https://www.percona.com/software/percona-toolkit/
/usr/local/Cellar/percona-toolkit/3.0.10 (244 files, 8.4MB) *
  Poured from bottle on 2018-05-31 at 09:52:48
From: https://github.com/Homebrew/homebrew-core/blob/master/Formula/percona-toolkit.rb
==> Dependencies
Required: mysql ✔, openssl ✔
==> Options
--HEAD
    Install HEAD version

DB server versions:

show global variables like '%version%';
+-------------------------+---------------------------+
| Variable_name           | Value                     |
+-------------------------+---------------------------+
| innodb_version          | 5.6.36-82.1               |
| protocol_version        | 10                        |
| slave_type_conversions  |                           |
| version                 | 10.1.26-MariaDB           |
| version_comment         | Source distribution       |
| version_compile_machine | x86_64                    |
| version_compile_os      | Linux                     |
| version_malloc_library  | system                    |
| version_ssl_library     | OpenSSL 1.0.1f 6 Jan 2014 |
| wsrep_patch_version     | wsrep_25.19               |
+-------------------------+---------------------------+
10 rows in set (0.01 sec)

Funny is in order to install percona-toolkit I had to install Oracle MySQL and afterwords change back to MariaDB

brew install mariadb
brew unlink mariadb
brew install percona-toolkit
brew unlink mysql
brew link mariadb

We use Galera cluster with row based replication:

show global variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

I did a simple use case:

mysqlbinlog  mysql-bin.0013* > all.sql

pt-query-digest --type binlog all.sql
all.sql:   1% 37:37 remain
(...)
all.sql:  96% 01:23 remain
all.sql:  98% 00:30 remain

# 2417.5s user time, 51.4s system time, 89.16M rss, 4.24G vsz
# Current date: Fri Jun  1 07:42:57 2018
# Hostname: aukVivi0009
# Files: all.sql
# Overall: 0 total, 2.05k unique, 0 QPS, 0x concurrency __________________
# Time range: 2018-05-26 02:00:54 to 2018-05-31 08:05:28
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query size        10.68G       6 287.51k  492.88  833.10   1.92k  107.34

# Profile
# Rank        Query ID    Response time Calls       R/Call      V/M   Item
# =========== =========== =========== =========== =========== ===== ======

why is the output empty?
Is pt-query-digest not compatible with MariaDB? Changes in binlog format? Any workaround?


texts: Trim a value in an xml column in MS SQL Server

texts:
I have a database with an XML column. I am trying to update (trim) an xml value of the xml column. I am running on Windows, SQL Server 2008 Express.

I did not find any way to trim directly in Xquery so I tried by selecting the value. Here is the SQL

select RTRIM(LTRIM(col2.value('(/Root/Row/Rowid/text())[1]','varchar(20)')))
from   dbo.T1

But now I am not getting how to update this xml value.

Is there any way to Trim the xml value directly? If not how can I Trim the value?

I am not trying to update the single row in the table, I am trying to trim the xml value of all the rows in the table


texts: Get the max value from a slice of a JSON Array?

texts:
I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw.

The limits Start & End of the slice are contained in an other Json object named Features contained in a table named features

Here is the input:

CREATE TABLE raw (
    id     int PRIMARY KEY
      GENERATED BY DEFAULT AS IDENTITY

    data   json
);

INSERT INTO raw (data) VALUES
  ('[1,2,3,5,6,7,9,10]');

CREATE TABLE features (
    id         int,
    features   json
);

INSERT INTO features (id, features) VALUES
  (1, '{"Start" : 1, "End": 5}');

The output I would like is 7, i.e. the max value of the slice [2,3,5,6,7]

Here is what I came up with looking at other posts, but it does not work...

SELECT
    R."ID",
    F."Features"->>'Start' AS Start,
    F."Features"->>'End' AS End,
    sort_desc((array(select json_array_elements(R."Data")))[F."Features"->>'Start':F."Features"->>'End'])[1] as maxData
FROM
    raw AS R
INNER JOIN
    features AS F ON R."ID" = F."ID"

The approximate error message I get is concerning sort_desc :

  No function corresponding to this name or this type of arguments. You
  should convert the type of data


texts: Problem to apply replacement with regexp that include look ahead in Postgres

texts:
I have this kind of string 12s2-3a abc def and I am trying to apply the regexp ^(?=.*\d) to remove the first part and having as left abc def.

I have tried with:

select substring ('12s2-3 abc def' from '^(?=.*\d)');

and

select regexp_replace ('12s2-3 abc def', '^(?=.*\d)', '');

but none of those has worked. The only one that looks working is the match verification (~).

Any idea?


texts: Can I Simply Change A Transaction Log From Unrestricted to Restricted?

texts:
I've got a database with a data file size of 674 MB and a transaction log of 6.1 GB. The transaction log is about 10 times bigger than the data file.The transaction log settings can be seen in the image below.

The server is constantly running out of space and this transaction log takes up about 11% of the total disk space. I'd like to make this transaction log file smaller.

Can I simply just change the "Maximum file size" by switching to Restrict file growth [MB]: and set the size to say 80 MB?


texts: Date of last index rebuild for Mysql Database

texts: is there any way we could check the last time when a database in MySql was rebuilt?


texts: Manually moving SQL instance to another node causes immediate database mirror failover

texts:
I have a two-node SQL cluster (2008 R2).

Some of the databases within that SQL instance are mirrored to another server on a remote site, using the High safety with automatic failover.  The mirroring connection timeout value for those databases is set to 90 seconds.

When I move SQL from one node in the cluster to another node, using the Failover Cluster Manager application's option of "Move this service or application to another node" the databases that are mirrored are instantly failing over to the mirror.

This is undesirable behaviour.  My reason for setting the mirroring connection timeout value is that I only want to fail over to the database mirror if the cluster fails completely and there are no functioning nodes.

Is there any way to achieve this?  It feels as though it should be possible, otherwise the concept of mixing clustering and automatic failover database mirroring would be unworkable as every node failover within the cluster would trigger a mirror failover.

Thanks.


texts:
I am trying to install the ROracle library. This fails:

install.packages("/storage/Downloads/ROracle_1.1-11.tar.gz", repos = NULL, type = "source")

** package ‘ROracle’ successfully unpacked and MD5 sums checked
configure: error: OCI libraries not found

I have downloaded the Oracle Instantclient RPMs and installed them via alien. Then,

export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
R CMD INSTALL ROracle_1.1-11.tar.gz

Warning: invalid package ‘ROracle_1.1-11.tar.gz
Error: ERROR: no packages specified

How can I install the ROracle package on a Ubuntu 64 system?


texts: converting/adapting time postgres SQL to H2

texts:
I have a quite large SQL that runs towards postgres in production today, and due to some performance reasons, I am converting our integration tests to unit tests.
The integration tests ran towards a docker postgres image, but it is simply way to slow to setup, so I am experimenting with an H2 in-mem database. It is as expected really quick to setup and works in most cases... However I have this statement
(timezone('America/New_York', timestamp) - ('1 hour')::interval)::date

That simply does not work in H2.
One reason is that the function timezone does not exist, but that seems like an easy fix by just creating an alias to a java timezone function.
What is slightly trickier is this part ('1 hour')::interval as it results in this exception
Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "SELECT (('1 HOUR')::INTERVAL)[*]::DATE FROM t"; expected "YEAR, MONTH, DAY, HOUR, MINUTE, SECOND"; SQL statement:

And I am kind of at an loss as to if it is possible to adapt h2 to accept it. And I am saying adapt h2, because there might be other issues related to changing the SQL (that needs to run towards postgres in prod).
So is it possible to adapt h2 to accept this?
and if not, is there some other way to write the sql to have it working for both postgres and h2?
and as a sidenote: does this question belong here, or at stackoverflow?


texts: Mongodb restrict access on collections level

texts: I'm building a backend system using MongoDB. Now I want to restrict access on certain numbers of database collections to this system. For example I have users, admin_users, pageviews and I want this system to be able to read and write only admin_users and pageviews collections but no read or write access on users colection. Is there any ways to do so?


texts: Sql_text Data Missing When Querying Extended Event File Target Using TSQL

texts:
I set up an Extended Events session to capture SQL Server error events.
Below is the TSQL I used to create the XE session:
CREATE EVENT SESSION [ErrorCapture] ON SERVER
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([severity]>=(11)))
ADD TARGET package0.event_file(SET filename=N'D:\ErrorCapture.xel')
WITH (MAX_MEMORY=4096 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=ON)
GO

Using SSMS, I am able to view all of the available event data, including sql_text.  However, when using TSQL to query the event file, data for the sql_text action shows up as NULL.  Below is the query used.  Any thoughts on why this might be?
with ErrorCaptureEventData as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'D:\ErrorCapture*.xel', NULL, NULL, NULL)
)
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp
       ,EventData.value('(event/data[@name="error_number"]/value)[1]', 'int') AS [Error_Number]
       ,EventData.value('(event/data[@name="message"]/value)[1]', N'nvarchar(max)') AS [Error_Message]
       ,EventData.value('(event/data[@name="sql_text"]/value)[1]', N'nvarchar(max)') AS SQL_Text
       ,EventData.value('(event/data[@name="statement"]/value)[1]', N'nvarchar(max)') AS SQL_Statement
       ,EventData.value('(event/data[@name="severity"]/value)[1]', N'nvarchar(max)') AS Severity
       ,DB_NAME(EventData.value('(event/action[@name="database_id"]/value)[1]', N'nvarchar(max)')) AS [DB_Name]
       ,EventData.value('(event/action[@name="username"]/value)[1]', N'nvarchar(max)') AS [User_Name]
 from ErrorCaptureEventData
 order by timestamp desc


texts: Innobackupex apply-log should be done before emergency restore or after each backup?

texts:
Basically Percona Innobackupex consist with three steps:
1. Backup
2. Apply log
3. Copy back restore during emergency

My question:
When should I do apply log? Is it after each backup or before emergency restore?


texts: Cannot restore azure database to local due to missing master key

texts:
I create a bacpac from azure and restore to my local on-prem server. However, it throws an error like “Please create a master key in the database or open the master key in the session before performing this operation”. The reason is one of the column in the table is encrypted. I don’t know how to restore the database from azure with master key and certificate etc. Do you know what I could do?

Thanks in advance,


texts: How To Detect locking due to multi row updates

texts: Hi we are thinking of moving our updating records operations over from c# to sql, I am afraid of locking problems, how can I detect this problem if it occurs, and if it causes locking, is there any solution other then making small batches


texts: How to use aggregate functions on each array in a table, in Postgres?

texts:
Imagine I have a table like:
games (
    scores INTEGER[]
)

And it had values
{1,2}
{3,4}
{}

I'd like to do arbitrary aggregations on that array.
For example, how would I do SELECT SUM(scores) AS total FROM games? Or an average? Or a max?
I'd like to get out:
3
7
0

I tried SELECT SUM(UNNEST(scores)) AS total FROM games but that gave ERROR:  aggregate function calls cannot contain set-returning function calls.


texts: False positive error message in MS Access (concurrent users)

texts:
I have discovered a strange apparent bug in MS Access and would like to know if others know it already and hopefully how to get around it.

Trying to count records based on known column categories.

One particular set of records can't be saved or output because of apparently one or more records in that range. They appear to have some corrupt character which is generating a false error message:

"Another user is currently using this record" or words to that effect.

Is there some obscure character (combination) that can cause Access to lock a record? It is physically impossible that anyone else actually has access to the DB.

There could conceivably be all kinds of strange characters in there because the data come from a wide variety of sources.

I think I have narrowed it down to a personal name field but this is not a final verdict...


texts: How to get all the data before the current date and data for the next 3 days from current date.DATEDIFF(now(),d.tentative_date_of_return) <= now() + 3

texts:
SELECT DISTINCT
d.name AS "ID:Link/Demo Request:120",
d.requester_name AS "Requester Name:Data:190",
d.customer_name AS "Customer Name:Data:250",
d.date_of_request AS "Date Of Request:Date:120",
d.tentative_date_of_return AS "Tentative Date Of Return:Date:170",
d.material_sent_from_location AS "Material Sent From Location:Data:190",
d.delivery_note AS "Delivery Note:Data:100",
d.material_status AS "Material Status:Data:120",
d.product_supplied_condition AS "Product Supplied Condition:Data:150",
d.courier_service AS "Courier Service:Data:120",
d.shipped_date_from_rahi AS "Shipped Date From Rahi:Date:120",
d.tracking_no AS "Tracking No:Data:120",
DATEDIFF(now(),d.tentative_date_of_return) AS "No Of Days:Data:100"

FROM
`tabDemo Request` as d

WHERE
d.material_status not like "Recieved From Customer"
AND
DATEDIFF(now(),d.tentative_date_of_return) <= now() + 3

ORDER BY d.tentative_date_of_return ASC


texts: Two MySQL requests at the same time - Performance issue

texts:
I have a MySQL server with many innodb tables. I have a background script that does A LOT a delete/insert with one request : it deletes many millions of rows from table 2, then insert many millions of rows to table 2 using data from table 1 :

INSERT INTO table 2 (date)
SELECT date from table 1 GROUP BY date

(The request is actually more complex but it is to shown what kind of request I am doing).

At the same time, I am going to run a second background script, that does about a million INSERT or UPDATE requests, but separately (I mean, I execute the first update query, then I execute an insert query, etc...) in table 3.

My issue is that when a script is running, it is fast, like let's say it takes 30minutes each, so 1h total. But when the two scripts are running at the same time, it is VERY slow, like it will take 5h, instead of 1h.

So first, I would like to know what can cause this ? Is it because of IO performance ? (like mysql is writing in two different tables so it is slow to switch between the two ?)

And how could I fix this ? If I could say that the big INSERT query is paused while my second background script is running, it would be great, for example... But I can't find a way to do something like this.

I am not an expert at MySQL administration.. If you need more information, please let me know !

Thank you !!

I have already asked my question on Stackoverflow and they only proposed to use an event scheduler.
My issue is that, in production, my first script (the least important/urgent) can take 5h or even more, and the second one would only take 30min and is a little bit urgent. I can't wait for the first script to finish before running the second one.


texts:
I use an ODB SQL Database for electronic components.
The Database comes from Omnify which a PLM system that is used to store all the electronic components that we use. The database is accessed using Altium Designer which is a schematic and PCB layout program.
The database connection does work, but there are several issues with it:
I have to access it over VPN which slows everything down.
I cannot change in data on the Omnify side because I am not an administrator so I have to ask someone to do this. Also I do not believe there is a way to mass populate fields that need to have the same value.
So I think it would be better if I had a local copy of the database so I would not have use VPN and I could also quickly and easily make changes to it. I can access the database in Excel save it as an Excel file and make changes to it, but any new Omnify entries would have to added manually. So Ideally I need something that links to the live database but keeps a local copy that I can edit and adds new entries as they become available but does not change the edits I made to existing entries. I am not sure if such a thing exists. Thanks for any advice.


texts: Recursively find the path to all leaves descending from a given parent in a tree

texts:
I'm trying to write a query to identify the path to each furthest descendant of a particular parent node in a table of tree structures like:
    0     1
    |     |
    2     3
    |     |
    4     5
   / \    |
 *6*  8  *7*
      |
     *9*

There are many parents, all children have one parent, parents have 0-5 children (but the graph is very "long" – most parents only have one child). There are no cycles.
I'm trying to efficiently identify the path to the furthest descendants of a specific node (and not to any intermediate nodes). E.g. in the above:

get_leaf_paths(1) would return 1 row: {1, 3, 5, 7}
get_leaf_paths(2) would return 2 rows: {2, 4, 6} and {2, 4, 8, 9}

Sample table:
CREATE TABLE graph (
    id bigint PRIMARY KEY,
    parent_id bigint,
    FOREIGN KEY (parent_id) REFERENCES graph(id)
);
INSERT INTO graph (id, parent_id)
    VALUES (0, NULL),
           (1, NULL),
           (2, 0),
           (3, 1),
           (4, 2),
           (5, 3),
           (6, 4),
           (7, 5),
           (8, 4),
           (9, 8);

I'm hoping for a result that looks something like:
SELECT get_leaf_paths.* FROM get_leaf_paths(0);
path
-----
{0, 2, 4, 6}
{0, 2, 4, 8, 9}
(2 rows)

In my initial attempt at a function with a recursive query, I've had trouble selecting only the furthest leaves, especially since some branches are shorter than others (e.g. 6 and 9 above are at different depths). The paths can be very deep (thousands or millions of elements), so I would also like to avoid the excessive memory usage of generating paths for every intermediate node.
Any ideas would be greatly appreciated. Thanks!


texts:
I'm struggling with the design phase for a data warehouse.  It's not the traditional sales example you find on MSDN or all books etc.  I've been tasked with creating an education data warehouse, that tracks student progress over time.  There is nothing that quite matches it in Kimball's books either.  This is what I've come up with so far. I'm following best practices i.e. Creating primary identity keys on each table and including the business key which I may attempt to make an unique key depending on the quality of data (whether referential integrity can be kept)

DimStudent

StudentDwKey
StudentBizKey

DimSubject

SubjectDwKey
SubjectBizKey

DimTime or date

DateKey

Different format dates, quarters etc. etc.

DimCollege

CollegeDwKey
CollegeBizKey

DimTeacher or tutor

TeacherDwKey
TeacherBizKey

FactGrades

FK_StudentDwKey
FK_SubjectDwKey
FK_CollegeDwKey
FK_TeacherDwKey
Grades

(Obviously there is many more fields in the dimension tables, but just for simplicity)

Student and Subject both have the same business key.  Now I'm really struggling with the fact table.  The exam grades are stored in the Subject business table for some reason.  The ETL I've created splits the grades from the subject table so DimSubject just contains subjects and FactGrades just contains Grades with foreign keys from other tables.  The problem I'm having is the relationships and whether or not I need to create multiple fact tables.  There are other grades they want analysing that are stored in different places for example i.e. Predicted grades, or admission exam grade. So should I create a fact table for each one, or try and merge them all into one fact table?  Also if the relationships are defined by the business key, should I not be creating foreign key relationships between the business keys and not the surrogate keys?  So in short.

Do I define relationships by newly created keys or business keys.
Do I create multiple fact tables for each type of grade if they are in different parts of the business database or put them all in one table.
Can I link one dimension to multiple fact tables or does this cause duplication issues.
How do I define relationship to date dimension, should this relationship be between the fact table and what do I join on?  I have an exam start date and end date but then I will have different dates for other grade types.


texts:
I have two question which are rally about the same thing - how to read more documents per second? I'm running a MongoDB cluster on Amazon EC2 and I'm load testing it from 10 servers (on Amazon EMR). It looks like I hit Amazon's bandwidth cap long before hardware issues.

MongoDB cluster

Mongo 3.2.4
6x EC2 r3.2xlarge
3x shard 2x replicas
Data:

Totals
 data : 36.91GiB docs : 7183252 chunks : 1024
 Shard shard0 contains 31.24% data, 31.23% docs in cluster, avg obj size on shard : 5KiB
 Shard shard1 contains 32.48% data, 32.48% docs in cluster, avg obj size on shard : 5KiB
 Shard shard2 contains 36.27% data, 36.28% docs in cluster, avg obj size on shard : 5KiB

Load Test Environment

Amazon EMR (the same availability zone)
11x EC2 m3.xlarge
Local instance of Mongos connected to the MongoDB cluster

Problem 1

I would like client to use all available server (Primary and Secondary). From my understanding I have to use ReadPreference NEAREST:

clinet = new MongoClient(Arrays.asList(
                new ServerAddress("ip-172-30-0-98", 27017)));
clinet.setReadPreference(ReadPreference.nearest());

Unfortunately this doesn't seam to be working. When I send lots of queries to the cluster only PRIMARY servers look busy.

In this test I exclude the biggies filed (Doc) which is ~5Kb. It allows me to read 250,000 documents per second.

collection.find(in(keyName, ids)).projection(Projections.exclude("Doc")).into(documents);

It looks to me like I'm wasting 50% of potential capacity of my cluster. What should I do to utilise it fully?

Problem 2

When I load the whole document collection.find(in(keyName, ids)).into(documents) I can only read ~22,000 documents per second. Quick look into htop shows light load on the cluster.

The only explanation to this doc/sec drop I can find is Network I/O. If an average document is 5kb and I'm reading 22k per second does it mean that my Mongos instance can only read ~107MB per second from the cluster?

If I didn't miss anything obvious how should this problem be solve? Add more Mongos?


texts:
When I run the following query,

SELECT engines, manufacturer, model, MAX(seats)
FROM   planes
GROUP BY engines;

I am getting the correct engines and seats results but not the correct manufacturer, model combination. In addition, there are multiple rows with the same maximum value for seats number which I need but just getting one result by engines/seats. I have viewed other Stack Exchange posts and elsewhere but cannot seem to find a good solution to fix the query. Any advice?


texts: How to speed up foreign key creation in MySQL for large tables?

texts:
I am trying to set a foreign key constraint on a 5.7 InnoDB table with 30M+ rows.
It now already runs for over 1h on a quad core 64GB server. The processlist outputs the state copy to tmp table for the issued alter table command.
InnoDB_buffer_pool_size is set to 32G and has room. The system does not swap.
Why does the system create a tmp table and can this somehow be increased in performance?


texts:
I want to create a database where one user can either be a Doctor or a Patient (but not both). So that, I can get all doctors or patients from doctor or patient table as well as I can search doctor or patient credentials details from user table. I really confused about which way I use. Please suggest me which diagram I use.

Diagram 1:

Diagram 2:


texts:
I was trying to run a migration file to create a table of users but I kept getting the following error:
Error: Not run migration 1604169742656_add-users-table is preceding already run migration 1604024614269_table-comments

I worked around it by creating a new database as I was pressed for time, but does anyone know what the interpretation of this error is? Did I need to first perform a drop of the current table?
This is the migration I was trying to run:
exports.up = pgm => {
  pgm.sql(`
    CREATE TABLE users (
      id SERIAL PRIMARY KEY,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
      bio VARCHAR(400),
      username VARCHAR(30) NOT NULL
    );
  `);
};


texts:
I'm writing function in plpgsql and stuck with following question: my function has parameter service_id_input and query. I want to create additional clause in WHERE section if this variable is not null. For example

SELECT
    MIN(time_from) AS min_0,
    MAX(time_to) AS max_0,
    CEIL((MAX(time_to) * 60 - MIN(time_from) * 60) / reception_duration_input) AS diff_0,
    user_id AS user_id_0,
    workingtimetable.date AS date_0
FROM
    workingtimetable
WHERE
    -- IF service_id_input is not null then add another restriction
    department_id = department_id_input AND date >= NOW() :: DATE
GROUP BY user_id, date
ORDER BY date ASC;

How can I do it?
Full code of function:

CREATE OR REPLACE FUNCTION find_days_business(IN reception_duration_input INTEGER, IN department_id_input BIGINT, IN service_id_input BIGINT)
      RETURNS TABLE(avail_records NUMERIC, busy_records BIGINT, percentage NUMERIC, day_date DATE) AS
    $BODY$
    DECLARE
    BEGIN
      CREATE TEMP TABLE sum_of_avail_records ON COMMIT DROP AS
        SELECT
          SUM(diff_0) AS sum_0,
          date_0
        FROM (
               SELECT
                 MIN(time_from)                                                             AS min_0,
                 MAX(time_to)                                                               AS max_0,
                 CEIL((MAX(time_to) * 60 - MIN(time_from) * 60) / reception_duration_input) AS diff_0,
                 user_id                                                                    AS user_id_0,
                 workingtimetable.date                                                      AS date_0
               FROM workingtimetable
               WHERE
                 -- IF service_id_input is not null then add another restriction
                 department_id = department_id_input AND date >= NOW() :: DATE
               GROUP BY user_id, date
               ORDER BY date ASC
             ) AS perday
        GROUP BY date_0;

      CREATE TEMP TABLE num_of_busy_records ON COMMIT DROP AS
        SELECT
          COUNT(appointments_book_records.id) AS count_1,
          appointments_book_days.date         AS date_1
        FROM appointments_book_records
          INNER JOIN appointments_book_days ON appointments_book_records.appointment_day_id = appointments_book_days.id
        WHERE appointments_book_days.date >= NOW() :: DATE
        GROUP BY appointments_book_days.date
        ORDER BY appointments_book_days.date;

      RETURN QUERY
      SELECT
        sum_0,
        count_1,
        (count_1 / sum_0 * 100),
        date_0
      FROM sum_of_avail_records
        LEFT OUTER JOIN num_of_busy_records ON date_0 = date_1;
END;$BODY$
LANGUAGE plpgsql;


texts:
We have a database table and we found a weird query from old code. We can't really explain it, so we decided to analyze why it could have been done that way. The weird thing is that the results are the same, but the performance is quite interesting.

SELECT * FROM table WHERE column='97807dsad1' group by indexedColumn ORDER BY CreatedDate ASC;

SELECT * FROM (SELECT * FROM table WHERE column='978asdad' ORDER BY CreatedDate ASC) as thetable GROUP BY indexedColumn;

column has no index.
CreatedDate has index.
indexedColumn has index.

The first query takes 30 seconds, the second 2.26 seconds.
I tested it a bit more and inner select:

SELECT * FROM table WHERE column='978asdad' ORDER BY CreatedDate ASC;

It is slow without the ORDER BY and quick with the ORDER BY.

May matter: the total results for the query is 4 rows (out of the 1.8 million records in the table)

The results of EXPLAIN for the first:

+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | table | index | NULL          | indexedColumn  | 202     | NULL | 1806555 | Using where; Using temporary; Using filesort |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+

The results of EXPLAIN for the second:

+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |     108 | Using temporary; Using filesort |
|  2 | DERIVED     | table  | ALL  | NULL          | NULL | NULL    | NULL | 1806555 | Using filesort                  |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+

The result of explain for only the inner select of the 2nd query:

+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 1806555 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+

What is the explanation for this?


texts: Behavior of Varchar with spaces at the end

texts:
When I use a Varchar with spaces it ignores the spaces at the end.

ex:

declare @X varchar(50)

This...

set  @X= 'John'

...is the same as...

set @X= 'John           '

It considers these to be equal.  How can I cause the system to recognize these as different?


texts:
I am having a postgres database on my portable and on a server. I would like to keep those two in sync as easy as possible. I know about slony but I am not able to figure out:

Does it work when the two servers are not always connected? - I may do changes on the db on my portable when it is not able to reach the other server.

Does it work both ways? Can I update the slave and get the updateds pushed to the master?

Does it sync changes in the db-structure? The database in question is a geographical database, and occasionally I import a new map as a new table, I am also often adding new view for data processing.

If slony is not useable in my case, are there any other usable tools? I've looked at dblink, but that does neither seem to be what I need.


texts: How to get sum of last previous X days of everyday?

texts:
Let's say I have sales number of last 60 days. How would like to get sum of every last X days of everyday.

For example my graph will include

Sum of Day - 60 to 30
Sum of Day - 59 to 29
Sum of Day - 58 to 28
Sum of Day - 57 to 27

......

Sum of Day - 33 to 3
Sum of Day - 32 to 2
Sum of Day - 31 to 1

So that it will give me 4 results ( Day 8 to 4, Days 7 to 3, Day 6 to 2, Day 5 to 1 ). It will be sum of number_of_sales

CREATE TABLE sales (
  sales_id          int  PRIMARY KEY,
  sales_create_date timestamp,
  number_of_sales   int
);

INSERT INTO sales (sales_id, sales_create_date,number_of_sales)
VALUES
  (0, '2015-05-02 05:30:40',100),
  (1, '2015-05-02 00:00:00',200),
  (2, '2015-05-03 00:00:00',300),
  (3, '2015-05-03 00:00:00',400),
  (4, '2015-05-05 00:00:00',500),
  (5, '2015-05-06 00:00:00',600),
  (6, '2015-05-07 00:00:00',700),
  (7, '2015-05-08 00:00:00',800),
  (8, '2015-05-08 00:00:00',900);

This is the final result that I need from this query

In my real case, I will get sales of last 60 days, and it will return me 30 records but summing up previous 30 days every day. In example I gave you rather than analyzing 60 days, I analyzed 8 days, which will product me 4 entries.
Plase don't spend time by getting the date of last entry. Just think the start date is today always.


texts: Shrink Database/Log (growing and non growing)

texts:
Before you yell and scream do not shrink at all. I have read every article that says why you shouldn't but I could not pinpoint the info I was looking for to help me understand my situation.  Here is what I do:

For a database that grows:

Shrank my database once
Rebuilt all the indexes to make sure everything is up to date and I will not shrink this database again
Check index fragmentation
Shrank the log file as it was using too much space and I am not too concerned with going back as i keep full backups of my database.
Check index fragmentation (still same as before step 4)

For non growing databases:

Is it safe to rebuild all the indexes and shrink the database (will cause fragmentation in indexing)?

What I am not sure about is will this impact query performance or is the index fragmentation only there if the database is growing?  Because when I rebuild all the indexes it leaves a LARGE chunk (almost same size as the database) as "free space" which I will never use as my databases don't grow.


texts: How to transfer Right-to-Left text from Excel to SQL Server?

texts:
I have a collection of data in my Excel which the language is right-to-Left. My data collection is very large. I want to transfer this to my SQL Server database and insert the values into a table.

For example it would be like this:

Insert City (Id, City, State_Id)
values (1, N'right-to-left text', 5)

Right-to-Left sample :    (2, N'تهران')

As you might be aware the prefix N is used to describe the character.

My question is how would I be able to transfer all this data without having to overwrite them again?

The main problem is the right-to-left characters


texts: Postgresql - archive_cleanup_command with multiple standby servers

texts:
Regarding the archive_cleanup_command command, the Postgresql 9.6 documentation states the following:

  Note however that if multiple standby servers are restoring from the
  same archive directory, you will need to ensure that you do not delete
  WAL files until they are no longer needed by any of the servers.

Is there a built-in way that the master can know when all of the slaves do not need particular WAL files?  Or does that functionality need to be added via scripting or other mechanism?  If so, what would that look like?

Also, would it be typical to have an archive_cleanup_command on both the master and the slaves, or would the slaves even be archiving?


texts: PostgreSQL complaining about shared memory, but shared memory seems to be OK

texts:
I've been performing kind of intensive schema dropping and creating over a PostgreSQL server, but now complains..:

WARNING:  out of shared memory
ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.

But the problem remains if PostgreSQL is just restarted with service postgresql restart, I suspect max_locks_per_transaction won't tune nothing.

I am a bit estranged because the troubleshooting lists for this error is not working for me.

MORE INFO 1409291350: Some details missing but I keep the core SQL result.

postgres=# SELECT version();
PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2,
 64-bit

And:

$ lsb_release -a
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 14.04.1 LTS
Release:        14.04
Codename:       trusty


texts:
I want this:

"ACCOUNT_ID","MAJOR_VERSION","MINOR_VERSION","COUNTRY","ACCEPTANCE_TIMESTAMP","AGREEMENT_ID"
"abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos"

But I get this:

'"'||"ACCOUNT_ID"||'"'||','||'"'||"MAJOR_VERSION"||'"'||','||'"'||"MINOR_VERSION"||'"'||','||'"'||"COUNTRY"||'"'||','||'"'||"ACCEPTANCE_TIMESTAMP"||'"'||','||'"'||"AGREEMENT_ID"||'"'
"abcdefgh-1234-5678-ijkl-mnopqrstuvwx","20110901","1","CN","1329574013737","tos"

Using the following SQL:

select '"'|| "ACCOUNT_ID"||'"'||','||
  '"'|| "MAJOR_VERSION"||'"'||','||
  '"'|| "MINOR_VERSION"||'"'||','||
  '"'|| "COUNTRY"||'"'||','||
  '"'|| "ACCEPTANCE_TIMESTAMP"||'"'||','||
  '"'|| "AGREEMENT_ID"||'"'
from THE_TABLE aaa
where aaa.country='CN' and rownum < 10;

How can I get the first output ?


texts: SQL Server select columns which contain data

texts: I'm working for a client to import data out of a generic off the shelf application database.  If you can imagine a database that is designed by software developers that have no concept of database design, that is what i'm dealing with.  I'm selecting from tables that have 50+ columns, where half the columns contain null data for every row (i.e. not used).  My question is, how do i identify the columns that are null for EVERY row (i.e. useless) and exclude them from my queries? Thanks for your help!  (i want to avoid going column by column saying isnull etc. as that will take forever!)


texts: How can I DRY up a series of upserts of the same fields from multiple tables?

texts:
I have a bunch of tables that all have a storenumber, extracteddate and extractedtime column.

I'm doing ETL on these tables and I want to put the last extracted date and time for each store in a single table, with an additional column for the input table name.  So I've ended up repeating the same upsert a bunch of times and really the only thing that is different is the CTE that I am using for input:

with rows as (
  select distinct on(storenumber)
         storenumber, extracteddate, extractedtime
    from storefinancialdate
  )
  insert into star.stat_etl (storenumber, filename, last_extracted, last_loaded)
    select rows.storenumber,
           'storefinancialdate',
           (rows.extracteddate + rows.extractedtime::time),
           transaction_timestamp()
      from rows
    on conflict(storenumber, filename) do update
      set last_extracted = excluded.last_extracted,
          last_loaded    = excluded.last_loaded;

with rows as (
  select distinct on(storenumber)
         storenumber, extracteddate, extractedtime
    from storeproduct
)
insert into star.stat_etl (storenumber, filename, last_extracted, last_loaded)
  select rows.storenumber,
         'storeproduct',
         (rows.extracteddate + rows.extractedtime::time),
         transaction_timestamp()
    from rows
  on conflict(storenumber, filename) do update
    set last_extracted = excluded.last_extracted,
        last_loaded    = excluded.last_loaded;

-- repeat a bunch more times...

Is there some way that I can DRY this up?  I can't pass a CTE into a function, can I?

(I get that I could call a function on each record of each CTE, one at a time. That would make the code shorter but also much, much slower. So I don't want to do that.)


texts: How to remove a secondary database from an availability group and rejoin it

texts:
I have an availability group (AG) with multiple databases (DB-A, DB-B, DB-C), and multiple secondaries (SEC-B, SEC-C) and one of the databases will not resume synchronization on just one of the secondaries.

For this example, DB-C is not synchronizing on SEC-C and no amount of restarting SQL Server or resuming HADR will get it to start again.

I don't want to remove the replica (secondary SEC-C) from the AG because I would then have to resync all of the databases (DB-A, DB-B, and DB-C), and that would take more time than is necessary.
I also don't want to completely remove the database (DB-C) from the AG because there are other secondaries (SEC-B) where there is no problem, and I don't want to have to resync it or temporarily lose my HADR on the secondary where it is working.

How can I remove just this one secondary database from the AG, resynchronize it, and add it back to the AG?


texts: Need to Sync Oracle DB table data with mysql

texts:
I have a table with some data in oracle which i want to sync with mysql table once in a day, like a cron job.

Is there any tool available which i can use as a cron job in Linux?.

Thanks,


texts:
I have used below query to get the history queries on ran SQL Server but it's not giving me the whole history it just returns queries ran on nearly about last 1/2 an hour. I want to get the queries ran on week ago. Is there any solution?

SELECT  dest.text, deqs.last_execution_time
FROM    sys.dm_exec_query_stats AS deqs
        CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE    dest.text NOT LIKE '%dest.text%'
         order by deqs.last_execution_time desc


texts: MDF file is close to 900gb but my calculation of reserved table space is less than 30gb... what am I missing?

texts:
In a nutshell, my server is running out of free hard drive space so I ran the script found here to get an idea of tables I could possibly delete old data from:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=10932
but when I got the results it seems that deleting old data doesn't seem to be my solution. I have a feeling there is something else I should be aware of here...

results from sp_spaceused

database_name   database_size   unallocated space
prod            993333.19 MB    94327.32 MB

reserved        data         index_size unused
834819768 KB    833565632 KB 1065480 KB 188656 KB

The results of my "find big tables" script:

reserved_KB     data_KB     index_size_KB   unused_KB
27,257,368      26,126,568  1,003,184       127,616


texts: pg_ctl status responds with "available" during failed startup

texts:
I am using pg_ctl -D $PGDATA status to monitor whether my postgres standby database is up and running. Sometimes the database fails to start due to a missing WAL file, but its status is reported as "available".

Is there a simple way to see if postgres is available using the pg_ctl status option, or do I need to write a script to query the postgres database and monitor the output from script?


texts: Why does DBMS_SESSION.session_trace_enable create multiple files?

texts:
I have a trace on a session configured like this:

execute immediate 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''TEST1''';

DBMS_SESSION.session_trace_enable (waits => TRUE,
                                   binds => TRUE,
                                   plan_stat      => 'all_executions'
                                      );
-- do SQL stuff --
DBMS_SESSION.session_trace_disable();

When completed executing, I have some 80 .trc files created.  Why is that, and is there a way to force all tracing to one file?  Thanks.


texts: Howo specify numactl settings when running mongodb as docker swarm service?

texts:
I have a mongodb 3.6 replica set. The replica members are running in docker containers. The whole replica set was created as a docker swarm service. Something like this:

docker service create -d \
        --name "mongo-master" \
        --mode global \
    --constraint "node.labels.mongo == master" \
        --network test-internal \
        --endpoint-mode dnsrr \
        --mount type=bind,src=/docker/mongodb/conf,dst=/conf \
        --mount type=bind,src=/docker/mongodb/data/master,dst=/data/db \
        --mount type=bind,src=/docker/mongodb/data/backup,dst=/backup \
        mongo:3.6 -f /conf/rs0.conf

docker service create -d \
        --name "mongo-replica" \
        --mode global \
    --constraint "node.labels.mongo == replica" \
        --network test-internal \
        --endpoint-mode dnsrr \
        --mount type=bind,src=/docker/mongodb/conf,dst=/conf \
        mongo:3.6 -f /conf/rs0.conf

docker service create -d \
        --name "mongo-arbiter" \
        --mode global \
    --constraint "node.labels.mongo == arbiter" \
        --network test-internal \
        --endpoint-mode dnsrr \
        --mount type=bind,src=/docker/mongodb/conf,dst=/conf \
        --mount type=bind,src=/docker/mongodb/data/arbiter,dst=/data/db \
        mongo:3.6 -f /conf/rs0.conf

When I start the mongo shell then I get this warning:

MongoDB shell version v3.6.3
connecting to: mongodb://127.0.0.1:27017
MongoDB server version: 3.6.3
Server has startup warnings:
2018-03-19T08:51:27.726+0000 I CONTROL  [initandlisten]
2018-03-19T08:51:27.727+0000 I CONTROL  [initandlisten] ** WARNING: Access control is not enabled for the database.
2018-03-19T08:51:27.727+0000 I CONTROL  [initandlisten] **          Read and write access to data and configuration is unrestricted.
2018-03-19T08:51:27.727+0000 I CONTROL  [initandlisten]
2018-03-19T08:51:27.727+0000 I CONTROL  [initandlisten]
2018-03-19T08:51:27.727+0000 I CONTROL  [initandlisten] ** WARNING: You are running on a NUMA machine.
2018-03-19T08:51:27.727+0000 I CONTROL  [initandlisten] **          We suggest launching mongod like this to avoid performance problems:
2018-03-19T08:51:27.727+0000 I CONTROL  [initandlisten] **              numactl --interleave=all mongod [other options]
2018-03-19T08:51:27.728+0000 I CONTROL  [initandlisten]
rs0:PRIMARY>

So I should start the service with numactl. There is an issue about this here:

https://github.com/docker-library/mongo/issues/113

But that solution cannot be used in my case, because the instances are created by docker swarm. E.g. there is no "docker run" command, only "docker service create" command. I cannot specify "--security-opt seccomp=unconfined", or at least I don't know how. I'm using the official docker image from here: https://hub.docker.com/_/mongo/


texts: MySQL unique index only if column has specific value

texts:
I have a table where I use a boolean column deleted to signify whether a specific record has been 'deleted' by the user (meaning, it appears in the trash list instead of the normal list). These items all have slugs which should be unique.

However, if the user wants to add an item with a slug that is the same as the slug of a deleted record, it should be allowed. I tried doing this with a UNIQUE index on deleted and slug, however, this gives a problem if this item would get deleted: there should then be two items in the trash list with the same slug, which would give a constraint violation.

So actually I need a constraint which is only applied for the records with deleted=0

I've seen the question Custom unique column constraint, only enforced if one column has a specific value, however, it only applies to PostgreSQL and I'm on MySQL. Is there a way in MySQL to do this, or a nice workaround?

I thought of making deleted an integer instead of a boolean, where 0 would signify not deleted and any other number deleted. Then deleted items with the same slugs could have a different value in the deleted column.

However, this would mean the interfacing software (written in PHP) would have to check what it could put in the deleted column if a constraint violation occurs, and of course I'd prefer a pure MySQL solution. If I'm going to write this in the PHP code anyway, I'd rather change the slug value, for example, to prepend the id of the record to the slug when an item has been deleted - or, create a second table for the deleted records, where no such constraint exists.

Anyway, is there a pure MySQL way to do this?

Note: of course, such a scheme will have difficulties when the user requests to restore a previously deleted item when its slug is already assigned to a new record. This will have to be done in the interfacing code in any case.


texts: unable to login to mysql console for non root unix users

texts:
My Issues is that when I am logged in as a normal user on unix server and then try to login as MySQL root. It throws below error but when I am logged in as root to unix server and then try to connect to MySQL command line it allows me. I don't understand why is this happening. and how to resolve it. I want connect to MySQL command line with any normal unix user not root always.
-sh-4.2$ mysql -u root -pPASSWORD
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (13)

-sh-4.2$ su Password:
[root@hostname]# mysql -u root -pPASSWORD
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL
connection id is 78203 Server version: 5.5.52-log MySQL Community
Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights
reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input
statement.

mysql>


texts:
I am currently trying to benchmark the performance of PostgreSQL when:

the tablespace is set to a folder on an HDD partition
the tablespace is set to a folder on an SSD partition

I am using the pgbench tool to perform the benchmarking, and am following the procedure according to this tutorial. So basically, I am looking at the tps (avg. transactions per second) at the end of the run as the metric for comparison.
Note: The size of my database is close to 10 GB so there is sufficient load for a stress test.
Before each run, I am manually changing the tablespace to hdd_tbspc (tablespace on HDD parition) and ssd_tbspc (tablespace on SSD parition), using pgAdmin.
The question

However the value that I am getting at the end of each of the runs is more or less the same, i.e., I can't observe any significant performance improvements when I switch to an SSD. Is there any reason for this?

Also, please let me know if the benchmarking protocol I am following makes sense.

Commands used
Here are the commands that I am using:
To initialize and scale the DB:

$: pgbench -U postgres -i -s 50 db-name

The above command is run only once. The two queries shown below are run on the same database with different tablespaces to contrast the performances.
Using the number of transactions as a metric:

$: pgbench -U postgres -c 10 -j 2 -t 10000 db-name

Using time as a metric:

$: pgbench -U postgres -c 10 -T 600 db-name


texts: SQL developer cannot connect to external databases. Only local ones

texts:
IO error: The Network Adapter could not establish the connection.

Local databases work fine, but external one are returning me this error. I reinstalled once Oracle DV, and in the first time I executed SQL Developer it connected to the server okay, and showed database and tables, now it's showing the same error again:


texts: Could a null column be part of a primary key?

texts:
I'm developing a SQL Server 2012 database and I have a question about a One-to-Zero-Or-One relationship.

I have two tables, Codes and HelperCodes. A code could have zero or one helper code. This is the sql script to create these two tables and their relationships:

CREATE TABLE [dbo].[Code]
(
    [Id] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [SentToRanger] BIT NOT NULL DEFAULT 0,
    [LastChange] NVARCHAR(50) NOT NULL,
    [UserName] NVARCHAR(50) NOT NULL,
    [Source] NVARCHAR(50) NOT NULL,
    [Reason] NVARCHAR(200) NULL,
    [HelperCodeId] NVARCHAR(20) NULL,
    CONSTRAINT [PK_Code] PRIMARY KEY CLUSTERED
    (
        [Id] ASC
    ),
    CONSTRAINT [FK_Code_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level]),
    CONSTRAINT [FK_Code_HelperCode]
       FOREIGN KEY ([HelperCodeId])
        REFERENCES [dbo].[HelperCode] ([HelperCodeId])
)

CREATE TABLE [dbo].[HelperCode]
(
    [HelperCodeId] NVARCHAR(20) NOT NULL,
    [Level] TINYINT NOT NULL,
    [CommissioningFlag] TINYINT NOT NULL,
    [LastChange] NVARCHAR(50) NOT NULL,
    CONSTRAINT [PK_HelperCode] PRIMARY KEY CLUSTERED
    (
        [HelperCodeId] ASC
    ),
    CONSTRAINT [FK_HelperCode_LevelConfiguration]
       FOREIGN KEY ([Level])
        REFERENCES [dbo].[LevelConfiguration] ([Level])
)

Is that correct?

A Code and a HelperCode are both different entities. A HelperCode can be an used (none Code references it), or used (only one Code references it).

Maybe Code.HelperCodeId must be part of Code table primary key. But I'm not sure if a null column could be part of a primary. Doing this, I want to prevent that two or more Codes reference the same HelperCode.


texts: Attacks on Postgresql listening for requests on 'localhost'

texts: I am using jboss server for my servlets and the postgresql as the database server, listening on port 5432 on localhost. My jboss server is bound to a public IP. I want to know that since my postgesql is bound only to the localhost, is there any chance of an attack at all. Will any one be able to see the all the table data even if they have the user name and password for the database?


texts:
I'm gonna get straight to the problem:

Image dividend reinvestment as this scenario:

You buy 10 shares of AAPL on 2018-01-01 for $10
-- AAPL pays $1 / per share dividend on 2018-02-01 -> you get $10 CASH

This CASH is automatically reinvested into shares: 1 AAPL share costs $20 on 2018-02-01 -> shares_per_dividend = $1 (dividend amount) / 20 (AAPL price_per_share) = 0.05 -> you get + 0.5 shares calculated as: 10 (cash from dividends) * 0.05 (shares_per_dividend)
Now you have 10 (initial shares) + 0.5 (new shares) = 10.5 shares

-- AAPL pays $1 / per share dividend on 2018-03-01 (month after first dividend) -> now you get $10.5 cash as you have 10.5 shares of AAPL

For the sake of simplicity AAPL again costs $20 on 2018-03-01 so you automatically buy 10.5 * shares_per_dividend (0.05) = 0.525 new shares -> now you have 10.5 + 0.525 = 11.025 shares

This could go on and on... The point is, after every paid dividend I want to add "CASH-BOUGHT" shares to initial shares, and then on next dividend date I want to multiply shares_per_dividend by current amount of owned shares (eg. 10.5 mentioned above)

Hope I expressed myself right :)

I would write query similar to this using window function, but it does not take current shares in account.

SELECT shares + shares * SUM(shares_per_dividend) OVER(PARTITION BY stock_symbol ORDER BY dividend_date ASC) as current_shares
FROM some_table;

shares_per_dividend is NULL if no dividend was paid on that day.

example of calculations:

the final result should look like this:


texts: Having problems trying to back up a Postgres database from AWS RDS

texts:
I am trying to backup a Postgres database from RDS and I am getting this error message.

  pg_dump: [archiver (db)] query failed: SSL error: unexpected message

  pg_dump: [archiver (db)] query was: SET search_path = public,
  pg_catalog

RDS: psql (9.4.4, server 9.4.1)

Running pg_dump from server with the following:
openssl 1.0.1e.30.el6.11
pg_dump (PostgreSQL) 9.4.4


texts: Is it possible to create a multi-layer schema in SQL Server 2008 R2?

texts:
For instance so you can specify a department, and then maybe have finer levels of granularity?

CREATE SCHEMA hr;
CREATE SCHEMA hr.reportl;
CREATE SCHEMA hr.report2;

The last two statements fail.


texts: Do long names for jsonb keys use more storage?

texts:
Consider the example: Two tables foo and bar, each with a jsonb column.

For foo, there are a million rows where the value of the jsonb is [{"a":123}].
For bar, there are a million rows where the value of the jsonb is [{"very_long_key_not_premature_optimization_at_all":123}]

The json key in bar is 46 characters longer than that in foo. Would the size of bar be 46 million bytes greater than foo?


texts: MySQL slow log logs every query instead of just slow queries

texts:
I have the following MySQL configuration for logging the slow queries:

    [mysqld]
    ### Logging options
    general_log_file = /data/mysql-log/mysql_general.log
    log-error = /data/mysql-log/mysql_error.log
    slow_query_log_file = /data/mysql-log/mysql_slow.log
    log-warnings = 1
    log-queries-not-using-indexes = ON
    log-slow-verbosity = full
    long_query_time = 2
    log-slow-admin-statements = 1
    log-slow-slave-statements = 1
    general-log = OFF
    slow-query-log = ON

However, I see the following in my slow log file:
[

To my knowledge, the Query_time in the log file denotes the time query took to run, but it defers the log time mentioned in the config file.

Anyone here to help me explain as to why this is happening?