texts: Is the PostgreSQL 'connection limit' shared across all users in a role?

texts:
I have created a role that many users can share:

> create role foo_read_only connection limit 5;

I have then assigned permissions to that role:

> grant select on table1 to foo_read_only;

Then I have assigned that role to people:

> grant foo_read_only to fred;
> grant foo_read_only to ginger;

Question: Do both fred and ginger get a connection limit of 5, or would that limit be shared between them?

Thanks!


texts: Best way to automate running several MySQL commands

texts:
We have several core db tables on our system and allow our employees access to these tables via views that just show them the data they need to see.

We created these views in a new db specific to the new user.  Creating all of these views requires running several mysql queries.

I would like to automate this process into a script, but feel there must be tools already out there that take a list of queries and run them.

Can anyone recommend any such tools or even any built-in features in mysql that could assist me here ?

Thanks!


texts: SQL Server 2008 - Cross database performance on same physical machine and server instance

texts:
Is there any performance hit when doing a select across another DB on the same physical machine? So I have 2 databases on the same physical machine running within the same SQL 2008 instance.

For instance in SomStoreProc on_this_db I run SELECT someFields FROM the_other_db.dbo.someTable

So far from what I have read on the internet, most people seem to indicate NO.


texts: Can multiple Oracle databases share a single parameter file?

texts:
I'm a developer working on an application team that supports a system with one production database and 4 test databases (2 Dev, a SIT and UAT environment).

The prod database exists on it's own Unix server and the 4 test databases all live on a different Unix server. For various reasons, over the years these 6 databases have become quite out sync with regards to their Initialization Parameters. A quick comparison of the V$Parameter table reveals a number of key setting differences that have been causing our application team problems and need to be corrected.

I'm working with our DBA team now to try and correct them but it is a large Enterprise and the process is painful. I'd love to find a way to keep these parameters better in line for those databases that share the same server.

Is there a way to have multiple databases share a parameter file? I reviewed some documentation and saw some references to doing this for a RAC setup but these databases aren't clustered but rather just copies of one another. I know certain parameters like SID and db_name would need to be unique but I was wondering if common settings like global_names and resource_limit could be centralized somehow?

Note I'm currently using 11G but we plan to upgrade to 12 within a month.


texts: Can a database table not have a primary key?  SQLite lets you define a table with no primary key therefore there can be no insertion anomaly.


texts:
I need to get all my SQL scripts in some kind of script repository. I have searched the web but no luck so far. I thought of creating a repository in a SQL database, my problem so far is to properly manage versioning of the scripts.

We are using to many scripts and everyone is using a different version of a script and is causing skewed data outputs. Has anyone successfully created something like this or is there a free application that can do this?


texts: Postgres one-liner import & export SQL without login & without asked for password

texts:
I'm looking for one-liner command for import & export SQL without login into postgres role or psql prompt.

Below is what I got for import SQL, but it asking password every time:

psql -h localhost -d dbname -U postgres -f /var/www/db.sql

Below is equivalent command in MySQL (import & export):

mysql -u root -p password dbname < /var/www/db.sql

mysqldump -u root -p password --single-transaction --skip-extended-insert --quick dbname > /var/www/db.sql


texts:
Question is most  probably stupid but I am stuck on this and cant think anymore(Pissed Off).

I have these tables->  employee(has a column foreign to location),location and location_flag. And I need to select employee that are related to locations that have some specific flags on (like provides holidays, provides healthcare).

Suppose, a location(1) provides healthcare and holidays(having 'YES' for location_flagS) then I can simply check if they are 'YES'. So far so good?

Here's the complication:

What to do if the location have no track for some flags, Then joins will be failed.

My query will be:

select *
from employee
inner join location
  on employee.locationId=location.id
inner join location_flag lf
  on lf.locationId=location.id
inner join location_flag lf2
  on lf2.location_id=location.id
where lf.value='YES' and lf.flag_id=1
  and lf2.value='YES'and lf2.flag_id=2

If one of the flags are simply don't exists, the result will be NULL.

What I want to is to give me the employees of the location that if have flags must be 'YES'. How can I do this?


texts: Normalization to Third Normal Form (3NF)

texts:
I have been given the example data and have been struggling to normalize to 3NF. I think the issue lies with the Tutor Name but this is my final chance at submitting so would like to get it right.

This is how I have normalised it,

3NF

Course

Course Code, Course Title, Level, Mode of Attendance, Course Fee, Start Date, End Date, Department*

Student/Course

Course Code, Student ID, Enrolment Date, Enrolled By

Student

Student ID, Student First Name, Student Last Name, Student Street, Student Town, Student Postcode, Student Landline, Student Mobile, Student DOB

Department

Department, Department Head Title, Department Head First Name, Department Name Last Name, Tutor First Name, Tutor Last Name

Thank you in advance


texts: How to handle connection pooling for massive multi-tenancy/multi-schema environment

texts:
Here's a quick run down of the situation:

We have 1 Schema per customer.
We have 2000+ customers.
We have 50+ Database servers (with the above schema's distributed
unevenly amongst them).

We are creating a true stateless app frontend (i.e. improving legacy software).  This means that we could have many (100+) app servers dealing with user traffic, with each app server potentially needing to connect to any one of the 50+ servers to pull data at any one time.

The reason for having so many app servers is that the code is extremely computationally intensive.  It may be that eventually we can move towards fewer app servers with more powerful hardware, but we're not quite there yet.

My question:  How does one manage the connection pooling situation in this type of environment?
If we imagine a "good" connection pool is around 64 connections, it doesn't seem feasible to have each app server generate a connection pool to each DB server. It would result in 100+x64 = 6400 persistent connections being made to each DB server... is that too much?

What can be done?  Is there some sort of connection-pool proxy software that can be used?


texts: SQL Parameter Sniffing Possible Workaround

texts:
Our system has roughly 500 "Customers" that have extreme differences in record counts. Here is a (greatly simplfied) sample query that could return 0 - 100000 rows depending on the parameter passed. This query runs find but I am fairly certain it suffers from parameter sniffing depending on which parameter gets cached.

exec sp_executesql N'
               SELECT *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

If I modify the query to include the parameter as a selection it seems to cache the query for each individual customer.

exec sp_executesql N'
               SELECT ''bda43162-2d98-4e79-8e81-7056f6df5e51'', *
                 FROM Widgets
                WHERE CustomerId=@0
',N'@0 nvarchar(40)',@0=N'bda43162-2d98-4e79-8e81-7056f6df5e51'

The performance is greatly improved because each customer gets it own version of the query cached. Are there any side effects of this approach?

Assumptions:

It must be dynamic SQL for this portion of the system
This query is run frequently
The number of customers will not grow quickly

EDIT: I considered using OPTION (RECOMPILE) but I did not want to recompile every time if I could get the benefits of the compiled query with this approach.


texts:
I have 2 mongoDBs. One running as PRIMARY, its version is 3.0.13. The other works as SECONDARY in the replica set, its version is 3.0.11. Replication works perfectly at this point.

I want to add a 3rd mongoDB, empty data folder, and its version is 3.4.5.

Using mongo client I can access to PRIMARY and SECONDARY, and from PRIMARY I can access to this 3rd mongoDB, and from SECONDARY I can access to this 3rd mongoDB too.

In the primary, I execute rs.add("server3:27017"), and the command rs.status() displays this 3rd server in status STARTUP2. But after a while I get this error

2017-07-25T09:24:52.159+0000 I REPL     [replication-0] Initial sync attempt finishing up.
2017-07-25T09:24:52.159+0000 I REPL     [replication-0] Initial Sync Attempt Statistics: { failedInitialSyncAttempts: 0, maxFailedInitialSyncAttempts: 10, initialSyncStart: new Date(1500974688699), initialSyncAttempts: [] }
2017-07-25T09:24:52.159+0000 E REPL     [replication-0] Initial sync attempt failed -- attempts left: 9 cause: UnknownError: error while getting last oplog entry for begin timestamp: find command not yet implemented

and the daemon dies when the attempts left reaches 0.

2017-07-25T09:25:03.887+0000 F REPL     [replication-0] The maximum number of retries have been exhausted for initial sync.
2017-07-25T09:25:03.899+0000 E REPL     [replication-1] Initial sync failed, shutting down now. Restart the server to attempt a new initial sync.
2017-07-25T09:25:03.899+0000 I -        [replication-1] Fatal assertion 40088 UnknownError: error while getting last oplog entry for begin timestamp: find command not yet implemented at src/mongo/db/repl/replication_coordinator_impl.cpp 635
2017-07-25T09:25:03.899+0000 I -        [replication-1]

***aborting after fassert() failure

what does it mean this find command not yet implemented? any workaround for having this 3rd server joined in the replica set?


texts: Incorrect process utilization from System health extended events?

texts:
I have been recently working on understanding the data or metrics being stored in system health extended events event file.

We are trying to implement the data collection of performance metrics using system health as provided here

There are reports that gives metrics like CPU UTILIZATION, other process utilization etc being collected from system health event named

scheduler_monitor_system_health_ring_buffer_recorded

I am not able to understand for few of my busy servers why the field "process_utilization" which is listed in report as SQL CPU utilization be most of time above 100. IN peak time it varies between 120-160 and because of which the report is always showing CPU above 100 even though when i checked from servers activity monitor that's never the case.

I've raised this issue in Github but seems like no fix or response.

Therefore, my question would be

How can i get accurate number for SQL CPU utilization for my servers using system health ring buffer recorded?
Report also shows counter for below 2 fields calculated per their report

  100-System_idle-process_utilization as OtherProcessUtil
  100-system_idle as SystemUtil

What are these OtherProcessUtil and SystemUtil needed/helpful for ?

I also see memory utilization every time always showing as 100. That does not seem correct as well. Has anyone noticed it?

Other tools like Idera & sentry [which i tested] do not show CPU usage above 100 % for the same servers. I did a side by side comparison for same load.


texts: Mysql Command line Parameter passing to sql file

texts:
In windows mysql(5.7 version),
I want to run mysql commmand using command line for .sql file as input .
Also i was to pass some variable whose value should get used in .sql file.
somehow I am not able to achieve it.

mysql -u root -p xyz  -e "set @tmp='abc'; source d:/v_test1.sql"

Here I want to file v_test1.sql having mysql code and also variable @tmp being used in v_test1.sql.

But getting error:

  MySQL Error: Unknown column '@tmp' in 'field list'

I think issue might be because of version.
Please suggest some solution.


