texts:
I'm using SQLite3 and the following query, It seems like there should be a better performing way to write this query; Is there?

Select Name,
    Sum(Case
       When
          Used
             Is Null
       Then
          ItemQty
       Else
          0
       End) As QtyNotUsed
From
   ContainersReceived
Group By
   Name

Basically my thought (that I can't realize) is something like:

Select
    Distinct Name
From
   ContainersReceived

combined with

Select
   Sum(ItemQty)
From
   ContainersReceived
Where
    Used Is Null

The result I want is a list of all Names and how much is in stock from the table ContainersReceived.

UPDATE:

Possible solution is to use:

Select
    Name,
    Sum(ItemQty)
From ContainersReceived
Where Used Is Null
Group By Name
UNION ALL
Select
    Name,
    0
From ContainersReceived
Where Used Is Not Null
Group By Name


texts: Select tables on column entry in schema (with many tables)

texts:
I have a schema in a PostgreSQL-DB that includes approx. 3000 tables with each having one row/ entry (size, id, geom). I'd like to select all tables which have a size value lower than 20. How can I query over multiple tables without joining them?
Would it be better to merge all these tables into one and execute a simple SELECT query?


texts: SQL Server login error while revalidating the login on the connection

texts:
Recently we started seeing the below error in our environment.

  Failed to open the database ‘XXXX’ configured in the login object while revalidating the login on the connection.

Any suggestions?


texts: SQL Server 2008: High CPU historical queries

texts:
We have a SQL Server with 15 databases on it. This is used by many users in each database simultaneously. Recently we started getting high CPU alerts from this server. When the alert comes, I can trace to the exact user and query.

We cannot turn on SQL Server Profiler on this server. I like to know if we can get information about historical expensive queries run in any of the databases (causing high CPU) using any script. for example - I need most expensive queries in last 4 hrs along with database name and username.


texts: Changing a column's data type fills the transaction log

texts:
Running this code:

ALTER TABLE npidata
ALTER COLUMN npi varchar(20)

Gives this error:

  Msg 9002, Level 17, State 4, Line 2
  The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I am changing the NPI column's data type from varchar(80) to varchar(20).

The following code gives me the same error message:

insert into npidata1 select * from npidata

log_reuse_wait_desc shows NOTHING
The recovery mode is SIMPLE
autogrowth is set to NONE
autoshrink is set to true

What else can I do? My understanding is the logs should just be truncating every time they get too big. What am I doing wrong?


texts: How can I get LOAD DATA LOCAL INFILE to work for MySQL 8.0.21 on a Windows machine?

texts:
I've created a file: 'C:\fff.txt' which contains data for my database table.
Then I'm trying to load data from that file like this:
LOAD DATA LOCAL INFILE 'C:\\fff.txt' INTO TABLE fff
LINES TERMINATED BY '\r\n';

but I get the following error all the time:
ERROR 2 <HY000>: File 'C:\fff.txt' not found (OS errno 2 - No such file or directory)

I've added local_infile=1 to [mysql] and [mysqld] sections of my.ini file.
When I type: SHOW GLOBAL VARIABLES LIKE 'local_infile'; I can see that it's set to 'ON'. Is there a way to solve this problem?


texts: Using Transactions with PostgresQL dependency issue

texts:
I am trying to use transactions when inserting into a PostgresQL DB, however due to dependencies, I ran into some issues:
During the transaction I am inserting into multiple tables. The first table into which data is inserted is a table that saves company profile information (name, etc.). One of the other tables has a foreign key column that references to the key of the company which I am inserting in the same step. The company key is a BigInt auto-increment key, which leads to the following problem.

If I use a transaction to insert into all the tables at once, I cannot query for the company key (the insert-command has not come into effect yet), which is needed to insert into the other tables. On the other hand using multiple transactions might lead to an inconsistent state if something fails...

Is there a way to query for what the next assigned BigInt key in the company table would be, so that I can put everything into one transaction? How would you suggest resolving this issue?

Any help is highly appreciated!
Thank you in advance!

Sam


texts: simulate sequence on mysql in preparation for move to oracle

texts:
I've been developing an application against MySQL while a new development server has been slowly being set up.  The new dev server will use oracle, and since we're getting close to it being ready, I want to remove the auto_increment attribute from my primary keys, and simulate Oracle's sequences.  I haven't used oracle for several years.

Since every pre-insert trigger is going to do the same thing - get the next id for table foo out of *foo_sequence*, does Oracle make any allowances for implementing some form of dynamic/lexical triggering ?

Any tips, tricks or gotchas?

EDIT #1: 1550 - this is not a heavy-use app. a single use will cause 13 inserts of small, simple records in the biggest transaction. Concurrency is unknown, but the likely hood of one user causing lock starvation of any significance is estimates very low IMO.

EDIT #2 1612: the intent is to simulate oracle sequences in mysql, and then use oracle sequences on oracle.

Thanks.

Resolution:

I'm implementing a class to act as my interface to the sequence.

on mysql, I'm implementing a table to hold sequence values, and a stored procedure to get the next value and update the row.  The class will call this stored proc and return the value.
on oracle I'll just get the next value from an oracle sequence. The class will make this request. The Sequence class will request the next value from the sequence.

the application code will use the Sequence class to obtain the next sequence value for the insert, abstracting the "nextval" implementation details away from the insert statement that consumes the value.


texts:
This question Whats the easiest way to get sql 08 spatial data onto a map? (and many other articles) recommends Craig Dunn's Geoquery, but all the download links appear to be kaput.

SQL Server Managament Studio's spatial results tab will show a single dataset, and this MSDN article, Spatial Data Support In SQL Server 2008 mentions a workaround using UNION ALL to display point data overlayed on a map:

SELECT geog, name FROM Mondial.dbo.city WHERE geog IS NOT NULL
UNION ALL
SELECT geog, cntry_name FROM SpatialSamples.dbo.cntry00

Whenever I try something similar, I only ever get a single resultset displayed.

Is there something built into either SQL Server 2008 or 2012 to do this?


texts: How to model a relationship between Sandwiches and Toppings?

texts:
I want to create a database structure pertaining to my product, which is a sandwich that can have one bread and many toppings.

To illustrate the problem I created a quick diagram:

For the one bread I can easily assign the sandwich_id as seen in the picture, but how would I assign the many toppings in a beautiful way?

My current considerations

I could think of some ideas:

topping_1_id, etc... and a toppings index as in 1 is ‘cheese’, 5 is ‘onion’, so topping_id = 15, but I am not sure how to do that.


texts: How to model inheritance in MySQL for adding and deleting users?

texts:
I currently have a MySQL database and our ERD has inheritance for various tables. One is User which has as children: administrators, students and professors. The tables look as follow:
CREATE TABLE `Users` (
  `id_number` int DEFAULT NULL,
  `First_name` varchar(50) DEFAULT NULL,
  `Last_name` varchar(50) DEFAULT NULL,
  `User_Type` varchar(14) DEFAULT NULL
) ENGINE=InnoDB
CREATE TABLE `Advisor` (
  `Faculty_id` varchar(2) DEFAULT NULL,
  `Student_id` varchar(5) DEFAULT NULL,
  `Date_Assigned` date DEFAULT NULL
) ENGINE=InnoDB
CREATE TABLE `Faculty` (
  `Faculty_id` varchar(13) DEFAULT NULL,
  `Room_id` varchar(2) DEFAULT NULL,
  `Faculty_Speciality` varchar(16) DEFAULT NULL,
  `Faculty_Rank` int DEFAULT NULL,
  `Faculty_Type` varchar(9) DEFAULT NULL,
  `user_type` varchar(13) DEFAULT NULL
) ENGINE=InnoDB

I'm aware that things such as PRIMARY KEY are missing for the ids and REFERENCES for foreign keys. Similar is found on this post How to model inheritance of two tables MySQL. However, what I want to know is when adding users how to populate both tables (parent and children)? Meaning that both tables user and student get the same information? How would this work on data that gets over 3000 students?


texts: List all databases of a certain user ( SQL Server 2008 R2 )

texts:
I'm really looking for this answer for some time. I bet there is a post here but, I really can't find ( even on google ).

I think it's a simple task.

I want to list all databases for a specific user.

Is there a way to do this?

It can be a view or a cursor. I just can't think of anything.


texts: Removing a Windows Server Cluster Node and Then Adding Back - Do I need to remove the Availability Group replica?

texts:
We had a secondary server bite the dust... we were remarkably able to resurrect it. Everything is great and intact... except for the windows server cluster configuration. We basically need to evict it and add it back...
I was thinking.. we could stop SQL services on this secondary replica, do the cluster work (evict / re-add) and then once the WSFC is happy for this node, start sql services back up... databases continue syncing as normal.
Or... would we need to completely remove the secondary replica from the AG and then rebuild everything?
Thanks for thoughts!


texts: Count Total time difference between selected row

texts:
My data contains an ID, status and time(hh:mm:ss).

I need to calculate the total time between every 'Location' and 'Out of tunnel' status

Current table

    ID   ||       time       ||     Status
________________________________________________
      1  ||       08:09:14   ||   Out of Tunnel
      2  ||       08:10:59   ||     Location
      3  ||       08:11:42   ||   Out of Tunnel
      4  ||       08:11:55   ||     Location
      5  ||       08:16:36   ||     Location
      6  ||       09:41:36   ||     Location
      7  ||       09:43:10   ||   Out of Tunnel
      8  ||       09:43:19   ||     Location

The 'Location' status marks where I need to start counting. I need the count to stop the next time the status is 'Out Of Tunnel'. If there is no corresponding 'Out of Tunnel' given, then don't count it.

Each total would then be summed to give a grand total. For example:

Start at ID2: 08:10:59
End at ID3: 08:11:42
Total 1: 00:00:43
Start at ID4: 08:11:42
End at ID7: 09:43:10
Total 2: 01:31:28
Grand Total: Total1 + Total2 = 01:32:11

Desired output

 ______________________
| Total Time in Tunnel |
|______________________|
|                      |
|        01:32:11      |
|______________________|


texts: Set up read-only privileges on the dbo schema, but allow creation of entities in another schema?

texts: Is it possible to create a custom schema in a database such that users that have read-only access in that database for the dbo schema can be set up with the ability to create and modify entities for the custom schema?


texts: Does trigger "see" all changes done in the transaction it was fired from?

texts:
Let me elaborate on the title.

Suppose we have two tables, t1 and t2. t2 has a trigger t2_trgon it. Same transaction inserts/updates data in t1, then does insert/update in t2. t2_trg is fired and starts doing its thing. Is this trigger able to see all the changes done in t1?

P.s. I found other topics on this problem, but they we're mostly for SQL Server.


texts: Is it possible to tail a log with a cursor?

texts:
I thought the DYNAMIC keyword would work but it doesn't seem to. Basically I have an ASP.NET Application using the log4net AdoNetAppender. I'd like to be able to SELECT * FROM Logging.Logging in a cursor and have it keep reading new columns inserted after I opened the cursor.

I know I could just keep sending SELECT * FROM Logging.Log WHERE Id > @LastLogId and sleeping. That seems like not the optimal method.

Do I need to set transaction isolation to something other than the default for the cursor to see the changes?

This is my current code:

USE AlwaysEncryptedSample;
GO

SET NOCOUNT ON;
DECLARE
    @Date CHAR(19),
    @Thread VARCHAR(255),
    @Level VARCHAR(50),
    @Logger VARCHAR(255),
    @User NVARCHAR(50),
    @ClientIP NVARCHAR(45),
    @Message VARCHAR(4000),
    @Exception VARCHAR(2000),
    @Severity INT;
PRINT '-------- Log4netLog --------'

DECLARE curLog CURSOR DYNAMIC FORWARD_ONLY
    FOR
        SELECT
                FORMAT([Date],'g'), Thread, [Level], Logger,
                [User], ClientIP, [Message], Exception,
                CASE
                    WHEN [Level] = 'Debug' OR [Level] = 'Info' THEN 0
                    WHEN [Level] = 'Warning' THEN 10
                    ELSE 16
                END
            FROM Logging.Log
            --WHERE Date > GETDATE();
OPEN curLog;

FETCH NEXT FROM curLog
    INTO
    @Date,
    @Thread,
    @Level,
    @Logger,
    @User,
    @ClientIP,
    @Message,
    @Exception,
    @Severity

WHILE 1=1
BEGIN;
    IF @@FETCH_STATUS = 0 RAISERROR('%s - %s - %s', @Severity, 1, @Date, @Level, @Message) WITH NOWAIT;
    ELSE
    BEGIN;
        SET @Date = FORMAT(GETDATE(), 'g')
        RAISERROR('%s - NULL - No Log written sleeping for 5 seconds', 0, 1, @Date) WITH NOWAIT;
        WAITFOR DELAY '00:00:05';
    END;
    FETCH NEXT FROM curLog INTO
        @Date,
        @Thread,
        @Level,
        @Logger,
        @User,
        @ClientIP,
        @Message,
        @Exception,
        @Severity
END;


texts: What is the idiomatic solution in SQL Server for reserving a block of ids for use in a bulk insert?

texts:
I have a table with an identity column and I want to reserve a block of ids which I can use for bulk inserting, whilst allowing inserts to still happen into that table.

Note this is part of a bulk insert of several tables, where those other tables relate to these ids via an FK. Therefore I need to block them out so I can prepare the relationships beforehand.

I've found a solution which works by taking a lock on the table in a transaction and then does the reseeding (which is pretty fast). But it looks a bit hacky to me - is there a generally accepted pattern for doing this?

create table dbo.test
(
    id bigint not null primary key identity(1,1),
    SomeColumn nvarchar(100) not null
)

Here's the code to block out (make room for) some ids:

declare @numRowsToMakeRoomFor int = 100

BEGIN TRANSACTION;

        SELECT  MAX(Id) FROM dbo.test WITH (  XLOCK, TABLOCK ) -- will exclusively lock the table whilst this tran is in progress,
        --another instance of this query will not be able to pass this line until this instance commits

        --get the next id in the block to reserve
        DECLARE @firstId BIGINT = (SELECT IDENT_CURRENT( 'dbo.test' )  +1);

        --calculate the block range
        DECLARE @lastId BIGINT = @firstId + (@numRowsToMakeRoomFor -1);

        --reseed the table
        DBCC CHECKIDENT ('dbo.test',RESEED, @lastId);

COMMIT TRANSACTION;

select @firstId;

My code is batch processing blocks of data in chunks of about 1000. I have about a billion rows to insert in total. Everything is working fine - the database isn't the bottle neck, the batch processing itself is computationally expensive and requires me to add a couple of servers to run in parallel, so I need to accommodate more than one process "batch inserting" at the same time.


texts: Postgres Windows post-install "The database cluster initialization failed"

texts:
I've seen many solutions and have been working on this issue for hours now.

After installation the post-installation script fails with the message above.

This is a development installation of EnterpriseDB On Windows 10 Pro (German Edition with English language pack).

No user created
No service created
No initialization happened

What I read about and tried without success (I hope I don't forget anything, as far as I remember these are all (at least most) solutions I tried posts from up to 10 years ago):

Checked whole file system privileges up to root of C:\, I have no other drive - this is a dev laptop.
Installed different locales because I read there could be issues. I tried "default", "C", "US English"
COMSPEC and other ENVs are correct, no trailing semicolon or other disturbances.
postgres password is simple

The installation works perfectly on a different laptop.

What I observed:

This issue is caused by a failing objShell.Run call in the VBS script.

The call is simply not executed. No error message. It "looks like" objShell.Run is called, seems to fail somehow and the DoCmd function where it is called returns. I don't even get a return value from objShell.Run, it just jumps out of DoCmd, thats it.

The written batch file exists. The path variables in the VBS are correct. The log file created inside the batch file is not created therefore I think it is not called at all. But it is created when I prevent the batch file from being deleted and calling it manually from command line. I prefixed the objShell.Run with "cmd /c " which caused it to run "more" but kept on getting other errors.

Unfortunately after several reinstalls, tries, reboots and so on I don't have the logs anymore. But if it is required for problem solving I'd be happy to provide it tomorrow.

I need to say I am not familiar with VBS but that is how far I was able to debug it. I also tried /X /D but I think i need deprecated tools for debugging, do I?

I spent hours with this problem and I'll leave it for today but I'm in desperate need for help.

Thanks.

(edit 1) Added which binary distribution


texts: SQL Server Fulltext : small word and prefix return no result?

texts:
I've a field fullname witch contains firstname and lastname, this field use full-text search
I've a member called "xavier le fourbe", my query is
select * from members where CONTAINS(fullname, '"xavier*" AND "le*" AND "fourbe*"')

This one return no member, but if I do
select * from members where CONTAINS(fullname, '"xavier*" AND "l*" AND "fourbe*"')

It's works, but why ?


texts:
lets say I have a users,posts and categories tables ..

users table would have statuses like ( active, expired .. etc )
posts table would have statuses like ( published, draft, archived .. etc )
categories would also have different statuses

Is it bad if I did my tables like this:

users table:

- id
- name
- email
- password
- status_id

posts table:

- id
- title
- desc
- status_id

categories table:

- id
- title
- status_id

status table:

- id
- name ( active, expired , published, draft, archived .. etc )


texts:
I have a database that will track "location" in a building.

Location1 contains country data.
Location2 contains building data.
Location3 contains room data.

Each location table has a FK to the parent table.  So for example, I have data that looks like this:

 Location1
 id   | name          | description
 ----------------------------------------------
 1    | United States |

 Location2
 id   |  loc1_id   | name          | description
 ------------------------------------------------------------
 1    |   1        |  buildingx    | building x in new york

 Location3
 id   |  loc2_id   | name          | description
 ------------------------------------------------------------
 1    |   1        |  5-125       | 5th Floor Room 125
 2    |   1        |  1-12b       | 1st Floor Room 12b

In my widgets table, i now want to specify where each widget is located.
I don't really want to have 3 columns, one for each location table.
I was thinking of saving values like this:

 widgets
 id   |  loc_id   | name
 -----------------------------------
 1    |   1.1.1    | test widget
 2    |   1.1.2    |  my favorite widget

Does anyone have any comments on the advantages / disadvantages of this approach?


texts: How to check if a client can use windows authentication

texts: My ORACLE (10g) sqlnet.ora has SQLNET.AUTHENTICATION_SERVICES=(NTS). I need to validate that no client is using Windows authentication to connect. What can I check next to make sure they don't ?


texts: mysql - Waiting for table level lock on a table

texts:
I have a problem with my website.. The last few days, at random times the website has become very slow. I started to investigate the best I could. I saw that the MySQL process was using 85 - 95% of the available memory of my server. (Should I upgrade my memory also?)

I checked my MySQL process log, and I noticed a huge list of queries with: Waiting for table level lock

But what I also noticed, what that ALL of these queries with "table level lock", was only queries which had something to do with my table called "users".

I have 20 other tables, with constant queries, but I don't see them on the list.. So I guess the problem is with the users table?

I want to know how I can improve the table, and eventually remove the table level lock?

I also ran this:

SHOW VARIABLES LIKE 'query_cache%';

Which resulted in this:

query_cache_limit
1048576
query_cache_min_res_unit
4096
query_cache_size
33554432
query_cache_type
ON
query_cache_wlock_invalidate
OFF

Please let me know what I can do to improve my database/mysql.


texts: Check when a privilege was granted - PostgreSQL

texts:
Is there any information_schema view that can tell when an object privilege was granted?
The pg_namespace view doesn't have that.
select
  *
from pg_namespace
join lateral (
  SELECT
    *
  from
    aclexplode(nspacl) as x
) a on true
join pg_user e on a.grantee = e.usesysid
join pg_user r on a.grantor = r.usesysid
 where e.usename = '<<MY_USERNAME>>';


texts: Index strategy for TEXT / NTEXT column to filter out NULL / NOT NULL rows

texts:
There is a table with TEXT or NTEXT data type column and I need to improve the performance of queries with WHERE clause filtering out NULL or NOT NULL rows in this column.
Unfortunately, I cannot change the data type to NVARCHAR(MAX) nor add a computed column to the table.
What possibilities do I have, please?


texts: Create a table via an INSERT trigger in MariaDB

texts:
I'm trying to make a small database I can use to log changes I make to configuration files on various Linux systems.

My thought was have a Database named for the system, and then a table called ConfigFiles that has an ID column, a FileName column and a FilePath column, to act as an index of all the entries. The ID is an Int and the FileName and FilePath columns are varchars

I then wanted to have a Trigger set so whenever I insert a row into ConfigFiles, a new table would be created, with the name , which would contain an ID column, a ConfigOption Column, and a ConfiguredValue column.

When I went to set up the trigger in PHPMyAdmin, however, I am given the error "Explicit or implicit commit is not allowed in stored function or trigger."

My SQL looks like this:

CREATE TABLE IF NOT EXISTS new.filename (
`id` int(11) NOT NULL auto_increment,
`ConfigOption` varchar(25) NOT NULL,
`ConfiguredValue` varchar(150) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ConfigOption` (`ConfigOption`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

with the Table set to ConfigFiles, the Time set to AFTER, and the Event set to INSERT.

So what am I doing wrong, and what should I be doing if I want to achieve the above?

Thanks in advance for any help offered!

N.B In case you're looking at me like "What a terrible idea!", this isn't a production sort of thing for use in anything business critical, this is literally just so when I configure things on my own computers, which is usually while messing with new software to learn how it works, I can basically keep a journal of the things I do. A database seemed more searchable and better to store key-value pairs than keeping a bunch of text files. And it's a good excuse to get more familiar with SQL


texts: SQL server database Role management - Azure SQL MI

texts:
In my database I have multiple users with SQL server authentication login and AD login.

For ex: User 1 and user 2 are from same team(ABC), if I create a role db_abc

User 1 needs read access to table a and table b.
User 2 needs read access to table a, b and additionally to table c.

How do I manage this case? Should permissions be given at user level than to roles.?
What is the best practice in managing users and roles?


texts: Replicate tables to different database name

texts:
Our QA environment has all it's database names suffixed with "test".  For example dbname1 in production would have a counterpart dbname1test in QA.  (This is largely to help protect against prod/qa configs getting mixed up).

There are a handful of tables I would like to replicate the actual production tables into QA.  I'm not sure how I would go about telling it to say "replicate from dbname1 there to dbname1test there"

Is this even possible?


texts:
So, as part of my assignment, I have to prove that any relation with two attributes is in BCNF.

As per my understanding, if for a relation we have 3rd normal form and one non key attribute functionally determine key attribute, it violates the BCNF.

Say my relation consists of two attributes A1,A2

Scenario1(only one functional dependency)

A1 -> A2 (so A1 is the key, and A2 does not FD A1 : so no violation)

same applies for

A2 -> A1

But what if

A1->A2 and A2->A1

Here key can be either A1, A2. And the other non key attribute functionally determines the key.


texts: How to assign day of the week to different tills

texts:
Just wondering if someone would be able to please help me with a problem that I am trying to solve using T-SQL.
I have a table for till numbers assigned to 2 members of staff as seen below:
CREATE TABLE [dbo].[TillNumbersAssignedtoStaff](
    [AssignedWeek] [date] NULL,
    [StaffID] [int] NULL,
    [TillNumbers] [varchar](3) NOT NULL,

) ON [PRIMARY]
GO

INSERT INTO [dbo].[TillNumbersAssignedtoStaff]
VALUES ('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB1')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB5')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB4')
,('2020-10-19',190,'AB2')
,('2020-10-19',190,'AB3')
,('2020-10-19',190,'AB2')
,('2020-10-19',180,'BY5')
,('2020-10-19',180,'BA3')
,('2020-10-19',180,'BY4')
,('2020-10-19',180,'BA1')
,('2020-10-19',180,'BY2')
,('2020-10-19',180,'BE2')

I also have a daily allocation table as seen below:
CREATE TABLE [dbo].[DailyAlloc](
    [DayNo] [int] NOT NULL,
    [DayName] [nvarchar](20) NOT NULL,
) ON [PRIMARY]
GO

INSERT INTO [dbo].[DailyAlloc]
VALUES (1,'Monday')
,(2,'Tuesday')
,(3,'Wednesday')
,(4,'Thursday')
,(5,'Friday')
,(6,'Saturday')
,(7,'Sunday')

I would like to assign the daily numbers from Mon-Fri to the staff. Each daily count should not be more than the maximum average for the 5 days of the week e.g. If there are 9 tills to be assigned for the week, there should not be more than 2 tills assigned daily i.e. 9/5.
I would like the result to be as seen below:

Any help with this would be greatly appreciated.
Thanks


texts: How do you get the graph plugin for Squirrel SQL?

texts: I installed Squirrel SQL and the website claims the Graph plugin is part of the install.  It is not available nor is it in the jar.  How do I get the graph plugin?


texts: Percona: Do I need apply-log switch for a weekly full backup and hourly incremental?

texts:
I am planning a script to work on our percona. Our strategy is to do full backup weekly but incremental backups every day/hour. I have read innobackupex manual and got confused further as to whether I really need it or no.

A post here on dba.stackexchange.com , to my understanding suggests that I do NOT need this if I am also doing the incremental backup. This was also confirmed on a percona forum

To be on the safer side this is how I planned to achieve this:

#first day of the week
innobackupex --user=myuser --password=mypassword /path/to/backup/dir

#Every day/hour
innobackupex --incremental .... ... ...

I hope I got it right, if not kindly advise.

Thank you


texts: MySQL UPDATE to replace text in LONGTEXT field

texts:
I have a WordPress table wp_posts and I'd like to run an UPDATE that will replace a url from my old domain to my new domain.

For example, let's say that one record in the field post_content has the following content:

This is my old web site.

I'd like that to become:

This is my old web site.

I've tried the following query

UPDATE wp_posts AS w`
SET w.post_content = REPLACE(w.post_content, 'my-old-site.com', 'my-new-site.com');

but I don't get any result.

Any idea how to fix it?


texts:
Is MySQL v5.7.33 an acceptable version for production or might be upgraded to mysql 8.
I am mostly thinking in security concerns.
Thanks,
·_-


texts:
My SQL Server sometimes reports a high amount of Lazy Writes.
e.g. "Lazy Writes per second is 119 Writes/sec"

What should I be checking to find out why this is high?
What are potential causes?


texts: How to speed up Oracle to PostgreSQL data transfer

texts:
I have an Oracle schema that I am transferring to PostgreSQL with ogr2ogr.
This particular table has 5 columns with number(22) each. There are some 300M rows. In under 10 minutes I can transfer this table between two oracle servers over the network. ogr2ogr took a day to transfer 10M records and will take a month to transfer the whole table.

I suspect the problem is on the receiving end. Data is transferred to AWS PgSQL with 4 Gb ram. How to debug the bottleneck and speed up the transfer?


texts: Update all geometry points by adding/subtracting from XY

texts:
I want to update the POINT geometry column for all rows in a table.

For example, I want to change POINT(1 1) to POINT(1.1 .9)

UPDATE myTable SET geoPoint = asText(geoPointX+1 geoPoint Y-1)

The query is example of what I want to achieve, but I'm unsure how to split the text and then handle the values.


texts: How can I save a file into a longblob column on a database located on another server?

texts:
I have a database table that contains large files in one of the columns, stored as longblob data. The blobs can currently be up to around 300MB in size.

When reading the blobs from the database I'm querying them one at a time and getting the InputStream from the blob, in java like this:

public InputStream getInputStream(ResultSet rs, String name){
        InputStream in = null;
        try {
            Blob blob = rs.getBlob(name);
            in = blob.getBinaryStream();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return in;
    }

Using this stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `q_file_get`(
    p_file_id integer,
    p_file_number integer
)
begin
    declare v_file_number integer;

    if(p_file_number is null) then
        set v_file_number = 0;
    else
        set v_file_number = p_file_number;
    end if;

    select f.id, f.file_number, f.file_from, f.file_to, f.file
    from file_db.file as f
    where f.id = p_file_id
    and f.file_number = v_file_number;

end

But I can't figure out how to send data the other way.
I will receive a file and some metadata over a socket and save it locally. Then I need to send it to a database on a different server.
Also, at the moment I'm querying one blob at a time because the whole blob is returned with the query, it's contained in the InputStream and isn't streamed from the database directly.

So a couple of questions:

Is it possible to stream the data somehow with a stored procedure? in both directions so I can stream directly from/to the database without holding the whole file in memory?
If it's not possible, how is something like this normally done? What other options are available to me?


texts: Why more (and varying number of) logical reads with read ahead (prefetch)?

texts:
After creating tpch database in my SQL Server, I tried below query:

    set statistics io on
    DBCC DROPCLEANBUFFERS;
    select top 100 * from dbo.lineitem order by l_partkey;

The table lineitem has a non-clustered index on l_partkey. I issued the above queries for several times and found out that the logical reads vary each time:

    Table 'lineitem'. Scan count 1, logical reads 1019, physical reads 4, read-ahead reads 1760, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'lineitem'. Scan count 1, logical reads 1007, physical reads 4, read-ahead reads 1720, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
    Table 'lineitem'. Scan count 1, logical reads 1030, physical reads 4, read-ahead reads 1792, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

From the post here: Logical read count varies, I know it could be caused by read ahead behavior.

BUT exactly why read ahead could cause more logical reads? How does it change the SQL Server behavior? Like SQL Server may read more index page since it's in cache anyway?

Anyway, I disabled read ahead and issue the above query again. Now it reports the same amount of logical reads each time. BUT the logical reads is much smaller!!

    Table 'lineitem'. Scan count 1, logical reads 404, physical reads 160, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So my question is, why the read ahead feature could cause many more and various logical reads count?

Out of curiosity, I tried another query without the "order by":

    select top 100 * from dbo.lineitem

Here's the result without read ahead:

    Table 'lineitem'. Scan count 1, logical reads 5, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Here's the result with read ahead:

    Table 'lineitem'. Scan count 1, logical reads 15, physical reads 2, read-ahead reads 3416, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

The one with read ahead still has more logical reads. So, why?


texts: Why are arrays of rows of mismatched types allowed in PostgreSQL?

texts:
I'm suprised that I can do the following.  Why is that allowed? Doesn't it violate some sort of type check?  Or am I misunderstanding what it is actually doing?
postgres=# SELECT ARRAY[ROW(1), ROW(true)];
   array
-----------
 {(1),(t)}
(1 row)

It also permits unnesting, which seems even worse.
postgres=# SELECT UNNEST(ARRAY[ROW(1), ROW(true)]);
 unnest
--------
 (1)
 (t)
(2 rows)


texts:
I'm new to postgres and I'm trying to configure table level logical replication(PUBLICATION/SUBSCRIPTION) on postgres-12.
I have updated postgresql.conf file and updated wal_level=replica. Also, then created 2 databases(test1 and test2). Here is what , I'm doing:
test1=# create table t1(a int primary key, b int);
CREATE TABLE
test1=# insert into t1 values(1, 1);
INSERT 0 1
test1=# create publication my_pub for table t1;
CREATE PUBLICATION

test2=# CREATE TABLE t1(a int primary key, b int);
CREATE TABLE
test2=# CREATE SUBSCRIPTION my_sub CONNECTION 'host=localhost port=5432 dbname=test2 user=postgres
password=password' PUBLICATION my_pub;
invalid integer value "CONNECTION" for connection option "port"
Previous connection kept

When creating subscription I'm getting this message: invalid integer value "CONNECTION" for connection option "port"
Previous connection kept and I'm unable to add this table into replication. In my postgresql.conf file as well I have mentioned 5432 port only. However, I'm unable to understand why it's saying invalid integer value


texts: PostgreSQL 9.6 on linux server gives a different collation than windows installation

texts:
I am running into an issue with a script that makes a backup towards the SQL. What happens is that with our script we make a dump of SQL we can read out to restore the PostgreSQL database.

Our client runs this on an CENTOS server, where we installed the SQL via commandline with our regular settings (basic locale). They also have a windows machine where our software runs that can make the backup. This is controlled by a script which basically controls

Here we run it in windows where we install it also with basic settings.

Both run on the 9.6.3 version.

With our software, which is windows based, we have a backup tool that runs a cmd batch. This controls the bin/pg_dump of Postgres.

bin\pg_dump  -h 10.0.0.80 -p 5432 -U postgres -F c -b -v -f %BACKUP_FILE% dbcatocs 2>"%BACKUP_DIR%logs\backuplog_%datestr%.txt"

So when we make a dump at their place, in linux environment, it goes wrong on the database creation when we do a restore. It dumps the query as following.

CREATE DATABASE dbcatocs WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'

While when we run it in windows it creates the database as following

CREATE DATABASE dbcatocs WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'English_United States.1252' LC_CTYPE = 'English_United States.1252';

So as you can see, windows handles the language code and the character set completely different than the server at their place. This causes the query in windows not to be executed.

I reinstalled PostgreSQL on our system here to see if we could match it to get it to output UTF8 as part of the process, however, windows sticks to numbers rather than the characterset. Plus the languagesets just simply don't match. So PostgreSQL decides just not to execute, leaving us with nada.

Now if we make a dump from PgAdmin manually in their system (we installed a pgadmin on their main windows server and can control there the windows output), it works fine, but since pgAdmin just dumps plain SQL queries, it shouldn't matter. So we have a way around it.

My question simply is if we can control the locale coallation and lc_type to simply be ignored scriptwise (so to create a database with standard entries, rather than trying to pick a locale that isn't there) within postgresql, or is it mandatory. And if so, is it controllable by the pg_dump script?


texts: How to normalize a table with common and non-common fields?

texts:
and thanks in advance.

I am trying to normalize a table "People", where each entry (person) belong to one of many different categories (e.g. "lawyer", "client", "judge"). Some fields are common across categories (e.g., firstName, lastName).

However, some (set of) fields are unique to each category: for instance, a "client" will likely not have a "LawyerBoardID" number. Therefore, an entry will have a value in that field if and only if the person belongs to the "lawyer" category; in other words, that field will be NULL for all other categories.

This has implications for child tables. One person, many entries in the "Address" table - textbook example. If I split my "People" table in many tables - namely, one per category - then I will have to put as many FK in the "Address" table, right? Therefore, if I have 10 categories, there will be 9 NULL FKs.

Any suggestion? Links to relevant web pages are also very welcome.


texts: set -x equivalent for the Postgres cli/console/prompt/repl

texts:
Is there a "set -x" for the Postgres prompt/repl ?

In bash when you want to execute a set of commands, and come back to check it after lunch you can do

set -x
command1
command2
command3

and bash prints out the command to STDOUT just before it executes it.
this is helpful to see, what's taking a long time, which did line did it last execute, etc.

Similarly, inside the psql console, I also do sets of long running commands:

VACUUM FULL VERBOSE ANALYZE events;
REINDEX VERBOSE TABLE events;

Can I do

set -x
VACUUM FULL VERBOSE ANALYZE events;
REINDEX VERBOSE TABLE events;

so that when I come back from lunch I can see which one of my commands was the last one it did.


texts: Oracle SQL Throwing "ORA-00934: group function is not allowed here" error for simple Select w/AVG

texts:
I am having a hard time with what should be a simple query. I have studied the documentation on the AVG function and understand that I may need to execute this  with a subquery. This is the gist of what I want to accomplish - finding values greater than 3 * the average of the non-zero values of a column. Here is what I thought would work. Any help appreciated.

SELECT COL1, COL2
FROM TABLE1
HAVING COL3 > 0 AND COL3 > AVG(COL3*3)
ORDER BY COL1;


texts: Should I update to sql server 2008 sp2 before restoring the db's or do the update after?

texts: I'm making a move from SQL Server 2008 to SQL Server 2008 R2 (Azure VM) as part of a hosting provider switch (from MyHosting to Azure). Should I update the VM to R2 SP2 before restoring the db's or do the update after or don't even do the update?


texts:
I am trying to drop a database in mysql, but every time I get this error

ERROR 2013 (HY000): Lost connection to MySQL server during query

even logged in as root.

Originally, I had a corrupted table that I could not recreate, hence I decided to recreate the db. I have innodb file per table, not sure if that matters.

How can I drop this database?


texts: SQL Server : multiple databases with transaction log on one SSD array

texts:
I know for HDD arrays that having multiple log files from different databases has a performance hit as it changes the IO pattern from sequential to random for the array.  So having an array per database for it's logs is a best practice.

Does anyone have any suggestions about running multiple SQL Server databases with the log files for each of those databases existing on one SSD array?

Does anyone have experience with the differences of Hitachi's FMDs vs SSDs for their HUS hardware?  I'm concerned with consolidating individual drives down to a few FMDs and not being able to sustain performance between the user databases and tempdb if all I have is disk pool of 4 FMDs for everything.


texts: PostgreSQL - WAL segment file creation and naming conventions

texts:
I was under the impression that WAL segments files are normally generated before they are being written to and archived, and are continuously renamed and reused to save disk space. I was wondering if I safely assume that when restoring these unarchived WAL segments, they are renamed to match that of the database's current timeline?

All of the files from 00000004...13 to 00000004...19 previously had the timeline ID of 1 (00000001...13 to 00000001...19 respectively), and these were placed in the pg_wal directory before the restore process was executed.

  Whenever an archive recovery completes, a new timeline is created to identify the series of WAL records generated after that recovery.

Since these WAL segments were generated in a previous timeline but were never written to, does this allow Postgres to rename those same WAL files to match that of the current timeline without any issues?


texts: Redesign the storage of large amounts of sensor data

texts:
I have been tasked to implement/redesign a solution that will store weather data from a sensor array. The array will consist of ~40 towers, each with about ~10 sensors each that'll sample atmospheric conditions at 10 second intervals for an undetermined amount of time (years).
Some of the applications and requirements for this task are as follows:

Manage and retrieve tower/sensor configurations to make sense of data analysis.
Data visualization by sensor or time intervals for meteorological observations.
Provide customers with reliable and persistent data resources/data sets to compare model and sensor performance (may require some post-processing to deliver in the format required?).

Note: The current solution (implemented as a proof of concept, with 5 towers) stores data as flat files (one file per hour).

I was originally unsure if this would constitute a big data problem in the future, so I researched on a couple of solutions both for relational and NoSQL databases, but I feel I need a little more guidance, as I am no expert in data management.

One of the solutions I thought was to store data in a relational database indexed by tower, sensor, and timestamps and partitioning the table by date.

Another, based on future scaling, was to store it in a document-type NoSQL database, like MongoDB, and mimic the structure of the current solution.

Are any of these good approaches? If not, what would be a better/recommended solution? Also, would it even be necessary to redesign the current solution? I was told that the rationale for using flat files is that they believed a relational database would take too much overhead. Is there a way to avoid this if it was the case?


texts: Inner join on array columns from 3 tables

texts:
I have 2 tables that are related between them using integer data which is stored in arrays.

Data table

telemetria_data.historico_354677050164513

CREATE TABLE historico_354677050164513 (
    imei bigint NOT NULL,
    utc timestamp without time zone NOT NULL,
    clase integer NOT NULL,
    valor double precision[],
    registro timestamp with time zone DEFAULT now(),
    power integer DEFAULT (-1),
    voltaje double precision DEFAULT 0.0
);
ALTER TABLE ONLY historico
    ADD CONSTRAINT fk_telemetria_data_clase_id FOREIGN KEY (clase) REFERENCES clase(id) ON UPDATE CASCADE ON DELETE CASCADE;

Sample data

      imei       |         utc         | clase |     valor      |           registro            | power | voltaje
-----------------+---------------------+-------+----------------+-------------------------------+-------+---------
 354677050164513 | 2015-11-17 14:20:52 |    86 | {525,217,53,1} | 2015-11-17 14:21:04.238665+00 |     1 |       1

Structure table A

telemetria_data.clase

CREATE TABLE clase (
    id integer NOT NULL,
    nombre character varying(100),
    estructura integer[],
    descripcion text
);

Sample data

 id |    nombre    |  estructura   | descripcion
----+--------------+---------------+-------------
 86 | Caiquen - E4 | {35,34,20,21} | Telemetria

Structure table B

telemetria_data.clase_parametro;

CREATE TABLE clase_parametro (
    id integer NOT NULL,
    nombre character varying(50),
    descripcion text,
    unidad integer
);

Sample data

 id |                   nombre                   |                descripcion                 | unidad
----+--------------------------------------------+--------------------------------------------+--------
 20 | Numero Energizacion                        | Numero Energizacion                        |      0
 21 | Numero de Partida                          | Numero de Partida                          |      0
 34 | Tiempo trayecto en que se inicia el evento | Tiempo trayecto en que se inicia el evento |     15
 35 | Duracion del Evento                        | Duracion del Evento                        |     15

Actual data is column 'valor' from table telemetria_data.historico_354677050164513, that table is related to table 'clase' using the clase and id columns, and 'clase' table is related to 'clase_parametro' using the estructura and id columns.  (I hope this explanation is clear enough

Can you please provide hints on the needed JOIN to get data out of the "data table" with proper "column" names (from clase_parametro.nombre) for each of the items in the "historico_354677050164513.valor" column?

PS 1: I know normalization would help, but that's not actually doable in our scenario, where we already have tons of data stored.

PS 2: I've read several articles / questions regarding similar subjects, but could find a way to join all 3 tables.


texts:
This may be a no-brainer to some of you, but I am a newcomer to DBA and this question popped into my head and I couldn't answer it on my own and so I am turning to the experts here.

I have a app that requires two databases –– one production and one for staging. Do I need 2 database servers, or should I use one server and simply keep them in different databases?

On the one hand, you can create a new server using Docker containers so easily. I've been wondering if keeping the data for two environments on the same database is a safe approach.


texts: Maintenance Plan fails but the query it generates Runs

texts:
I have an SQL Server 2005 Enterprise Edition whose Maintenance plan fails constantly with the error:

backup MYSERVER (MYSERVER)
Backup Database on MYSERVER
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All databases
Type: Differential
Append existing
Task start: 2011-10-18T00:10:09.
Task end: 2011-10-18T00:10:09.
Failed:(-1073548784) Executing the query "BACKUP DATABASE [model] TO  DISK = N'\\myNetworkDrive\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp' WITH  DIFFERENTIAL ,  RETAINDAYS = 13, NOFORMAT, NOINIT,  NAME = N'model_backup_20111018001008', SKIP, REWIND, NOUNLOAD,  STATS = 10
" failed with the following error: "Cannot open backup device 'C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\Arca\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But the query that this maintenance plan generates:

BACKUP DATABASE [model] TO  DISK = N'\\myNetworkDrive\\opovo\\BackupSQL\\MYSERVER\\model\\model_backup_201110180010.bkp' WITH  DIFFERENTIAL ,  RETAINDAYS = 13, NOFORMAT, NOINIT,  NAME = N'model_backup_20111018001008', SKIP, REWIND, NOUNLOAD,  STATS = 10

runs normally.

even stranger is the error message: "Cannot open backup device 'C:\Program Files(...)" maybe there's a difference between how SSIS and SSMS/SS Agent handles the backslash?

The user for SQL Agent and the user with which I ran this query successfully in all these cases was a domain user "ABC.MyDomainUser" that has permissions to access the network mapping \myNetworkDrive. I even used MSTSC to log in the server that runs SQL Server and ran the query locally, it runs fine, only fails when in the maintenance plan.
Is this a bug? What am I missing here? What is the elegant way to backup to a network location?
Thanks in advance,
Lynx Kepler


texts: SQL Server Merge Replication, Agent Batch Size

texts:
Is there anyway to increase the merge agent batch size after the publication is created?

I know DownloadGenerationsPerBatch flag when creating a new Publication, however what I'm looking for is a way to modify this flag in a running publication


texts: SQL Server database size didn't decrease after deleting large number of rows.

texts:
I'm no good in SQL, but I've got a database to maintain.

There's almost no place left for it, so I've decided to delete all the data for, let's say, year 2008. After executing delete query (had about 10 000 000 rows cleaned) and cleaning transaction log I've found out that my actions had no effect on database size. Is there anything else I have to do?


texts:
One of our applications report on error message 2628:
string or binary data would be truncated in table '******', column '******'. Truncated value: '******'
instead of
String or binary data would be truncated in table 'mytable', column 'mycolumn'. Truncated value: 'myvalue'.
which I get with SSMS.
What setting do I need to change to get the full message?
I've tried to search for this, but all I get is that I need to set VERBOSE_TRUNCATION_WARNINGS to ON and compatibility_level to 150.
Which I've done ages ago.
The application has this error handler:
 try
     {
         ES.isWorking = true;
         Worker worker = new Worker(new DBConnection(Settings.ConnectionString));
         worker.DoWork();
         ES.isWorking = false;
     }
 catch (Exception ex)
     {
         ES.isWorking = false;
         this.eventLog.WriteEntry("In OnTimer exception ! message: " + ex.Message.ToString());
         errorHandler.HandleException(ex, "In OnTimer exception !", ErrorSeverities.Error);
     }

The errorHandler looks like this
Public Function HandleException(ByVal incommingEx As System.Exception,
                                   ByVal note As String,
                                   ByVal errorSeverity As ErrorSeverities,
                                   Optional ByVal sessionId As String = "",
                                   Optional ByVal applicationSource As String = "",
                                   Optional ByVal ignoreDB As Boolean = False,
                                   Optional ByVal noMail As Boolean = False) As Integer
    Dim stackTraceBuilder As New System.Text.StringBuilder
    Dim targetSite As String = ""
    Dim errorTime As DateTime = Now
    Dim messageBuilder As New System.Text.StringBuilder
    Dim innerEx As Exception = incommingEx.InnerException
    Dim errorId As Integer = 0
    Dim innerCount As Integer = 1
    Dim loggedToDb As Boolean = False
    Dim appSource As String = Me._applicationSource
    If applicationSource <> "" Then
        appSource = applicationSource
    End If
    '... more code'

    messageBuilder.AppendLine("(" + incommingEx.GetType.Name.ToUpper + ")" + vbCrLf + incommingEx.Message + " - SOURCE: " + incommingEx.Source)
    '...'
    While Not innerEx Is Nothing And innerCount <= 5
        messageBuilder.AppendLine("INNER" + Convert.ToString(innerCount) + " (" + innerEx.GetType.Name.ToUpper + "):" + vbCrLf + **innerEx.Message** + " - SOURCE: " + innerEx.Source)
        If Not innerEx Is Nothing AndAlso Not innerEx.StackTrace Is Nothing Then
            stackTraceBuilder.AppendLine("INNER" + Convert.ToString(innerCount) + ": " + innerEx.StackTrace) 'Me.GetNumberedStack(innerEx))
        End If
        innerEx = innerEx.InnerException
        innerCount += 1
    End While
    '... more code where the error message is inserted in a log table, via a sproc'
End Function

We have not seen this behavior before, but we have recently updated the .net framework to version 4.7.2, so it could be related.
the error text "string or binary" is comming from the bit that goes

innerEx.Message +
and that is a simple built-in Exception data type. So it is VB and/or .net and/or SQL Server that is building the string, not us.

The error comes after a call to a stored procedure, where the user has probably entered a varchar(1000) text. The stored procedure adds a bit more, and the table hasn't got room for all 1100 bytes. Hence the error.
The stored procedure does not have a try-catch block.


texts: Oracle error 43914 in CASE WHEN expression: arguments have undefined collation

texts:
I want to know which is the difference between this query:
SELECT *
FROM TEST_TABLE
WHERE CHAR_FIELD = CASE WHEN KEY_FIELD = 'Q' THEN 'M'
                   ELSE CASE WHEN KEY_FIELD = 'T' THEN 'K' ELSE
                              CHAR_FIELD2 END
                   END;

And this:
SELECT *
FROM TEST_TABLE
WHERE CHAR_FIELD = CASE WHEN KEY_FIELD = 'Q' THEN 'M'
                        ELSE CASE WHEN KEY_FIELD <> 'T' THEN CHAR_FIELD2
                        ELSE 'K' END END;

I expect both queries to return same data in all cases but, first one gives me an ORA-43914 "unable to determine collation: arguments have undefined collation" error while second one works just fine.
I read this information about collation determination in Oracle but it's not clear to me how it works with case-when expression.
Thanks for your help.


texts: Optimal query to show active records by date


texts: PostgreSQL Continuous Archiving & Point-in-Time Recovery in combination with netbackup

texts:
I have the following setup:

OS: CentOS
PostgreSQL 12
Netbackup 8

What I want to achieve:
Configure continuous archiving with the possibility of Point-in-Time Recovery
My current idea:

Generate a pg_basebackup every day at midnight and put the generated files in a folder outside of the data folder
Delete the files older than the created backup from the pg_wal folder
Create a Netbackup of the VM

With these three steps I should be able to make a PITR every day starting from the last base backup until the upcoming base backup, and my pg_wal folder will never grow too much. Also the usage of the internal archiving is not required.
Somehow this sounds very simple and I would like to ask if I am missing something here?


texts: pgBouncer exceeds connection limit even with idle connections

texts:
I'm using pgBouncer to balance connections in PostgreSQL.

Sets a limit of 2048 connections in the PostgreSQL main configuration and also in pgBouncer.

It happens that there are several connections in idle and pgBouncer does not use them, trying to create new ones and triggering the error:

Pooler Error: no more connections allowed (max_client_conn)

This is my postgresql.config setting:

# DB Version: 9.5
# OS Type: linux
# DB Type: oltp
# Total Memory (RAM): 28 GB
# CPUs num: 8
# Connections num: 2048
# Data Storage: ssd

max_connections = 2048
shared_buffers = 7GB
effective_cache_size = 21GB
maintenance_work_mem = 1792MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3584kB
min_wal_size = 2GB
max_wal_size = 4GB
max_worker_processes = 8

This is my pgBouncer setting:

pool_mode = session

server_reset_query = DISCARD ALL

max_client_conn = 2048

default_pool_size = 2048

min_pool_size = 32

I only have one database and one user that connects using pgBouncer.

I sure did something wrong, but I can't identify what it is.
What can it be?

This is the situation of my server.

This is the error message that is displayed even with multiple idle connections.

2019-09-02 16:35:33.423 1652 WARNING C-********: (nodb)/(nouser)@***.***.***.***:43682 Pooler Error: no more connections allowed (max_client_conn)
2019-09-02 16:35:35.048 1652 WARNING C-********: (nodb)/(nouser)@***.***.***.***:44360 Pooler Error: no more connections allowed (max_client_conn)


texts: How to delete orphaned records from sysmergesubscriptions table?

texts:
I have some old orphaned records for old subscriptions that I want to delete from the sysmergesubscriptions table.

Each time I delete unnecessary records, it show up again after it syncs with any subscriber!!

How can I delete these records permanently ?

P.S : These records were left out in this table even after I cleaned up our Merge Replication Setup in my question : How to properly clean Merge Replication Setup


texts: Why does my differencing virtual disk grow so much after restoring a differential SQL Server backup?

texts: Inspired by RedGate's upcoming SQL Instant Clone, I'm using virtual disks to store my test database and gain fast clones via differencing disks.  I created a 30 GB VHD and restored a 18 GB database from a full backup using WITH NORECOVERY to be able to later restore a differential backup on top.  I then created a child differencing VHD and restored a 1 GB differential backup.  I expected the differencing VHD to grow to 1 GB after the restore, but instead it grew to 15 GB.  Why is this?


texts: How can I rearrange these two queries to get the second column from inside the IN()

texts:
How can I get the random.id column from inside this first select

select id, name from random where id IN(1,2,3,4);
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Frank     |
|  2 | Tracey    |
|  3 | Bos       |
|  4 | Charlotte |
+----+-----------+

select `where`, `who`, COUNT(*) from cards where who = "." OR who IN(select name from random where id IN(1,2,3,4)) GROUP BY `who`, `where`;
+-------+--------+----------+
| where | who    | COUNT(*) |
+-------+--------+----------+
| pack  | .      |       78 |
| hand  | Frank  |       15 |
| hand  | Tracey |       15 |
|       | .      |       20 |
+-------+--------+----------+
4 rows in set (0.00 sec)

out when using the second more complicated select, above. Both tables have more columns than show and I do not want to add an extra row to the random table simply to define the . as comes out of the cards table. I think it should be possible to rearrange the second query to provide the desired output of where, id, count but sadly it eludes me.

The results I would like would look like below

+-------+-----------+--------+----------+
| where | random.id | who    | COUNT(*) |
+-------+-----------+--------+----------+
| pack  | NULL      | .      |       78 |
| hand  | 27        | Frank  |       15 |
| hand  | 32        | Tracey |       15 |
|       | NULL      | .      |       20 |
+-------+-----------+--------+----------+

The random.id column can be NULL or a consistent number, zero would be fine, and the who column is only a nice to have. Thanks for the welcome.
The Mysql version is 8.0.19-0ubuntu0.19.10.3 however it may well be better if the version was considered to be broader as I hope to run it on a server over which I have no control.

~~~

Well I have an answer of sorts, not what I was looking for but by divide and conquer two "simpler" queries supply me with the data I need. The data has changed so the numbers in the answers look different but the rules are still the same.

select `where`, 0 AS id, who, COUNT(*) AS cnt from cards WHERE who = "." AND `order` != 0 GROUP BY `where`;
+---------+----+------+-----+
| where   | id | who  | cnt |
+---------+----+------+-----+
| pack    |  0 | .    |  63 |
| dis_top |  0 | .    |   1 |
| discard |  0 | .    |   1 |
+---------+----+------+-----+

The data when there is no who defined.

SELECT `where`, id, who, cnt FROM random AS a RIGHT JOIN (SELECT `where`, who, COUNT(*) AS cnt FROM random AS b INNER JOIN cards AS c ON b.name = c.who GROUP BY `who`, `where`) AS d ON a.name = d.who;
+-------+------+-----------+-----+
| where | id   | who       | cnt |
+-------+------+-----------+-----+
| hand  |    1 | Frank     |  11 |
| hand  |    2 | Tracey    |   7 |
| table |    2 | Tracey    |   2 |
| hand  |    3 | Bos       |  11 |
| hand  |    4 | Charlotte |  11 |
+-------+------+-----------+-----+

The data when there is a who defined.

I have not claimed to have answered my own question as I still think it must be possible to do it a single query, but....


texts: Efficient matching rows across two tables with huge data set

texts: I have two tables with about 300+ columns and 20k+ rows each. Each row is characterized by a primary key which both tables have.I have to find the identical rows, rows with changes,and rows without a corresponding match in the second table(i.e there is a row with primary id=x in table 1 and no row has primary id=x in table 2). Attributing to this huge data set, can somebody suggest an "efficient" method to solve this.


texts: Is there another/better way to create and maintain large amounts of procedures?

texts:
So I've decided to take a step back, and scrutinize the way I implement procedures on my databases.

I'll list what I have done, give my reason for doing so and hopefully I will be told that I'm a moron, and should be doing it a better way

All my procedures are stored in an SQL file, in the the following way:

SELECT 'Creating guestuser user' AS ' ';
DROP USER 'guestuser'@'appserver';
CREATE USER 'guestuser'@'appserver' IDENTIFIED BY 'Password';

SELECT 'Dropping mydatabase guest procedures' AS ' ';
DROP PROCEDURE IF EXISTS myProcOne;
DROP PROCEDURE IF EXISTS myProcTwo;

SELECT 'Creating mydatabase procedures' AS ' ';
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcOne`(_id BIGINT)
    DETERMINISTIC
    COMMENT 'selects name from fluffy cats table given id'
BEGIN
    SELECT id FROM FLUFFYCATS WHERE id=_id;
END
$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `myProcTwo`(_name varchar(100))
    COMMENT 'inserts a new name into fluffy cats'
BEGIN
    INSERT INTO FLUFFYCATS (name) VALUES (_name);
END
$$
DELIMITER ;

SELECT 'Creating mydatabase guest permissions' AS ' ';
GRANT EXECUTE ON PROCEDURE db_mydatabase.myProcOne TO 'guestuser'@'appserver';
GRANT EXECUTE ON PROCEDURE db_mydatabase.myProcTwo TO 'guestuser'@'appserver';

I'll start from the top. I drop and create the user, so that it looses all it's procedure permissions. As you can see, I don't give the use any permissions until the end of the file. The reason I have chosen to do this, is so that the permissions are as restrictive as possible (And therefore more secure?) ...

Then I drop the procedures (If they exist) so that they will be created with any edits I have made

Then onto the actual creation of the procedures ... I don't think I've done anything out of the ordinary here, but always open for suggestions.

Then the script finishes with giving the user the permissions for each procedure.

Of course this is just an example file, but my real one has just reached the 100th procedure --Brings out cake-- ... And would be grateful for any suggestions as to how I could maybe reduce the workload when creating a procedure (Drop,Create,Permission) ...


texts: How do I optimize my transaction level running balances cartesian join?

texts:
This is a continuation of an question found here:

Generate multiple running totals with GROUP BY day

This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).

All of this is being done on a PostgreSQL 11.5 DB.

I am trying to sort out ways in which I can optimize the giant cartesian join that seems necessary in the query below:

SELECT c.customer_id, d.the_day
 , sum(t.tsla_amount) OVER w AS tsla_running_amount
 , sum(t.goog_amount) OVER w AS goog_running_amount
FROM   (
   SELECT the_day::date
   FROM   generate_series(timestamp '2019-01-01'
                    , date_trunc('day', localtimestamp)
                    , interval '1 day') the_day
   ) d
CROSS  JOIN (SELECT DISTINCT customer_id FROM transactions) c  -- !
LEFT   JOIN (
SELECT customer_id
    , created_at::date AS the_day
    , sum(t.amount) FILTER (WHERE stock_ticker = 'tsla') AS tsla_amount
    , sum(t.amount) FILTER (WHERE stock_ticker = 'goog') AS goog_amount
FROM   transactions t
WHERE  created_at >= timestamp '2019-01-01'
GROUP  BY customer_id, created_at::date
) t USING (customer_id, the_day)
WINDOW w AS (PARTITION BY customer_id ORDER BY the_day)
ORDER  BY customer_id, the_day;

While this query works, the end goal that I am trying to achieve is that, instead of every single customer having an entry on each day, I want only the customers that have transacted up through that day to be included in the report, and then as soon as a "new" customer has a transaction, they are then included moving forward.

(Currently this query creates rows for each customer even on days in the past in which they never had a transaction yet, and defaults values to 0)

CUSTOMER DDL:

CREATE TABLE customers (
customer_id varchar(255) NOT NULL,
city varchar(255) NULL,
state varchar(255) NULL,
postal_code varchar(255) NULL,
inserted_at timestamp NOT NULL,
updated_at timestamp NOT NULL,
CONSTRAINT customers_pkey PRIMARY KEY (customer_id)
);

CREATE TRANSACTION DDL:

CREATE TABLE public.transactions (
transaction_id varchar(255) NOT NULL,
amount float8 NOT NULL,
stock_ticker varchar(255) NOT NULL,
transaction_type varchar(255) NOT NULL,
customer_id varchar NOT NULL,
inserted_at timestamp NOT NULL,
created_at timestamp NOT NULL,
CONSTRAINT transactions_pkey PRIMARY KEY (transaction_id)
);

Example:
Old customers are making stock transactions from 01-01-20 forward
New Customer makes their 1st stock transaction on 01-03-20 (purchase 2 goog)

The report generated in this case for these customers would have an entry for each day, for each customer showing a running balance of their stock transactions:

01-01-2020 - old_123_cust_id - 5 - tsla
01-01-2020 - old_234_cust_id - 10 - goog

01-02-2020 - old_123_cust_id - 5 - tsla
01-02-2020 - old_234_cust_id - 10 - goog

01-03-2020 - old_123_cust_id - 5 - tsla
01-03-2020 - old_234_cust_id - 10 - goog
01-03-2020 - NEW_567_cust_id - 2 - goog

01-04-2020 - old_123_cust_id - 5 - tsla
01-04-2020 - old_234_cust_id - 10 - goog
01-04-2020 - NEW_567_cust_id - 2 - goog

In this example, the NEW_567_cust_id, does not appear in the report, until their 1st transaction, and then moving forward, that customer continues to appear in the report, even if they do not make any additional transactions.

Additional Information:
Estimated transactions row count: 300,000
Estimated customers row count: 45,000
Earliest transaction: 01-01-2019
Range: 01-01-2019 -> Today() (When query runs)
The day of a customer's 1st transaction will never change

Happy to provide any additional information!


texts:
I have a question regarding this Split String function Aaron Bertrand recommends here.

With it you can extract information between two slashes. Now if I want to extract not only one piece of info like the servername, but also a second piece like the day of the week. How would I have to rewrite the code in order to get that info in one result set?

Here's a sample of my data

E:\BaseData\RUK\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv
E:\BaseData\RUK\Manual_Review\manual_review_report_2015_11_01.csv
E:\BaseData\RUK\Disputes\dispute_report_2015_11_01.csv
E:\BaseData\RSE\HPP_Conversion_Detail\hpp_conversion_detail_report_2015_11_02.csv
E:\BaseData\RSE\Manual_Review\manual_review_report_2015_11_01.csv
E:\BaseData\RSE\Disputes\dispute_report_2015_11_01.csv

I'm interested in everything between the second and third slash and the third and fourth slash. What Geoff suggested is a good idea. I'm then getting the results in rows. Is it possible to get the results back in additional columns?

Thanks!


texts: PostgreSQL: Very high read IO on one of the servers

texts:
We have two servers dedicated to PostgreSQL 11.3:

master (128Mb RAM, 16 core CPU, SSD) - all write queries and 65% of read queries
slave (64GB RAM, 12 core CPU, SSD) - 35% of read queries

For unknown reason, slave dies with giant CPU IO wait times (around 70%) as soon as we enable it, while master has 0.5% IO wait times and runs fine. iotop on slave is showing a lot of disk reads from postgresql processes with SELECT quries.

Configuration files:

master (works great)

max_connections = 100

# for replication
wal_keep_segments = 100
wal_level = replica
max_wal_senders = 10
hot_standby = on
archive_mode = off

archive_command = 'cp %p /backups/pgsql-wal/%f'
max_standby_streaming_delay = 30s

# how much dedicated memory will be used by PostgreSQL for cache
shared_buffers = 20GB

# estimate of the memory available for disk caching.
effective_cache_size = 80GB

# transaction commits very quickly because it will not wait for a WAL file to be flushed, but reliability is compromised
synchronous_commit = off

maintenance_work_mem = 2GB

checkpoint_completion_target = 0.7
checkpoint_timeout = 5min

wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 256MB
min_wal_size = 1GB
max_wal_size = 10GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

# vacuum
vacuum_cost_limit = 1000
autovacuum_vacuum_cost_delay = 10
autovacuum_max_workers = 6

# io performance
effective_io_concurrency = 2
random_page_cost = 1.5
seq_page_cost = 1.0

shared_preload_libraries = 'pg_stat_statements'

track_activity_query_size = 10000

slave (gets huge amount of disk read io)

max_connections = 100

# for replication
wal_keep_segments = 100
wal_level = replica
max_wal_senders = 10
hot_standby = on
hot_standby_feedback = on
archive_mode = off
archive_command = 'cp %p /backups/pgsql-wal/%f'
max_standby_streaming_delay = 30s

# how much dedicated memory will be used by PostgreSQL for cache
shared_buffers = 10GB

# estimate of the memory available for disk caching.
effective_cache_size = 40GB

# transaction commits very quickly because it will not wait for a WAL file to be flushed, but reliability is compromised
synchronous_commit = off

maintenance_work_mem = 2GB

checkpoint_completion_target = 0.7
checkpoint_timeout = 5min

wal_buffers = 16MB
default_statistics_target = 100
work_mem = 128MB
min_wal_size = 1GB
max_wal_size = 10GB
max_worker_processes = 16
max_parallel_workers_per_gather = 6
max_parallel_workers = 12

# io performance
effective_io_concurrency = 2
random_page_cost = 1.5
seq_page_cost = 1.0

shared_preload_libraries = 'pg_stat_statements'

# Increase the max size of the query strings Postgres records
track_activity_query_size = 10000

Any ideas what parameters are miconfigured on slave?


texts:
I want to know what does "quiesce" in "db2 luw" means, how can I use it in linux and how it can affect the access to the tablespaces

I want to know how to tell whether or not a tablespace in a ddl was quiesced or not.


texts: Connect Oracle Database 12c via SQL Developer when it is in restricted mode

texts:
I would like to connect to database 12c via SQL Developer(in same VM where db is hosted) when it is in restrict mode. Tried below approaches but nothing worked.
Kindly help !

Added (UR=A) in tnsname.ora as below:
OBIEE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.37)(PORT = 1521))
(CONNECT_DATA =
  (UR=A)
  (SERVER = DEDICATED)
  (SERVICE_NAME = OBIEE)
)
)
Also, defined this in sqlnet.ora:
tcp.validnode_checking = YES
tcp.invited_nodes = IP address

*Note: I am able to connect to it via cmd.


texts:
I have about 5 schemas in my oracle 12c database for which I need the DDL for all the indexes for each of the schemas. DDL I have checked it on how to get the code from it, but for all the indexes and for each schema in one go.. is it possible?
If yes, help me out.!

Thanks.!


texts: Database design for multiple account types management system

texts:
I am trying to create multiple account types management system.
At first time, I have to types  normal type, and professional type (each type has own table, so I have two tables at this time), and new tables are coming.
Each account type has some profiles. Each profile has own table, example: table profile_from_service_A, table profile_from_service_B, ... (new profile types are coming).
When new record is inserted to normal (or professional) table, I call to service A, service B, .. to create profiles, and insert profile result to corresponding table. There is not any relation between these service about business domain.
Most used query is used for reporting , example result: (account_id, account_type, profile_A_id, profile_A_propery_1, profile_A_propery_2, profile_B_id, profile_B_name, profile_E_id,...)
I intend create two mapping tables. For profile_from_service_A, mapping table is service_A_account_mapping with columns: (id_service_A, account_type, accounnt_id), account_type is normal or professional at this time.
This structure is also used for service_B_account_mapping, but I use id_service_B instead of id_service_A
If there is new profile, I have to create new mapping table.
Another solution: add profile_A_id, profile_B_id to normal, professional tables. Don't use mapping table anymore.
So which solution is better?
Thank you!


texts: Is it more efficient to create check constraints on PostgreSQL table before or after bulk load?

texts:
I am creating a table in PostgreSQL and bulk load with data first. PostgreSQL's documentation recommends to insert large volumes of data first and then create indexes and foreign key constraints. What's about check constraints? Is it better to create them before inserting initial data or after? Or does it matter?
For example, a table will have an index on time, NOT NULL constraint on it, check constraint on time interval and possibly on device. If no bulk load, the definition will be:
CREATE TABLE conditions(
    time timestamptz NOT NULL,
    device int,
    value float,
    CONSTRAINT cond_time CHECK ("time" >= '2020-01-16 01:00:00+01'::timestamp with time zone
                            AND "time" < '2020-01-23 01:00:00+01'::timestamp with time zone),
    CONSTRAINT cond_device CHECK (my_hash_function(device) >= 1073741823
                              AND my_hash_function(device) < 1075441823)
);
CREATE INDEX conditions_time_index ON conditions(time);

The index will be create after the bulk. The data to bulk load is cleaned and satisfies the constraints. The constraints are needed in future during normal operations.
Will it be more efficient if the check constraints and NULL NOT are created before bulk load or after? Does it matter if an index will exist on the column targeted in the check constraint?


texts: Oracle 11 IMPDP Table Exists but gets Does Not Exist Error

texts:
I'm running in to a weird problem with IMPDP and Oracle DB version 11.2.0.4.0 where I have a table in the destination schema that does indeed exist, but IMPDP thinks it doesn't. See this entry from the import log file:

  Starting "EXPORT_USER"."SYS_IMPORT_TABLE_01":  export_user/********
  directory=EXPORT_DIRECTORY
  dumpfile=SMARTCLOUDCONSOLE_OWNER_20170830.dmp
  TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
  REMAP_SCHEMA=SMARTCLOUDCONSOLE_OWNER:MIKETESTCONSOLE_OWNER
  TABLES=SMARTCLOUDCONSOLE_OWNER.ACCOUNTDEPLOYMENTLINKS QUERY="WHERE
  account_id = (select account_id from accounts where lower(guid) =
  '#####')"

  Processing object type SCHEMA_EXPORT/TABLE/TABLE

  Table "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" exists. Data
  will be appended to existing table but all dependent metadata will be
  skipped due to table_exists_action of append

  Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

  ORA-31693: Table data object
  "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" failed to load/unload
  and is being skipped due to error: ORA-00942: table or view does not
  exist

We are using VPDs such that the _owner user owns all of the objects, so I don't think it's a problem of ownership. And it seems like it figures out that the table exists from the second line there Table "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" exists. but then when it proceeds it fails with ORA-00942: table or view does not exist

What might be wrong with my use of IMPDP?


texts: Model a family tree database in Access 2007

texts:
I designed my database with the intention of implementing it with MySQL, but have now decided to implement it in Access 2007 using form and subforms. While creating my UML diagram, I realised I have several N:M relationships.

A tribe has many clans (1:M)
A clan has many families (1:M)
A family has many Sims, and a Sim can belong to many families (M:N)
A lot can have many families living on it (1:M)
A Sim can be a member of many classes, and a class has many Sims (M:N)
An era has many classes, and a class can be in many eras (M:N)

This is what my model looks like so far:

SIM
----
Sim_id - unique PK
Sim_fname - First name of the Sim
#misc other details which will be filled in
fam_id - FK to FAMILY
Is_heir - yes/no

TRIBE
-----
tribe_id - unique PK
tribe_name
founder_id - FK to SIM who is the founder of the tribe

CLAN
----
clan_id - unique PK
clan_name
founder_id - FK to SIM who is the founder of the clan

FAMILY
------
fam_id - unique PK
partner1_id - FK to SIM who is the first partner in the relationship
partner2_id - FK to SIM who is the second partner in the relationship
start_day - Day the relationship began
clan_id - FK to CLAN the FAMILY belongs to
lot_id - FK to LOT on which the FAMILY lives
end_day - can be NULL
end_reason - reason why the relationship ended

ERA
---
era_id - unique PK
era_name

CLASS
-----
class_id - unique PK
class_name

LOT
---
lot_id - unique PK
lot_gen - generation number of the male head of the lot
tribe_id - FK to TRIBE
lot_number - number of the lot in the sequence

linkSIM_CLASS
-----------
sim_id - FK to SIM, composite PK
class_id - FK to ERA, composite PK

linkCLASS_ERA
-------------
class_id - FK to CLASS, composite PK
era_id - FK to ERA, composite PK

An example will help illustrate it:

Caleb is the founder of the Cullen tribe. He is the founder of the first clan, and the head of the first family (together with his wife Calliope). Their firstborn is daughter Bethany, followed by triplets (daughter Ferial, son Flint and daughter Feena). Flint is the firstborn son, so he is the heir. As he is a Generation 2 male, he is the founder of the clan Flintese.

Flint moves into his own lot and marries Ferial, forming his own family. They have several children, and then Ferial dies. Flint then marries Rahab (from the Swan tribe), forming another family. He is also given a concubine, Cullen-Fer, forming the another family. He has multiple children with both females, and they all live on the same lot. Flint was born in the Neanderthal Era as part of the Standard Class. He currently lives in the Roman Era as part of the Plebian Class. I will add a picture of the tables populated with this example data once I gain enough rep to post images.

(Please ignore the fact that he married his sister, it's part of the Sims 2 challenge I'm doing). Yes, I am creating a database to track my Sims, it's a fun way for me to use my SQL skills and to learn new things. I would like to maintain a historical log of where the Sim has lived, the multiple families they belong to, the different eras they have lived in etc.

There will be other tables also, but these are my core tables, so I would like to finalise these ones before implementing the other tables, as those relationships are simpler. Is my structure as normalised and efficient as possible?


texts: Is it okay to define a foreign key referencing a ‘user_id’ column from every table?

texts:
I have a multi user site which in which

a user can have many restaurants,
each restaurant can have many menus and
each menu can have many items.

For security purposes, I was going to add a user_id foreign key making a reference to the user.user_id column from each of the pertinent tables. That way, when updating the data, I can use the id of the row they are updating in combination with their user_id (stored in a session at the application program level) to ensure they don't maliciously update or view another users content.

Questions

Is this a common practice?
Is it still normalized to do this?

Responses to comments

The code is only running on a website I control. But I want to prevent a user from accessing a row of data that he/she does not own, based on his/her user_id value. For instance, at the application program level, if they send a GET request to /menus/1 it will retrieve the row from the menu table with the id value of 1.


texts: Sample YAML Configuration Files for MongoDB?

texts:
The MongoDB configuration options documentation lists all the available options that can be specified, but does anyone have a set of fully formed example YAML formatted config files for MongoDB instances in various roles?

A set of examples for the common roles would be a very useful starting point for those starting from scratch, or looking to test with the latest configuration file format.


texts:
I wanted to know if MySQL let the admin set a timezone per database ?

I have three different databases for three different customers. Each of them being in their own timezone.

I would like to load the exact same event for each customer and would like it to run everyday at 00:00:01 for their respective timezone. Is it possible ?

I can load three different sql script specifying in each their respective timezone, but it doesn't scale well and it make me have three version of almost the same file.


texts:
I have a Postgres database that has grown to the size where it is no longer feasible to store everything on a single database node. There is a Customer table in my schema where each row represents a (surprise!) customer. Every other table in my database is foreign keyed on this customer table and I would like to shard my database along customer ranges. For instance, I want customers with an id of 1 - 100 to go to database node A, 101 - 200 to go to database node B, and so on.

I've found information on table partitioning but I've found little else that shows me how I would enable database partitioning in Postgres.

What are my options for sharding a database in Postgres? If sharding is not possible, what are my alternatives?


texts: How to see text of the query which triggers the trigger inside trigger?

texts: I have very small database with a bit complex triggers which works very slow. Those triggers are written to extend business logic (becase we can not manage the code of the application we are using). To find the problem I need to trace what happens during different SQL requests. I will log different sections of the trigger, but I need to have SQL query that causes the trigger to execute. How can I extract it from within the trigger (if it is possible) so I can write it inside my log table?


texts: Increment of integer PK values by 1 doesn't throw duplicate key error. Why?

texts:
As stated in the title I am wondering why the following piece of code will note fail with duplicate key error. As a result of the following test, obviously, the update does not check for duplicity after each row. What happens inside? Is this part of the standard of just SQL Server specific behavior? Thanks!

DECLARE @T TABLE(
    x INT PRIMARY KEY
);

INSERT INTO @T VALUES (1)
INSERT INTO @T VALUES (2)

UPDATE @T SET x = x + 1;

-- DUPLICATE KEY ERROR EXPECTED.

SELECT * FROM @T;

--- VALUES HAVE BEEN INCREMENTED. NO ERROR THROWN.


texts: How does my system understand if data got masked?

texts:
I was trying to understand the difference between encryption and masking
Below statement says that real data replaced and gone!

Masking protects your data by transforming it into a readable format that’s useless to anyone who steals it. The actual data is replaced by fictional information. There is no encryption solution to revert the data to its original state. The real data was replaced and is gone forever.

What does it mean? If real data are gone, then how do I use it in my system?
For example:
I might masked EMAILID and my system wants to do some logic based on the email ID, then how do I compare the email ID in code?
Basically, if I am not using the sensitive data, why do I store it in my database in the first place?


texts: Count duplicate versions of databases (SQL Server 2008 R2)

texts:
I wish to pull statistics on the number of duplicate database versions we have running in our environment - most databases run with version control in the database name, so I built this query:

SELECT 'Production' as Class, getdate() as DateRecorded, left(d.name, CHARINDEX('_v',d.name)-1) as [DBGroup], d.name as [DatabaseName], ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
ON D.DATABASE_ID = MF.DATABASE_ID
where d.name like '%[_]p[_]%'
and d.name not like '%import'
and d.name like '%[_]v[0-9]%'
GROUP BY left(d.name, CHARINDEX('_v',d.name)-1),d.name
order by coalesce(left(d.name, CHARINDEX('_v',d.name)-1), d.name)

which is great; however this excludes any databases which do not contain any version control ('%[_]v[0-9]%').  When I remove the respective like statement, it crashes with the following:

Invalid length parameter passed to the LEFT or SUBSTRING function.

based on how it can't parse the charindex parameter on databases which do not contain any version control, so I built a secondary query to hoover everything else up:

SELECT 'Production' as Class, getdate() as DateRecorded, NULL as [DBGroup], d.name as [DatabaseName], ROUND(SUM(MF.SIZE) * 8 /1024,0) AS SizeMB
FROM SYS.MASTER_FILES MF
INNER JOIN SYS.DATABASES D
ON D.DATABASE_ID = MF.DATABASE_ID
where d.name like '%[_]p[_]%'
and d.name not like '%import'
and d.name not like '%[_]v[0-9]%'
GROUP By d.name

So the purpose of this question is to request how to compile a query capable of running both queries as one single query?


texts: Why is Convert being used in this statement and how does it differ from CAST?

texts:
This is a piece of code on MSDN page for OVER clause:

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, TerritoryID
,DATEPART(yy,ModifiedDate) AS SalesYear
,CONVERT(varchar(20),SalesYTD,1) AS  SalesYTD
,CONVERT(varchar(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID
                                        ORDER BY DATEPART(yy,ModifiedDate)
                                       ),1) AS MovingAvg
,CONVERT(varchar(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID
                                        ORDER BY DATEPART(yy,ModifiedDate)
                                        ),1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL OR TerritoryID < 5
ORDER BY TerritoryID,SalesYear;

So I am having issues understanding why CONVERT function had to be used there. I assume it is to do with return types of one of the fields in the expression part of the convert function? Can CAST be used instead?
Second question I have is, how exactly does this part work?

SUM(SalesYTD) OVER (PARTITION BY TerritoryID
ORDER BY DATEPART(yy,ModifiedDate)

What exactly is that saying? Is it calculating sum for every year? Is that it?


texts:
I am a .net developer and not an expert database administrator.

I have an application running on SQL server 2008.  Yesterday there was an issue with a certain query getting executed in an infinite loop which caused the database to grow from 500 MB to 380 GB.  The log file itself was 300 GB and the mdf file 80 GB.

Today I stopped the query and truncated the log file to 12 MB.  Then I figured out which tables had grown and truncated them, but still my database size is 67 GB!

I ran a query to determine the size of all tables https://stackoverflow.com/a/7892349/287100

However, the total size of all tables was less than 100 MB.  I want to know why the database size is so big and how I can shrink it back to around 500MB.

Thanks.


texts: Multiple to_json(array_agg), separate joins

texts:
I'm building a messaging system, and trying to get a list of messages and their replies; and also join the participants of the message threads.

So I'm shooting for messages = [{id, subject, body, replies: [..], users: [..]}]

SELECT messages.*,
       to_json(array_agg(users)) users,
       to_json(array_agg(replies)) replies
FROM messages
LEFT JOIN (SELECT id, fullname, email, company FROM users) users
   ON users.id = messages.user_id OR users.id = messages.to
LEFT JOIN (SELECT * FROM messages ORDER BY created_at) replies
   ON replies.message_id = messages.id
WHERE messages.to = :to OR (messages.to IS NOT NULL AND messages.user_id = :to)
GROUP BY messages.id;

I've tried various other queries, this gets me the closest. The problem is that for each message, all the replies are repeated once. I.e., if there are 3 replies in a thread, I get 6 (2x each). Any ideas what might be wrong?

I am using Postgres 9.5.


texts: Merge Similar Schema from one MySQL DB to Another

texts:
I have two databases (db1 and db2). db2 has all tables from db1 but not necessarily the same columns. I want to move any missing columns in db2 from tables in db1 but keep the rest unchanged.

example:

db1 schema for table 'account'

CREATE TABLE IF NOT EXISTS `account` (
  `ListID` varchar(255) DEFAULT NULL,
  `FullName` varchar(255) DEFAULT NULL,
  `IsActive` varchar(255) DEFAULT NULL,
  `ParentRef_ListID` varchar(255) DEFAULT NULL,
  `ParentRef_FullName` varchar(255) DEFAULT NULL,
  `CurrencyRef_ListID` varchar(255) DEFAULT NULL,
  `CurrencyRef_FullName` varchar(255) DEFAULT NULL,
  `CustomField1` varchar(50) DEFAULT NULL,
  `CustomField2` varchar(50) DEFAULT NULL,
  `CustomField3` varchar(50) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  KEY `accountIdIndex` (`ListID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

db2 schema for table 'account':

CREATE TABLE IF NOT EXISTS `account` (
  `qbsql_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ListID` varchar(255) DEFAULT NULL,
  `TimeCreated` varchar(255) DEFAULT NULL,
  `TimeModified` varchar(255) DEFAULT NULL,
  `FullName` varchar(255) DEFAULT NULL,
  `IsActive` varchar(255) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  KEY `accountIdIndex` (`ListID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

final schema for table 'account':

CREATE TABLE IF NOT EXISTS `account` (
  `qbsql_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `ListID` varchar(255) DEFAULT NULL,
  `TimeCreated` varchar(255) DEFAULT NULL,
  `TimeModified` varchar(255) DEFAULT NULL,
  `FullName` varchar(255) DEFAULT NULL,
  `IsActive` varchar(255) DEFAULT NULL,
  `ParentRef_ListID` varchar(255) DEFAULT NULL,
  `ParentRef_FullName` varchar(255) DEFAULT NULL,
  `CurrencyRef_ListID` varchar(255) DEFAULT NULL,
  `CurrencyRef_FullName` varchar(255) DEFAULT NULL,
  `CustomField1` varchar(50) DEFAULT NULL,
  `CustomField2` varchar(50) DEFAULT NULL,
  `CustomField3` varchar(50) DEFAULT NULL,
  `Status` varchar(10) DEFAULT NULL,
  KEY `accountIdIndex` (`ListID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

The databases have over 200 tables in them so a "single table at a time" solution will not work for me here.


texts:
Learning sql server over here and I'm trying to figure out how to see the securable permissions I have added via T-SQL , in SSMS

For example, I have a database user called "LoginUser1" in Database1 and I granted "grant create table, create view to LoginUser1".

I'm trying to see this permission in SSMS. I go to the properties for the user in this database but i'm not seeing anything. What am I missing?
Thanks!


texts: Why do I have two collations in mysqldump, under stored procs?

texts:
When I run mysqldump --routines  I'm seeing multiple collations for my stored procs. Neither my DB nor columns use utf8mb (I understand the benefits of utf8mb over utf8 but let's leave it for now).

/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
ALTER DATABASE `pushcoin` CHARACTER SET utf8 COLLATE utf8_general_ci ;
/*!50003 DROP PROCEDURE IF EXISTS `sp_cron_get_money_pool_balance` */;
ALTER DATABASE `pushcoin` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`admin`@`%` PROCEDURE `sp_name...`

The database seems all utf8...

MySQL> SHOW VARIABLES LIKE  'char%';
+--------------------------+-----------------------------------------------+
| Variable_name            | Value                                         |
+--------------------------+-----------------------------------------------+
| character_set_client     | utf8                                          |
| character_set_connection | utf8                                          |
| character_set_database   | utf8                                          |
| character_set_filesystem | binary                                        |
| character_set_results    | utf8                                          |
| character_set_server     | utf8                                          |
| character_set_system     | utf8                                          |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)

MySQL> SHOW VARIABLES LIKE  'coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)


texts: I want to categorizes things in data base so that one thing can be in several categories. I have a table for categories. I want to make foreign key to them but how can I make a field have several value? is there a way to make the coulmns dynamically or have an array of an enumeration or array of another table fields?


texts: 3NF Decomposition - I'm not sure about this example

texts:
I am trying to make sense of an example of 3NF decomposition using the 4-step algorithm mentioned by Ullman here, but I'm not understanding what my lecturer is doing with the last step (or, worse, I'm not understanding the algorithm itself).

I realize this is a bit of a newbie question, but I did all the googling but couldn't find anything illuminating and I've been sitting here scratching my head for a few hours now.

Now,

the algorithm in question is:

Find a canonical cover F_c for F
For each FD X->Y in F_c create a relation with schema XY
Eliminate a relation if its schema is a subset of another
If none of the schemas created so far contains a key or R add a relation schema containing a key of R.

Now, the example my lecturer gave us starts with

 R<A,B,C,D,E,F>
 CE->A, C->D, A->B, D->BE, B->F, AD->CF

Fc is shown to be

C->A, C->D, A->B, D->B, D->E, B->F, AD->C

and the keys are C and AD.

All's well until now.

Now we apply step 2 of the algorithm.

We have:

R_i:    A,B   |   D,B,E  |   F,B    |   C,D,A   |   A,D,C
     ---------+----------+----------+-----------+-----------
F_i    A->B   |   D->B,  |   B->F   |   C->D    |   AD->C
              |   D->E   |          |   C->A    |

Now this is were it gets ugly.
The lecturer remarks that the fifth one, R_5 is a subset (a trivial subset, say I) of the fourth and proceeds to... "merge" them, leaving us with

R_i:    A,B   |   D,B,E  |   F,B    |   C,D,A
     ---------+----------+----------+-----------
F_i    A->B   |   D->B,  |   B->F   |   C->D
              |   D->E   |          |   C->A
              |          |          |   AD->C   - - - - - notice this!

Notice how AD->C popped up in F_4.

The question is simply: why?

Why didn't he remove the fifth column altogether?

Is he applying step 3 of the algorithm?
But it only instructs to eliminate the offending schemas, it doesn't say anything about adding dependencies to other schemas.

Is he applying step 4 of the algorithm?
But it says that the schemas must contain a key of R, not all of them (which would then make some sense).

Is this simply a trivial mistake?
Is the final result correct anyway?

Thanks everybody.