texts: Why Can't I find my databases from Mysql on linux?

texts:
I'm new to linux. I cannot get a list of my databases by typing SHOW DATABASES on the MySQL shell. Though this MySQL program is aware of the MySQL datadir because it is listed when I type SHOW VARIABLES LIKE "%dir", as datadir =  /var/lib/mysql. I am logged in as root.

Normally on windows, I would type SHOW DATABASES on the MySQL command console that comes with WAMPSERVER and I'd get a list of all available databases on the localhost.


texts:
I have a big table with BLOB objects and some references from other tables to this one.
I prepare dev and test schema for my app by making expdp (data pump) from production, then anonymizing data on dev/test host. The problem is that this table with the BLOBs is so large, that copying dump file from production to test/dev and then importing it takes ages...

Is there any solution to export SCHEMA without some columns in tables (maybe some filter which replaces all BLOBs with empty BLOBs or NULLs)?

I consider also making a "just BLOB schema" and link it to the production schema, and not export it at all, but this could cause performance issues.


texts: Determining SQL Server memory requirements

texts:
I just a bit of an issue there where a SQL Server database was eating up quite a lot of RAM for its size.

A bit of background:

The database is for a small content managed website. The CMS is custom built in ASP which a bit of unoptimised queries littered throughout, but nothing too bad. The actual database takes up 132MB on disk.

Now back to SQL Server.

When looking through the server options I came across the Memory section. I noticed it was set to 2147483647MB which got me thinking that setting this value to something smaller could be a way to deal with the issue.

I set it to 500MB, which is quite a big difference.

So far the website is running great and there has been no more issues with memory on the server.

My question:

How can I determine what is the best value to set this to? Is 500MB to low? There is going to be another database on this server. It will be much smaller though, about 50MB.

EDIT (More Information)