texts:
We have an unusual replication scenario and I am not sure which tool would be appropriate for that. Basically, we have a legacy non-normalized database that we want to refactor into a normalized database. Unfortunately, this has to be done over a long period of time (one or two years), hence the need for replication: legacy applications would use the old format until they are replaced by newer applications.

We would like to have three DBs:

Legacy -> Mirror -> New DB

Everything done on the legacy db would be replicated to a mirror, then using triggers and conversion scripts, we would transform the data into a normalized format. I'm not concerned about the conversion scripts (it's a different problem), but I'm not sure how to do the replication.

The load of the DB is mostly read with a few writes each day that need to be propagated in a matter of minutes (it is not acceptable to wait 15 minutes to see the result of an insertion on the mirror/new db).

We have around 50 tables divided in four databases on one server that we want to replicate.

The conversion scripts cannot be executed on the legacy db because we want to keep the replication cost to a minimum on the legacy db server.

I looked at the built-in replication in postgresql 9.1 but from my understanding, I cannot add a trigger on the slave that is not present on the master (both dbs must be identical copies).

Is there any way that I could use the built-in replication for our scenario? Otherwise, which replication strategy would be appropriate?


texts:
I am considering applying the Service Pack 3 in a SQL Server 2008 with Service Pack 1; however, I was not able to find if there are any drawbacks in this process, specially regarding to Replication Services and Reporting Services.

Has someone done this kind of update? Was it smooth or did it generate many issues?


texts: Data warehouse: using day number in Epoch in a date dimension

texts:
In Chapter 2 Retail Sales of Ralph Kimball's The Data Warehouse Toolkit, the author describes a date dimension with three fields which appear totally alien to me:

Date Key (PK)
Date
Full Date Description
Day of Week
**Day Number in Epoch**
**Week Number in Epoch**
**Month Number in Epoch**
Day Number in Calendar Month
Day Number in Calendar Year
...

I understand that an Epoch is the "instance in time chosen as a particular era" and that the current standard epoch for J2000.0 starts on January 1, 2000. But I don't see how these numbers would be helpful in a date dimension or a cube used for getting information about retail sales.

Are numbers in Epoch typically used in Date Dimensions?
What Epoch would be used for a modern day business?
How would a user slice data based on Epoch and why?


texts: Getting ERROR 1045 (28000): Access denied for user even with proper setup and on different connections

texts:
I am getting this error:  ERROR 1045 (28000): Access denied for user 'ipcuser'@'10.0.0.26' (using password: YES)

The database is Percona MySQL 5.6 and is on the 10.0.0.201 IP

We have servers in the 10.0.0.x range.  Testing from different servers we get different results using the exact same command: mysql -u ipcuser -pPASSHERE -h 10.0.0.201 -D ipc --protocol=tcp
The issue came up over night when nothing has changed and no one was working.  No code changes at all.  No database changes.  The database was last restarted 4 days ago before attempting today.

The results of trying to connect from:

10.0.0.200 Connects
10.0.0.70 Connects
10.0.0.26 Access Denied
10.0.0.20 Access Denied
10.0.0.101 Access Denied

Output from: SELECT user, host FROM mysql.user;

ipcuser     10.0.0.%
ipcuser     10.0.0.101
ipcuser     localhost
ipcuser     127.0.0.1
root        10.0.0.201
root        127.0.0.1
root        ::1
root        localhost

Already tested:
 - Restarting MySQL
 - Adding in the specific IP of the server (that would be the 10.0.0.101 address in the above output, removing it has no change)
 - Restarting the network on both the DB and failed connections
 - Checking the error log.  Nothing.
 - Disabling firewalls.  None of the connecting servers nor the DB had iptables running.
 - Running FLUSH HOSTS
 - Changing the bind

Also for some extra weirdness, I was able to connect once from the 10.0.0.101 IP, but after issuing a query it disconnected me and on attempting to reconnect gave access denied.

After more testing, I shut the MySQL server down and the servers failed to connect were still getting a specific access denied error.  The ones that had successful connections got could not connect.


texts:
Hi I am trying to use to MySql Workbench Data Import facility to import a .csv file (thousands of records). However it stops importing the first 5 records.

After studying the .csv file I could see that on line 5 there was a double quote - see below;

1,90945601,123.123.123.123,Fujitsu,Esprimo Q900,YLBX097807192,,01/07/2011,408000085A,G.039,Weekend  PT (BS),,,,
2,90456978905,123.123.123.123,Fujitsu,Esprimo Q900,YLBX009787188,,01/07/2011,408089005A,"G,.039",Joe (ert),,,,
3,90945610,123.123.123.123,Fujitsu,Esprimo Q900,YLBX007897202,,01/07/2011,408097805A,2.012,Jo,,,,
4,90945978616,,Apple ,Mac ZOPF00PJ ,DGKLM05BF8J9,,01/10/2013,408005A,1.029,Jim and ICRH,,,wif,
5,90456789917,,D4M ,FlexiBasic Kiosk ,,,01/08/2013,408098705A,1.029,,,,"19"" Saw Screen with Artwork",
6,94560918,143.117.198.197,3M,9100BC,91201338,,01/07/2013,408005A+J101,G.036,SD RF,,,Dual (91201567338) ,

I have tried to configure the settings during the import however it still doesn't work - see image;

It's not possible to change the 'Field Seperator' tp a comma, only three options are provided.

I manually removed line 5 from the csv and this time over 100 records were imported, but again, it stropped at the next double quotes ". The problem is the csv file contains thousands of items that have descriptions that include size in inches.

Are there any other changes I can make without having to manually remove all the double quotes from my .csv file?

I've also tried PhpMyAdmin, doesn't work there either.

Any suggestions appreciated.


texts:
What's the normal way to design a relationship where every entry in table A is connected(1:n) to either an entry in table B or table C?

Or specifically: There are a tables for characters, movies, episodes and TV show season. One character is connected to either 1) a movie or 2) a show(in addition to n:n to season(season regular) and episode)

x can be the id for both a movie and a show.

Two identical tables(tv_characters and movie_characters)?

Is it better with two primary keys(one will always be either "movie" or "tv")?


texts:
I've got the table "session" with

CONSTRAINT u_session UNIQUE (where, who, when)

I need to use that combination of (where, who, when) as the foreign key in table "results". How can I do it?


texts:
In the main, I've got two kinds of time intervals:

presence time and absence time

absence time can be of different types (eg breaks, absences, special day and so on) and time intervals may overlap and / or intersect.

It is not for sure, that only plausible combinations of intervals exist in raw data, eg. overlapping presence-intervals don't make sense, but may exist. I've tried to identify resulting presence-time intervals in many ways now - for me, the most comfortable seems to be the follwing one.

;with "timestamps"
as
(
    select
        "id" = row_number() over ( order by "empId", "timestamp", "opening", "type" )
        , "empId"
        , "timestamp"
        , "type"
        , "opening"
    from
    (
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 1 as "type" from "worktime" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
        union all
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 2 as "type" from "break" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
        union all
        select "empId", "timestamp", "type", case when "types" = 'starttime' then 1 else -1 end as "opening" from
        ( select "empId", "starttime", "endtime", 3 as "type" from "absence" ) as data
        unpivot ( "timestamp" for "types" in ( "starttime", "endtime" ) ) as pvt
    ) as data
)
select
      T1."empId"
    , "starttime"   = T1."timestamp"
    , "endtime"     = T2."timestamp"
from
    "timestamps" as T1
    left join "timestamps" as T2
        on T2."empId" = T1."empId"
        and T2."id" = T1."id" + 1
    left join "timestamps" as RS
        on RS."empId" = T2."empId"
        and RS."id" <= T1."id"
group by
    T1."empId", T1."timestamp", T2."timestamp"
having
    (sum( power( 2, RS."type" ) * RS."opening" ) = 2)
order by
    T1."empId", T1."timestamp";

see SQL-Fiddle for some demo data.

The raw data exist in different tables in form of "starttime" - "endtime" or "starttime" - "duration".

The idea was to get an ordered list of every timestamp with a "bitmasked" rolling sum of open intervals at each time to estimate presence time.

The fiddle works and gives estimated results, even if startimes of different intervals are equal. No indices are used in this example.

Is this the right way to achieve questioned task or is there a more elegant way for this?

If relevant for answering: amount of data will be up to several ten-thousand datasets per employee per table. sql-2012 is not available to calculate a rolling sum of predecessors inline in aggregate.

edit:

Just executed the query against larger amount of testdata ( 1000, 10.000, 100.000, 1 million) and can see that runtime increases exponentially. Obviously a warning flag, right?

I changed the query and removed aggregation of rolling sum by a quirky update.

I've added an auxiliary table:

create table timestamps
(
  "id" int
  , "empId" int
  , "timestamp" datetime
  , "type" int
  , "opening" int
  , "rolSum" int
)

create nonclustered index "idx" on "timestamps" ( "rolSum" ) include ( "id", "empId", "timestamp" )

and I moved calculating rolling sum to this place:

declare @rolSum int = 0
update "timestamps" set @rolSum = "rolSum" = @rolSum + power( 2, "type" ) * "opening" from "timestamps"

see SQL-Fiddle here

The runtime decreased to 3 sec regarding 1 million entries in the "worktime"-table.

Question stays the same: What's the most effective way to solve this?


texts: View crossing multiple databases, but a query only using one is slow

texts:
Given a view like this example, crossing multiple databases in the same database cluster, with each database having a unique INSTANCE_NO.

Please note that each database can be split using the INSTANCE_NO
Also, each table has only about 100 rows.

ALTER VIEW [dbo].[VI_MULTI_DUMMY]  AS
SELECT 1 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM [HUB_1].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 2 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_2].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)
UNION ALL   SELECT 4 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] FROM  [HUB_4].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)

I have a huge performance problem for a query that only accesses one database:

--> 0 sec
SELECT 3 AS [INSTANCE_NO], [T].[B1] AS [V1] ,[DESC_1] COLLATE SQL_Latin1_General_CP1_CI_AS AS [V1_DESCR] , ... [HUB_3].[DBO].[TA_DUMMY] AS [T] WITH(NOLOCK)

--> 5-7 sec
select T.* from VI_MULTI_DUMMY AS T  WITH(NOLOCK) where  INSTANCE_NO = 3 OPTION(RECOMPILE)

I've used RECOMPILE, NOLOCK, ... and I'm out of idea's, who can help me?

EDIT : SOLUTION
A solution as stupid as the problem :
Go to each database it's properties--> Options--> Automatic.
Set 'Auto Close' to false


texts: database duplication VS database replication

texts:
Is there a difference between duplication and replication with reference to a database?

I am not sure if I am correct at all, but duplication means to me that we are copying bit by bit without understanding what the data is about, while replication is more like logical copying, where we are copying at say, database level. Am I correct?

Is there more to duplication and replication?


texts: Striping IO with datafiles on different physical mounts

texts:
I have a server with three directly attached disk controllers. Each of these can provide internal redundancy and IO-striping accross disks, but I'm wondering what is the best way to configure the whole system.

Would it be sensible to allocate three datafiles for each tablespace, each datafile being created in a mount provided by a single controller? Oracle seems to distribute data somewhat evenly accross datafiles when doing inserts, but would this be enough striping for queries?

We are mostly concerned about read performance for single aggregate queries (data warehouse) and want to read data in parallel from each controller.

One answer would be to use ASM or LVM to do the striping, Oracle would then see only one mount point where to store datafiles. We do have a configuration with ASM + number of JBOD-disks; it does perform reasonably well, but ASM has it's own issues and we are considering alternatives.


texts:
Since MySQL 5.8 the function GeomFromText() is no longer available and one should use ST_GeomFromText() instead, which works exactly the same. Progress, of course :-(

Ref: https://dev.mysql.com/doc/refman/5.7/en/gis-wkt-functions.html#function_geomfromtext

Now I'd like to use this function in a way that is backwards compatible so it can run on very old MySQL server versions as much as possible. What is the best way to check if this function is defined and which of the two (if any) I can use? I'd like to prevent having to check the version number of the server to select the correct query, if at all possible.


texts: Restore a specific file group to a test environment without restoring the Primary file group (Error 3154)

texts:
Scenario.

I have a filegroup that we need to restore to get data back that has
been removed from the database after the last backup I cannot touch
The production systems because they are running
The primary filegroup is just over 1TB in size (don't blame me I didn't do that and it makes me want to cry)
I don't have anywhere large enough to restore that Primary filegroup
The filegroup I wish to restore is only 60 GB in size
I simply need the data from one table out of the filegroup
The system is all in a remote location so the backup can not be removed from there and potentially loaded elsewhere
I have a test 'copy' of the database, but that is generated through scripts and is 1/1000 of the size but contains all the same tables etc as the main database, just next to none of the data content
I only need to restore from the full backup

Code Sample

RESTORE DATABASE testEnvironment
filegroup = 'requiredFileGroup'
from disk =
N'\\remotestorage\backupfile.bak'
with REPLACE,
move
N'requiredFileGroup_file1' to N'F:\requiredFileGroup_file1.ndf',
move
N'requiredFileGroup_file2' to N'F:\requiredFileGroup_file2.ndf',
stats = 1

Issues

When trying to restore I get the error 3154

The backup set holds a backup of a database other than the existing 'testEnvironment' database.

This occurs when trying to restore to a new file location, or overwriting the files that currently exist in the test environment
As mentioned the Primary Filegroup is too large to be restored anywhere

Any ideas on a work around for this would be greatly appreciated

Ste


texts: SQLServer Row level security - Enforce from within database

texts:
Have a multi-tenant app running on a single SQL Server 2016 db. I have a RLS (row level security ) predicate on customer_id (unique for each tenant) and the column exists in all tables.

CREATE FUNCTION dbo.[RLSPredicate] (@CustomerId bigint)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS RLSPredicateResult WHERE (@CustomerId =
CAST(SESSION_CONTEXT(N'customer_id') AS bigint))

GO

 -- two table example
 CREATE SECURITY POLICY [dbo].[RLSPolicy]
 ADD FILTER PREDICATE [dbo].[RLSPredicate] ([customer_id]) ON [dbo].[user],
 ADD FILTER PREDICATE [dbo].[RLSPredicate] ([customer_id]) ON [dbo].
  [user_email]
  WITH (STATE = ON, SCHEMABINDING = ON)
  GO

  -- Sample Query
  EXEC sp_set_session_context @key=N'customer_id', @value='1231312'
  select top 1000 * from [user] where customer_id = 1231312

With the above design, EXEC sp_set_session_context always needs to be executed before any user query hits the database, however we have had cases where the application team forgot to add code EXEC sp_set_session_context in new projects.

The problem is that the there is no way to guarantee that queries coming to the db always have EXEC sp_set_session_context in them. The queries come in all forms, EXEC, sp_executesql, adhoc etc.

I would like to know if there is any way to redesign the filter predicate so that RLS is enforced from within the database and remove dependency of running EXEC sp_set_session_context before every query.

There are more than 500K customer_id values.


texts: advantages of single-leader (transactions) over multi-leader replication

texts:
I am reading the excellent book "Designing Data-Intensive Applications" which I wholeheartedly recommend, but I'm confused by a section comparing multi-leader (i.e. multi-writer) replication to single-leader replication. I understand the basic difference: In multi-leader, multiple leader nodes can accept writes, each leader sends its writes to the other leaders, and you have conflict-resolution rules to decide how to merge them. Single leader solves concurrency using transactions.

The following two paragraphs describe how multi-writer can be more challenging because conflicts aren't resolved right away. My question is afterward.

  [This paragraph and the diagram are describing multi-leader.] For example, consider a wiki page that is simultaneously being edited
  by two users as shown in Figure 5-7 [copied below]. User 1 changes the title of the
  page from A to B, and user 2 changes the title from A to C at the same
  time. Each user’s change is successfully applied to their local
  leader. However, when the changes are asynchronously replicated, a
  conflict is detected. This problem does not occur in a
  single-leader database.

  In a single-leader database, the second writer will either block and
  wait for the first write to complete, or abort the second write
  transaction, forcing the user to retry the write. On the other hand,
  in a multi-leader setup, both writes are successful, and the conflict
  is only detected asynchronously at some later point in time. At that
  time, it may be too late to ask the user to resolve the conflict.

I see the difficulty with multi-writer here, but I'm skeptical that single-writer would be much better.

Consider the most likely chain of events when two people edit a Wikipedia page at roughly the same time: 1) Person 1 loads the edit page, takes 3-5 seconds to edit the title and submits. 2) Person 2 loads the edit page, takes 3-5 seconds to edit the title and submits. Each database transaction to apply the edit is only a few milliseconds, so it is far more likely that that the updates will happen one after the other, than that they will happen at the same time. Therefore if your concern is that one of these 2 people's updates will be lost, you need to address the potential for conflicts at the application level somehow; transactions won't really help you.

Furthermore in the case where the two transactions do overlap, it doesn't help the users to simply delay one of the transactions until the other is done. Once it resumes it will still overwrite the first user's data.

So my question is, is there some helpful transaction technique I'm missing that would actually be useful here? It's been a while since I tried to use transactions so my technique is rusty.

The best improvement I can think of: add AND title='A' to the end of both UPDATE statements, and add a second statement to the transaction that checks the number of affected rows, and rollback if that is equal to 0. The rollback would have no effect but it would indicate a failure to the client. But this is a bit hackish.

I don't think it would help to begin the transaction with a check (i.e. SELECT * FROM pages WHERE title='A' and make sure you get something back). Both transactions could possibly see 'A' at the beginning even though only one transaction would win out.


texts: Optimal way of loading, registering, indexing and querying a huge XMLDB

texts:
I've got 35 million highly structured XML documents, with a total size of 500 GB. An XSD is available for this data, and the end goal is to transform the data into an RDB. All fields can be considered varchar, number, or date; except maybe one or two CLOB fields. The XSD structure contains a lot of 1-n relationships, and sub-nested structures, so is quite complex.

The data has to get loaded once, and then transformed into an RDB once, so speed of update/insert queries is irrelevant. Also the speed of loading/index creation is not so important, since it only has to run once. As long as it finishes within a week or two i'm happy.

At the moment I'm setting up the XMLDB by registering the XSD as Object Relational, with create objects and create tables set to true. For now, I haven't added any other significant annotations to the XSD, and I'm only loading about 50MB's of data as a test. I'm using a whole set of XMLTable/flwor queries to transform the XML data into RDB tables. I fear scaling this to 500 GB will cause queries that run for eternity.

As I'm quite new to XMLDB, and there are -a lot- of possible tweaks and settings in the XMLDB documentation, I'm wondering if there are some crucial features/annotations/optimizations.... i should be looking out for that can have an impact on the overall speed of the final queries, or other parts of the process.

This is a very broad question, but, any tips are welcome, and if there's more information that might be relevant, ask away!


texts: Log size is different between primary and secondary node with log_since_last_log_backup_mb

texts:
We get log backups from the secondary node. But now I see that log_since_last_log_backup_mb in sys.dm_db_log_stats returns null for databases which are in Availability groups, so Ola Hallengren's log backup procedure with

@LogSizeSinceLastLogBackup = 25,@TimeSinceLastLogBackup = 21000

limitations works on those databases. The backup process is currently working on the secondary node.

But I could not understand why it returns null? Have you encountered such a situation?

The following pictures show result of log size since last log backup on the primary and secondary nodes.

Primary Node

Secondary Node


texts:
I have a date dimension table in which I need to add a new column in which I define the iteration of the day of the week within the month (2 for the second Mon/Tue/Wed/Thu/Fri/Sat/Sun etc).

Is it possible to do this be making calculations solely on the date column of the table, which is of type 'date'?


texts: Syntax for correctly capturing current user in a trigger

texts:
So this worked as a test:

CREATE TRIGGER `Metrosky_spotters_2020_au`
BEFORE UPDATE
ON `Metrosky_spotters_2020`
FOR EACH ROW
SET NEW.trnmswid = 5000;

Now I'm getting syntax error substituting "5000" with  CURRENT_USER()

per chapter 6.4 MySQL 8.0 reference this would be correct reference...but not allowing the info to be passed?


texts: Convert multiple rows into different columns - SQL

texts:
I am using teradata for some data modification. My table current looks like:
|Appl_id| Account_no|

|123    |40001|

|123    |40002|
|125    |40233|
|125    |40234|
|125    |40235|
|126    |40647|

and I want to convert this into :
|Appl_id|Acc_no1|Acc_no2|Acc_no3|
|123    |40001  |40002  |   -   |
|125    |40233  |40234  |40235  |
|126    |40647  |  -    |   -   |

Since the number of accounts can go upto 9, I would not be able to use min max average as a work around.Not sure if partition by would be applicable here as well.
What I think I can do is order by appl_id, Account_no and then select row1, row2, row3...but I am not sure if I can do that.
Please suggest
thanks