I should add that the issue that caused me to seek out this memory setting was that of SQL Server not releasing memory back to the OS when it needed it. I run an old site that requires pages to be compiled on the fly (i.e. doesn't have a bin folder) and there was an issue with some pages not being compiled when needed. There was a server out of memory error.

This particular server has 2GB of RAM.


texts: Two Databases in Standby/ Read-Only, Can Only Alter to SINGLE_USER One of Them

texts:
I am setting up Transaction Log shipping in Standby/ Read-Only mode of two databases to multiple instances.

After backing up and restoring the transaction logs to their separate instances (one copy of each DB on each instance), one of the databases has no issue when I:

ALTER DATABASE [dbname] SET SINGLE USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [dbname] SET MULTI_USER;

the other one throws an error:

Failed to update database "other_dbname" because the database is read-only.

I cannot see any difference between the two. I've checked properties and all of the options are the same. From the options tab, the database that would throw the error had the Compatibility level set to SQL Server 2012 and one that would allow me to ALTER had that value set to SQL Server 2014, but changing them both to 2014 did not resolve the issue.I'm at a complete loss as to what the issue could be or what I could look into that would point me in the correct direction.edit: the above commands are executed on a linked server, if that could have anything to do with it. The users are the same, the permissions are the same, I'm able to execute the commands on the first database with no issue whether they are executed through SSMS or through a job and they always fail on the second database.

options:


texts: Proper way to index table columns which can be filtered in multiple ways

texts:
I have a comments table with the usual fields in it, i.e. author, title, body, created_at, etc. There's also a field called "user_id" which stores the id of the user the comment is directed at.

On the front end I've created a filter UI that basically allows users to filter comments based on date, whether user has responded to a comment, full text body search, category, etc. All these filters are optional and can be combined to create one fairly large query. It essentially looks like this:

SELECT * FROM comments WHERE user_id = ? AND .... AND ...

My first approach was to index user_id and then leave it at that. However, after reading more about it I'm not sure that's the best way. On the other hand, creating an index for each column that's "filterable" doesn't seem like a good idea either.

Any suggestions?

Edit: Postgres documentation seems to answer my question https://www.postgresql.org/docs/9.6/static/indexes-bitmap-scans.html


texts: MS-SQL stored procedure to insert into one-to-many-to-many relationship

texts:
I have three tables to represent a single record in a one-to-many-to-many relationship. I would like to create a stored procedure in MS SQL Server to create the record all at once. I have seen examples on how to do this with just two tables with a table-valued parameter to populate the child table values, but can't find how to do the same with one more table deep.

Table 1 - request(req_id, requester, date)
Table 2 - reqitems(line_id, req_id, descript, qty)
Table 2 - itemcharge(charge_id, line_id, dept, percent)

So a request can have 1 or more items & each item can have 1 or more charges, adding up to 100%.

I appreciate any help you have. Thanks

Example to populate Tables 1 & 2

CREATE TYPE dbo.reqlineTable AS TABLE
(
    descript nvarchar(100),
    qty int
);
GO

CREATE PROCEDURE InsertRequest
    @requester nvarchar(50),
    @date datetime,
    @lines reqlineTable READONLY
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @req_id int;

    INSERT INTO dbo.request
           (requestor
           ,date)
     VALUES
           (@requester
           ,@date);

    SET @req_id = SCOPE_IDENTITY();

    INSERT INTO dbo.reqitems
    SELECT qty
        , descript
        , @req_id
    FROM @lines;

    SET NOCOUNT OFF;

END
GO


texts: How to set indexes on a table with a FILESTREAM ROWGUID?

texts:
I'm confused with the requirement for a ROWGUID column on a table with FILESTREAM. How is this column going to be used?

Would it be okay if I used a different primary key (clustered) on the same table while letting that ROWGUID column "just be there" without a clustered index?

Like this:

CREATE TABLE Sample
{
    -- This is going to be clustered
    [Id] int NOT NULL,

    -- What shall I do with it index-wise?
    [RowGuid] uniqueidentifier ROWGUIDCOL NOT NULL UNIQUE
}


texts: How to modify the precision of a datetimeoffset column?

texts:
In a sql-server database with data I have several columns with type datetimeoffset(7). Now I would like to change the precision part of the type to datetimeoffset(0).

Is this possible without creating a new column of the desired type/precision and renaming the column?

I take solutions using SQL Server Management Studio as well as code (or also a no it's not possible).


texts: Excluding Read only databases using Ola Hallengren script

texts: Is there a way to exclude Standby or Read only databases from Index maintenance and also from backups using Ola Hallengren scripts.


texts: Performance impact of NOT using a foreign key (many dedicated 1:many key-key tables vs non-fk generic key-key table)

texts:
We're using a lot of association tables to manage 1:many relationships among a variety of different objects in our system.

To illustrate the question, two examples would be:

users, events, ass_users_events.  The ass_users_events would contain only the User_ID and Event_ID columns, both with foreign key relationships.
projects, tasks, ass_projects_tasks.  The ass_projects_tasks would contain only the Project_ID and Task_ID columns, both with foreign key relationships.

NB1: Every object table actually uses a combination of an Auto-Incrementing Integer Primary Key, plus a UUID column with a unique index which is the actual record ID. For the purpose of this question, we're using only UUID's so there is no chance of collision.

NB2: The reason we use this format rather than a straight Foreign Key column/index is that the reality is much more complex than this example, there are many different joins to many different tables and we don't want the ORM doing a lot of unnecessary work every time a record is loaded.

The problem is we're starting to create these associational tables for pretty much every new object type in the system for many of the other existing objects in the system and in the long run this doesn't seem sustainable, we'll end up with hundreds of association types.

A potential solution we're considering is getting rid of ALL of the current associational tables and instead creating one table with the following structure: obj_1_id, obj_1_type, obj_2_id, obj_2_type.  Every column would be indexed, likely as composite indexes (i.e. INDEX object_1 (obj_1_type,obj_1_id) and INDEX object_2 (obj_2_type,obj_2_id)).

The examples above in the ass tables would become: abc,user,123,event, and def,project,456,task.

This solution gives us the flexibility to build as many relationship types as we want between different objects, and there's ample indexing on the ass table to remain performant.  My question is is there a downside to using indexing only during joins, vs having defined foreign key relationships in smaller tables, but potentially hundreds of them?

EDIT:  I think there are some misconceptions below so this might clear up a couple things:

I use polymorphic object structures, but every object is stored in it's own table, i.e. user, product, category, event, etc.
As far as the proposed ass table, it will only have 4 functional fields, with very straightforward datatypes (plus one ai_col as primary).  The datatypes would be varchar(10) for the type cols and CHAR(32) / BINARY(16) for the id's.  SELECT's will only ever have one join, to the ass table, i.e.

SELECT event.*  FROM event INNER JOIN ass ON ass.obj_1_type = 'event' AND ass.Object_ID_1 = event.Event_ID AND ass.obj_2_type='location'

there's no reason I would ever ask for two of these objects at once, I would only be filtering results from one table by existence of ass links.

UUID's are the real Unique ID's here, but every table uses ai_col which is an INNODB construct for increasing the performance of their clustered index.
This is a read heavy environment, I'm not near as concerned with insert/delete performance.
We use handlersocket for simple read AND write queries removing the SQL overhead.
The integrity of this table is acceptable at Eventually Consistent in this specific use case.

EDIT 2: To make this crystal clear, here's the why...  if I have 200 objects each in their own tables, that I want to be able to link in all possible ways, I would either have to have 199 foreign key relationships in each object table, or I would have to create 40,000 tables with all the possible combinations and then all the business logic to go with that. Using a the method we're considering, there's one table and I have RI taken care of at the application level, which I indicated is ok at eventually consistent.


texts: Index not used with many WHERE LOWER(column) IN (...) conditions, works fine with just a few

texts:
I have the following table:

CREATE TABLE public.devices
(
  id integer NOT NULL DEFAULT nextval('devices_id_seq'::regclass),
  token text NOT NULL,
  ...some other columns...,
  CONSTRAINT devices_pkey PRIMARY KEY (id, token)
)
CREATE INDEX IF NOT EXISTS idx_devices_lower_token ON devices (lower(token));

If I run the following query, everything is fine, the query runs instantly, and the index is used:

SELECT *
FROM "devices"
WHERE (id > '0')
AND ((lower(token) IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb')))
ORDER BY id LIMIT 1000;

"Limit  (cost=58116.96..58119.46 rows=1000 width=345)"
"  ->  Sort  (cost=58116.96..58189.47 rows=29005 width=345)"
"        Sort Key: id"
"        ->  Bitmap Heap Scan on devices  (cost=1061.91..56526.65 rows=29005 width=345)"
"              Recheck Cond: (lower(token) = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a}'::text[]))"
"              Filter: (id > 0)"
"              ->  Bitmap Index Scan on idx_devices_lower_token  (cost=0.00..1054.65 rows=29005 width=0)"
"                    Index Cond: (lower(token) = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a}'::text[]))"

However, if I add a lot of conditions (e.g. 1k) for the (lower(token) IN (...)), the index will no longer be used anymore and the query will be VERY slow:

SELECT *
FROM "devices"
WHERE (id > '0')
AND ((lower(token) IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb', ... up to 1k tokens ...)))
ORDER BY id
LIMIT 1000;

"Limit  (cost=3900016.07..3900018.57 rows=1000 width=345)"
"  ->  Sort  (cost=3900016.07..3907219.10 rows=2881214 width=345)"
"        Sort Key: id"
"        ->  Seq Scan on devices  (cost=0.00..3742042.21 rows=2881214 width=345)"
"              Filter: ((id > 0) AND (lower(token) = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a,6116af468e0017ffe5c49309869be773,4a5ebc1cec6c452e74fbf1a4e22fe804,0a9981eaf530d22ad9fe12a73a0b486c,61eb008e86a5afeaa9edd3e56ba01e0b,a31775a866571d9c2ecadc6b2a8127a3,dfcb4bc34771883bb2a54ee44299b058,7c93581a24053021bd41b7cc4654bf9e,43182b0010cea255fef1d10849858d7c,c3f6503eafeaa1d25a6f5672310ef4e5,672641b1fcd23f67deab34deaf28f1d8,cb52e085618ecbdf6eef1669b7b38008,335626a32455e61e1b1de16550e43af0, (...)"

Ok so, it must not like many conditions, right?

Well, I tried replicating the same scenario but without using lower(token), and it works correctly:

create index IF NOT EXISTS idx_devices_token on devices (token);

SELECT *
FROM "devices"
WHERE (id > '0')
AND ((token IN ('92134f4bb6a2263fd044a5a04440bc1a','cbf7d4a094bd266fd3f1b7ef4196172a', 'aa0f7650cf28010b5c01b77ae206f7fb', ... 1k tokens ...)))
ORDER BY id
LIMIT 1000;

"Limit  (cost=25.70..25.71 rows=3 width=345)"
"  ->  Sort  (cost=25.70..25.71 rows=3 width=345)"
"        Sort Key: id"
"        ->  Bitmap Heap Scan on devices  (cost=13.69..25.68 rows=3 width=345)"
"              Recheck Cond: (token = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a,aa0f7650cf28010b5c01b77ae206f7fb}'::text[]))"
"              Filter: (id > 0)"
"              ->  Bitmap Index Scan on idx_devices_token  (cost=0.00..13.69 rows=3 width=0)"
"                    Index Cond: (token = ANY ('{92134f4bb6a2263fd044a5a04440bc1a,cbf7d4a094bd266fd3f1b7ef4196172a,aa0f7650cf28010b5c01b77ae206f7fb}'::text[]))"

So this seems to only happen if I use an index with lower and many WHERE IN conditions.

I have no idea what is going on here, any ideas?

I am using PostgreSQL 9.5


texts: num_rows in dba_tables not reflecting the real number of rows in tables Oracle 9i

texts:
I'm querying dba_tables in order to get a listing of tables and their rowcount with following sql request :

select * from dba_tables where owner='dbSID';

But I notice that even for some non empty tables, NUM_ROWS column is returning null values.

Could somebody please explain this strange behavior of dba_tables ?


texts: Tabled Value Function returns incorrect or no results

texts:
I recently learned of TVF's and it seemed like it was exactly what I was looking for. I did my first attempt at one and it is not going well.

Here is my code:

USE [RMC_Tracker] GO
/****** Object:    UserDefinedFunction [dbo].[TFV_Dashboard]
9:08:08 AM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO

Script Date: 4/13/2020

--------------------------------------------
Author:    John Clark
Create date: 4/12/2020
Description: Returns a recordset for the RMC Tracker

ALTER FUNCTION [dbo].[TVF_Dashboad]
(
-- Add the parameters for the function here
@CompDate datetime = NULL

)
RETURNS TABLE AS
RETURN
(
-- Add the SELECT statement with parameter references here
SELECT    dbo.ACTIVE.RECORD_ID, dbo.ACTIVE.CMD_ID, dbo.ACTIVE.EQUIPMENT, dbo.ACTIVE.EquipDes,
          dbo.ACTIVE.CASREP,dbo.ACTIVE.[UPDATE], dbo.ACTIVE.TYAST,
          dbo.ACTIVE.STATUS,dbo.ACTIVE.JCN, dbo.COMMANDS.Trigrpah

FROM      dbo.ACTIVE INNER JOIN dbo.COMMANDS ON dbo.ACTIVE.CMD_ID = dbo.COMMANDS.CMD ID

WHERE    (dbo.ACTIVE. RECCLS = @CompDate)
)

I call this Function from within MS Access via a Pass Through Query:

SELECT * FROM dbo.TVF_DashBoard(default)

It returns an empty set. What I am trying to do is create a simple Function that allows me to return a table that can be toggle based on whether a field (RECCLS) is either Null or Not Null.

Any assistance would be appreciated.


texts: Is selecting from the same server but different database slower than the same database?

texts:
I am thinking about 2 possibilities that I want to use in my project. So there are 2 projects, with different databases each. So Project P1, has Database D1, and Table T1. Project P2, has Database D2.

For Project P2, I need to select the data at Table T1 quite often. Is it better to create a replicate of T1 inside Database D2 (name it T2), and do SELECT * from T2 instead of doing SELECT * FROM [D1].[T1] ?

The 2 databases D1 and D2 are in the same server.


texts: QGIS PostGIS Authentication Failure with "trust" option

texts:
I'm trying to setup a PostGIS db using PostgreSQL 9.1 on Ubuntu 12.04. I've set up the database, created a user account "jay" with no password, added spatial functionality following section 2.5 here, and set my  pg_hba.conf for local to:

# "local" is for Unix domain socket connections only
local   all             all                                     trust

I can can connect to the database using the pgAdminIII, requiring no password to connect, but connection fails when I try to connect via PostGIS or with QuantumGIS. Here's a screencap of the connection window in QuantumGIS:

Looking into the problem a bit, I came across this post which led me to try connecting via the terminal using psql -U jay -h localhost gis. psql prompted me for a password, I hit enter because I had not set a password, and it returned psql: fe_sendauth: no password supplied. I tried again, this time supplying my Ubuntu user password, which returned

psql: FATAL:  password authentication failed for user "jay"
FATAL:  password authentication failed for user "jay"

I then tried setting a password for jay using ALTER USER, and entering this password in the prompt, and this failed as well. Clearly, I am unable to connect. However, I'm having trouble figuring out what troubleshooting steps to take next.

Any ideas?


texts: Pitfalls when using Java stored procedure

texts:
Recently I have worked on a project that I assume could be solved by using Java stored procedure (more precisely some recieving/sending email package).

Аfter a while I've found that there are very few number of Java classes and Java stored procedures in my company's database. I asked DBA and he said that using Java stored procedures is prohibited because "It can easily put the server down".

I would be greateful if someone could expain me: is it really dangerous to use Java stored procedures that use external packages? Are there any pitfalls? Is using Java stored procedures not a common practice?


texts: Select Query takes more than a minute to retrieve data

texts:
I have a table that has 99 columns, all of them are not needed but they were there when we started. There is a Primary Key (Objectuid) which is a GUI and has the Clustered Index on the PK.

The Table is updated often and has close to 2 million rows. there are no other non clustered indexes in this table.

I have a view that pulls necessary field from the table and does a left outer join with another table which has only 7 rows. It then adds 4 filters on the where clause. it retrieves about half a million record but takes about a minute.

Is it normal? How can I make it to run for less than five seconds? The Server has 64 GB RAM and 2 processors with 8 cores each. Its Hard Drive Space is about a TB.

Do you think it is improper Indexing? If so How can I figure out which Column to index to make it run faster? I need to keep in mind that there are inserts , updates and deletes on a regular basis.

The query is like this:

SELECT a,b,c,d,e,f,g,h,i,j
FROM TBLA LEFT OUTER JOIN TBLB
    ON TBLA.Objectuid = TBLB.objectuid
WHERE TBLA.a NOT LIKE '11%'
AND TBLA.b <> '2100'
AND TBLA.c <> 'alloc'
AND TBLA.d NOT LIKE 'lm-%'
AND TBLA.e NOT LIKE 'ap-%'
AND TBLA.f >= '2014'

TBLA has 99 columns out of which the view only uses 15. TBLA has a PK which is clustered is a GUID. even when i remove the index it takes the same amount of time.


texts: Query two SQL Server databases on two different servers

texts:
Suppose I have two database servers (DB-SERVER-1 & DB-SERVER-2), and there is a single database on each server (DB1 & DB2).  Both are SQL Server databases.

Is it possible to query both databases in the same T-SQL command or function?  Perhaps by connecting to one, then getting a result set, then connecting to the other, and getting that result set.  Then performing a union with both result sets?

I can do this from within .NET or Java code, but I want to do this strictly from the database side.  Is this even possible?


texts: replication breaks after upgrading master

texts:
I have a set up of replication with master 5.1.30 and slave 5.5.16
and the replication is working good

Now i have upgraded mysql master to 5.1.47

As far as i know we have to turn off the log bin with sql_log_bin=0 before using mysql_upgrade program in order to up grade the replication setup as well

but the problem here is the binary log was not turned off while mysql_upgrade program is running

The reason i found is in 5.1 the sql_log_bin is a session variable
and mysql_upgrade program runs in another session

so how to upgrade the replication as well along with the server with any breakage on replication setup.

any suggestions are really useful.....


texts:
Trying to incorporate Date wise folder in the script. Following is the original code

  INSERT INTO @CurrentDirectories (ID, DirectoryPath, Mirror, DirectoryNumber, CreateCompleted, CleanupCompleted)
      SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar(max)),'\','$') END + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0
      FROM @Directories
      ORDER BY ID ASC

I added '\' + REPLACE(CONVERT(varchar(10), GETDATE(), 101), '/', '') +'\' + to the above code to have folders by date

INSERT INTO @CurrentDirectories (ID, DirectoryPath, Mirror, DirectoryNumber, CreateCompleted, CleanupCompleted)
      SELECT ROW_NUMBER() OVER (ORDER BY ID), DirectoryPath + CASE WHEN RIGHT(DirectoryPath,1) = '\' THEN '' ELSE '\' END + '\' + REPLACE(CONVERT(varchar(10), GETDATE(), 101), '/', '') +'\' + CASE WHEN @CurrentAvailabilityGroup IS NOT NULL THEN @Cluster + '$' + @CurrentAvailabilityGroup ELSE REPLACE(CAST(SERVERPROPERTY('servername') AS nvarchar(max)),'\','$') END + '\' + @CurrentDatabaseNameFS + '\' + UPPER(@CurrentBackupType) + CASE WHEN @ReadWriteFileGroups = 'Y' THEN '_PARTIAL' ELSE '' END + CASE WHEN @CopyOnly = 'Y' THEN '_COPY_ONLY' ELSE '' END, Mirror, ROW_NUMBER() OVER (PARTITION BY Mirror ORDER BY ID ASC), 0, 0
      FROM @Directories
      ORDER BY ID ASC

Now after modifying the code with the above syntax , I could see the DateFolder gets created but somehow backup is not happening. See the error below

  DECLARE @ReturnCode int EXECUTE @ReturnCode =
  [master].dbo.xp_create_subdir
  N'N:\Test\FullUserDBBackups\10152017\CLU-DBSQL-TEST$DB2012_TEST\AX_TEST\FULL_COPY_ONLY'
  IF @ReturnCode <> 0 RAISERROR('Error creating directory.', 16, 1)

  BACKUP DATABASE [] TO DISK =
  N'N:\Test\FullUserDBBackups\10152017\CLU-DBSQL-TEST$AX2012_PROD\AX_TEST\FULL_COPY_ONLYCLU-DBSQL-TESTAX2012_TEST_AX_TEST_FULL_COPY_ONLY_20171015_042734.bak'
  WITH CHECKSUM, COMPRESSION, COPY_ONLY


texts:
Here is my situation:

I have a WebSocket connection that feeds me data real-time
I have an async callback function that gets the data and inserts into a queue
I have another thread that reads from the queue and stores it into the Postgres database, one row at a time (using python's psycopg2 library)

The problem is the real-time data comes in faster than the time it takes to insert into the database, and in a couple of hours, the server runs out of memory. (Is this because psycopg2 library is slow?)

A simple solution is to create more threads that insert into the database; however, this will cause the data to be out of order. Is there a database that sorts the data? Other suggestions would be very much appreciated.


texts: Database Design - One table for different types of transactions

texts:
Considering a procurement system as an example. Would it make sense to have one single table for the following: Quote, Order, Invoice and Credit Memo?

There will be some details that are different. For example each list will have its 5 special fields but the other say 50 fields would be exactly the same. Would it make sense in this case to join all tables in a single one. And some fields are unused for some types.

I can see that Navision system does something like that in its database. It uses one table, but then splits into separate tables once the items are posted. I think this may be for performance reasons because the posted items can tend to become very large. & splitting into separate tables would speed up queries. But this is just my thinking.

So my main dilemma is ... what is the recommended approach in such case: 1 table or separate tables? what are the pros and cons of each? What are the business considerations to take into account?

Thanks for your help!


texts: Compare two databases and get the differences

texts:
I want to between two databases and get the differences:

columns: in the first table the column name is varchar2(12), in the  second table the column name is varchar2(20). Or I have an
additional column in the first table.
data: I have to compare data between the same tables in different databases

I am aware of DBMS_COMPARISON, but need to do this on Oracle 10g.

Added after the answer by thatjeffsmith:

I know that SQL Developer can do that, but I have to develop a J2EE application which does that.


texts: What does "volume" mean in MAX_IOPS_PER_VOLUME?

texts:
When setting MAX_IOPS_PER_VOLUME in a Resource Pool, what exactly does "volume" mean?  Specifically, how many "volumes" would be the following cases:

Locally attached disk that's split into two partitions E: and F:
Software RAID 1 set E: composed of 2 locally attached disks (yes I know software RAID is bad-- adding this case to help me understand SQL Server's definition of "volume", not to design a production setup!)
Hardware RAID 1 set E: composed of 2 locally-attached disks
SAN disk E: on who knows/who cares how many disks.
1 SQL Server filegroup spread across two locally attached disks E: and F:

I assume the answers to #3 and #4 are "1 volume" and #5 is "2 volumes" but it's #1 and #2 that I'm most curious about.

The specific reason I'm asking is wondering if it's possible to increase the Resource Governor's IOPS limit for locally-attached SSD tempdb while having a lower limit for our SAN data storage.

So I'm wondering if splitting a single physical disk into multiple partitions might be a way to do this, by putting separate tempdb files on each partition so the total tempdb If #1 above makes SQL Server treat one physical disks as multiple volumes for throttling purposes, this may be an option. I'm assuming that this won't work-- that SQL Server is smart enough to know that 2 partitions is one "volume". But was worth asking.


texts:
I have a table with a PK and a unique non-clustered index, as follows:

CREATE TABLE Table1
(
    Id INT IDENTITY(1,1) NOT NULL,
    Field1 VARCHAR(25) NOT NULL,
    Field2 VARCHAR(25) NULL,
 CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Id ASC)
)

CREATE UNIQUE NONCLUSTERED INDEX IX_Field1_Field2 ON Table1
(
    Field1 ASC,
    Field2 ASC
)
WITH
    (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        SORT_IN_TEMPDB = OFF,
        IGNORE_DUP_KEY = OFF,
        DROP_EXISTING = OFF,
        ONLINE = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    )

I have 2 jobs whose execution times I find are overlapping each other.  They both include the same INSERT into this table and frequently the job that starts last fails because it tries to insert a record into Table1 with a duplicate index key value.

INSERT Table1
SELECT Field1, Field2
FROM SomeOtherTable sot WITH (NOLOCK)
WHERE NOT EXISTS (
    SELECT 1
    FROM Table1 t1
    WHERE sot.Field1 = t1.Field1
    AND sot.Field2 = t1.Field2
)

From what I've been able to discern, the INSERT in Job1 is still executing when the NOT EXISTS from Job2 is evaluated resulting in Job2 trying to insert a duplicate key value.  It seems to me that the locking for Table1 is not happening as expected.

I'm at a loss as to why this is happening.  Would this have anything to do with the NOLOCK hint used in the INSERT?  I didn't think that that hint would include Table1 in its scope, only SomeOtherTable.

I know I can mitigate the duplicate key error by setting IGNORE_DUP_KEY to ON for the index, and that would be fine for us in this situation.  I would like to know, though, why the duplicate is showing up in the 2nd INSERT.


texts: SQL Server - Select where using split - without declaring function

texts:
SQL Server. Compatibility level 120.

I have a table where one VARCHAR column contains semicolon-separated integral values. I would like to SELECT against the values in this column as if they existed in a column of ints somewhere.

Because of the compatibility level, I can't use STRING_SPLIT. I also cannot create any globally available functions.

I can however create a temporary table if needed. What's my course of action? Some sort of nesting of WHILE loops (One for the rows of the table, another for the semicolons in the string)?

update: This is perhaps a better explanation of what I want to achieve:

Let's say I have the following tables.

CREATE TABLE Service
(
      ServiceId int
      , CustomerIds varchar(255)
);

CREATE TABLE Customer
(
    CustomerId int
    , CustomerName varchar(30)
);

With some data

INSERT INTO Customer (CustomerId, CustomerName)
VALUES  (1, 'John')
    ,(2, 'Niels')
    ,(3, 'Frank')
    ,(4, 'Barbie')
    ,(5, 'Holly')
    ,(6, 'Ebeneezer');

INSERT INTO Service (ServiceId, CustomerIds)
VALUES  (1, '4')
    ,(2, '2;3');

And let's say I want to select Customer.CustomerName for any Customer who's ID shows up in the CustomerIds in any line in that table.

That is, I want to retrieve Niels, Frank and Barbie.


texts:
I'm trying to restore a mysql5.5 dump file using mysql command prompt using the command given below:

mysql> -u <username> -p <password> <dbname> < <dumpfile>

But it is giving the below error:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
  corresponds to your MySQL server version for the right syntax to use near '-u ro
  ot -p password mydb< "D:\mysql_dump\backup_2013_01_mydb.sql"' at line 1

Please help. I've tried it on both WinXP and Win7.
Also please guide how to do the same using Mysql Workbench.


texts: How to update a table by count from another table?

texts:
I have two tables as

CREATE TABLE country_statistics
(
id int(11) NOT NULL AUTO_INCREMENT
us int(11),
uk int(11),
ca int(11),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT

CREATE TABLE entries
(
id int(11) NOT NULL AUTO_INCREMENT
list varchar(255),
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT

I want to search for the occurrence of each country in the list column. I use multiple subqueries to do so

UPDATE country_statistics
us = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United States%',
uk = (SELECT COUNT(*) FROM entries WHERE list LIKE '%United Kingdom%',
ca = (SELECT COUNT(*) FROM entries WHERE list LIKE '%Canada%'
WHERE id = 'xx'

How can I reduce the number of subqueries, and count all values in one subquery, then updating all columns?


texts:
Just a question in how could i validate duplicate data's in my database if columns is set to be encrypted(deterministic) in db?

I've tried running the following code, but receive the error below.

declare '@test1' varchar(max) = 'XXXXXX'
select * from testing where t1 = '@test1'

  Encryption scheme mismatch for columns/variables '@test1'. The
  encryption scheme for the columns/variables is (encryption_type =
  'PLAINTEXT') and the expression near line '4' expects it to be
  (encryption_type = 'DETERMINISTIC', encryption_algorithm_name =
  'AEAD_AXS_XXX_CBA_HMAC_SHA_XXX', column_encryption_key_name =
  'CEK_Auto1', column_encryption_key_database_name = 'XXX') (or weaker).

How can I find the source of this error, or is there another way to determine duplicate rows?


texts: Where is the SQLS database backup virtual device string stored in the system tables?

texts:
In my SQLS Server 2005 log, when backups occur, I see something like:

Database backed up. Database: blahblah, creation date(time): 2011/08/11(15:49:19), pages     dumped: 1, first LSN: 142:489:1, last LSN: 142:491:1,
number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{9A325509-4285-4C8A-E1B2-2F4815C4C4DF}15'}). This is an informational message only. No user action is required.

Regarding that virtual device specification, {9A325509-4285-4C8A-E1B2-2F4815C4C4DF}15'}:

In what system table is that virtual device string?  If it matters, the reason I ask is I have a query regarding latest backups and need to filter based on the virtual device.

I have looked in all of the obvious places, like:

msdb.dbo.backupmediaset
msdb.dbo.backupmediafamily
msdb.dbo.backupfile
msdb.dbo.backupfilegroup
sys.backup_devices

but no luck.  If it is indeed stored somewhere, does the location change between SQL versions?

Any help appreciated.

TIA

dbaman


texts: mysqldump with --no-tablespaces on InnoDB with index?

texts:
Does mysqldump --no-tablespaces dumps index files of InnoDB?
I know that MyIsam uses separate index files *.MYI for index. So if one uses mysqldump with --no-tablespaces, does that mean index is not exported?


texts: Is it possible for it to be inpenetrable?

texts:
I was wondering if it is possible to create some sort of database that is physically impossible to enter. While creating it you store some information in it and program it to spit that information after a period of time. But not even you can access it even if you wanted to, you have no choice but to wait.

Thanks.


texts: Pivoting some row data into columns (SQL Server 2008 R2)

texts:
Sorry if this has been answered before but I'm exhausted and on a time line. Maybe I'm just an idiot. I'm designing a data export from One application to import into another.

Full-Disclosure: I did not design this disaster of an application

We have an application that stores email addresses and contacts on different rows in the same table. The problem is that the primary email for that user is simply the one that comes back first when the rows are sorted by a unique id field.

Dumb_ID   Col1        Col2
1A!       James Q.
2B$       1A!         JamesQ@Contoso.com
CD#       1A!         JamesQ@OldButRelevantContoso.Ru

I need to select the data like so:

Dumb_ID    Col1        Col2                  Col3
1A!        James Q.    JamesQ@Contoso.com    JamesQ@OldButRelevantContoso.Ru

There are a number of other columns being selected from 1A! but I cant think of an effective way to select these into a column based on their row order when sorted by Dumb_ID

Please help DBA's, you're my only hope to escape the madness of this application.

Thanks!

EDIT:
After @Daniel gave me an elegant method using a subquery with a partitioned row count this was my end result. Posted for posterity.

LEFT OUTER JOIN
  (SELECT LINKACCT
        , ADDRESS2
        ,MAX(CASE WHEN subQ.ORDINAL = 1 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL1
        ,MAX(CASE WHEN subQ.ORDINAL = 2 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL2
        ,MAX(CASE WHEN subQ.ORDINAL = 3 THEN (subQ.CONTSUPREF + subQ.ADDRESS1) END) AS EMAIL3
  FROM
  (SELECT recid
    , CONTACT
    , CONTSUPREF
    , LINKACCT
    , ADDRESS1
    , ADDRESS2
    ,ROW_NUMBER() OVER (PARTITION BY LINKACCT ORDER BY recid) AS ORDINAL
    FROM CONTSUPP CSEI WHERE CSEI.RECTYPE = 'P') subQ GROUP BY LINKACCT, ADDRESS2) CSEI ON CS.recid = CSEI.LINKACCT AND CS.CONTACT = CSEI.ADDRESS2


texts: Keep track of the last read record in MySQL

texts:
I have 2 databases in MySQL:

1) An input Latitude-Longitude_dB ('latlong_db', henceforth): It has the latitude and longitude of each reading from a GPS tracking device.

2) A Weather_db: I read the input latlongs from dB1, and calculate 'current' weather data for each pair of latlongs (eg: humidity, cloud_coverage) . This weather data is written into a Weather_db.

The issue is: I need to keep track of which record (which 'input latlong') was read last. This is so that I don't recalculate weather_data for the latlongs that I've already covered. How do I keep track of the last read input_latlong?

Thank you so much.


texts:
E. <52> [-10117] Stream Error: Internal error: DelayExpired: _state.http_state returned 0.
W. <52> [10088] The current SQL statement has been running for too long in the consolidated database.  The connection ID is SPID '76'

I am unable to sync the server db to android mobile and it gives the above error.


texts: MySQL Group Replication multi-primary - add new member

texts:
I am configuring MySQL Group Replication in multi-primary mode to replicate all databases between two members and allowing writes on either.

I have my two members IP addresses configured in the whitelist and seed list:

loose-group_replication_ip_whitelist = "x.x.x.x,y.y.y.y."
loose-group_replication_group_seeds = "x.x.x.x:33061,y.y.y.y:33061"

My understanding is that if in a month or two I want add a third member, I will need to update both member's my.cnf files to add the third member to the whitelist and seed list.
I will then need to restart the mysql services in order for the change to take effect.
What is the best practice here? I see two options:

Option 1: restart the members one at a time such that the group never becomes empty.

Restart mysql on member1
Re-join the group with START GROUP_REPLICATION;
Restart mysql on member2
Re-join the group with START GROUP_REPLICATION;
Start mysql on member3 with START GROUP_REPLICATION;

Option2: stop all members and rebootstrap

Stop mysql on both existing members
Bootstrap the group on member1
Join the group on member2 and member3

Option 1 should result in no downtime whereas Option 2 would have downtime. Are there any other options I'm missing or is Option 1 best practice?


texts: Eliminate Key Lookup (Clustered) operator that slows down performance

texts:
How can I eliminate a Key Lookup (Clustered) operator in my execution plan?

Table tblQuotes already has a clustered index (on QuoteID) and  27 nonclustered indexes, so I am trying not to create any more.

I put the clustered index column QuoteID in my query, hoping it will help - but unfortunately still the same.

Execution plan here.

Or view it:

This is what the Key Lookup operator says:

Query:

declare
        @EffDateFrom datetime ='2017-02-01',
        @EffDateTo   datetime ='2017-08-28'

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#Data') IS NOT NULL
    DROP TABLE #Data
CREATE TABLE #Data
(
    QuoteID int NOT NULL,   --clustered index

    [EffectiveDate] [datetime] NULL, --not indexed
    [Submitted] [int] NULL,
    [Quoted] [int] NULL,
    [Bound] [int] NULL,
    [Exonerated] [int] NULL,
    [ProducerLocationId] [int] NULL,
    [ProducerName] [varchar](300) NULL,
    [BusinessType] [varchar](50) NULL,
    [DisplayStatus] [varchar](50) NULL,
    [Agent] [varchar] (50) NULL,
    [ProducerContactGuid] uniqueidentifier NULL
)
INSERT INTO #Data
    SELECT
        tblQuotes.QuoteID,

          tblQuotes.EffectiveDate,
          CASE WHEN lstQuoteStatus.QuoteStatusID >= 1   THEN 1 ELSE 0 END AS Submitted,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 2 or lstQuoteStatus.QuoteStatusID = 3 or lstQuoteStatus.QuoteStatusID = 202 THEN 1 ELSE 0 END AS Quoted,
          CASE WHEN lstQuoteStatus.Bound = 1 THEN 1 ELSE 0 END AS Bound,
          CASE WHEN lstQuoteStatus.QuoteStatusID = 3 THEN 1 ELSE 0 END AS Exonareted,
          tblQuotes.ProducerLocationID,
          P.Name + ' / '+ P.City as [ProducerName],
        CASE WHEN tblQuotes.PolicyTypeID = 1 THEN 'New Business'
             WHEN tblQuotes.PolicyTypeID = 3 THEN 'Rewrite'
             END AS BusinessType,
        tblQuotes.DisplayStatus,
        tblProducerContacts.FName +' '+ tblProducerContacts.LName as Agent,
        tblProducerContacts.ProducerContactGUID
FROM    tblQuotes
            INNER JOIN lstQuoteStatus
                on tblQuotes.QuoteStatusID=lstQuoteStatus.QuoteStatusID
            INNER JOIN tblProducerLocations P
                On P.ProducerLocationID=tblQuotes.ProducerLocationID
            INNER JOIN tblProducerContacts
                ON dbo.tblQuotes.ProducerContactGuid = tblProducerContacts.ProducerContactGUID

WHERE   DATEDIFF(D,@EffDateFrom,tblQuotes.EffectiveDate)>=0 AND DATEDIFF(D, @EffDateTo, tblQuotes.EffectiveDate) <=0
        AND dbo.tblQuotes.LineGUID = '6E00868B-FFC3-4CA0-876F-CC258F1ED22D'--Surety
        AND tblQuotes.OriginalQuoteGUID is null

select * from #Data

Execution Plan:


texts: Availability Groups and Transactional Replication

texts:
My client has a setup as shown below.

AG Group A --------->       Server A (DB--> A) ----> Server B ( DB--> A)
Transactional Rep -->       Server A ( DB-->A ) ---> Server C ( DB--> B)
AG Group B --------->      Server C ( DB --> B)  ---> Server D ( DB --> B)

where  Server A -> Database A  is configured for Transactional replication to Server C -> Database B.
The Availabilty Group and replication are configured fine and the publisher is redirected to the Availability Group Listener.
The distributor database is hosted on Server C ( subscriber).
To top it all the subscriber is part of another AG group so it has its own secondary AG replica in Server D (Database B).
I know in this situation transaction replication is not required at all as we could open secondary replica for Read within Availability Groups. ( client - disagrees to change).
Question is as follows:
If The Subscriber ( server C) crashes along with the distribution database,  what options are available to redirect replication to a new distributor on replica server D ? Assuming both AG are synchronous and can be failed over together.
Your help and advise is much appreciated.


texts: Referential integrity when two FK columns need to match themselves in the same table?

texts:
It's possible my design is wrong, or there's simply a better way. I'll use a very simple example:

    --------- dbo.Book----------
   |                            |
   |  BookID int identity (1,1) |
   |  ShelfID int FK            |--
    ----------------------------   |
                                   |
    --------- dbo.Row-----------   |
   |                            |  |
 --|  RowID int identity (1,1)  |  |
|   ----------------------------   |
|                                  |
|   -------- dbo.Shelf----------   |
|  |                            |  |
|  |  ShelfID int identity (1,1)|--
 --|  RowID int FK              |
    ----------------------------

Simple enough.

But what if I wanted to assign a Row without the Shelf? Maybe we know the row but not the shelf just yet:

    --------- dbo.Book----------
   |                            |
   |  BookID int identity (1,1) |
   |  RowID int FK              | <-- New
   |  ShelfID int FK NULL       |
    ----------------------------

Is there a better design method for this? I need to make sure that ShelfID and RowID match up in their own respective parent/child tables.

What's the best way? A constraint that calls a function? Seems expensive. Different table structure? I could be missing something super simple obvious.

Elaboration:

If dbo.Shelf has the following value:

[ShelfID]  [RowID]
------------------
 3          1

Then we should expect the dbo.Row table to have a primary key with the value of 1. I want this same referential integrity on the dbo.Book table:

[BookID] [RowID] [ShelfID]
--------------------------
1        1       3

How can I enforce this? Is a function-called constraint the best way? Or is my design wrong?

My requirement is to uphold data integrity if both values are present in the Book table. There must be a correlating relationship in the other two tables. Maybe I've over complicated it and a check constraint that calls a function is the way to go.

I'm aware of being able to set a FK to null. Basically, if ShelfID and RowID both have a value in the main Book table, what is the best way to make sure that RowID and ShelfID match up? ShelfID must exist in the dbo.Shelf table, that one is easy. But the RowID in Book table must be the same parent found in dbo.Shelf.

What is the best way to make sure a ShelfID and RowID don't end up in the Book table that is a false relationship? For example, RowID = 1, and ShelfID = 3, but in the Shelf table, there is no such relationship.


texts: 2 Identical Servers/SQL Servers Processing Transactions w/ Merge Replication

texts:
Edited entire question for clarity.

Scenario: Have 2 identical SQL Servers on separate servers, processing transactions from different applications simultaneously while being completely synchronized (example mobile apps server 1 and web apps server 2).

We are going to use Always On to fail over one server to the other for maintenance and vice versa however, when maintenance is complete, both servers will be once again online processing transactions from our various applications and synchronized.

Trying to solve a request from a client to be online, even during maintenance, with 2 synchronized servers, each processing transactions from different applications.

What is the best approach to keep them synchronized?  Was leaning towards merge replication.

Current ver. SQL Server 2012 SP3-CU4

Thanks


texts: Selecting info from users who have been inactive for at least a week

texts:
I need to find the users who have not done a task in at least a week, and pull the relevant information from them. In this case, their id, names, and data1 from another table. This is what I have so far, but it's not working. Something to do with the joins I think?

Can someone help me out? :)

Additional info: the user_identity table is a bit complicated, with multiple rows with the same userid hence the distinct.

SELECT DISTINCT ON (u.id) u.id, u.given_name, u.family_name, i.data1
                FROM users u JOIN task t_week on u.id = t_week.user_id
                and t_week.created_at > %s JOIN user_identity i on (u.id=i.user_id)
                WHERE t_week.created_at IS NULL AND i.content_type='email_address' AND i.owner_id is NULL
                GROUP BY u.id, u.given_name, u.family_name, i.data1,i.created_at ORDER BY u.id, i.created_at


texts: Find the destination owner (schema) for replication on SQL Server

texts:
We have a table replicating to another server with a different Schema at the destination.  When I set the replication up it shows as destination owner in the GUI.  I am trying to create some reports and need to look at the publication and look for what the destination schema would be.

Unfortunately when I query the table [distribution].[dbo].[MSarticles] the destination owner column is null.  Is there somewhere else I can find this info?

Thanks


texts: Error 20598 - The row was not found at the Subscriber when applying the replicated command

texts:
I am facing the error 20598 with Transactional Replication:

The row was not found at the Subscriber when applying the replicated command.

Normally this error occurs when an UPDATE or DELETE statement is executed by the publisher for a primary key value and the record (against which UPDATE/DELETE executed) does not exist in the subscriber database.

But in my case the scenario is different.

I diagnosed and found the record exists in the article/table of subscriber database, because when I executed the command (retrieved with help of sp_browsereplcmds) at the subscriber it executed successfully.

What may be the possible reason of it?

I'm using SQL Server 2016 both side.


texts: How to select students that failed at least at all courses that student with id = 1 failed?

texts:
I have the following tables:

STUDENT (student_id, first_name, last_name, birth_date, year , domain)

PROFESSOR (professor_id, first_name, last_name, birth_date, hire_date, title, salary)

COURSE (course_id, course_name, professor_id)

GRADE (student_id, course_id, grade, date_of_exam)

I have to display the students that failed at least at all the courses that student with id = 1 failed.

What I tried:

SELECT
s.student_id,
s.first_name,
s.last_name,
n.grade,
n.course_id
FROM
    student s
    JOIN grade n ON n.student_id = s.student_id
WHERE
    n.grade <= 4;

...this gets all the students that failed but I don't know how to go from this to all the students that failed at least at all the courses that student with id  = 1 failed. If someone could point me in the right direction I would be grateful!

Additional details

For example: If student with id = 1 failed at courses with ids = 2,3. And we have other two students who failed at courses with ids  = 2,3 (like student 1) and they might fail at other courses too, then I want to display those two students.


texts: Automatically add core fields to a table when created

texts:
I was reading these articles on StackOverflow regarding triggers and created date and updated date in mysql, and was wondering whether it is possible to create a trigger that checks if these two fields are present in the create table statement, and either creates the relevant trigger, or creates the fields, as well as the trigger? I am aware that there will be some tables for which this is not relevant, but I will address that in the future.

What is the best way to set up last-modified and date-record-added fields?

adding column to all tables in a mysql database (unknown table names)


texts:
I recently received a warning from MySQL that stated:

[Warning] 'proxies_priv' entry '@ root@serverdomain' ignored in --skip-name-resolve mode.

I looked in mysql.proxies_priv and there is definitely an entry for "serverdomain", however there is no user with "@serverdomain".  Documentation on this is very sparse, this is all I could find.

How do I remove this entry and how was it ever created?


texts:
We have two servers that were set very similarly, but not identically. These two servers have hugely different performance running the same application.

On server A the application runs successfully (although slowly).

On server B the application runs successfully (and a bit faster) initially, but soon grinds to a halt with SQL timeout issues.

After some investigation I found that the write latency to the temp DB on Server B was 14s (not a typo) (using the query from here: http://www.sqlskills.com/blogs/paul/how-to-examine-io-subsystem-latencies-from-within-sql-server/).
I thought that this might be down to the fact that the temp DB was on the C: drive on Server B (and the swap file was being used). So I have moved the temp DB to the D: drive (where the other database files live). After restarting SQL server the write latency was intially around 1s - still terrible from what I have read, but the application was running happily in that state so it would be good enough for now. Less than 24 hours later the write latency on the temp DB has climbed to 11.5s and we're seeing timeouts again.
The temp DB write latency on server A is under 10ms.

So my question is, what could be causing this rather dramatic difference and change in performance?

My SQL is rather rusty so please don't assume that something is too obvious to be the solution!

The main differences that I know of between the two servers are:

Server A has two disks in a RAID mirror, the mirror is then split into two partitions, whilst Server B has two disks not in any kind of RAID set up
Server B has a couple of extra databases on it, although I don't believe that they are used (PerfStats, ReportServer, ReportServerTempDB).
Server B has eight tempDB files, whilst Server A only has a single file
Server B is running on a slightly stripped down database that has been reduced to a single big customer's data. Server A is processing this customer and a number of others (although the other customers take up a tiny fraction of the processing power needed for the big customer)
The database on Server B was stripped down by a SQL specialist who may well have changed other things that I'm not aware of :( (e.g. they are probably responsible for the 8 temp DB files)

Also in case it helps, there is another desktop machine also running the application as a test. This machine appears to have a single drive split into a couple of partitions, half the RAM of server A and server B but a much more modern CPU. It is performing rather faster than either server and has a tempDB write latency of around 0.5s. Again I think this indicates that we don't need great write latency, just not so terrible that it causes timeouts.


texts: Sum all unique values from previous dates

texts:
Let's say, for simplicity sake, I have the following table:
id      amount     p_id     date
------------------------------------------------
1       5          1        2020-01-01T01:00:00
2       10         1        2020-01-01T01:10:00
3       15         2        2020-01-01T01:20:00
4       10         3        2020-01-01T03:30:00
5       10         4        2020-01-01T03:50:00
6       20         1        2020-01-01T03:40:00

Here's a sample response I want:
{
 "2020-01-01T01:00:00": 25, -- this is from adding records with ids: 2 and 3
 "2020-01-01T03:00:00": 55  -- this is from adding records with ids: 3,4,5 and 6
}

I want to get the total (sum(amount)) of all unique p_id's grouped by the hour.
The row chosen per p_id is the one with the latest date. So for example, the first value in the response above doesn't include id 1 because the record with id 2 has the same p_id and the date on that row is later.
The one tricky thing is I want to include the summation of all the amount per p_id if their date is before the hour presented. So for example, in the second value of the response (with key "2020-01-01T03:00:00"), even though id 3 has a timestamp in a different hour, it's the latest for that p_id 2 and therefore gets included in the sum for "2020-01-01T03:00:00". But the row with id 6 overrides id 2 with the same p_id 1.
In other words: always take the latest amount for each p_id so far, and compute the sum for every distinct hour found in the table.


texts: How to update one database from another?

texts: I'm using SQL Server 2008. I have two databases called Adb and Bdb. There is a common table called commonTable. How can I update Adb database every 15 minutes using the data from the same table in Bdb?


texts: SQLCMD :connect directive - how can I specify connection protocol?

texts:
In a script I have the directive

:connect DbServer

and it seems that if I run this script in SQLCMD (like this:

sqlcmd -i script.sql

then sqlcmd attempts to connect using Named Pipes (which doesn't work). I get error messages saying that Named Pipes provider could not connect.
However, if I try to connect to the same server by specifying its name in the -S parameter, it works fine.

Is there a way to let the :connect directive know that I am trying to connect by TCP/IP?

I tried using :connect tcp:DbServer to no avail. Also tried to go by IP address, same results. On this machine I don't have the SQL Server Configuration Manager to specify connectino aliases or manipulate protocols. Maybe that's the next thing to do, but I'd like to know if somebody knows a way to contain the connection specification inside the script.


texts: How do I ensure I have split my tables without loss of data?

texts:
This is w.r.t Database Normalisation, when I have a redundant table that causes anomalies, I would want to consider splitting it in smaller tables as per the NF rules.
BUT
Say I split a big table into two eg. Student and Marks Table my questions is ---
How do I know I've done the right split?
Or to put it in another way,
How can I cross check if I haven't lost any information after the split i.e. I can still do a join on both tables and get my un-normalized table back?
This is easy to inspect visually when you have a small number of attributes, but what if the table is really huge.
How can I ensure that I've split the table in a right manner?
When I was in college my professor spoke of some equation which must always be true whenever you split tables, that ensures that the split is done without loss of any information. And by doing the right joins we can get the big dirty redundant table back.


texts: SQL Server seems to be ignoring specified backup file extension

texts:
I have created a maintenance plan using the Wizard and this is the SQL produced, per SSMS:

BACKUP DATABASE [FOO] TO  DISK = N'C:\SQLBAKS\FOO\FOO_backup_2019_08_09_161656_6414445.diff'
WITH DIFFERENTIAL , NOFORMAT, NOINIT,
NAME = N'FOO_backup_2019_08_09_161656_6414445',
SKIP, REWIND, NOUNLOAD,
STATS = 10

The resulting files do not have a ".diff" extension but ".bak"

I am trying to differentiate FULL backups from DIFFERENTIALS in the filename.


texts: Triggers After Insert and After Update - Good Practice

texts:
It is a good practice to have a separate trigger for After Insert and another one for After Update or there is no problem in having both in the same logic?

Also from a perspective of performance it will be better to have them isolated?


texts: 2ndQuadrant BDR installation not working anymore

texts:
I previously used the following to install BDR (9.4) on Ubuntu:

echo "deb http://packages.2ndquadrant.com/bdr/apt/ trusty-2ndquadrant main" > "/etc/apt/sources.list.d/2ndquadrant.list"
wget --quiet -O - http://packages.2ndquadrant.com/bdr/apt/AA7A6805.asc | sudo apt-key add -
echo "deb http:// apt.postgresql.org/pub/repos/apt/ trusty-pgdg main" > "/etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - https:// www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
apt-get update
apt-get install bdr-9.4

Since the end of May when I make use of this, it only installs normal PostgreSQL.
I've tried this on Ubuntu 14.04, 15.10 and 16.04.

Any help in this regard would be highly appreciated.


texts:
I'm trying to design an activity feed, but I have a small problem.

Here's my current table.

activity

id
user_id
status
activitytype_id (status, video, photo, etc)
created_at
location
video

I didn't add some unrelated columns to make it short.

This works when I want to create a feed for the friends' posts of a user. I basically create an array with PHP and check for user_id's in the activity table and sort them by date.

But a user also follows groups and events, this is the tricky part. Do I need to create a second query for groups and events and merge with the user posts together?

What's the best way to include them (groups and events) in the feed? I can add a column to the activity called event_post and group_post and add posts id's as foreign keys to the activity table (null by default), but I'd like to hear your opinions.

communities

id
communitytype_id (group or event)
name
created_at
user_id (FK from users table, owner of the group or event)


texts: Recommended structure? %70 write %30 read. 10M row. 200 query/sec. Select, update, insert, search


texts: how to convert a master cluster to a slave cluster?

texts:
I am learning about PostgreSQL streaming replication and so far I have learned how to create a slave cluster using:

pg_basebackup -x -R -D main -h host -p port

I also have learned that I can promote a slave to be a master like this:

pg_ctl -D main promote

or

pg_ctlcluster 9.4 main promote

(if you are working on Ubuntu).

What I like to do now is to say that my previous master is now a slave of the new master. So far I have learned that I can do it by removing the data directory of server, and running pg_basebackup again. This means that I have to wait until all of my files are copied back from the new master the old one, which means a lot of data traffic.

Is there another way to do the same thing without copying all the data again?


texts: How to Sum Each Enum Value in a Different Column and Don't Repeat Person's Name?

texts:
Let's say I have a table with SellerId, DateTime, ProductType to register each time a a Seller sells a product. The ProductType is from ENUM type with { 0=Phone, 1=Recharge, 2=SimCard }. Then I have a table with SellerId and SellerName.

I want to write a stored procedure that shows this result:

  ID | NAME | Phones | Recharges | SimCards | Total
 ----|------|--------|-----------|----------|-------
  05 | Eddy |      6 |         7 |        0 |    13

I know how to join the two tables to get the ID and NAME in this result set, but I do not know how to SUM each ProductType into a different column. And I also wanted only one row for each ID/NAME.

Can anyone show me how to achieve this in this simple example?


texts: Best practices on common person fields (Name, email, address, gender etc...)

texts:
What are the most common best practices on length and data type on common fields like:

First Name
Last Name
Address
Email
Sex
State
City
Country
Phone Number

etc....


texts:
I wanted to install MySQL on my dev Fedora machine for my work. I followed the following tutorial: Install MySQL 8.0/5.7 on Fedora 30/29. However, when I tried to start the service via sudo systemctl start mysql.service, I was not able to.

> sudo systemctl start mysqld.service
Job for mysqld.service failed because the control process exited with error code.
See "systemctl status mysqld.service" and "journalctl -xe" for details.

> mysqld
2019-09-17T17:24:18.516842Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-09-17T17:24:18.516948Z 0 [Warning] Can't create test file /var/lib/mysql/xzhans-xps15-on-fedora.lower-test
2019-09-17T17:24:18.516967Z 0 [Note] mysqld (mysqld 5.7.27) starting as process 8754 ...
2019-09-17T17:24:18.518447Z 0 [Warning] Can't create test file /var/lib/mysql/xzhans-xps15-on-fedora.lower-test
2019-09-17T17:24:18.518455Z 0 [Warning] Can't create test file /var/lib/mysql/xzhans-xps15-on-fedora.lower-test
2019-09-17T17:24:18.518690Z 0 [ERROR] Could not open file '/var/log/mysqld.log' for error logging: Permission denied
2019-09-17T17:24:18.518704Z 0 [ERROR] Aborting

2019-09-17T17:24:18.518712Z 0 [Note] Binlog end
2019-09-17T17:24:18.518742Z 0 [Note] mysqld: Shutdown complete

However, the log file has correct permissions:

> sudo ls -la /var/log/mysqld.log
-rw-r-----. 1 mysql mysql 42880 Sep 17 13:23 /var/log/mysqld.log

Any help is appreciated. Thanks in advance.


texts: Possibility to implement time to execute / TempDB usage restrictions on some role groups in SQL Server?

texts:
I have a SQL Environment of which I am sysadmin. This Environment includes the Company data warehouse. I conduct many different jobs from this Environment, and it is also Another sysadmin which has access to it.

Seeing that it contains the data warehouse, some users from the production team has access to it as well.

The underlying raw data is very spread out. The data comes from many different sources, which sometimes requires excessive use of OUTER JOINs, Temp Tables, Procedures etc. in order to fetch the data of interest.

I have seen that some users in the production team has sometimes done massive queries, resulting in TempDB growing uncontrollably by 50+ GB, eventually resulting in the server shutting down (thus far, we have the SQL servers and TempDB on the same drive).

Is there a way to prohibit the production team of using too big queries, while not putting the same restrictions on the sysadmins, as there is sometimes neccessary to conduct TempDB extensive queries given the complex raw data structure?

I do not want to hinder the production team of using/creating Stored procedures, Temp Tables etc. I just want to investigate if there is an option to restrict execution time / TempDB usage.


texts:
how to design movies with show times database structure

I'm building simple local cinemas guide website using Yii PHP framework.

I need Correct and improve this database, specially for showtimes

Cinema / done
City / done
Country / done

Movie
  id
  title
  year
  imdb_rate
  cast
  director
  summary
  views
  featured
  available_status

Genre
  id
  name
  movie_id

movie_showing
  id
  available_status
  show_time
20:00

22:00

  movie_id
  cinema_id

movie_video
  id
  youtube_uri
  movie_id
  trailer

movie_photo
  id
  photo_uri
  poster 0 1
  movie_id


texts:
i am working with a Postgresql database

having two tables:
professionals[professional_id,user_id,account_id]

users[user_id email]

accounts[account_id email]

professionals

 [professional_id,user_id,account_id]

    1,          2000,       null
    2,          3000,        120
    3,          4000,        155

users

   [user_id,   email]
    2000,   mike@gmail.com
    3000,   joe@gmail.com
    4000,   jack@gmail.com

accounts

   [account_id ,    email]
   120,          maria@gmail.com
   155,         travis@gmail.com

Here i have to get the email with doing  a join between accounts and professionals.

If account_id is not null we get the email

If the account_id is null we do the join between the users and professionals (we are sure that user_id is always not null)

Here is what i did :

select email from professionals
IF (u.id=m.user2ID)
left JOIN accounts ON accounts.account_id = professionals.account_id
ELSE
LEFT JOIN users  ON users.user_id = professionals.user_id;

Thanks indeed for any help


texts: What is the difference between hadoop, mongoDB, Big data?

texts: What is the difference between Hadoop, MongoDB, Big data ?


texts: Getting 'Connection refused' after installing PostgreSQL and PG Admin 4

texts:
I had installed PostgreSQL Server on a machine. I had installed PGAdmin4 on another machine but when trying to connect, I am getting error on the startup of the PG Admin4 'Connection refused'.

How can I get it working?  I have just started using PostgreSQL.


texts: Why does PostgreSQL create an additional NULL bitmap if all fields after the 8th are NOT NULL?

texts:
I don't refer to the 1 Byte null bitmap included in the row header that could be used to  flag null values for any of the first 8 columns. See:

https://stackoverflow.com/questions/12145772/do-nullable-columns-occupy-additional-space-in-postgresql/12147130#12147130

My question is about the new 8 bytes null bitmap added by Postgresql to each row:

with 9 or more columns
and including at least one null

I have done some tests by creating / populating some tables and checking row size using pgstattuple extension and I have observed this strange behavior.

The new null bitmap is created even if the 9th (and last) column is created with NOT NULL constraint.

Do you have any explanations? Or am I doing something wrong?


texts: Can anybody tell me what's wrong with this query?

texts:
CREATE PROCEDURE Click(
    in  ipaddress varchar(45))
BEGIN
    DECLARE ex int;
    SELECT COUNT(*) INTO ex FROM clickactivity WHERE IP = ipaddress;
IF ex = 0 THEN
    INSERT INTO clickactivity (IP, Clicks) VALUES(ipaddress, 0);
END IF;
    UPDATE clickactivity SET Clicks = Clicks + 1;
END

It complains about line 4 like so:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4


texts: Remove extra return from median calculation

texts:
I have a FileMaker database where I want to calculate the median value of 3 different fields. I used the code that can be found here.

In my setup, it looks like this:

Let ([
beak.trait = List (beak_depthA; beak_depthB; beak_depthC);
valuelist = SortValues ( beak.trait; 1);
count = WordCount ( valueList );
odd =  MiddleValues ( valueList ; Truncate (count/2;0) +1 ; 1 );
even = MiddleValues ( valueList ; Truncate (count/2;0)    ; 1 )];
Case( Mod ( count ; 2 ) ; odd ; (even+ odd) / 2))

When there is an even number of data in the cells it generates this:

However, when including 3 measurements, I get this:

But if I look into the data, it's there, with an extra line return...

Would there be a way to get the value, when the number of fields is odd without the extra return?

How the code would be affected?


texts:
I have successfully installed and started postgres-xl on localhost(Ubuntu 14.04).
(Two datanodes, 1 coord)
The problem is: 0 rows after insert

CREATE TABLE test1 ( id bigint NOT NULL, profile bigint NOT NULL,   status integer NOT NULL, switch_date timestamp without time zone NOT NULL, CONSTRAINT test1_id_pkey PRIMARY KEY (id) ) to node (datanode1, datanode2);

 insert into test1 (id, profile, status, switch_date) select a, round(random()*10000), round(random()*4), now() - '1 year'::interval * round(random() * 40) from generate_series(1,10) a;
INSERT 0 0

select * from test1;
 id | profile | status | switch_date
----+---------+--------+-------------
(0 rows)

And finally

SELECT pg_size_pretty(pg_database_size('test1'));
 pg_size_pretty
----------------
 0 bytes
(1 row)

I followed the instructions (with my parameters) but there was error
on creating database, and to fix it I run commands on my nodes:

  $ /usr/local/pgsql/bin/psql -p 15432 -c "CREATE NODE coord1 WITH (TYPE = 'coordinator', PORT = 35432)" postgres
  $ /usr/local/pgsql/bin/psql -p 25432 -c "CREATE NODE coord1 WITH (TYPE = 'coordinator', PORT = 35432)" postgres
  $ /usr/local/pgsql/bin/psql -p 15432 -c "select pgxc_pool_reload()" postgres
  $ /usr/local/pgsql/bin/psql -p 25432 -c "select pgxc_pool_reload()" postgres

And on coord:

SELECT pgxc_pool_reload()

After I created database, tables and DDL-commands(create table, alter table) were executed successfully,I see changes (all nodes)  but now I got problems with inserting, because no errors occures but tables empty!

My configs:

~$  find /usr/local/pgsql -name *.conf -print -exec grep "5432\|666" {} \;
/usr/local/pgsql/data_coord1/pg_hba.conf
/usr/local/pgsql/data_coord1/pg_ident.conf
/usr/local/pgsql/data_coord1/postgresql.conf
port = 35432                # (change requires restart)
pooler_port = 36667         # Pool Manager TCP port
gtm_port = 6666         # Port of GTM
/usr/local/pgsql/data_gtm/gtm.conf
port = 6666             # Port number of this GTM.
/usr/local/pgsql/data_datanode1/pg_hba.conf
/usr/local/pgsql/data_datanode1/pg_ident.conf
/usr/local/pgsql/data_datanode1/postgresql.conf
port = 15432                # (change requires restart)
pooler_port = 16667         # Pool Manager TCP port
gtm_port = 6666         # Port of GTM
/usr/local/pgsql/data_datanode2/pg_hba.conf
/usr/local/pgsql/data_datanode2/pg_ident.conf
/usr/local/pgsql/data_datanode2/postgresql.conf
port = 25432                # (change requires restart)
pooler_port = 26667         # Pool Manager TCP port
gtm_port = 6666         # Port of GTM

What I am doing wrong?

P.S. no matter what kind of distribution - allways empty

) distribute by REPLICATION; ...)  distribute by HASH(id); ....) distribute by MODULO(status);

or default create table t1(id serial, k integer);

all off them empty after insert, insert always returns

INSERT 0 0


texts:
I uploaded some tables to SQL Server Management Studio. Upon inspecting the tables I've found that the column headers area actually values. Is there any way to bump the headers down into a new row and to create new column headers, or will I have to re-upload all of the tables and remember to select the 'has header' option?

Thanks.


texts: parallel pg_restore with materialized views

texts:
I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS.

To troubleshoot the restore process, I have split the restore into 5 steps using the --section and the -L option. The steps are

Restore the pre-data section, but without materialized views, -j 1
Restore the data section in parallel, -j 5
Restore the post-data section in parallel, -j 5
Restore the materialized views, creation section (pre-data), -j 1
Restore the materialized views, refresh section (post-data) in parallel -j 5

Steps 1-4 runs without any problems. In step 5 I run into trouble. The step takes forever and if I list the pg_stat_activity I can see two processes that runs the same REFRESH MATERIALIZED VIEW

-[ RECORD 3 ]---------------------------------------------------
pid     | 17244
query   | REFRESH MATERIALIZED VIEW home_property_data;
        |
        |
        |
waiting | f
... snip ...
-[ RECORD 7 ]---------------------------------------------------
pid     | 17243
query   | REFRESH MATERIALIZED VIEW home_property_data;
        |
waiting | f

If I read the documentation for the MATERIALIZED views it looks like that they cannot be updated in parallel. Am I doing the restore wrong ?


texts: Tools for synchronizing database schema with Oracle, SQL-Server and Netezza databases

texts:
I'm looking for software tools to move database schema for Oracle, SQL Server and Netezza.  We use Oracle for our ODS, Netezza for our Data Warehouse (for it's bulk loading capabilities) and SQL Server for internal tool development.

When we move data schema from one environment to another, it's all done manually.  This takes a lot of time.  And we use Informatica to move the actual data.  To my knowledge, Informatica cannot move database schema.  What tools can I buy that can move database schema for all three database management systems?

I see Red Gate SQL Compare can move schema.  But it doesn't look like it can handle Oracle or Netezza.  If you don't know one single tool that can be purchased to move schema for all 3 DBMSs, can you give let me know what tools folks recommend for each one?

PS: I didn't see a Stack Exchange [tag] for Netezza.  But I believe Netezza is based on PostGres, so I included that in the tag list.


texts:
I'm using PostgreSQL on Windows for Planet OSM database and have noticed considerable decrease in performance when upgrading from v10 to 11 or 12.
Here are the details of the experiment I conducted trying to figure out what is causing the issue.

Installed PostgreSQL 10 from scratch.
Created a database and a table.

CREATE TABLE ways (
    id bigint NOT NULL,
    version int NOT NULL,
    user_id int NOT NULL,
    tstamp timestamp without time zone NOT NULL,
    changeset_id bigint NOT NULL,
    tags hstore,
    nodes bigint[]
);

Imported ways data from a file and added a primary key.

SET synchronous_commit TO OFF;
COPY ways FROM 'E:\ways.txt';
ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);

The file is 365GB in size.

  The copy operation took 3.5h and the
  resulting table size is 253GB. The primary key operation took 20
  minutes and occupied 13GB of disk space.

Then I uninstalled PostgreSQL v10, deleted the data directory and installed v11 from scratch. Created the same kind of database and table. v11 is not able to handle large files, so the I piped the data through the cmd type command, and then added the primary key with the same command as above. synchronous_commit turned off beforehand as above.

COPY ways FROM PROGRAM 'cmd /c "type E:\ways.txt"';

  The copy operation took 7 hours and adding primary key took 1h 40m !
  The resulting table and pk sizes are the same as in v10. Also very
  high load on disk drive (quite often at 100%) was observed.

v12 performs the same as v11.

Here are the changes in v11 default postgresql.conf file compared to v10 one.
Differences in Authentication, Replication and Logging sections are skipped.

-#replacement_sort_tuples = 150000
+#max_parallel_maintenance_workers = 2
+#parallel_leader_participation = on
~max_wal_size = 1GB     (in v10 is commented out)
~min_wal_size = 80MB    (in v10 is commented out)
+#enable_parallel_append = on
+#enable_partitionwise_join = off
+#enable_partitionwise_aggregate = off
+#enable_parallel_hash = on
+#enable_partition_pruning = on
+#jit_above_cost = 100000
+#jit_inline_above_cost = 500000
+#jit_optimize_above_cost = 500000
+#jit = off
+#jit_provider = 'llvmjit'
+#vacuum_cleanup_index_scale_factor = 0.1

So what is trapping the performance?

Update

I have managed to split the 365GB file into 2GB chunks with the help of split unix utility in mingw shell like so

split -C 2G ways.txt

Then I imported the files into a clean database with the help of the following cmd command

for /f %f in ('dir /b') do psql -U postgres -w -d osm -t -c "set client_encoding TO 'UTF8'; copy ways from 'D:\ways\%f';"

The operation took ~3.5 hour which is the same as v10!

Prior to that I set parallel_leader_participation = on and synchronous_commit = off in the config file and restarted the server.

Then I logged into the psql interactive terminal and ran

ALTER TABLE ONLY ways ADD CONSTRAINT pk_ways PRIMARY KEY (id);

It took 1h 10m which is 30m faster than with the default settings (after the type command if it really matters) but still 3 times slower than in v10.


texts: How should I design a relationship table for friendship?

texts:
If A is a friend of B, then should I store both values AB and BA, or one is enough? What are the advantages and disadvantages of both methods.

Here is my observation:

If I keep both then I have to update both when receive a request from a friend.
If I don't keep both, then I found it difficult when having to do multiple JOIN with this table.

Currently, I keep the relationship one way.

So what should I do in this case? Any advice?


texts: How to exclude data if another column is null?

texts:
This SQL query returns a column called "organisation_names" however the data is coming from a column called organisation_users and I would like to exclude "organisation_names"
if...
organisation_users.delete_at === null
otherwise return the column
Considering the SQL query below, got any ideas?
I was considering adding column:
GROUP_CONCAT(CONCAT(organisation_users.deleted_at)) as organisation_users_deleted_at
Then excluding it on the query if it has a date...
select
users.*,
user_types.type,
agent_profiles.user_id,
contacts.status,
contact_numbers.number as number,
GROUP_CONCAT(CONCAT(organisations.trading_name, '~', organisations.id)) as organisation_names
from `users` left join `agent_profiles` on `users`.`id` = `agent_profiles`.`user_id` left join `contacts` on `agent_profiles`.`contact_id` = `contacts`.`id` left join `contact_numbers` on `contact_numbers`.`contact_id` = `contacts`.`id` and `contact_numbers`.`primary` = '1' left join `organisation_users` on `organisation_users`.`user_id` = `users`.`id` left join `organisations` on `organisations`.`id` = `organisation_users`.`organisation_id` left join `user_user_types` on `user_user_types`.`user_id` = `users`.`id` left join `user_types` on `user_types`.`id` = `user_user_types`.`user_type_id` where ((`contacts`.`status` is null and `agent_profiles`.`user_id` is not null) or `user_types`.`type` = 'Admin' or (`contacts`.`status` is null and `agent_profiles`.`user_id` is null and `user_types`.`type` != 'Admin') or `contacts`.`status` in ('Active', 'Inactive', 'On-Hold')) and (CONCAT(users.first_name, ' ', users.last_name) LIKE '%john smith%' or email LIKE '%john smith%' or number LIKE '%john smith%' or user_types.type LIKE '%john smith%' or contacts.status LIKE '%john smith%') and `users`.`deleted_at` is null group by `users`.`id` order by `users`.`first_name` asc limit 10 offset 0


texts:
DBA newbie here. Did some searching online but cannot find a definite answer on this.
Is it acceptable to share the drive where .MDF files reside with other non-SQL Server files such as applications that go under a Program Files folder?
I assume it is not but maybe this depends on the type of storage that is used? We use SAN storage.
I'll rephrase....If the drive that contains .MDF files is shared with other non-SQL Server applications, could this potentially affect SQL Server's read/write performance depending on the storage that is used?
The answer isn't obvious to someone that is new to SQL Server, let alone hardware storage. If everything is located on the same SAN, does it really matter if they are on the same drive? I'm researching and can't find the answer and the only SQL Server hardware book out in the market is from 2011.


texts:
We have a package that performs data extraction from multiple external systems.  It has been running with no issues for a long time.  A few days ago, it failed with the following error:

The system reports 9 percent memory load. There are 1099463696384 bytes of physical memory with 991419998208 bytes free. There are 2147352576 bytes of virtual memory with 174698496 bytes free. The paging file has 1153150787584 bytes with 1033803776000 bytes free.

I have read a lot of suggestions on the internet regarding setting max server memory but as it has been running for all this time with the configuration, I am not sure why that would be a factor now.  I did change it to a lower number but set it too low and SQL Server would start up.  Had to go thru single user mode to reset it back.  The server has 1TB RAM.

I also saw something about setting the buffers but I am unable to find where to set that.

The package runs fine within Visual Studio but fails when run thru SQL Server Agent.  Also, although it is a 64bit server, everything has been configured to run as 32bit.

Disclaimer: I am programmer and not a DBA so I am just a novice trying to figure this out cold so all suggestions are welcome but please keep that in mind when submitting your responses. thanks!


texts: How to select all attributes of object type

texts:
I am running select query with a function in it. That function returns an object type with 4 attributes and I'd like to select all those attributes.

When I try it int this way. It returns just the type object as a whole.

SELECT SERVICE_TYPE, FN_GET_ADDRESS_DETAIL(USER_ID) FROM REG_ADDRESS;

It returns:

SERVICE_TYPE | FN_GET_ADDRESS_DETAIL(USER_ID)
---------------------------------------------
0            | USERNAME.ADDRESS_DETAIL_T
0            | USERNAME.ADDRESS_DETAIL_T
0            | USERNAME.ADDRESS_DETAIL_T

My object type is like this:

CREATE OR REPLACE TYPE ADDRESS_DETAIL_T AS OBJECT
(
  apt_number        NUMBER,
  district_name     NVARCHAR2(40),
  zipcode           NUMBER,
  whatever          NVARCHAR2(40)
);

And it works if I specify attribute names after the function call. But in order to select all the attributes, I have to call the function 4 times! O_O

SELECT SERVICE_TYPE, FN_GET_ADDRESS_DETAIL(USER_ID).apt_number,
 FN_GET_ADDRESS_DETAIL(USER_ID).district_name,
 FN_GET_ADDRESS_DETAIL(USER_ID).zipcode,
 FN_GET_ADDRESS_DETAIL(USER_ID).whatever, FROM REG_ADDRESS;

Is there any other options?


texts: Database design of Room Booking system in a Hotel

texts:
Intro and System Description

I am currently designing a Database for a Room Booking system in a Hotel.

Client fills a Form / Request with following information about Room:

amount of people in room
rating of a room
check in and check out date

Administrator has a dashboard with list of Forms from Clients, he will assign each Room for every Client manually.
After this User gets a Bill.

Database Design

This is actually a Sketch of my DB. I'll have following tables:

Users
Forms
Rooms
Bills

I am not considering table Passwords which contains Password's Hashes.

Questions

I would like to hear from you what is missing in my DB Design, what do you think about overall logic and correctness of relations between tables.


texts: Is Data Warehousing possible in MySQL and PostgresSQL

texts:
I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I have no luck of running these kind of queries, so I am manually calculating this data and storing in tables.

To be more specific of my application, I need to show charts/graphs on iPhone. So a users usage of words per language per day/week/month. I dont need realtime but the rows for one month of usage for a user is approx 5000.

I want to understand if it is possible for mysql to have warehouse scehemas and execute such queries without performance issues. What could be my options.

Postgres?
MySQL Enterprise?
Cubes

I really cant pay for enterprise solutions, I am ready to write code for this kind of processing in my application (which I have) but want to migrate to correct way.

I guess the most ideal case for me would be that without using any tools I can create fact and dimensions in my current db and then shift my api to run these olap queries. From what I have gathered this is not possible for mysql. Postgres is showing some promise but still reading.


texts: How to improve performance of query for distinct suppliers in big sales table?

texts:
I have two tables, one with store data:

              Table "public.store"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
 id        | integer                | not null
 hul_code  | character varying      |
 latitude  | numeric                |
 longitude | numeric                |
 name      | character varying(100) |
Indexes:
    "store_pkey" PRIMARY KEY, btree (id)
    "store-hlcode-idx" btree (hul_code)

Another with the sales data:

         Table "public.sale_data"
  Column   |       Type        | Modifiers
-----------+-------------------+-----------
 id        | integer           | not null
 hul_code  | character varying |
 partyname | character varying |
 rscode    | character varying |
 rsname    | character varying |
 cluster   | character varying |
 channel   | character varying |
 basepack  | character varying |
 total_16  | numeric           |
 total_17  | numeric           |
 total_18  | numeric           |
Indexes:
    "sale_data_pkey" PRIMARY KEY, btree (id)
    "hul-code-idx" btree (hul_code)
    "sd-bp-idx" btree (basepack)
    "sd-rscd-idx" btree (rscode)

The second table has over 11 million rows.

I'm trying to get the list of all stores which had items coming from a particular Regional Supplier (identified by rscode) by using a query like:

Select s.hul_code, s.name, s.latitude, s.longitude
from store s
Where hul_code in
  (Select Distinct(hul_code) as hulcode
   from sale_data
   where rscode='133955')
AND s.latitude is not null;

This query takes more than 1.5 to 2 seconds on average.

How to increase the speed of this query?


texts: Copying the value of ROWVERSION to another column in same row on insert

texts:
While pondering this question Is there a function like min_active_rowversion for Identity?, I thought about another possible solution for my problem - using ROWVERSION as primary key (and monotonically increasing sequence number).

Of course nobody thinks that's a good idea as ROWVERSION changes all the time, on updates to the row.

I wonder though, if it's possible to copy the ROWVERSION value to another column on insert. This column would not be updated on update and could thus be used as  primary key and sequence number?


texts:
In mongoDB Many-to-Many Relationship Data Modeling by Mark Starkman, he tries to see how many-to-many relationship can be implemented in the MongoDB database.

He suggests the following code will help us implement many-to-many relationship can be implemented in the MongoDB database:

An example of person's collections:

db.person.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a43"),
  "firstName": "Joe",
  "lastName": "Mongo",
  "groups": [
    ObjectId("4e54ed9f48dc5922c0094a42"),
    ObjectId("4e54ed9f48dc5922c0094a41")
  ]
});

db.person.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a40"),
  "firstName": "Sally",
  "lastName": "Mongo",
  "groups": [
    ObjectId("4e54ed9f48dc5922c0094a42")
  ]
});

An example of Group collection:

db.groups.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a42"),
  "groupName": "mongoDB User",
  "persons": [
    ObjectId("4e54ed9f48dc5922c0094a43"),
    ObjectId("4e54ed9f48dc5922c0094a40")
  ]
});

db.groups.insert({
  "_id": ObjectId("4e54ed9f48dc5922c0094a41"),
  "groupName": "mongoDB Administrator",
  "persons": [
    ObjectId("4e54ed9f48dc5922c0094a43")
  ]
});

The aforementioned solution is alright if a person can belong to groups on the 100s, and vice versa for groups.  However, Array of ObjectIds would Not be practical if a person can belong to 1000000 groups, and vice versa for groups.

What would be a better many-to-many solution in MongoDB of the relationship would number 1000000?


texts: SSDs tend to have a natural 4K sector size as far as I know, but would it be beneficial to format Windows cluster size at 64K? This is good practice for spinning rust, but is it relevant on SSDs?


texts: MySql replication with Raw Data and InnoDB tables

texts:
I want to start replication on DB that has many InnoDB tables and MyISAM tables.
I did everything according to manual Creating a Data Snapshot Using Raw Data Files.

Firstly, I tried copied all tables files with relay log  and etc. without ib_logfile and ibdata. Replication didn't work it couldn't find InnodDB tables, after that I tried to do the same with ib_logfile and ibdata, it failed with error message Error 'Unknown table engine 'InnoDB'' on opening tables.

When I tried to copy my.cnf from original server mysql failed to start at all (version 5.1.41).

I am not sure if it's possible to start replication with raw data and InnoDb files. Please, if you have experience, share it with me. How can I deal with it.

Thanks!


texts: SQL Server 2008 R2 Reporting Services: "Exception of type System.OutOfMemoryException was thrown"

texts:
When I run a report on SSRS reporting services to fetch 600000 row.. I get a error "Exception of type System.OutOfMemoryException was thrown".

I read so many blog's for solving this issue but still I can't to solve it.

I am working on 32 bit OS(window server 2008) and SQL Server 2008 R2 32 bit.

Is it possible to solve this issue?


texts:
I've got a file with timestamps like 20130807183000000. I'm trying to load using timestampformat="YYYYMMDDHHMMSSUUU" and I'm getting the error

  SQL0181N  The string representation of a datetime value is out of range.

I'm pretty sure the issue is that db2 is interpreting the minutes as months. I've been unable to find any documentation on how to explicitly format it as a minute for the load statement. I tried using 'MI' since that works for the timestamp_format function, but no luck. Does anyone know of any hidden documentation or have any suggestions that don't involve editing the file? Thanks.


texts: How to install the additional module pg_trgm

texts:
I just want to know how to install the module pg_tgrm as used in the trigram indexing scheme that allows you to do un-anchored search patterns on an index.

WHERE foo LIKE '%bar%';


texts: Spatial query to get enclosing radius/polygon of result set

texts: Is it possible in SQL to run a query of spatial data, and then just return a single polygon, or a radius, that encloses, aggregates, all of the spatial data that was in that query into one feature?


texts: Need to resolve a Library Cache Lock blocking an impdp job from Oracle 11g Dump file to Oracle 19c

texts:
I have been trying to get a Data Pump job to complete and am stuck with the following specifics:

I am importing an 11g dump file into an 19c instance on a Windows 2012 R2 sever using sys as sysdba from sql plus.
The job appears to 'hang' at several places and I am getting timeout errors that result in failed DLL execution from the dump file. I have run several maintenance script to check on which processes are blocking and whether the Data Pump workers are indeed WAITING.  They are. STATUS from the Import> prompt shows the first of the 4 workers executing but three waiting.
The script reveals that there are three sids blocking with Library Cache Lock designation.
As per another article here, I have executed 'ALTER SYSTEM QUIESCE RESTRICTED;' to prevent interruptions from competing processes during the impdp job. I am still getting the Library Cache Lock blockers. Is there a way to drill down to the actual blocking 'Library Cache' process and kill it?  Freeing the job to complete? Any advice is greatly appreciated.

I will kill and re-run this job as system not sys as sysdba and see if there is a difference.
I have run the impdp job as SYSTEM without RESTRICTING the database.  The job is stalled although Import>STATUS shows JOB and WORKER 1 EXECUTING. The diagnostic scripts run from SQL Developer show no blocking processes this time. Is there any other way to diagnose what has stalled this job?
UPDATE: One of the data pump progress scripts drawing on data from V$SESSION_WAIT where sw.wait_class <> 'Idle' shows the EVENT to be 'SQL*Net message from dblink'.  This is where the job consistently hangs and although Import>STATUS shows MASTER and one WORKER EXECUTING, they are all waiting indefinitely.  The research I have done on the EVENT refers to traffic across a network.  There is a DBLINK in the 19c database to an 11g database, however, would that be utilized when executing DDL exclusively from a dump file?  I have created an sql file from the impdp job with all the DDL and searched for the script immediately after the last recorded remark in the impdp command window.


texts:
I installed oracle client 11.2.0 version in my machine and also installed Oracle data access providers for visual studio.I also installed Oracle Data Access 11.2.0.3.20. I am trying to connect to the database through the visual studio i am getting the below exception

ORA -12504 : TNS: listner was not given the SERVICE_NAME in CONNECT_DATA**

The tnsnames.ora file contains the folloing code

creds=
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = aaa.cedd.net)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = creds)
    )
  )


texts: I'm trying to find when a database on our instance was configured as SINGLE USER MODE but can't find that information. Either using tsql or the GUI. Maybe it is not possible to know that information? Are these actions logged somewhere? Or do we need to force this logs?


texts: How to design a database with one-to-many and then many-to-many relationship?

texts:
I'm not very specialized with database design so much and I'm designing a database for the delivery company.

The situation is that we have two main actors, customer and truck driver. The customer will ask for a delivery and the truck driver must answer with an offer or a rate for that customer. The difficult part is, how to design a relationship between them, so the customer can request for a delivery and truck driver can give a good rate for that request. The customer should receive many offers from many drivers and drivers can receive many requests from other customers as well.

I thought about having Customer, Truck Driver, and Request as entities
and Request_Driver for the many-to-many relationship.


texts: SQL Server: Should the same log records be backed up multiple times?

texts:
I came to SQL Server database administration from an Oracle DBA background. So maybe naturally, I always try to understand the differences between the two DBMSs as well as the similarities. One question that have struck me recently is that, should the log records be backed up multiple times, and how? In Oracle database, assuming the database is in the "Archive-Log" database mode, the "redo" log files are archived as separate "archived redo log" files, whose role will be critical in case of a point-in-time recovery scenario, and so because of that, it's very important for these files to be backed up regularly. Often times, DBAs take multiple backups of each "archived redo log" file so that if, for example, a particular tape is lost or damaged, there would still be another copy of the same backup available somewhere else on some other backup device. This will obviously provide a high level of protection against a data loss.
In SQL Server, as we know, there are transaction logs containing log records, which play avital role for performing database recovery. Now my question is this: for a database that is on the "FULL" recover model, would it be possible to backup the log records multiple times? And if possible, would it be a good idea? And how that can be done?


texts: how can i rename all the tables in a database so the first character of each table's name is removed. thanks! I'm using mysql 5.6.


texts:
Hello i'm trying to model a simple banking scheme
where I have 3 transactions: withdraw, transfer, deposit.
of these 3 operations, the transfer is the only one that needs more attributes, I need to know the accounts participating in the transfer, and who received and who sent it.
At first I thought of something simple like that, but it seems very strange:

but this seems very vague to me, and when do I need the statement from the two accounts participating in the transfer?
could someone help me with this?