texts:
I cant find my proxy user to "Run as" that user !? I am confused which kind of proxy user is appropriate to run a job with a simple T-SQL command.
(I have created a credential and proxy but cant find it under the "Run as" in my Agent job).Please advise!


texts: Options for copying data out of continuously updating tables

texts:
I work with a pretty large (O(10^1) TB) DB2 (LUW, v. 9.7) database.  It's got data coming in on a continuous basis via Golden Gate replication from another DB2 database.  It's used for business intelligence and analytics.

Now I'm working with a group in the parent company which is trying to build an enterprise data warehouse.  They want to collect data from their own databases, as well as from their acquisitions (like my site).  To this end they purchased an Oracle BDA appliance (Cloudera Hadoop), and sometime soon an Oracle Exadata box will be stood up.

Putting aside the fact that the target database is Hadoop, not a traditional RDMS, I'm having a hard time coming up with solutions that will faithfully copy the data out of the source database, given that rows are not only being continuously inserted, but also updated.  (As far as I can tell, rows are never deleted.)

Question

I'm interested in what the landscape of possible approaches looks like, as well as which approaches will scale and not be too much of a performance burden on the source database?

Current Solution

Currently we copy data to Vertica in-house, using a home-built solution. Small tables are dumped on the target and then copied in their entirety to Vertica. Large tables have a trigger that updates a table with a single row. That datum records the oldest value of a timestamped and indexed column seen in any row that's inserted or updated. All the SELECTs are done as uncommitted reads. This appears to work, but we do transfer a large amount of data; the new project requires transmission over a much greater distance and with presumably less bandwidth. Moreover, this process is only run once per week. While I don't think a lag measured in minutes is required for this new project, the principals might not be very happy with a weekly refresh.

Possible Solutions

Here's what I've brainstormed so far:

A vended replication solution like Golden Gate.
Some in-house solution that ships transaction logs (probably beyond our dev capabilities).
A trigger that exports any inserted or updated row.  I assume this would be a horrendous performance hit on the source DB.
A trigger that records the primary key of any inserted/updated row.  Also would appear to be a big performance hit.
Adding an indexed timestamp column for time of last modification, and an accompanying trigger to modify it on update.  The DBAs I work with claim this would be a performance hit (I can't really tell if it would be superior to numbers 3 and 4 above).  Moreover, it adds the complication that the upstream data source doesn't have this column, with possible implications for the current replication process between the two DB2 databases.


texts: Differences Between Setting Up Transaction Log Shipping through Wizard vs Scripts

texts:
There appears to be a fairly significant difference after setting up transaction log shipping (in Standby/ Read-Only mode) through the SSMS wizard, which generates jobs that then call on outside .exe to handle the backup and restore vs doing the log shipping through a hand-written script:
Backup:

BACKUP LOG [DBName]
TO DISK = 'Z:\DBName_TimeStamp.trn'
WITH NAME = N'DBName_Log', INIT, FORMAT;

Restore:

EXEC [LSServer].[master].sys.sp_executesql N'RESTORE LOG [DBName] FROM DISK = N''B:\DBName_TimeStamp.trn''
WITH STANDBY = N''B:\DBName.standby'';';

When utilizing the log shipping through the wizard the database seems to be ready immediately and does not experience any lag time the first time any stored procedure is executed/ query run. However, when I set up the backup and restore through my script above the first time a stored procedure is executed against the recently restored database it takes 10x or more time to complete (I originally asked about this issue here: After Restoring Log Shipping to Secondary Server, First Stored Procedure Execution is Slow ).I've eliminated all possibilities of different disks/ hardware etc. causing the problem as the issue is now affecting the same instance/ database that was originally using the log shipping set up by the wizard and has begun to experience the same slow initial execution/ query after restore.The .trn file sizes look the same which ever method I use.

What can be done to identify/ eliminate this difference without going through the SSMS wizard?

Some additional information included in the linked question:

The only difference being the primary server executes with 1.4 seconds of Wait time on server replies and the secondary takes 81.3 seconds.I do see a large number of PAGEIOLATCH_SH locks from the first execution, as you predicted:

diff after first exec vs diff after second exec
waiting_tasks_count    10903    918
wait_time_ms          411129  12768


texts: MySQL JOIN query takes too much time to get the result

texts:
I have a MySQL query as follows which JOINs 8 tables. When I use 3 tables to get the data the result is getting within 10 seconds. But whenever I add one more table the fetching time goes upto 1 minute. And if added more it takes infinite time. Any idea how to resolve this problem?

This is my query:

SELECT  c.`user_name`, e.`event_name`, e.`event_code`, e.`event_id`,
        COUNT(distinct ep.`participant_id`) as participants,
        COUNT(DISTINCT pm.`program_material_id`) as material_count,
        COUNT(DISTINCT ev.`event_news_id`) as news_count ,
        COUNT( DISTINCT  es.`event_speaker_id`) as speaker_count,
        COUNT( DISTINCT  epr.`event_program_id`) as program_count,
        COUNT( DISTINCT  sw.`social_id`) as socail_wall_count
FROM `event` e
LEFT JOIN `event_participant` ep ON ep.`event_id` = e.`event_id`
LEFT JOIN `program_material`  pm ON pm.`event_id` = e. `event_id`
LEFT JOIN `event_news` ev     ON ev.`event_id` = e. `event_id`
LEFT JOIN `socialwall` sw     ON sw.`event_id` = e. `event_id`
LEFT JOIN `event_speaker`     es ON es.`event_id` = e. `event_id`
LEFT JOIN `event_program`   epr ON epr.`event_id` = e. `event_id`
LEFT JOIN `event_customer`    ec ON e.`event_id` = ec.`event_id`
LEFT JOIN `customer`          c ON ec.`customer_id` = c.`user_id`
GROUP BY e.`event_id`
ORDER BY participants DESC
LIMIT 0,10

I indexed all tables' primary keys and the columns which I used to JOIN in the subsequent tables. Here event is the master table and all other tables will have event_id.

Giving the system specs if it might helpful,

SPECS

mysql  Ver 14.14 Distrib 5.7.27, for Linux (x86_64) using  EditLine wrapper

Intel(R) Core(TM) i5-7200U CPU @ 2.50GHz- Thinkpad


texts: problem with column oriented dbms features

texts:
I am currently looking for a free(or at least low cost) column oriented DBMS  with really good compression abilities (at least 10:1 ratio) and fast response time. Having evaluated multiple open source systems (monetDB, Infobright CE...) i couldn't find what i was looking for. I am wondering what is the best approach among :

1- adapting an open source system to my requirements (adding multi-threading or compression...)?

2- building a custom storage/query system from scratch

3- waiting for mariaDB column store (a new system based on an infiniDB fork) before making a decision

On a side note i wonder if the source code available on Github is that of infinDB community or entreprise edition ?

i am limited to one server with 32 GB RAM and around 200 GB of initial raw data, with that in mind i welcome any suggestions or alternatives


texts: Parameter Sniffing - Running query in code vs in management studio

texts:
I just had a problem at work where a stored proc, being called by some C# code, was very slow. When I ran the same stored proc with the same parameters in Management Studio, it was very fast. After a little Googling, I followed the suggestion in this answer and it solved my problem.

I don't like fixing things without learning how they work, so I decided to read up on "parameter sniffing". My understanding is that SQL Server occasionally builds and caches an execution plan for the stored proc, and optimizes that plan based on whatever parameter values it happens to see at the time. So if you pass in different parameters later, you might get bad performance.

But in my case, I ran the stored proc with exactly the same parameter values from Management Studio and C#. Management Studio was fast, C# was slow. I would expect both to use the cached plan, so how could it be fast in one place and slow in another?

I unfortunately can't paste the code, but I can provide any additional details necessary. Just trying to understand this!


texts: mongodb restore multiple collections with single command

texts:
How do I restore multiple collections at once in mongodb.

I have tried

mongorestore -c Role -c UserAccount -c Permission -d movie-app dump/

and I got an error file dump is a directory, not a bson file

I can restore single collection at a time I have to  specify the bson file like

  mongorestore -c UserAccount -d movie-app dump/movie-app/UserAccount.bson

I need to know how do I restore multiple collection with one command.


texts: Scheduled Job Does not work in oracle SQL*PLUS

texts:
I am just a beginner in Oracle and can't seem to get this job running :-

I want to delete old rows from my database. And created a job as follows :-

CREATE OR REPLACE PROCEDURE delete_trips
AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Here');
        DELETE FROM trip y where sysdate > y.arrival_date;
    END;
/
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name        => 'delete_old_rows',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'EXECUTE delete_trips;',
        start_date      => sysdate,
        repeat_interval => 'FREQ=SECONDLY',
        end_date        => add_months(sysdate,12),
        auto_drop       => FALSE,
        enabled         => TRUE
    );
END;
/

After executing the above code I can see that the job 'delete_old_rows' is scheduled in DBA_SCHEDULER_JOBS, but neither the rows get deleted, nor output is produced in the terminal. I tried logging out and logging in again, and yet no row was deleted and no output was produced. And rows satisfying the condition for delete do exist. Please help. What am I missing ?


texts: potgresql foreign key index unused for some columns on one specific server

texts:
I have a table publication, with two columns type_id and sub_type_id (there are, of course, more columns, but they are not important) on a server, all containing int8 foreign keys to tables type and sub_type, respectively.

My problem is that while indices are present for both columns, they are not used for sub_type_id, so from the below SQL queries:

EXPLAIN ANALYZE SELECT * FROM publication WHERE type_id=1068114;
EXPLAIN ANALYZE SELECT * FROM publication WHERE sub_type_id=1068114;

The first shows an index scan, the second shows a sequential scan. This is even true if I set enable_seqscan=off.

What is even more frustrating is that doing the same on my local server, both SELECTs use indices (both running 9.4 postgres on debian).

There is obviously a difference in either the indices or the columns on the server, but I do not know what. How can I find out why one index is used, and the other is not?


texts: Can I have different publishers and one subscriber in replication?

texts:
I have the same databases in three cities which each database have three tables:

1- tbl_worker
2-tbl_workshop
3-tbl_users
and cities are:city1,city2,city3

city2 and city3 insert informations in their databases.

The city1 database should be updated every 1 hour from data inserted in city1 and city2 databases.

For example, the data inserted in tbl_kargah in city1 and city2 databases should be merged and inserted into tbl_kargah of city1 database every 1 hour.

Will using replication cover my requirement and which replication?

As I read in MSDN the publisher distribute data in subscribers, but I have different publishers and 1 subscriber.


texts:
In a couple of interviews i have had recently, i have been asked to explain my workflow when troubleshooting or resolving an incident with MySQL. Here are ways they have asked the question:

Explain the steps or measures you take to respond to, troubleshoot and resolve a database issue.
Do you use monitoring and graphing tools? Explain in what circumstances you use them?

Personally, if there is an incident, like too many threads or whatever issue is with our database that is having an impact on performance or service (not a db crash), the first thing i do is to check the running processes on that DB using show processlist or using mytop. That normally gives me an idea of the activities going on on that db server. Then i would also look at our xymon monitoring system to see if it detects any issues with that server, e.g., disk issue, Raid issue, memory issue (e.g., swapping), high CPU usage etc. So this is somewhat my workflow when dealing with my first question.

I would like to get some good ideas from other DBAs on their workflows on dealing with database incidents in their production environments. So please help answer my two questions above with your own workflows.


texts: database restore with filestream on Windows Server 2016 renames data file

texts:
I have a database in SQL Server 2008 R2 sp2 that has filestream enabled. Restore of the database on a Win10 machine works as it should.

However, on Windows Server 2016, the data directory (file) for the filestream gets renamed. So the restore on Win10 leaves a CSFiles01 data directory for the same named database. The Windows Server 2016 restore creates the data directory CSFiles01.CSFiles01.

I have no idea why the renaming occurs. Subsequent restore start a CSFiles01_2.csfiles01 pattern. After days of working with this, I'm guessing that Windows Server 2016 is the problem.

Any suggestions?


texts: What are the limitations of Toad for SQL Server freeware?

texts:
I just downloaded the freeware version of Toad for SQL Server. Toad's download site says: "the Freeware edition has certain limitations, and is not intended to be used as a TRIAL for the Commercial edition of Toad for SQL Server." It also says that no more than 5 people from my organization can use the freeware version, and that I'm not entitled to hard-copy documentation, phone assistance,  tech support or upgrades.

Is the product fully functional besides that?


texts: How to free up disk space? which logs/directories to clean?

texts:
I want to free up the disk space on my Linux machine. I've drill down the space usage and found that the following directories have a big size

/u01/app/11.2.0/grid/cv/log
/u01/app/11.2.0/grid/log/diag/tnslsnr/r1n1/listener_scan2/alert (Contains xml files)
/u01/app/11.2.0/grid/rdbms/audit(Contains .aud files)
/home/oracle/oradiag_oracle/diag/clients/user_oracle/host_XXXXXXXXXX/alert(Contains xml files)
/u01/app/oracle/diag/rdbms/crimesys/crimesys1/alert (Contains xml files)

Can I delete contents from these directories? Note: I mean contents and not directories.


texts: How to use OR in IIF statement in row visibility?

texts:
How to use OR in IIF statement in SSRS

=IIF((Fields!txtCurrentSubjectReportName.Value="",
      Fields!txtSubjectArchivedName.Value,
      Fields!txtCurrentSubjectReportName.Value) = "School Life"
      OR
      "My goal for this term..."
      OR
      "Student Reflective Comment", True, False)

Its throwing error


texts: We have 30-40 SQL server instances in different servers (2012 with AlwaysOn). Is there any kind of report that can be made which captures the daily backup status and sends a mail, perhaps, or at least can be appended in an Excel sheet manually and then sent later? Any help would be appreciated.


texts: SQL SERVER – Find Most Expensive Queries

texts:
I am running the following script to find most expensive queries.

However I want to narrow it down to only queries ran on Saturday 18th October 2014
Is there any way to do this?

SELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

ps I got this script from http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

The server is SQL Express 2012


texts: I am considering making a recommendation to enforce a password verify function that will not allow users to change their own passwords.  My understanding of the PASSWORD_VERIFY_FUNCTION in Oracle is that the function only restricts the password criteria when the user changes his or her own password.  It would not restrict a privileged user from changing someone else's password or a third party IAM software from changing the password, but I cannot find that expressly documented.  Am I correct?


texts:
I would like to lock a table to only allow users to perform read operation in Sybase ASE 15.5, how can I do it? I have tried the below command, but seems there is some confusion. Any idea?

Session A: Perform below commands and after that try to perform insert operation. New records can still insert into locked table (but I think it is not commit the transaction yet). Can still select record from the locked table.

begin transaction
lock table testB in share mode

Session B: Cannot select data from the locked table.


texts:
Searching around the web, I have found conflicting advice on whether there is a performance impact when specifying overly-wide VARCHAR columns, e.g. VARCHAR(255) when VARCHAR(30) will probably do.

I consistently see agreement that there's a performance hit if the entire row exceeds 8060 bytes.  Other than that, I see disagreement.

Is the claim true that The default is SET ANSI PADDING ON = potential for lots of trailing spaces?  As long as the total row width is less than 8060, are there any real performance concerns in over-sizing VARCHAR columns?

Evidence that column width matters

The same goes for CHAR and VARCHAR data types. Don’t specify more characters in character columns that you need.

http://www.sql-server-performance.com/2007/datatypes/

Length is a constraint on the data (like CHECK, FK, NULL etc)
Performance when the row exceeds 8060 bytes
Can not have unique constraint or index (key column width must be < 900)
The default is SET ANSI PADDING ON = potential for lots of trailing spaces

What are the consequences of setting varchar(8000)?

Evidence that column width DOES NOT matter

If you're talking about varchar and nvarchar then no, there is no penalty for allowing a higher field length.

https://stackoverflow.com/questions/7025996/overstating-field-size-in-database-design

The varchar datatype, by contrast, consumes only the amount of
actual space used plus 2 bytes for overhead

http://sqlfool.com/content/PerformanceConsiderationsOfDataTypes.pdf


texts: What is the difference between threads connected and threads running

texts:
I am trying to understand the dashboard in mysql workbench and I saw this chart on bottom left.

threads connected: 149
threads running: 13

This was under a load and threads running increased as I put even more load on the server but it never went high. Is that normal? If threads connected is helping with parallel processing during loads, should it be higher?


texts: Primary key and index column for Merge replication- Id vs GUID

texts:
I currently am revising the tables structure for a few geographically distributed databases- all tables currently have a UUID that is set as primary key. Merge subscription is currently (and in the future) used to synchronize the databases and tables and it uses the UUID column for it.
I am having a few issues with performance.

Update: The UUID is supplied from the application end - not generated during data load.

Based on the research I have done (here is where I got much clarity on the issue Thank you! Thank you! )
https://stackoverflow.com/questions/11938044/what-are-the-best-practices-for-using-a-guid-as-a-primary-key-specifically-rega][1]

Here is the takeaway-

UUID as PK is ok- as long as it is is NOT Clustered
Do not create a clustered index on columns if you are not going to
search by the columns
Identity (bigint) + HostName much efficient as a clustered index

I am currently planning on designing to include an Identity , DatabaseServerName  and Date as a clustered Index and the UUID as a primary key only.

Here are my questions:-

since I DO WANT to enforce FK using the UUID columns - do I still
create a separate clustered index on identity?
All the joins are going to be based on the UUID- will I still benefit
from the clustered index?
Should I rethink joins on the UUID?If so- how should I go about it?


texts: postgres partition trigger and checking for child table

texts:
I found some sample code for creating a partition table.

Basically, setup the master table and partition on date field concatenated with the type of entity it is then, inherit from the master.

The first call of the trigger is fine, but subsequent ones fail because the 'if' condition to check for an existing child table yields false but upon trying to create the table fails for an already existing table.

I have tried 3 ways to check for a child tables existence and none yield correct answers.

These 2 statement demonstrate the contradiction:

SELECT count(*) FROM pg_class WHERE relname='DRIVER_2016_12_28';
count(*)
0

select count(*) from DRIVER_2016_12_28;
count(*)
1

Master table:

CREATE TABLE partition_test
(
  pk SERIAL PRIMARY KEY NOT NULL,
  id VARCHAR(36) NOT NULL,
  organizationid VARCHAR(36) NOT NULL,
  lat REAL NOT NULL,
  lon REAL NOT NULL,
  basetype VARCHAR(16) NOT NULL,
  name VARCHAR(64) NOT NULL,
  updatetimestamp TIMESTAMP NOT NULL
) WITHOUT OIDS;

trigger code:

CREATE OR REPLACE FUNCTION telemetry_insert_trigger() RETURNS trigger AS
$BODY$
DECLARE
  partition_date TEXT;
  partition TEXT;
BEGIN
  partition_date := to_char(NEW.updatetimestamp,'YYYY_MM_DD');
  partition := NEW.baseType || '_' || partition_date;

  IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
    EXECUTE 'CREATE TABLE ' || partition || ' (check (updatetimestamp = ''' || NEW.updatetimestamp || ''')) INHERITS (' || TG_RELNAME || ');';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (id)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' USING HASH (organizationid)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lat)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (lon)';
    EXECUTE 'CREATE INDEX ON ' || partition || ' (updatetimestamp)';
    RAISE NOTICE 'A partition has been created %',partition;
  END IF;
  EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').* RETURNING pk;';
  RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER telemetry_partition_insert_trigger
BEFORE INSERT ON partition_test
FOR EACH ROW EXECUTE PROCEDURE telemetry_insert_trigger();

insert example:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

first output

  [2016-12-28 12:15:12] [00000] A partition has been created
  DRIVER_2016_12_28 [2016-12-28 12:15:12] completed in 33ms

second:

INSERT into partition_test (id, organizationid, lat, lon, basetype, name, updatetimestamp) VALUES ('123','456',12.0,12.0,'DRIVER','SomeName',now());

  [2016-12-28 12:16:15] [42P07] ERROR: relation "driver_2016_12_28"
  already exists [2016-12-28 12:16:15] Where: SQL statement "CREATE
  TABLE DRIVER_2016_12_28 (check (updatetimestamp = '2016-12-28
  12:16:15.467012')) INHERITS (partition_test);" [2016-12-28 12:16:15]
  PL/pgSQL function telemetry_insert_trigger() line 11 at EXECUTE
  statement

The question: How can I effectively check for the existing of the child table so that I don't get this error? If you'd like to point out existing problems with my trigger, that'd be great too


texts: Error replicating password change in MySQL slave

texts:
So I built a master-slave Mysql replication architecture in production because of a database FOO that I wanted to query (slave node) without affecting the performance of the main site (master node).

The thing is, I was afraid of someone breaking replication by inserting data in the slave which would conflict with data coming from master, so as soon as I created the slave node, I removed all privileged users (e.g. dba1, dba2) in the slave node and added a single readonly one (with the SELECT grant): readonlyUser.

The problem I have now is that suddenly dba1 has changed his password in the master node, and the result is this replication error:

Replication reported error 1396:
Error 'Operation ALTER USER failed for 'dba1'@'localhost'' on query.
Default database: 'mysql'.
Query: 'ALTER USER 'dba1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*DAB3A57F75AD41285552E115420DA2EFD756CD19''

Should I prevent the replication of the mysql DB then? I'm afraid that, if I do this, any structure changes for my FOO database will not be replicated, because the structure of the tables is stored in the mysql database, right?

Or should I just ignore errors of type 1396? Is error1396 always related to users or is it more broad?


texts: Login / Password for Oracle Enterprise Manager in VM template?

texts:
I want to setup Oracle Enterprise Manager to access our development Oracle databases.

I downloaded the following VM template from https://edelivery.oracle.com/EPD/Search/handle_go

Oracle VM VirtualBox for Oracle Enterprise Manager Cloud Control 12c Release 3 (12.1.0.3) Media Pack for x86 (64 bit)

I unzipped the OVA files, merged them and imported them into Oracle Virtualbox.

Then I started up the VM, logged into Sqlplus and changed the password of SYSMAN and unlocked that account. Then I pointed Firefox (inside the VM) to https://emcc.example.com:7799/em , where the Login screen of Enterprise Manager was shown.

However, I cannot log in to Enterprise Manager. I tried it with the following usernames/passwords:

SYSMAN / [the new SYSMAN password I entered before]
oracle / welcome1 (the Linux account)
cloudadm / cloud (these are credentials I found on the web)

It always says Authentication failed. If problem persists, contact your system administrator.

What are the credentials to log into Enterprise Manager in these VM templates by Oracle?


texts: What are the pro's and con's of using PostgreSQL vs MySQL for binary data?

texts:
I read that MySQL has binary safe blobs, but PostgreSQL does not.

So far what I've found suggests that "binary safe" is something that really relates to functions and handling of data, not the data itself. It seems that the difference is that MySQL natively has ways of storing and manipulating blobs without having to specify data formatting, but PostgreSQL requires using either "quoted-printable
encodings" or "base64" encoding for binary data.

Is that all there is to it? What else should I worry about with respect to the differences between MySQL and PostgreSQL in the handling of blobs? I'm starting a new project, not migrating data, so I'm just wondering if there are particular reasons to choose one or the other with respect to their handling of binary blobs.


texts: How to rename an index in informix using numbering

texts:
I am currently facing issue of renaming an index to a number.

I have tried the below:

RENAME INDEX 584_1973_tmp TO '584_1973';
RENAME INDEX 584_1973_tmp TO ' 584_1973';
RENAME INDEX 584_1973_tmp TO "584_1973";
RENAME INDEX 584_1973_tmp TO " 584_1973";

None of the above seems to work. Is it possible to rename an index to a number within Informix?


texts: I was wondering (if anyone knows) if Aria database is crash safe . I had some tables with MyISAM which were getting corrupted very often and switch them to InnoDB . However innodb is a bit slower and uses more space. Does anyone know if Aria is good alternative to MyISAM and does not corrupt the table ?


texts: I read somewhere that MongoDB holds db level write lock, does this mean that when someone updates any collection others are not allowed to read other collection or how it works. Also when someone writes anything to the db how MongoDB acquires write lock and what is its granularity?


texts: Join and aggregate result and insert blanks for missing appendants

texts:
Consider these two simplified tables:

CREATE TABLE "sources"
(
    "id" serial NOT NULL,
    "name" text NOT NULL,
    CONSTRAINT "sources_pkey" PRIMARY KEY ("id")
);

CREATE TABLE "values"
(
    "time" timestamp NOT NULL,
    "source_id" integer NOT NULL,
    "value" json,
    CONSTRAINT "values_pkey" PRIMARY KEY ("time", "source_id"),
    CONSTRAINT "values_sources_fkey" FOREIGN KEY ("source_id")
        REFERENCES "sources" ("id")
);

The intention is that one table keeps sources represented by name and the other table keeps timestamped values with a reference to the corresponding source. I took json just as an arbitrary example.

Sample data:

INSERT INTO "sources" ("id", "name") VALUES
    (1, 'Source 1'),
    (2, 'Source 2'),
    (3, 'Source 3')
;

INSERT INTO "values" ("time", "source_id", "value") VALUES
    ('2014-01-01 12:00:00', 1, '123'),
    ('2014-01-01 12:00:00', 2, 'true'),
    ('2014-01-01 12:00:00', 3, '1.23'),

    ('2014-01-01 13:00:00', 1, '456'),
    -- hint: no data for source 2
    ('2014-01-01 13:00:00', 3, '4.56'),

    ('2014-01-01 14:00:00', 1, '789'),
    ('2014-01-01 14:00:00', 2, 'false')
    -- hint: no data for source 3
;

I now want a query returning all the values and their sources grouped by timestamp.

The best (and probably most simple) I could come up with is this:

SELECT "time", array_agg("name"), array_agg("value") FROM "sources", "values"
WHERE "sources"."id" = "values"."source_id"
GROUP BY "time" ORDER BY "time";

"2014-01-01 12:00:00";"{"Source 1","Source 2","Source 3"}";"{123,true,1.23}"
"2014-01-01 13:00:00";"{"Source 1","Source 3"}";"{456,4.56}"
"2014-01-01 14:00:00";"{"Source 1","Source 2"}";"{789,false}"

This works good for me, however it would be a lot better if it would contain blank values for missing sources within a group of timestamp. The desired result would look like this:

"2014-01-01 12:00:00";"{"Source 1","Source 2","Source 3"}";"{123,true,1.23}"
"2014-01-01 13:00:00";"{"Source 1",,"Source 3"}";"{456,,4.56}"
"2014-01-01 14:00:00";"{"Source 1","Source 2",}";"{789,false,}"

If there was a fourth source for example, with no values at all, I want it to appear in the result as well. What I basicly want here is a complete list of all sources and their values grouped by timestamp with blanks for sources and their values in case there is no record for that specific group.

What's the best way to accomplish this task? And also, is this even a good approach of doing it like this? I mean, it would be even better if I could get a column for each source and value pair. Or probably not? My goal is to make it as much CSV-friendly as I can get it. As it is now, I do quite a lot in the next layer of business logic to get it into that, but it feels like I am not using my DBMS as I should with all its potentials.

As a side note, this is what my CSV file looks like:

timestamp;Source 1;Source 2;Source 3
"2014-01-01 12:00:00";123;true;1.23
"2014-01-01 13:00:00";456;;4.56
"2014-01-01 14:00:00";789;false;


texts: Oracle TNS Permission denied when creating database

texts:
I hope you can help me.

I need to restore an Oracle 9i (9.2.0.6.0) database on a Windows Server 2003 R2 x64 from a cold backup.
I managed to get the 9i installer and the version patch (since I was told I need the exact Oracle version to restore cold backups).

I installed 9i and the patch, but the Database Configuration Assistant throws an "ORA-12546:TNS Permission denied" error when creating the database (In the first step "Creating and starting Oracle instance").

Windows firewall is disabled, and my user (Administrator) is in the ORA_DBA group.

I'm new to Oracle, therefore I don't know which steps should I follow in order to solve this, and I haven't been able to find any solution on the web (maybe because 9i support ended long time away). I'm assuming if I can't even create a database, I won't be able to restore the cold backup either. Please help

Thanks in advance


texts: How to Represent Equation with Variables in Relational Database

texts:
I am building an application to store the different formulas used for different subjects in school, and I have drawn the following database diagram to depict that scenario:

I am having trouble figuring out how to represent an equation in a relational database.

Each equation is made up of variables of different types. For example,

E=mc^2

Has the following components:

E, which is energy in joules(J), which is actually kg*m^2*s^2.
m, which is mass in kilograms(kg), which cannot be broken down further
c, which is the speed of light, with the value of 3*10^8, and units of m/s

The problem is not the representation of the string of the equation within the database. Rather, I want to represent the relationship between the individual variables within an equation.

How should I go about representing these different types of variables within the same database?


texts: Identifying processes that open MySQL connections

texts:
I need to identify which application process issues a large number of MySQL connections on a Linux system.
The show processlist sql command or tools like innotop do not really help, as they only show db user and host, and i doubt that i catch all statements that way.
I was thinking to use something on the network level, like tcpdump or netstat. Any suggestions?


texts: Figuring out the physical size of a group of tables in SQL server

texts:
I have a group of about 30 tables, and I want to know the physical size on disk of all of these tables (plus indexes).

Is there an easier way of doing this than through the GUI in SQL server 2008 R2?


texts: I need to export all tables (500) of a sql server 2008 R2 database to XML. What is the best and fastest way to do that?


texts: How to deny viewing definition of server level trigger?

texts:
I have several server level triggers, that I want users to deny viewing definition of. I control server level permissions, users aren't sysadmins etc.

I've tried 2 options:

DENY VIEW ANY DEFINITION to public

it prevents users from even seeing that those triggers exist (and who knows what else), so I don't want to go with that option

CREATE SERVER TRIGGER ... WITH ENCRYPTION

It's an option, but it says that those triggers won't be replicated, and I need to have those triggers replicated (although I can recreate them on replicas, if necessary).

Could you advice if there any other options?

Ideally it would be nice to have something like

DENY VIEW DEFINITION ON OBJECT::trigger_name TO PUBLIC

but I can't make it work for server level trigger


texts:
I've a problem about my ER diagram. Assume that I want to draw a diagram with student, member and team. Here is my specs:

A student does not have to be a member of any team.
A student could be a member of at most one team.
member has a unique member ID.

I did my ER Diagram:

                                                  member-id (key attribute)
                                                            |
--------                          -                    -----------
- team -------------------- -  - mem -   ------------- - student -
--------                          -                    -----------

As I said before, student does not have to be a member of any team. In this case, my diagram works fine? I asked it because of the member_id is a unique key.


texts: Restoring differential backup creates DEFUNCT log file?

texts:
Here is my problem.  I'm trying to move a database to a new server via a full restore, then cutover with a quick differential backup/restore.  I can do a full restore without a problem, but when restoring the differential backup, I get the following warning:

  Msg 3127, Level 16, State 1, Line 1 The file 'Database_Log2' of
  restored database 'DatabaseName' is being left in the
  defunct state because the database is using the simple recovery model
  and the file is marked for read-write access. Therefore, only
  read-only files can be recovered by piecemeal restore.

The database restores and is considered online, but any backup operation fails due to this DEFUNCT file with the following error:

  Msg 3636, Level 16, State 2, Line 1 An error occurred while processing
  'BackupMetadata' metadata for database id 10 file id 6. Msg 3046,
  Level 16, State 2, Line 1 Inconsistent metadata has been encountered.
  The only possible backup operation is a tail-log backup using the WITH
  CONTINUE_AFTER_ERROR or NO_TRUNCATE option. Msg 3013, Level 16, State
  1, Line 1 BACKUP DATABASE is terminating abnormally.

If I do a RESTORE FILELISTONLY on the full and differential both give me the same output, which matches what I see from sys.database_files on the source database.  Server is SQL2012 SP1, on Developer edition.

I can do a full backup, and immediately afterward do a differential, and restore these files to a different database on the same server and see the exact same problem, so there is something with how the differential is created that is causing this.  If I restore the full backup WITH RECOVERY there is no problem.  I do not know if this file used to exist on this database, but it is entirely possible this file used to exist and was deleted a long time ago.  If I query sys.database_files on the restored database, the DEFUNCT file has a value for drop_lsn, which seems to confirm this.  Currently in the source database there is only one filegroup (PRIMARY), 4 data files and one log file.

Any ideas?


texts: selecting and Inserting data from two different servers

texts:
I have two server mysql and psql. I am fetching data from mysql server database table and inserting it into my psql server. The way I am doing is by selecting all the column from mysql server and saving it into an array.

sql->prepare("SELECT id, date,studentName, FROM student", array());

variablArray= array();

Then inserting the data in psql with the help of implode().

psql->prepare("INSERT INTO student",implode(",", $variableArray);

The problem is every time I ran the query I too 45 min to complete, because I truncate the psql database table before inserting so that the query can run.

Is there is an another way to do this?


texts: Implications of many materialized views in Postgres?

texts:
I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be calculated for a specific time period using a Postgres function and then stored in a materialized view to avoid having to recalculate every time.

The system I'm working on serves multiple businesses, each with their own calendar and customers. My thought was that a materialized view would be created on a per-business basis. However, if the service is successful, there could be well over a 1000 businesses using the system. That said, are there issues with having that many materialized views? And if so, is there a better pattern that would scale as the service grows?

FYI: I'm running Postgres 9.4

Thank you!


texts: Does it make sense to use full recovery if the data and log files are on the same HD?

texts:
We have a database that was set up to use full recovery and my guess is that the reasoning was only to prevent data loss if a failure happened between full backups.

We make daily full backups of the database and we have no need to recover to any point in time previous to our last full backup.

Our data files and log files are in the same hard drive. From my experience (as programmer, I'm not a DBA), most database failures I've seen were related to disk failures and so I wonder if this setup makes any sense. I imagine that if the hard drive fails, we wouldn't be able to recover using the transaction logs.

So my question is twofold:

Is the most likely cause of a database failure, a hard drive failure, or are there other common reasons that would justify this setup ?
Would it make more sense to switch to the simple recovery model and convince the business that in the worst case scenario they would have to re-input data for the day ?


texts: Prevent SSMS from seeing the server's file system

texts:
I have several users who share an MS SQL server 2017 under my administration. They should not see (or even be aware) of the other users and their data on that server. Each user has his own database. They can do whatever they want with their database.

I'm using SQL Server's Partial Containment feature to lock the users in place. The logins are created inside the database. This works well, as they don't see other user accounts or databases this way. The DB logins are added to a Database Role that I create with this command:

USE dbname
CREATE ROLE dbrole
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, CREATE VIEW, ALTER ANY SCHEMA TO dbrole
DENY EXECUTE TO dbrole

I freshly create a db login account and only add it to said role. The user has no other permissions (that I'm aware of).

The only problem left is that SSMS is still able to browse the server's file system. If I right click on the database and choose Tasks -> Restore -> Database, then select Device: -> [...] and Add a file. This allows SSMS to browse the server's file system, which I would like to deny. The user cannot actually restore the DB, but he can browse the file system.

This question here suggests that SSMS is using the stored procedures xp_fixeddrives, xp_dirtree and xp_fileexist. However, those stored procedures return empty results when executed as a user with the permissions of said group. I've read that this is the behaviour when a user is not a member of the sysadmin role. This already confuses me slightly since I explicitely deny EXECUTE to the dbrole, yet the user can still execute the stored procedures. But still, when browsing the file system through SSMS, it is not empty.

Where does SSMS get the file system information from and how can I prevent this?

edit: I also just noticed that SSMS is able to retrieve a list of all DB backups existing on the server for all databases. Again, I don't how it gets this information and how I can prevent it.


texts: Getting two values for a colums which is not PK

texts:
I have a table with 4 columns

id,C_number,C_reopen,c_closed

Where id is the pk.
I want to pull rows which has two different values for c_closed for the same c_number. Not c_number is not pk hence can have duplicate values.

create table claim
(
      id not null number(10),
      c_number string(50),
      c_reopen date,
      c_closed(date)
);

Sample data:

insert into claim(111 , 'c101', '10/1/12015','15/1/2015');
insert into claim(112, 'c102','10/2/2015','15/2/2015');
insert into claim(113,'c101','18/1/2015','28/1/2015');

So, here for the same c_number-c101, I have two closed one is 15/1/2015 and the other 28/1/2015. I want a query to retrieve c_cumber which have more than one value for close.


texts: Problems connecting to Windows-based Postgresql

texts:
Other computers are having trouble even finding the running Postgresql 9.1 on my Windows 7 machine.  Local connections work just fine of course.

In my pg_hba.conf I have:

#office subnets
host    all             all             192.168.##.0/24            md5
host    all             all             192.168.##.0/24            md5

In postgresql.conf I have (Yes, port 5433 is intentional):

listen_addresses = '*'      # what IP address(es) to listen on;
                # comma-separated list of addresses;
                # defaults to 'localhost', '*' = all
                # (change requires restart)
port = 5433             # (change requires restart)

Upon running netstat -a -b in the cmd I find:

 Can not obtain ownership information
   TCP    0.0.0.0:5433           XXXXX-PC:0           LISTENING

Heck, I even made a firewall exception:

Protocols and Ports:
   Local port: Specific Ports, 5433
   Remote port:  All Ports
Scope: Any IP Address
Advanced: Profiles: Private, Public
Programs and Services:  Apply to this service:  postgresql-x64-9.1

The typical response from pgAdmin is always the dreaded:

Server doesn't listen

could not connect to server: Connection timed out (0x0000274C/10060) Is the server running on host "...." and accepting TCP/IP connections on port 5433?

Any ideas?


texts: How to get "mysqlbinlog" utility for Ubuntu?

texts:
I can't find "mysqlbinlog" utility in my Ubuntu server, i can see mysql, mysqldump, mysqlshow etc... command line utilities but not mysqlbinlog utility.

Can someone please help me from where I can get this utility and install it?


texts:
Each user is on exactly one team. A team has exactly one user in the team_lead role. How can we normalize this in the database? Examples in pseudo-SQL:

Strategy 1:

table user:
id       int  pk
team_id  int  fk references team(id)
is_lead  int  //1 or 0

table team
id            int  pk

Problem: Multiple users on one team could be marked as the team lead

Strategy 2:

table user:
id       int  pk
team_id  int  fk references team(id)

table team
id            int  pk
team_lead_id  int  fk references user(id)

Problem: There is no guarantee the referenced team lead is actually a member of that team

Is there any way to normalize this in the database design, or do we have to use constraints or enforce on the front-end?


texts:
Hi im trying to develop a database design in which its gonna have 100K+ tables residing in it.
My major concern here is will Mysql be able to hold that much amount of tables with no performance degrade.
Also mysql has a limit of open_file_limit of 64K but i know it can be overcome with ulimit, but again what would be the effect on performance when i have huge open files.
i dont want to have Error:24 in long run.
Also whats other parameter should i tune or have to keep in mind.
Did anyone try to have 100K+ tables in their environment with good performance???
My Server will be running Percona 5.6.14 x64 on RAID10 with BBU and 30GB RAM.

Regards
Rohan.


texts:
TLDR; If I store a phone number in MySQL(or any DB with equivalent constraints), would the correct format be E.164 values into a VARCHAR(32) field?

Have been reading up on format/storage of international phone numbers. In the past for various reasons it was suggested to store as a string due to this data being an identifier not an actual number(along with issues like leading 0's). I'm aware that I should not attempt to parse/validate phone numbers via regex, and that libphonenumber developed by Google is generally recommended for handling this and producing E.164 formatted values.

I'm assuming it's safe to say E.164 is the best suited storage format for phone numbers today. As the leading + does not seem required and that leading 0's would not occur in this format? I've seen BIGINT(15) suggested for storing this in MySQL databases. This would require an additional field(string?) to support phone numbers with extension numbers. It's not stated on the Wikipedia article but I've seen many mentions in blog posts that the E.164 format supports extensions by appending ";ext=12345" so would it be more advisable to store into a VARCHAR(32) field? "+19995556789;ext=12345", 32 should be the maximum length with this format, so the length of 32 is appropriate? <+><;ext=>

EDIT: I'm not sure exactly what the extension value should be, according to this it is 11, however the article linked to has been modified since and archived by Apple, there is no clear information on extension length there. Would it be best just to a higher limit such as VARCHAR(50), or go with BIGINT(15) and a additional field for extensions that is nullable?


texts: Finding the first parent (manager) of an employee that makes twice as much in a self-referencing table?

texts:
Example you have the following table

CREATE TABLE emp (id,name,salary,manager)
AS
  VALUES
    ( 1  , 'james' ,  10000 , null ),
    ( 2  , 'alex'  ,   5000 , 1 ),
    ( 3  , 'Alice' ,   4500 , 1 ),
    ( 4  , 'Jone'  ,   3000 , 3 ),
    ( 5  , 'Omar'  ,   2200 , 2 );

Anyone who is a manager of your manager is also your indirect manager so Omar has Alex and James as indirect managers. I need a query that will get me the first indirect manager who has twice salary or more than the employee.

So result should be:

ID | Manager
1  | null
2  | 1
3  | 1
4  | 1
5  | 2


texts:
I'm quite green in mysql, but I have been assigned a task for which I completely lost. I have a unique codes table in mysql and then I have another 6 tables for individual clients. I would like to extract those codes which have a value in cat_code = 'wine' from all 5 clients and item_grp_code ='00101' from 1 client since the last client has no cat_code?

Any idea how to do around this?

This is what I tried so far:

SELECT DISTINCT
  items.code, nxr.departmentcode, nxr.itemgroupcode, las.departmentcode,
  las.itemgrpdesc, las.desc1, nxr.desc1
FROM (
retail_append.items items
left outer JOIN
retail_append.nxr nxr
  ON (items.code = nxr.code))
left outer JOIN
retail_append.las las
  ON (items.code = las.code)
WHERE (las.itemgroupcode = '00101') OR (nxr.categorycode = 'wine');


texts:
I have a table:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `status` enum('new','paid') NOT NULL DEFAULT 'new',
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  `can_be_closed` tinyint(1) NOT NULL DEFAULT '1',
  `close_reason` varchar(255) DEFAULT NULL,
  `interval_before_close` int(11) NOT NULL DEFAULT '10',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

And following select:

SELECT `id`, `status`, `close_reason` AS `closeReason`, `createdAt`, `updatedAt`
FROM `t1` as t
WHERE (
`t`.`status` = 'new'
AND `t`.`updatedAt` < '2018-06-22 10:14:36' + INTERVAL `interval_before_close` MINUTE
AND `t`.`can_be_closed` = true
)

For this query, explain return a full table scan.

I tried to add the following indexes:

ALTER TABLE `t1`
ADD INDEX `updatedAt` (`updatedAt` ASC);

and:

ALTER TABLE `t1`
ADD INDEX `updatedAt` (`updatedAt` ASC, `interval_before_close` ASC);

but without any change in explain.

Fidle ex.: link


texts: Common practices to determine what database technology to use

texts:
Supposing I've started a new project that needs a database for persistence purposes.

What are common practices to determine which database technology (MSSQL, MySQL, MongoDB, etc...) would best fit the requirements of the project?

What are important considerations to keep in mind when looking for a proper database technology?


texts: How to change default collation for any charset in MySQL?

texts:
How to change the default collations listed by SHOW CHARACTER SET?

For utf8 I need to change it to utf8_unicode_ci.

Or is there any switch to enforce the _unicode_ci over _general_ci for any UTF8xx charsets?


texts: Can't create SSIS package from Import and Export Wizard

texts:
Problem

I can't create a SSIS package from SQL Server Import and Export Wizard.

I get to the very last screen "Run Package" and only have the option to "Run Immediately" . At the bottom of said window I get a paragraph stating:

  In SQL Server Express, Web, or Workgroup, you can run the package that the Import and Export Wizard creates, but cannot save it. To save packages that the wizard creates, you must upgrade to SQL Server Standard, Enterprise, Developer or Evaluation.

Context

I have several CSV files located on a client system that I want to load into a remote system hosting the SQL Server instance.

The Wizard is being run on the client system.

"Run Package " of the wizard DOES work and data loading is possible.

System permissions are such that the file system of client and host are invisible to each other. I would have BULK INSERT'ed otherwise.

Technical Details

Client System:

Microsoft SQL Server Management Studio      10.50.4000.0
Microsoft Data Access Components (MDAC)     6.1.7601.17514
Microsoft  MSXML                            3.0 4.0 5.0 6.0
Microsoft Internet Explorer                 9.11.9600.18665
Microsoft .NET Framework                    2.0.50727.8669
Operating System                            6.1.7601

Host System:

[Microsoft SQL Server 2008 R2 (SP1) - 10.50.2811.0 (X64)   Apr  6 2012
01:59:29   Copyright (c) Microsoft Corporation  Enterprise Edition
(64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


texts: Playlists design for a music streaming app

texts:
So we have the following tables for a music streaming app,

Table tracks
----
id
name
album
artist

Table album
----
id
name

Table artist
----
id
name

Now, each user is given a feature to create playlists. Hence, a song can be part of multiple playlists belonging to the same user and other users as well. How do I design that part? The closest I have come to is

Table users
----
id
email

Table playlists
----
id
name
by_user

And then create a table for each playlists with the songs included in them.

Table playlist_1
----
track_no
track_name

Table playlist_2
----
track_no
track_name

Table playlist_n (technically incorrect, but you might understand)
----
track_no
track_name

The 1,2...n in the above table names is the value from playlist.id. The problem with this method is that if there are 100,000 playlists created, 100,000 tables are created, which is sort of not the right way I feel.


texts: Is replication from SQL Server 2008 to PostgreSql possible?

texts: Is it possible ? Sql Server as publisher(master) and PostgreSql as slave(subscriber) ? Any type of replication really.


texts: "Unclean server" connections not being cleaned up

texts:
I have an application connecting to a PostgreSQL server via PgBouncer, all running in Kubernetes. Every couple of minutes, the PgBouncer process seems to crash, and Kubernetes restarts it. Just before PgBouncer crashes, I see the following in the logs:

2018-05-29 17:44:59.897 1 LOG S-0xe09d60: my_user/my_database@10.201.0.49:5432 closing because: unclean server (age=112)

Looking at pg_stat_activity after PgBouncer has restarted, I still see old connections from the old PgBouncer process that seem to be hanging around waiting on locks.

What is an unclean connection, and why does PgBouncer crash when closing them?
Should I expect to see connections from old processes remaining in pg_stat_activity? What's the best way of cleaning these up?


texts:
I have a query like:

SELECT a.id, a.name, json_agg(b.*) as "item"
  FROM a
  JOIN b ON b.item_id = a.id
 GROUP BY a.id, a.name;

How can I select the columns in b so I don't have b.item_id in the JSON object?

I have read about ROW, but it returns a JSON object like:

{"f1": "Foo", "f2": "Bar"}

I would need to remap the JSON object once it is fetched to match the proper column keys. I'd like to avoid that and keep original column names.


texts:
I am new in Game development, and want to develop Playing Card game in android but i have no idea that how manage game database for different user (which are connected through Facebook) and their data like their Inventory,mutual friends score game progress, points etc

I want to Know that:

How manage redundancy of data
How to design game database Structure.

Please help me for guide to design database


texts: Why default character_set_server is latin1?

texts:
I am using MySQL 5.5 and when I show variables about charset, I have

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

Should I need to change the character_set_database and character_set_server to utf8?


texts: MySQL Call Stored Procedure along with an UPDATE statement

texts:
I am pretty much convinced that this is not possible but again I wanted to see if there is a possibility.
I have an UPDATE statement and I want to call a SP for each row updated. Is it possible someway to include the SP call along with the UPDATE statement?
Using a temporary table or another select query seems like a solution here though.


texts: Setting up HA and a synced up reporting box all together using SQL Server 2014 Standard Edition

texts:
Scenario:
We are going to upgrade our third party application. Also we are going to upgrade our DBMS from SQL Server 2008 R2 to SQL Server 2014 Standard edition. We also wanted to have a high availability option for our back end. The HA options available for SQL Server 2014 Standard editions are

Log Shipping,
DB Mirroring(safety full only),
Alwayson Failover Cluster Instances (Node support: 2) and*
Replication.

We also want to have a third box which should have the exact replica of data that is present in above two. This box will be used for reporting purpose.
Question:
Please provide the best possible setup that can be made with all the options available so that we can have best HA option and also a reporting box with synced-up data.
Note: VMs is not a problem. Currently we are allocated 3 VMs, we can go for 4 as well if needed.
It will great if somebody can provide their insights.


texts: Attempt to decompose up to second normal form (2NF) in a scenario about students and courses

texts:
1NF:

(student_id, name, start, program, course_code, course_name, faculty, credits, time, grade)

Minimal functional dependencies (identified):

{student_id} → { name, start, program }
{course_code} → { course_name, faculty, credits, time }
{student_id, course_code} → { grade }

2NF:

student (student_id, name, start, program)
course (course_code, course_name, faculty, credits, time)
student_course(student_id, course_code, grade)

Is this 2NF decomposition really correct, or am I completely out of scope?


texts: Change SQL Server 2005 from Standard Edition to Workgroup

texts:
I have been tasked with performing an edition change from SQL Server 2005 Standard Edition to SQL Server 2005 Workgroup Edition, on Windows Server 2003 Standard Edition. Due to a previous failed attempt, I have a few questions:

I have backed up my databases using the Backup tool in tasks when right clicking on a database in the server. Is this the correct approach? Or is there something I am possibly missing?
I keep getting a warning when the installation wizard checks my system, that in order to do the edition change (I can't remember the feature of every edition of SQL Server 2005 so I'm not sure if we’re downgrading or upgrading!), I need to supply the SKUUPGRADE=1 parameter in command prompt. I type that statement (SKUUPGRADE=1) in sqlcmd. I'm assuming this is wrong. Please tell me how to fix this problem, if you know.
After that, I select the default instance then the next step in the wizard tells me there is nothing to upgrade. I can't remember the exact error, but does this mean I need to uninstall my current version of SQL Server first?


texts: How to get all logged in users to SQL Server database for this week?

texts:
I'm using SQL Server 2008R2 and I setup a testing database that I need to know who tried logging in so far. Is there a stored procedure or a View or anything that I can gather these type of information for this week or any specific day?

This is the same server that also has the production database, not sure if this will matter with performance issues as I'm reading online that Traces could affect?

Thanks for any advice.


texts:
Can the following code be converted to database queries?
In our application we have a dictionary, where each value is given by in a table like:
CREATE TABLE dictionary
(
    id serial NOT NULL,
    key text NOT NULL,
    language text NOT NULL,
    value text,
    PRIMARY KEY (id)
)

Insertion is like:
INSERT INTO public.dictionary VALUES
    ('yes_button', 'en-us', 'yes'), ('yes_button', 'nl-nl', 'ja')

Now to get a translation one would do like
SELECT key, "en-us"
FROM dictionary
WHERE key in ('yes_button', ...)

Now the problem we have is that we wish to have "paths" to get to the default value, so if the value doesn't exist (or is NULL) it should follow a given path to lookup and get the nearest ancestor.
Our current solution is to do this calculation on the backend, but this basically means that we get the full translation table at once, or at least the full path to the root tree.
IE paths would be:
nl-be -> nl-nl -> en-us
en-gb -> en-us
fr-be -> fr-fr -> en-us

With the above insertion if I select nl-be as locale it would hence find "nl-nl", and thus return "ja". While if I select fr-be it would collapse to "yes".
Is it possible to provide such lookups given the above table directly? Or will this always be a task of the backend code and I should just get all translations for a given path?