texts: Is it possible to insert multiple IP addresses in a single entry in a pg_hba.conf file?

texts:
Let's say I have the following
3 child IP addresses - 192.168.10.15 ,192.168.10.41 and 192.168.10.81
1 master IP address - 192.168.10.37
I want the 3 child IP addresses to be able to access the master IP address. Now, in order to do this, the pg_hba.conf file of the Master must contain an entry for each of the child IP addresses.
My question is instead of having 3 separate entries like this,

host  all  all  192.168.10.15/32  md5
host  all  all  192.168.10.41/32  md5
host  all  all  192.168.10.81/32  md5

Can it have 1 entry for all 3, something like

host  all  all  192.168.10.15/32,192.168.10.41/32,192.168.10.81/32  md5

I tried the above method but none of the 3 IPs got the access. Is it even possible?


texts:
What's difference between parallel (4) and parallel 4 with and without brackets?
For eg :

select /*+parallel(4) */ * from table_name;
select /*+parallel 4 */ * from table_name;


texts: Migrate data from MySQL to SQL Server, database names differ

texts: I'm using SQL Server Migration Assistant for data migration from MySQL to SQL Server. I would like to know whether it is possible to migrate data from databases of different names but tables inside databases have the same names.


texts: SQL Trigger fails retrieve new record in second query

texts:
Any advice on this will be appreciated
This script created a table with new records.
My script is failing to assign the "Inserted" ID to the query but manually assigning a value works
(a) Tried Cast both side of the Where Clause to int
(b) Moved this section within the second BEGIN -END section
@MAIL_BODY = @MAIL_BODY +
    '
' +
    '' + @OrderID + '	' +
    '' + @Description + '	' +
    '' + @DiscountedPrice + '	' +
    '
' FROM [dbo].[OrderItem] it INNER JOIN [dbo].[Product] pt on pt.ProductID=it.ProductID WHERE it.OrderID = @OrderID``

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[EMAILFINAL]
   ON  [dbo].[Order]
AFTER INSERT
AS

BEGIN
DECLARE @OrderID VARCHAR(3),
@inpToEmail VARCHAR(MAX),
@Description VARCHAR(MAX),
@DiscountedPrice VARCHAR(MAX),
@MAIL_BODY VARCHAR(MAX)

SELECT @OrderID = i.OrderID FROM inserted i;
IF (@OrderID IS NOT NULL)

SELECT @inpToEmail=dr.DistributorEmail from [dbo].[Distributor] dr
inner join [dbo].[User] Ur on ur.DistributorID=dr.DistributorID
inner join [dbo].[Order] odr on odr.UserID=Ur.UserID
where odr.OrderID= + @OrderID;

PRINT @inpToEmail

SET @MAIL_BODY = '
' +
    '

    OrderID
    Feature
    Cost($)

'

SELECT
    @MAIL_BODY = @MAIL_BODY +
        '
' +
        '' + CAST([OrderID] AS VARCHAR(11)) + '	' +
        '' + CAST([ProductName] AS VARCHAR(50)) + '	' +
        '' + CAST([DiscountedPrice]  AS VARCHAR(11)) + '	' +
        '
'
  FROM [dbo].[Order_Product]
  where Orderid = @OrderID

BEGIN
    SET NOCOUNT ON;

declare
@HtmlHeader nvarchar(500),
@body nvarchar(max),
@inpEmailProfiler VARCHAR(50) ='Notifications',
@inpBodyFormat VARCHAR(5) ='HTML',
@inpSubjectLine NVARCHAR(MAX)='Quotation'

SELECT

@MAIL_BODY = @MAIL_BODY +
        '
' +
        '' + @OrderID + '	' +
        '' + @Description + '	' +
        '' + @DiscountedPrice + '	' +
        '
'
 FROM [dbo].[OrderItem] it
INNER JOIN [dbo].[Product] pt on pt.ProductID=it.ProductID
WHERE it.OrderID = @OrderID

SELECT @MAIL_BODY = @MAIL_BODY + '
' +
'
' + '
' + '
' + '
' + 'Kind regards,' +
 '
' + 'Kvikna ehf.'

EXEC msdb.dbo.sp_send_dbmail
@profile_name = @inpEmailProfiler,
@recipients = @inpToEmail,
@body_format='HTML',
@body = @MAIL_BODY,
@subject = @inpSubjectLine;

END
END


texts: creating stored procedure to populate empty table using pl/sql

texts:
I have created a table and inserted a values one row at a time. However, I am trying to populate the table using a "stored procedure" or "trigger". The values can be a random string or number. I have been researching on it, but couldn't find any related examples.

Could some please provide an example or suggest some reference to it? Cheers!!

CREATE TABLE BOOKING
(
  BOOKING_ID VARCHAR2(7) PRIMARY KEY
, BOOKING_DATE DATE
, BRANCH_ID VARCHAR2(7)
, ROOM_PRICE NUMBER(15)
, IS_BOOKED VARCHAR(5))
TABLESPACE TS02
PCTFREE 20  PCTUSED 70
STORAGE (INITIAL 200K NEXT 500K PCTINCREASE 20);

--to insert value to BOOKING table for one row per at a time
INSERT INTO BOOKING
VALUES ('HIM001','02-FEB-2016','KTM',500,'NO');


texts: SQL - Need some advices for building a large db

texts:
currently, as I understand, to build a large db, we need to pay attention to its size and performance. Below are what to reach:

Size: HDD has > 4tb to store filegroup if using partition tables.
Performance: Using partition tables. In diagram, we will not create relationship among tables. I heard that it will help us query data faster.

But, in reality, I don't know it is good solution or not, please advice me with some concerns:

if I have 100 milion records in each tables, and using parttion table, each quarter is 1 table/part. So, when I need to report in 1 year, how is its performance to query/filter data?
With 100 milion records, it is posible to query data faster with no relationship?
How about insert data with checking exist data first? it will take a long time to check duplicate before insert?
How about replicate data by using partition table?

I know that, some ways, this is the job of DBA, but I readly want to understand them, please advise me.

I'm very appriciate for you helps.

Thanks.


texts: Are the ALL_OBJECTS and DBA_OBJECTS different when you have DBA Role?

texts:
I am trying to fully understand the difference between ALL_OBJECTS and DBA_OBJECTS.
I am still new in Oracle and get confused by the whole Access/Roles thing.

From here:

ALL_OBJECTS shows all objects of current and those object which you have rights to access.
DBA_OBJECTS shows all object of all users .

My question is, if you have DBA role, should ALL_OBJECTS be equal to DBA_OBJECTS?
I have DBA role and in the Database I am working on and ALL_OBJECTS returns fewer elements than DBA_OBJECTS.

If I have DBA Role shouldn't I have access to all the objects in DBA_OBJECTS and therefore both views should be equal?


texts:
By chance, I happened to see that the autoclose feature on SQL dbs (I'm using SQL 2008) shouldn't be on and I found that all of my SQL dbs were set to have autoclose on.

I've set autoclose to off on them all (I have about 400 dbs of varying sizes on my SQL Server instance - biggest db probably 350MB), but the memory allocation on my SQL Server process is now on 1,807,252k when its usually about 1,100k.

Is this normal?  Should I go back to autoclose on?

Should the system ones (eg master, model, etc) have their autoclose set to off as well?  This might have already been like this, but I'm not 100% sure.


texts:
I have a table which I use to determine actions based on conditions provided. This is what it looks like right now.

IPAddress, MACAddress, Hostname, ScriptName, Argument1, Argument2, Argument3
10.2.1.* ,  .*       , .*      , diagnostics.sh, -runAll,        ,

In this table, the first three columns are the conditions, and the last four columns are the actions. Values of the condition cells are regex values that I do REGEX_LIKE on, so when I am looking up the correct script to run on a device, I look at it's IP (or MAC I'm given one or the other, not both) and hostname and if the regex matches, then I get the script name that needs to be run and arguments for that script.

Some scripts have one argument, some have two and others have three.

How do I modify this so I can an arbitrary list of conditions and actions. e.g. I am given a Key, Value map (attribute name, attribute value) for conditions, and once a rule has been satisfied, I retrieve a list of action Key, Value elements that are relevant for that device.

To keep things simple, there won't be any updates done to this database once it is initialized. It's initialized on start up by reading rules from a flat file, and the used at runtime to determine rules that have been satisfied for provided input.

This is the schema I think is better suited for this, but I don't know if this is correct and I don't know how to get the matching rules either.

Table Name: Column List
Attributes: attributeId, name, value
Conditions: conditionId, ruleId, attributeId
Actions   : actionId, ruleId, attributeId
Rules     : ruleId


texts: Calling SELECT DISTINCT on multiple columns

texts:
I am unclear on exactly on,

SELECT DISTINCT col1, col2, ...
FROM table_name

When called on one column it gives back only distinct values of that column. What happens when we use it with multiple columns and should we ever do this?


texts:
First I install oracle 11g database with this configuration

Step 2 of 9

I selected Create and configure a database

Step 3 of 9

I selected Desktop Class

Step 4 of 8

Global database name: Blitz

Administrative password: Pass1234

After that it gives me this information

Global Database Name: Bliz

System Identified(SID): Blitz

Server Parameter File name: C:\app\annie\product\11.2.0\dbhome_3\database\spfileBlitz.ora

The Database Control URL is https://localhost:5501/em

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.
The encryption key has been placed in the file: C:/app/annie/product/11.2.0/dbhome_3/localhost_Blitz/sysman/config/emkey.ora.
Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

Then configure this:

Username - Password

SYS      - Pass1234

SYSTEM   - Pass1234

Then I try to install Oracle Enterprise Manager 12c

First in Command Prompt I enter the following command to get the host name:

sqlplus / as sysdba

select host_name from v$instance

that gives me PC1

Then in Step 6 of 9 of Enterprise Manager Installation

I provide the following for Database Connection Details

Database Host Name: PC1

Port: 1523

Serivice/SID: Blitz

SYS Password: Pass1234

But I got this error

There was a problem connecting to the database on the host and port specified. Check to make sure the host is up or that the database listener is running on that host.

So I tried tried  to changed the Database Host Name to localhost

But I got this error

Enterprise Manager Database Control schema exists in the database. To proceed with the installation of Enterprise Manager Cloud Control, you must deconfigure the same by running the following command:

To deconfigure Database Control for a single instance database, do the following command on database host:
 (a) Set the environment variable <ORACLE_HOME> to the database home.(b) Run the following command:
<Database ORACLE HOME>/bin/emca -deconfig dbcontrol db -repos drop -SYS_PWD <sys password> -SYSMAN_PWD <sysman password>

To deconfigure Database Control for an Real Application Clusters (RAC) database, do the following command on database host:
(a) Set the environment variable <ORACLE_HOME> to the database home.(b) Run the following command:
<Database ORACLE HOME>/bin/emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD <sys password> -SYSMAN_PWD <sysman password>

NOTE: If the existing database is on different host, then install Oracle Management Agent on the database host, to monitor the database.

So in Command Prompt I enter this command:

set ORACLE_HOME=C:\app\annie\product\11.2.0\dbhome_3

C:\app\annie\product\11.2.0\dbhome_3\bin\emca -deconfig dbcontrol db -repos drop -cluster -SYS_PWD Pass123 -SYSMAN_PWD Pass123

Then it ask me for the details so I input the details.

Database Unique Name: Blitz

Service Name: Blitz

Listner port number: 1523

Listener Home_Oracle: C:\app\annie\product\11.2.0\dbhome_3

But I go this error:

And I'm now stack up with this. Please Help.


texts:
I'm searching for a database that can run on Windows and can be accessed with C#.
The Database also requires that the data can be encrypted (ideally without backdoors).

Can MariaDB serve here?


texts:
I hope this is the right stackexchange place to ask.

Im really new using databases and I have a little question: Where to save the user data? I mean, if Im logged as "Foxandxss" on my app, and I want the list of my... questions... How it knows the questions that are mine?

I ended with a "Owner" column in the table with the data. I filter the rows comparing Owner with the current logged user. For one table maybe is OK but if I have like 5, is quite overkilling.

So, how is the typical database that manage user data?

Thank you.


texts: Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs

texts:
I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs.
All the primary keys are type serial (auto incrementing integer) and all have the name in the format TablenameID eg so a table with the name "person" will have the serial primary key called "PersonID".  The only way I can find to do this is:

http://wiki.postgresql.org/wiki/Retrieve_primary_key_columns

SELECT
  pg_attribute.attname,
  format_type(pg_attribute.atttypid, pg_attribute.atttypmod)
FROM pg_index, pg_class, pg_attribute
WHERE
  pg_class.oid = 'person'::regclass AND
  indrelid = pg_class.oid AND
  pg_attribute.attrelid = pg_class.oid AND
  pg_attribute.attnum = any(pg_index.indkey)
  AND indisprimary

the disadvantage is the extra time this will add to the creation of the audit logs, from explain (on an empty table)

Nested Loop  (cost=0.01..54.92 rows=1 width=72)
  ->  Nested Loop  (cost=0.00..14.48 rows=1 width=31)
        ->  Seq Scan on pg_index  (cost=0.00..6.20 rows=1 width=31)
              Filter: (indisprimary AND (indrelid = 17606::oid))
        ->  Index Only Scan using pg_class_oid_index on pg_class  (cost=0.00..8.27 rows=1 width=4)
              Index Cond: (oid = 17606::oid)
  ->  Index Scan using pg_attribute_relid_attnum_index on pg_attribute  (cost=0.01..40.42 rows=2 width=78)
        Index Cond: ((attrelid = 17606::oid) AND (attnum = ANY ((pg_index.indkey)::smallint[])))
Time: 0.103s

Is there a better / faster way of doing this? The serial autonumber key name can be changed at this stage of the project.


texts: Compare a date and a timestamp with time zone with now() in the same query?

texts:
I have multiple database servers I'm querying with a query that compares an expiration column with now(). The problem is that one of the servers' expiration column is a timestamp with time zone, and all the rest are simply date. I can't change this because I don't have admin access, and in fact I'm only querying the view. Postgres is fairly new to me, so I don't really understand how the dates and times work with each other.

When I try and query the server with timestamp with time zone by casting the timestamp as a date:

...
WHERE
    (
        status_code = '30000'
        OR status_code = '30005'
    )
AND CAST(expiration AS DATE) > now()

It works, but using the same query on the servers where expiration is already a date fails:

  [Err] ERROR:  invalid input syntax for type date: "No End Date"

Any help would be appreciated, I'd really rather not hard code an exception for this one DB server.


texts: RMAN - Fail To Restore Oracle DB with CDB and PDB

texts:
I'm having some issues restoring an Oracle 12C database from one server into another one.
After trying this procedure:
1>Connect to the ORIGIN Database server
2> Create an rman script file:
CROSSCHECK ARCHIVELOG ALL;
CROSSCHECK BACKUP;
DELETE EXPIRED
ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
DELETE NOPROMPT EXPIRED BACKUP;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG TAG DAILY_BKP DELETE INPUT MAXSETSIZE=5G KEEP UNTIL TIME 'sysdate+3';
spool log to '<bkp_logfile>';
EXIT;

Run the command:
export ORACLE_SID=;rman target / @

After the backup finished, copy the files to the DESTINATION database server. Also copy the backup control files.

Connect to the DESTINATION database server

Create pfile:

     *.audit_file_dest='<Path1>\adump'
        *.audit_trail='db'
        *.compatible='12.2.0.0.0'
        *.control_files='<Path2>\control01.ctl','<Path2>\control02.ctl'
        *.db_block_size=8192
        *.db_domain=''
        *.db_name='CAODB01U'
        *.db_recovery_file_dest='<Path2>'
        *.db_recovery_file_dest_size=10737418240
        *.diagnostic_dest='<PAth Diagnostic>'
        *.dispatchers='(PROTOCOL=TCP) (SERVICE=CDBXDB)'
        *.enable_pluggable_database=true
        *.memory_target=2729m
        *.open_cursors=300
        *.processes=500
        *.remote_login_passwordfile='EXCLUSIVE'
        *.undo_tablespace='UNDOTBS1'

rman> target /

rman>startup nomount pfile='< pfile >'

rman>restore controlfile from '< Path of datafile >';

rman>Alter database mount;

rman>catalog start with '';

rman>restore database;

rman>recover database;

rman>alter database open resetlogs;

After the last step the message i get the message:
> RMAN-00571:
> =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS
> =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 05/20/2021 12:21:30
> ORA-01152: file 1 was not restored from a sufficiently old backup
> ORA-01110: data file 1: '<Path DB >\SYSTEM01.DBF'

As ive seen in all the procedures to restore it, that should be the way to do it , but it doesnt seems to be working. Does anyone have an idea of what im doing wrong?
Also, when i try with
restore database;
recover database;
it tells that there are some datafiles missing, but there shouldnt be missing because its supposed to be a full backup.


texts: Optimize Percona MySQL InnoDB 3.2TB database

texts:
(Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project)

I have a database server that is crazy big in size and is running on crazy small server, ie. 32GB RAM, 24 cores. During the weekend we are going to add 18GB of RAM to the machine to make it itsy bitsy more capable (server load during the day jumps up to average 80!!) until we will sort out performance issues with the application using this database.

I suspect that the load is caused by DB is swapping because of lack of free RAM (about 5GB always in SWAP).

This database has multiple small-ish tables and one huge table which is receiving GPS and IO data at rate around 350,000 records / h and this table is partitioned by a partition/month.

Mysqltuner suggests running optimize table on all tables but I have had read that doing so on InnoDB tables is useless and also it would take long hours.

Here is a snip from the script output:

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
  query_cache_limit (> 16M, or use smaller result sets)
  join_buffer_size (> 2.0M, or always use indexes with joins)
  tmp_table_size (> 16M)
  max_heap_table_size (> 16M)
  table_cache (> 4096)
  innodb_buffer_pool_size (>= 1726G)

Should I blindly trust it or is there another way to do this?

I know the first thing is to reconfigure the DB after the upgrade, what else should I be aware of and what should I try? Downtime should be as always as short as possible so I really don't want to run a query which would run for 5h straight.


texts: How to make sure that simultaneous INSERT and SELECT on one table won't make a deadlock?

texts:
I have the following table in my SQL Server 2008 database:

CREATE TABLE [dbo].[SomeTable]
(
   [Id] [bigint] IDENTITY(1,1) NOT NULL,
   [Column1] [varchar](30) NOT NULL,
   [Column2] [varchar](30) NOT NULL,
   [Column3]  [varchar](50) NOT NULL,
   CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED
   (
  [Id] ASC
   )
 )

One process inserts data into this table, another process reads all data from this table. Both processes work simultaneously.

one process:
INSERT INTO dbo.SomeTable VALUES ('col1', 'col2', 'col3')

another process:
SELECT Id, Column1, Column2, Column3 FROM dbo.SomeTable

I see that second query does a full table scan, but I need to get all data from this table for further processing(it won't be a very big table, because it will be cleaned periodically. it will contain a 1K-2K rows).

Are there any way to make sure that deadlocks will never happen for this two queries?


texts: HADR wait on servers without HA configured

texts:
I am investigating a slow SQL Server 2012 server in a virtualized (VMWare 6.5) environment, and have come across three different waits on the server that are... confusing. These are HADR_FILESTREAM_IOMGR_IOCOMPLETION, DIRTY_PAGE_POLL, and SQLTRACE_INCREMENTAL_FLUSH_SLEEP waits.

All three of these have essentially the same wait time (about 1000ms / sec). From research, I see that the DIRTY_PAGE_POLL and SQLTRACE_INCREMENTAL_FLUSH_SLEEP waits are (usually) nothing to be concerned about. However, documentation for the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait states that it is a wait generated by the system while waiting for HA replication to complete.

This particular Dev server has never had HA replication configured on the server (AFAIK / AFAICT), and does not now have replication or Always On High Availability of any type configured.

In this case -

Why would the HADR_FILESTREAM_IOMGR_IOCOMPLETION wait occur at all?
Would this wait possibly cause, or indicate a cause of, a slowdown in a data load situation (5x-10x longer data loads than expected)
Since all three waits are running the same duration, is there any significance to the other two waits in this specific case, and could they indicate a slowdown situation on the server?

This server appears to be configured identically to the live server (also virtualized) that we are attempting to duplicate, with the exception of the OS version (Nt6.3 Build 9600 on live, NT6.1 SP1 Build 7601 on Dev). None of these three waits show up significantly on the live server.

Any ideas?


texts: How to guarantee data consistency across multiple reads?

texts:
Let's imagine my application needs to do a series of consecutive SELECTs (to different tables) to collect various bits of information from the db, and we don't want any of those tables to change while we're collecting the data.
With postgresql one can use "repeatable read" or "serializable" isolation levels, however if another transaction commits changes to a table we haven't referenced yet we'll see them even if our transaction had started already, as the following sequence of actions shows:

T1: BEGIN ISOLATION LEVEL SERIALIZABLE;    # imagine here table t has 10 rows
T2: INSERT INTO t VALUES(1, 2, 3);
T1: SELECT COUNT(*) FROM t;                # we'll see 11 lines for the rest of the transaction

However, if T1 accessed t before T2 did the insert, it would see 10 lines for the duration of the whole transaction:

T1: BEGIN ISOLATION LEVEL SERIALIZABLE;    # imagine here table t has 10 rows
T1: SELECT COUNT(*) FROM t;                # we'll see 10 lines for the rest of the transaction
T2: INSERT INTO t VALUES(1, 2, 3);
T1: SELECT COUNT(*) FROM t;                # still sees 10 lines etc.

With the above behavior, if we need to access multiple tables during the transaction, many of them may change in the time interval between the beginning of the transaction and the moment we access them, and we'll see those changes which we don't want to see.
I understand that's the way isolation levels are supposed to work, so no explanation is needed here.

But then, is there a way to have some kind of "snapshot" starting at a given point in time? Are explicit locks needed in this case?


texts:
First question on this SA but I feel my problem is more Database related than code related.

I have multiple Delphi Datasnap servers connecting to SQL Server 2012.
The SQL server has multiple databases (500+) which the different Database servers connect to. The SQL Server and the Application servers run on different VM's. Different users connect to these application servers and open connections to the databases.

Since today we are getting different SQL errors on the servers, but no errors are seen on the SQL Server. These errors happen only sporadically for different users on the server in different databases. We are almost unable to reporoduce as this seems like a small % of the calls to the SQL server.

Example of errors we get are :

[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]TCP Provider: An existing connection was forcibly closed by the remote host.
[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]SMux Provider: Physical connection is not usable [xFFFFFFFF].
[FireDAC][Phys][ODBC][Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection.
[FireDAC][Phys][ODBC][sqlncli11.dll] SQL_ERROR

Googling these errors gives some results on Chimney settings but most of the results are older than 2012 and most are for SQL Server 2008.

Are there any suggestions on possible causes? Any other info you need?


texts:
I'm struggling with indexes, primary keys and foreign keys... And the need of having them all.

If I have two tables, both of them have an integer as a primary key.
The first table references through a FK to the second table's primary key.

On both tables I have a primary key index on the ID column
I created a FK constraint on the table1.ref_field referencing the PK of the second table (table2.id)
and I added an index on table1.ref_field

Is this the best way to organize these indexes, primary and foreign keys?


texts: Why is the speed of executing statements dependent on the network connection?

texts:
It looks like the speed of executing T-SQL is dependent on the latency of the network connection against the server. I assumed that if SQL Server has nothing to report back to the client about, it will just execute away until it is done but testing shows another story.

create procedure UselessLoop
  @I int
as
declare @D datetime = getdate()
while @I > 0 set @I -= 1
print datediff(millisecond, @D, getdate())

exec UselessLoop 100000

Server    Milliseconds
local     53
nearby    63
faraway   660

exec UselessLoop 1000000

Server    Milliseconds
local     546
nearby    640
faraway   6183

The tests are executed against the same server from different computers using SSMS. Local is executed from the Server, nearby is on the same local network and faraway is executed from another office 500 km away connected with 1 gigabit fiber.

There is obviously some communication going on between SQL Server and the client that directly is dependent on the number of statements executed.

I used Wireshark to look at what is transported and I can't say that I understand that much but it was a tcp.stream exchanging a total of 26 MB in 22740 packets.

How about a useless function instead?

create function dbo.UDFUselessLoop(@I int)
returns int
as
begin
  declare @D datetime = getdate()
  while @I > 0 set @I -= 1
  return datediff(millisecond, @D, getdate())
end

print dbo.UDFUselessLoop(1000000)

It executes in 406 milliseconds regardless of where it is executed from. Looks like there is no communication with the client in the loop.


texts: An SQL query to derive data from one to many rows based on another field

texts:
I have an issue I can't get my head around. This is the table:

Table1

c1      c2      c3
1       A
2       A       DD
3       A       DD
4       B       HH
5       B
6       B

This is the desired outcome:

c1      c2      c3
1       A       DD
2       A       DD
3       A       DD
4       B       HH
5       B       HH
6       B       HH

I'm running PostgreSQL 9.4. Basically I want to use the non-null values in c3 to fill c3 based on the common data the rows have in c2.

Thanks for the help!


texts:
I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular.

I want a database query to change the status of a standby - JUST ONE - and return the server IP that is to be used. The pick can be arbitrary: since the status of the server changes with the query, it doesn't matter which standby is selected.

Is it possible to limit my query to just one update?

Here is what I have so far:

UPDATE server_info SET status = 'active'
WHERE status = 'standby' [[LIMIT 1???]]
RETURNING server_ip;

Postgres doesn't like this. What could I do differently?


texts: cloning foreign keys after prod-dev wipe

texts:
I am currently using mariadb, and I am working with quite a bit of data.  we are creating nightly wiped clones of our production database, so our devs have a fresh copy without any customer data.
I am using a
create table X like Y;
drop table Y;
rename table X to Y;
None of our devs were using the new databases until just recently, and a migration script said it had to created a metric crap ton of foreign keys.
I was looking to see if anyone had any ideas how to run a script to generate a list of all the foreign keys to before, and i can run the script after words? there are over 298 foreign keys.
any suggestions would be appreciated
::UPDATE::
select DISTINCT CONCAT ('ALTER TABLE `', kcu.TABLE_NAME, '` ADD CONSTRAINT `', kcu.CONSTRAINT_NAME, '` FOREIGN KEY (`', kcu.COLUMN_NAME, '`) REFERENCES `', kcu.REFERENCED_TABLE_NAME, '` (`', kcu.REFERENCED_COLUMN_NAME, '`) ON DELETE ', rc.DELETE_RULE, ' ON UPDATE ', rc.UPDATE_RULE, ';') FROM information_schema.KEY_COLUMN_USAGE AS kcu INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS AS rc ON kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME AND kcu.CONSTRAINT_SCHEMA = rc.CONSTRAINT_SCHEMA;

using this I was able to generate a list of all the Alter Table statements, now i am trying to have the script output into a .sql file.


texts: mysql_upgrade check if it's neccessary to run it

texts:
How can I determine, do I need to run 'mysql_upgrade` command on database?

Percona-server-5.6.41 is installed and running now for a several monthes. Before it mysql-5.6 was installed. In-place upgrade was done (removed mysql and installed percona). But I don't know, was mysql_upgrade executed or not.

There're no errors about table mismatches in logs.

DB is large enough, 900+ tables and about 150Gb of size.


texts:
I have a query that takes around 1 second on first execution and 15-50 ms after running the same query again. The only difference between EXPLAIN outputs is that the second execution contains a lot of Buffers: shared hit which are probably (and correct me if I'm wrong) responsible for the noticeable speed up.

So I was wondering, when people talk about queries taking x milliseconds, are they usually referring to the time it takes on first execution?

Do you care about a slow query that after caching is actually quite fast? This assumes that your application requires reasonable response times (e.g. e-commerce, real-time analytics, etc).

I'm using PostgreSQL 9.5, but I intended to ask something general enough to be widely useful.


texts: Is possible to use ORDER BY with IF ELSE condition?

texts:
Here I have some data. I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the remaining records in descending order(date). I have written a query, but it is not working as expected:

(SELECT *
FROM `test`
WHERE fav = 1
ORDER BY date DESC)

UNION

(SELECT *
FROM `test`
WHERE fav = 0
    AND date>DATE(NOW())
ORDER BY date ASC)

UNION

(SELECT *
FROM `test`
WHERE fav = 0
    AND date>=DATE(NOW())
ORDER BY date DESC)

Please share the query if you know how to do it. Thank you.


texts:
I'm creating a project that could get a lot of users very quickly and I wanted to know what is the suggested approach I should consider from the start.

I'm developing it with a PHP framework and MYSQL both optimized as far as my knowledge allows :P I basically want to start small...I mean running on a simple VPS and scale to better hardware as needed but in the end I was thinking that one database's table could be become a real problem.
Basically there will be a table that stores activities every user could do 100 times per day (I'm not going into details) so considering that in the future I would have, say, 10k users doing that every day I would get to a point of millions of new data per day (even if it just stores 2-3 numbers). And this table even if it's not queried to retrieve data at every visit but only only to INSERT I would need to read it and manipulate data...and what happens with 1 trillion rows? I'm not an expert but I'm pretty sure that having those numbers in a table cannot be good especially if it grows everyday non-stop.

SO my questions are:

is there another database method I should start with? Something in front of MYSQL made for large tables? If any!
how do big web sites handle these things? They store "part" of the rows on multiple servers?
is it OK to use the simple BIGINT (primary key) for this table considering that is not unlimited? I read that using varchars as primary key slows down the database
significantly.

could services like AWS do this for me transparently?

Thank you.


texts: SQL Server 2008 database is not coming online after restore.. Pleae need help asap

texts:
I have restored a 1TB database and now it shows the restore completed 100% but the database is not accessible.

sp_who2 has a SPID 50 performing this restore but current command its showing is 'CREATE INDEX' and this SPID is not completing from quite long time.

When I verified through sp_whoisactive this spid is trying to create index on a table of 12gb size but the % complete shows as 100%...

Now for 2 hours the DB has not been accessible and this spid is not finishing. Main thing to note is this restore happens daily and used to work fine from ages.

Any help ides would be much appreciated. I already tried taking SQL Server services offline and removed the underlying db files and start fresh restore but still it got stuck in same way....


texts: Running multiple instances on the same host

texts:
I need to set up multiple MySQL instances on the same host for one of our customers. As far as I see I could go with:

mysqld_multi
MySQL Sandbox

This should be a long term solution for an enterprise class production environment.

We need to migrate an already existing environment which currently uses the RedHat Cluster with active/passive nodes to a new VM without the clustering software (VMware HA solutions will be used, it will be different, I know).

There are 3 separate MySQL instance/cluster resources. I don't know the original motivation for this separation, but I suppose that the customer wants to be able to stop/start (patch, upgrade ...) the different resources autonomously.

Any comments and suggestions - pros, cons and real world experience are very welcome!


texts: Data Model: Parent, Child, Grandchild with Child Being Optional

texts:
I posted the question in another forum and I was suggested to re-post it here which may be more appropriate. Thank you in advance.

My organization structure:
   Department  -- has many sections ( and employees)
      Section   -- has many employees
         Employee  -- most of the employees belong to Section directly.

However, there are two departments which have no medium Sections and employees of these Sections report to Department Director directly.

I do not know what can be done to better reflect the business structure using an Entity Relationship model.

I learned of a similar posting which is still different than what I need.
  https://stackoverflow.com/questions/2530215/model-a-zero-or-one-to-many-relationship?rq=1

John


texts: Exporting MySQL Database into single SQL File in TOAD

texts: Using TOAD, is it possible to export an entire MySQL database to a single file?


texts: How do I enable full text search on MySql 8?

texts:
I'm using MySql 8.  I would like to speed up searches on my table of the form
select * FROM dirctory_coop where name like '%mystr%';

and someone mentioned using Full text searches -- https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html.  However, I'm having an issue with this.  Below is my table ...
mysql> desc directory_coop;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int          | NO   | PRI | NULL    | auto_increment |
| name     | varchar(250) | NO   |     | NULL    |                |
| enabled  | tinyint(1)   | NO   |     | NULL    |                |
| phone_id | int          | YES  | MUL | NULL    |                |
| email_id | int          | YES  | MUL | NULL    |                |
| web_site | longtext     | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+

I tried the below statements to update my column definition but am getting the following errors ...
mysql> alter table directory_coop modify column name FULLTEXT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT' at line 1
mysql> alter table directory_coop modify name FULLTEXT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FULLTEXT' at line 1

What else do I need to do to enable the performance optimizations that full text search supposedly provides?


texts: MySQL - How to group data by Day and Hour

texts:
I have some problem in getting the query for this sample format. My idea is select hours on that day and group it by day or select all the 7 days and group it by hours?

here is the format:


texts: In a two-server dataguard setup, can I store the recovery catalog in the same database?

texts:
We currently have an Oracle 11gR2 database that is backed up with rman, with the rman records stored in the control files. Control file auto-backup is turned on. Backups are to disk (and, ultimately, via Bacula, to more disks and to tape, but rman doesn't know this).

I'm planning on adding a physical standby database using Data Guard Redo Apply. The Data Guard and and RMAN documents all tell me that I must use a recovery catalog now. I don't actually, so far as I can tell need any of the features of a recovery catalog. The control file was fine. I don't need to offload backup from the primary database; it has more than enough capacity to do them itself (and the workload basically guarantees that will always be the case).

So, I would just put it in the same database (the one I'm backing up with rman), but those same documents tell me I mustn't, but they only reason I've found so far is:

  To illustrate why data separation is advised, assume that you store the catalog for database prod1 in prod1. If prod1 suffers a total media failure, and if the recovery catalog for prod1 is also stored in prod1, then if you lose the database you also lose the recovery catalog.

But this isn't true. The recovery catalog would still exist on the standby. And even if both the primary and standby broke simultaneously (say, due to a madman at a sqlplus prompt, but that'd break a recovery catalog in a separate database, too), they say:

  At this point the only option is to restore an autobackup of the control file for prod1 and use it to restore and recover the database without the benefit of any information stored in the recovery catalog. ("Protecting the Recovery Catalog" from Oracle Database Backup and Recovery User’s Guide 11g Release 2 (11.2), p. 13-15.)

The "only option" of using a control file backup doesn't sound bad at all.

Am I missing something? Are there some non-obvious downsides to putting the recovery catalog in the same database, or is this really only a concern in a much larger operation?

(would tag this recovery-catalog and data-guard as well, but seems I can't with less than 300 rep here)


texts: Can't find source code for building mysql

texts:
I'm trying to follow the instructions for building MySQL from source. They guide me to a link where I can download the latest GA. I end up at the downloads page, where I can download mysql-5.6.19-osx10.7-x86_64.tar.gz for my mac, and then untar and unzip.

All good so far. But when I try to cmake . per the instructions, I get an error saying that it can't find CMakeLists.txt. Poking around looking for that, I've discovered that the download doesn't come with any actual source code except .h files. In fact, the download looks like an already-built setup with binaries such as mysqld already there.

I am probably missing something obvious, but I can't figure out where I'm supposed to get the full source code and the makefile, if not from the links provided under the title, "Installing MySQL Using a Standard Source Distribution". Anyone have an idea?


texts: What is a good size to accommodate a single-use database for a small application?

texts: I'm looking to provision out arbitrarily many sandboxed MySQL databases for anyone to use, similar to how SQLFiddle does, but permanent and with API keys (one database per key), which would allow small developers to set up single-use databases for small applications. What is a sensible upper limit for the drive space I should allow these to take up?


texts: How to sync a database with an external full text engine?

texts:
How do you keep your external full-text engine (Lucene, Solr, etc) in sync with your database data? For example, keeping track of inserts, deletes, updates.

My current solution involves a big union all query on modified records, that is polled by Lucene every few minutes.

Any better solutions?

Thanks!


texts: SQL Server database on an SSD - any advantage to a separate file for every table?

texts:
I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key column in order to maximise query efficiency in the face of heavy updates and insertions and make heavy use of clustered indexes. Two of the tables will contain variable-length textual data, with one of them containing hundreds of millions of rows but the rest will contain only numeric data.

As I really want to squeeze every last drop of performance out of the hardware I have available (about 64GB of RAM, a very fast SSD and 16 cores), I was thinking of allowing each table to have its own file so that no matter if I'm joining on 2, 3, 4, 5 or more tables, each table will always be read using a separate thread and the structure of each file will be closely aligned with the table contents, which would hopefully minimise fragmentation and make it faster for SQL Server to add to the contents of any given table.

One caveat, I'm stuck on SQL Server 2008 R2 Web Edition. Which means I can't use automatic horizontal partitioning, which rules that out as a performance enhancement.

Will using one file per table actually maximise performance, or am I overlooking built-in SQL Server engine characteristics that would make doing so redundant?

Second, if using one file per table is advantageous, why does create table only give me the option to allocate the table to a file group and not to a specific logical file? This would require me to create a separate file group for every file in my scenario, which suggests to me that perhaps SQL Server isn't envisioning the advantages I am assuming would come from doing what I'm proposing.


texts: SQLServer - how to move tables between instances?

texts:
I'm not very familiar with SQLServer and, thus, I inadvertently created a database in a fashion I thought it would work.

In SQL Management Studio, I right clicked the instance and created a xpto database, so all my tables are xpto.tableName instead of dbo.tableName.

Should I expect performance issues?

Also, is there a somewhat safe way to move tables from xpto to dbo?


texts: How dangerous is granting the ALTER TABLE permission?

texts:
Imagine the following scenario

CREATE DATABASE test

GO

USE test;

CREATE TABLE dbo.Customer
  (
     CustomerId   INT,
     Email        VARCHAR(100),
     SensitiveData VARCHAR(20)
  );

INSERT INTO dbo.Customer
VALUES     (1,'abc@foo.com','12346789');

At some point an ETL process is written that performs some activities in the test database.

CREATE USER etlUser WITHOUT LOGIN; /*For demo purposes*/

CREATE TABLE dbo.StagingTable
  (
     StagingTableId INT,
     SomeData       VARCHAR(100),
  )

GRANT UPDATE,INSERT,DELETE,SELECT,ALTER ON dbo.StagingTable TO etlUser;

DENY SELECT ON dbo.Customer TO etlUser;
DENY SELECT ON dbo.Customer (SensitiveData) TO etlUser; /*For good measure*/

The etlUser should not have permissions to the Customer table (and certainly not to the SensitiveData column) so these are explicitly denied above.

The ETL process truncates dbo.StagingTable so is given ALTER table permissions on that.

This is flagged during a security audit. How dangerous is this scenario?


texts: Mariadb default logs location & --log-error

texts:
I am using mariadb and I to investigate some issue I wanted to check the logs. To my surprise, log file is not generated for mariadb.

I suspect this cannot be the case so I am doubting my search skills.

MariaDB [(none)]> show variables like 'log_error'
    -> ;
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_error     |       |
+---------------+-------+
1 row in set (0.00 sec)

I have added the entry in my.cnf still above field is coming to be empty.

[root@cslcodev11-oem ~]# cat /etc/my.cnf
[mysqld]
!includedir /etc/mysqld/conf.d
datadir=/mnt/mgmt/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
tmpdir=/mnt/mgmt/var/lib/mysql_tmp
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
### TRT-3229 #####
sync_binlog=1
innodb_flush_method=O_DIRECT
innodb_support_xa = 1
myisam_repair_threads = 2
myisam_recover_options = FORCE
###################
innodb_file_per_table=1
innodb_log_buffer_size = 8M
table_open_cache=256
max_heap_table_size=256M
### TRT-4685 ###
max_connections=500
################
innodb_log_file_size = 512M

[mysqld_safe]
log-error=/var/log/mariadb/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@cslcodev11-oem ~]#

So, I want to know do we have any default location where these logs are getting generated, if the path cannot be read from config files.

Update:

After some investigation, I found this information,

Reference: https://mariadb.com/kb/en/mariadb/error-log/

Activating the Error Log

The error log is active by default. The log-error=filename option determines where the output will be written. If no file name is specified, the log will be written to host-name.err. If no absolute path is specified, the file will be written to the data directory (determined by the value of the datadir system variable).

On Unix systems, if the --log-error option is not used, the errors are written to stderr (usually, the command line).

On Windows, if the --console option is specified, and --log-error is not used, the errors are written to the console. If --log-error is present, --console is ignored.

So now I understand that due to some reason the configuration log_error in my.cnf is not considered.  So as per the suggestion, on Unix system, if --log-error is not used, logs are redirected to stdout.

BUT, I am starting the service using service mysqld start command so I want to know what all options are used by default when we start the service using service mysql start? ESP, is --log-error used?


texts: How to get a column A which has the smaller column B grouping by column C?

texts:
I'd like to get the ID of a dataset for each category (my grouping column) which has the smallest "order" column.
Here a set of data to explain my thoughts:

CREATE TABLE DATAS (
    ID INT(2) ,
    CATEGORY INT(2) ,
    ORD INT(1)
);

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 2);

Result expected:

ID   CATEGORY
-------------
3    1
4    2

Bonus question, how to retrieve just one ID even if there's some duplicates for the "order" column (ORD) ?

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 1), (6, 3, NULL), (7, 3, NULL);

Result expected (using a MIN for example or other suggestion):

ID   CATEGORY
-------------
3    1
4    2
6    3

Oracle or MySQL queries are welcomed, thanks a lot.


texts: Is it a good idea from a performance perspective to store audio files in a mysql database?

texts:
I have an application where I have audio files. I need to store them in a mysql table since we already have a mysql database available.

I saw that some people don't advise to do so.
What are the advantages and inconvenients of storing the audio files in the table?

Does it affect the operations performed on the database?


texts: query discrepancies on very large postgres table

texts:
I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.

these two queries although similar are taking massively different amounts of time and I am not sure why.

SELECT MAX(logdatetime) FROM tlog WHERE pointid = 'and.andrew' (5100ms)
SELECT MAX(logdatetime) FROM tlog WHERE pointid = '359710040139645' (41ms)

logdatetime is indexed, and I ran a VACUUM and REINDEX on the table when I noticed the discrepancy, but it hasn't resolved the issue.

Here is the EXPLAIN ANALYZE output for each of the queries:

--- and.andrew
Result  (cost=35.20..35.21 rows=1 width=0) (actual time=1924.811..1924.811 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..35.20 rows=1 width=8) (actual time=1924.804..1924.804 rows=1 loops=1)
          ->  Index Scan Backward using logdatetime on tlog  (cost=0.00..2434879.14 rows=69168 width=8) (actual time=1924.800..1924.800 rows=1 loops=1)
                Index Cond: (logdatetime IS NOT NULL)
                Filter: ((pointid)::text = 'and.andrew'::text)
Total runtime: 1924.853 ms

--- 359710040139645
Result  (cost=1.44..1.45 rows=1 width=0) (actual time=32.525..32.525 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..1.44 rows=1 width=8) (actual time=32.518..32.518 rows=1 loops=1)
          ->  Index Scan Backward using logdatetime on tlog  (cost=0.00..2434879.14 rows=1688018 width=8) (actual time=32.513..32.513 rows=1 loops=1)
                Index Cond: (logdatetime IS NOT NULL)
                Filter: ((pointid)::text = '359710040139645'::text)
Total runtime: 32.568 ms

The column pointid is defined as character varying (50), in case that matters.


texts: Get data from database and put it back to a new datasheet

texts:
I want to get the data from the database and put it back with the new format

I have a lot of datasheet like follow

ID a b c  d
1  1 5 9  13
2  2 6 10 14
3  3 7 11 15
4  4 8 12 16

and I want to put it in a new datasheet like follow

ID a b c
1  1 5 9
2  1 5 9
3  1 5 9

and the second sheet's data's column C can be the column D like

ID a b c d
1 1 5 9 10
2 1 5 9 10
3 1 5 9 10

how to make the SQL?


texts: sys.dm_sql_referenced_entities fails to provide used columns list when joining with temp table

texts:
I think I need some help as I run out of ideas.

In my database I have a simple table like this:

CREATE TABLE dbo.Invoices
(
        CashDiscounts MONEY,
        Cost MONEY
)
GO

and a stored procedure that uses this table in SELECT context:

CREATE PROCEDURE Reporting.pLoadTest
AS
BEGIN
        CREATE TABLE #t ( Test INT );

        SELECT
                  Invoice.[CashDiscounts]       AS [CashDiscounts]
                , Invoice.[Cost]                        AS [Cost]
        FROM dbo.Invoices AS Invoice
        LEFT OUTER JOIN #t -- if you remove this join, dm_sql_referenced_entities should return also CashDiscounts and Cost attributes
        ON 1=1

END
GO

Now, the problem I got is following: I am using system function sys.dm_sql_referenced_entities to get list of columns used by Reporting.pLoadTest.

SELECT *
FROM sys.dm_sql_referenced_entities ('Reporting.pLoadTest', 'OBJECT')

I expect the results of this function to be:

dbo.Invoices
dbo.Invoices.CashDiscounts
dbo.Invoices.Cost

However, when I do a join to temp #t table function sys.dm_sql_referenced_entities returns only

dbo.Invoices

Everything in this case works fine if I use table variable instead of temp table. I tried to fiddle with permissions for tempdb as written in documentation

  Requires SELECT permission on sys.dm_sql_referenced_entities and VIEW
  DEFINITION permission on the referencing entity.

but with no positive result.

What should I do to get correct results of columns being used by a stored procedure that do a select and a join to temp table?


texts: How to merge multiple rows into one row with SQL?

texts:
Originally, I have one table, storing UserID and the corresponding value based on Year, Month, as shown below:

Now, I want to 'merge' the rows according to the Year and Month value and to store them into another table, as shown below:

Note that, M1 means Mth = 1 (January), M2 means Mth = 2 (February), and so on. (The images are meant for description, you may ignore the accuracy of the figure.)

Question, how to do this in SQL Server?


texts: MySQL: parallel restores at same time from backup files?

texts:
Slightly related to this question, but for MySQL: Would it be possible/wise to execute 2 parallel restores from a single backup?

Is it possible/wise to do a parallel restore of two independent databases from two separate MySQL dump files?

Restoring one of them seems to only take up about 10% of CPU cycles consistently and takes a long time to finish, so I'm thinking that a second restore shouldn't cause any load problems - but might it cause any other MySQL/DB issues?


texts:
I'm trying to clean up a change-set query that returns rows from a table that have changed since a given time and also do not exist in a known list of already previously returned results.

select ID, Field1, Field2
from Table1
where Field = 'Foo'
and (
    ID not in ( %s )
    or (
       ID in ( %s ) and
       GREATEST(created, COALESCE(modified, from_unixtime(0))) > from_unixtime(:stamp)
    )
)

Now using some basic 9th grade logic operators....

A or (B and C) = (A or B) and (A or C)

Except in my instance, A = "id not in (%s)" and B = !A. C = the date constraint. Which reduces to...

(A or !A) and (A or C) => A or C

So, following that logic, my where clause should now look like this, but it seems funky. Can anybody more experienced with SQL and/or logic tell me if I've missed something simple?

select ID, Field1, Field2
from Table1
where Field1 = 'Foo'
and (
    id not in ( %s )
    or GREATEST(created, coalesce(modified, from_unixtime(0))) > from_unixtime(:stamp)
)

Does that look right?


texts: Is an automatic failover to the primary replica of a readonly replica in an availability group possible?

texts:
I want to set up an availability group with two nodes. One node will be the primary replica and the second one will be set up as a read-only replica.

In my application I do all active stuff with a read/write-connection and the reporting stuff with a read-only connection. The read-only connection will connect to the read-only replica.

But what happens if the node with the read-only replica goes offline? As far as I understand the documentation, a read-only connection can't be established any more if there is no read-only replica available in the group.

Is there a mechanism I can use to fallback with the read-only connection to the primary replica? Or do I have to build such a fallback manually in my application?


texts: For every insert statement I see sp_executesql in SQL Server Profiler

texts:
Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows.

I saw for every insert I called, a call was made to sp_executesql with the text of the insert statement.

This seems to be a big hit, is there a way to turn this off?

Environment:

SQL Server 2008 R2 Standard
Windows Server 2008 R2 Enterprise
32 GB RAM
2x4 Xeon


texts:
On our automated test system we use flashback all the time to reset our Oracle 11g database to a state before a test run. We noticed some of the scripts involved, started to become slower and slower. In particular the following RMAN statement takes a very long time:

delete archivelog all

Apparently, at that time the following SQL is executing:

SELECT DBINC.RESETLOGS_CHANGE#
FROM
  (SELECT RESETLOGS_CHANGE#,
    PRIOR RESETLOGS_CHANGE# NEXT_RESETLOGS_CHANGE#
  FROM V$DATABASE_INCARNATION
    START WITH RESETLOGS_CHANGE#        = :B2
  AND RESETLOGS_TIME                    = :B1
    CONNECT BY PRIOR PRIOR_INCARNATION# = INCARNATION#
  ) DBINC
WHERE DBINC.RESETLOGS_CHANGE#   <= :B3
AND DBINC.NEXT_RESETLOGS_CHANGE# > :B3

Table V$DATABASE_INCARNATION on our system already contains 72K entries. Probably because we open the database with the Resetlogs option a lot in our scripts.

What we already tried in RMAN but didn't seem to help much:

sql "alter session set optimizer_mode=RULE";

Any sugestions on how we can make this faster?

Oracle Database version 11.2.0.1.0 on a Windows 2008R2


texts: want to find out which databases are used in last 30 days or not

texts: I have about 120 databases in my SQL. I want to migrate so I want to know which databases are not used in last 30 days.


texts: PostgreSQL pattern to split a shared-database into a database-per-service pattern

texts:
What would be your recommendation to move from a shared-database pattern, to a database-per-service pattern. In a production database with as little downtime as possible?

This would be only considering the database splitting part, the application logic split would be a different concern.


texts: How do you store tabular data in an RDBMS?

texts:
First: I really haven't got any idea how to google for this. If you got one, leave a comment.

If I want to store arbitrarily large tables in a database, how should I set up my database tables?

Requirements:

A table has an arbitrary number of columns and rows (columns about 1-10k, rows about 0-10M)
There are thousands of tables
A table has a name
The columns of a table have a name
Column-Cell values can be either numbers or strings
A column always has the same datatype

Data I want to store:

"Bob the Table"

| "Name"   | "Size" | "Comment" |
---------------------------------
|  "Homer" |  170   | "A guy"   |
|  "Lisa"  |  120   | "A girl"  |
|  "Bart"  |  130   | "A boy"   |

You get the idea. Table could be much larger.

Data I want to query:

Most of the time I want to query a single table, that is, given a table's name, just display "the table" as depicted above.

How would you implement the RDBMS tables and how would you write queries to select the data for a single table?

Disclaimer: This isn't homework. This here is purely out of curiosity while working on another problem.


texts: MySQL: create ER diagram, but in text only

texts:
With MySQL Workbench it is possible to create a graphical ER diagram; however, this can become very messy when there are many tables and relations, so it might be good to have something like one or more text trees, like this, where TABLE1 has a foreign key to TABLE2A, TABLE2B and TABLE2C, and TABLE2B has a foreign key to TABLE3A and TABLE3B:
TABLE1 -+- TABLE2A
        +- TABLE2B -+- TABLE3A
        |           +- TABLE3B
        +- TABLE2C

and so on. Is there a tool that can produce that?
Edit
Having thought a bit, I realise that I can create a list like this:
mysql> select distinct table_name,referenced_table_name
    -> from key_column_usage
    -> where table_schema='demoplay'
    -> and referenced_table_name is not null
    -> order by table_name;

I can probably create a greater_than function based on this in python and then sort things, and perhaps from there print it out nicely.


texts: An SQL update query works fine unless it is invoked by an SSRS subscription. Why?

texts:
A stored procedure that updates data in production data tables was written by a very talented staff member (with developer-only rights) and works fine when executed in SSMS.  However, the same SP does not run when it is invoked by an SSRS subscription.  The subscription was created by and is owned by a manager with System Administrator rights.  If this problem is caused by SSRS not recognizing the proper credentials (whatever they may be), how can we change the subscription so that it works?  If it's not caused by incorrect authentication or authorization, what might the cause be?

Thank you for your responses; they are very much appreciated.
The "very talented" reference is a (not-so-obvious) joke and self-reference. SQL SMSS is version 2014, SQL DB Server is 2005, and the SSRS server is...2003.
We use the subscription to perform a weekly update to customer records, changing/increasing their credit limit and terms if they are in good standing.
The SP and report works fine when invoked directly by either the author or the manager (administrator).  But the subscription, created by the manager, does not perform any update in the report's SP.

So the question might be, "will a subscription created by the developer work?"
Or, "does the authentication to the report's data-source need to be changed?"
Or, "how can the SP be changed to permit its invocation from the SSRS subscription?"
Or, "if the subscription is using the credentials of the SSRS service, how do we change it?"


texts: Access query with "last" and display the rest too

texts:
I have an Access DB about measuring tools.

I have 2 tables: tblTools, which countains the details of the device, the tblCalibration contains when each device was calibrated.

tblTools: tool_ID; tool_Name
tblCalibration: calibration_ID; tool_ID ;calibration_Date

I wrote a query to get the last calibration date, but it only shows the devices which have a calibration date.  I need the results to show all devices, not just those that have been calibrated.

Is there any solution for a query that will list all of the devices plus the last calibration date if one exists?


texts:
I've run a migration query within a transaction, it went like this:

alter table big_and_loaded_table
    add column col1 bool;

update big_and_loaded_table set col1 = false;

It seems quite innocent, but it took about two minutes to execute. Moreover, it "locked" big_and_loaded_table: any query in my application that involved it -- both read and write -- took oh so long to execute, the same minute or so. I don't use transactions in my application at all, as well as any kind of explicit locks.

So I have two questions:

Why did that migration query take so long to execute? Was it because of a transaction? Or was it because of the query itself?
Why did it lock application queries that involved table big_and_loaded_table?
How should I run such kinds of migrations in the future? Probably this point won't make any sense when I'll figure out the first two though.

Postgres version is 11.6.


texts: Grant permission to KILL connections (sessions) for certain databases only?

texts:
For a SQL Server on a VM/physical box (not Azure SQL database!)

Is this possible to grant KILL permission, but in a way that grantee can kill connections only for certain databases ?

I would not want to grant this user ability to kill any connection, but just 1 database

Thanks!!


texts:
I am getting deadlock from 2 select statements. What is the reason for that? And how can I fix this issue?

Deadlocked processes:   11278
Victim process: 5185
Lock type:  Index/(Key)

Both queries are the same

  object: unicas_ux.dbo.Course, lock mode: Exclusive (X), index: NCIDX_Course_TermId

  (@P0 bigint)select courses0_.TermId as TermId17_130_1_, courses0_.Id
  as Id1_45_1_, courses0_.Id as Id1_45_0_, courses0_.ConvertedGrade as
  Converte2_45_0_......courses0_.VerifiedCredits as Verifie14_45_0_,
  courses0_.VerifiedDate as Verifie15_45_0_, courses0_.VerifiedGrade as
  Verifie16_45_0_ from dbo.Course courses0_ where courses0_.TermId=@P0
  order by courses0_.CreatedDate asc


texts: Why does PostgreSQL allow NULLs in domains that prohibit NULL?

texts:
PostgreSQL allows NULLs in domains marked as NOT NULL. Why is this, the docs say this about it,

It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join.

Can this be better explained?


texts: Foreign Key NULL vs Foreign Key to an empty string

texts:
This is a table for a catalog of medicines. Some have a pharmaceutical brand, others are generic (i.e. they will never have brand information)

CREATE TABLE medicine (
   id serial PRIMARY KEY,
   name text NOT NULL,
   brand_id integer
   CONSTRAINT brand_fk FOREIGN KEY (brand_id) REFERENCES brand (id)
);

CREATE TABLE brand (
  id serial PRIMARY KEY,
  name text NOT NULL
);

For storing generic medicines, e.g. abc & xyz, there are 2 options:

Use NULL for the Foreign Key brand_id

INSERT INTO medicine (name, brand_id) VALUES ('abc', NULL)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', NULL)
Insert just 1 empty string in brand name and use that for all generic medicine's brand_id

INSERT INTO brand (id, name) VALUES (1, '')
INSERT INTO medicine (name, brand_id) VALUES ('abc', 1)
INSERT INTO medicine (name, brand_id) VALUES ('xyz', 1)

From what I've read on StackExchange, it seems like 1 is the general way of doing this. However if I want a unique index on medicine name & brand_id, I'll have to use partial indexes (i.e. 2 indexes respectively where brand_id IS NULL & brand_id is NOT NULL).

If I go with the second approach, I can achieve it with a single index.

Is there any other advantage / disadvantage of either of these approaches. I feel the second method is unconventional and there might be some trouble that I'll discover later along the way.

P.S. I've used the example of medicines to illustrate my query but I want to understand the technical merits of the solution purely from a database standpoint i.e. without going into a discussion of which approach would be better for medicine example.


texts: What happens to the transactional replication if azure sql Vm(publisher and distributor) is stopped and restarted from portal?

texts: I am a newbie...I have azure sql vm and sql managed instance. I have setup transactional replication between them. Now if I stop the vm from the portal will the replication still continue after restarting the sql server vm? or will I have to reset the transactional replication?


texts: I need to insert data from a column from another table with different data type and the value is based on a lookup table

texts:
I need to import data taken from table_1.column_1 (BOND.MOODYS_RATING) to table_2.column_2 (LCDOffering.KeyValidCreditRatingsMoody)

WHERE:

MOODYS_RATING data type is nvarchar(10) and KeyValidCreditRatingsMoody data type is int(4)
Each value in the column MOODYS_RATING represent a lookup value, being KeyValidCreditRatingsMoody the key for that lookup value
KeyValidCreditRatingsMoody values are equal to KeyValidCreditRatings, taken from  the table ValidCreditRatings, while MOODYS_RATING values are equal to the field CreditRating, also from the table ValidCreditRatings, subject to certain restrictions, which can be summarized in this query:

select * from Lookup..ValidCreditRatings
where KeyInstnRatingAgency = 108462
and KeyValidCreditRatings <=21 order by keyvalidcreditratings

Can you please help me build an insert statement that can fulfill all of these requirements?


texts: How to pass, specific day month and year in the below query?

texts:
I have registration according to the data in a table and I want to show the whole month, I found the easy solution with below query but I do not know how can I pass specific day, month and year in below query?

SELECT AAA.date_field,
    IFNULL(BBB.val,0) val
FROM
(
    SELECT date_field
    FROM
    (
        SELECT MAKEDATE(YEAR('2014-02-06'),1)
             + INTERVAL (MONTH('2014-02-06')-1) MONTH
             + INTERVAL daynum DAY date_field
        FROM
        (
            SELECT t*10+u daynum FROM
            (SELECT 0 t UNION SELECT 1 UNION SELECT 2 UNION SELECT 3) A,
            (SELECT 0 u UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7
            UNION SELECT 8 UNION SELECT 9) B ORDER BY daynum
        ) AA
    ) AA WHERE MONTH(date_field) = MONTH('2014-02-06')
) AAA LEFT JOIN (SELECT date_field,val FROM school) BBB USING (date_field)

Can you please help me how can I pass the day month and year in the above query?


texts: Store same sequence value in two columns

texts:
I have a table in which one column is auto increment sequence number. I want the same value to be stored in another column as well (so it can be changed later).

My table structure is like this:

CREATE TABLE Test
(
    test_id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
    ...
    ...
    uid integer not null DEFAULT currval('test_id_seq'::regclass),
)

I want uid = test_id but the problem is when multiple row are inserted in one sessions. Then currval doesn't take the correct id.

I don't want to use a trigger. How can I manage it?


texts: Database Design For reporting comparison results

texts:
I'm going to design an table architecture. Here I wanted to compare same data coming from different sources say Source_A and Source_B. I have to compare few attributes and identify

Mismatches
Data that are missing in source_a
Data that are missing in Source_B.

Finally i have report to the same in PowerBI with charts. For now I have 2 tables A_DATA and B_DATA and both are having below structure (this is just a sample, I have lot more columns)
+---------------+
| Columns       |
+---------------+
| Material_ID   |
+---------------+
| Material_Name |
+---------------+
| Material_Type |
+---------------+
| Quantity      |
+---------------+

Now I'm confused whether should I create separate table for 3 cases(Mismatch,Source_A missing,Source_B Missing) or In single table I should have one more column saying Status and keep everything there. For reporting in PowerBI (like out of 1K rows, 5K are mismatches). Please suggest. Im really confused.


texts: How (if at all) does Galera enforce authentication for SST via rsync when adding a node?

texts:
I have to be missing something here.

It just hit me as added a new node to my cluster in order to prepare for the removal of a different node:  "How does the cluster know that it is okay to send the new node a SST?"

I am pretty sure that the only information the new node has about the cluster is the gcomm:// address.  Surely that isn't looked at as "secure" information that passes for authentication.  To my knowledge, no shell account on the new node has the same password as on the existing cluster nodes.

So what would prevent anyone from spinning up a new node and pointing it's gcomm:// address at one or more of my nodes to just get an SST and be able to see all of my data?

Of course, certificates will be put in place.  But I'm talking about a default setup and how things work "out of the box."  I couldn't find much of anything that talked about this out there.

Am I going nuts?


texts: Storing Dynamic data in relational database (SQL Server 2014)

texts:
Hi my data model contains some static type properties and one dynamic type property. That property could contain array, string, integer, range. Now, simple solution would be create separate column for each one, but problem with that approach is, I will end up with few of optional columns as only one of the type is present at a time(rest of the columns will be null).

My preferred approach is to save it in one field (preferable text or json) and parses the data according to the type. Well parsing has its cost but I still feel this is better approach, but I am no expert in DB, so I am looking for your opinions.
Thanks


texts: How would I select distinct records from the same table on two different databases?

texts:
I have two different databases that both contain a table with the same structure. I have to go across and select the distinct records across both, how is this best achieved? The uniqueness is based on Id. The other columns in the table could have varying data, but Id should be used as the basis of the distinct.

Structure is:

[Media_One].[dbo].[Video]
[Media_Two].[dbo].[Video]


texts:
I am reseeding my Identity Seed with the following T-SQL:

USE DATABASENAME;
GO
DBCC CHECKIDENT ('dbo.DOCUMENT', RESEED, 400000000);
GO

I am subsequently checking it went through:

USE DATABASENAME;
GO
DBCC CHECKIDENT ('dbo.DOCUMENT', NORESEED);
GO

But when I use the GUI it doesn't reflect my changes:

It leaves me to believe that I have mixed up definitions and terms here and that I'm not actually changing what I think I'm changing.

Incidentally when I add a new row to the table DOCUMENT then the PK column shows a value starting at 400000000.

Can someone explain what I am missing or is this a bug?

Thanks!


texts: Why are these queries slow in some environments and not others? Can we fix their performance?

texts:
The following stored procedures in a MySQL 5.x environment:

DELIMITER //

CREATE PROCEDURE sp_belongs_to (IN schemaName VARCHAR(100), IN tableName VARCHAR(100))
    BEGIN
        SELECT TABLE_NAME as keyTable, GROUP_CONCAT(COLUMN_NAME) AS keyColumns, REFERENCED_TABLE_NAME AS refTable, GROUP_CONCAT(REFERENCED_COLUMN_NAME) AS refColumns, CONSTRAINT_NAME AS constraintName
        FROM INFORMATION_SCHEMA.key_column_usage
        WHERE TABLE_SCHEMA = schemaName  COLLATE utf8_general_ci
        AND TABLE_NAME = tableName  COLLATE utf8_general_ci
        AND REFERENCED_TABLE_NAME IS NOT NULL
        GROUP BY constraintName;
    END //

CREATE PROCEDURE sp_has_many (IN schemaName VARCHAR(100), IN tableName VARCHAR(100))
    BEGIN
      SELECT REFERENCED_TABLE_NAME AS keyTable, GROUP_CONCAT(REFERENCED_COLUMN_NAME) AS keyColumns, TABLE_NAME AS refTable, GROUP_CONCAT(COLUMN_NAME) AS refColumns, CONSTRAINT_NAME AS constraintName
      FROM INFORMATION_SCHEMA.key_column_usage
      WHERE TABLE_SCHEMA = schemaName COLLATE utf8_general_ci
      AND REFERENCED_TABLE_NAME = tableName COLLATE utf8_general_ci
      GROUP BY constraintName;
    END //

DELIMITER ;

In one environment they run in a .03 seconds and in another closer to 1 or even 2 seconds.

Our current best guess is that it is primarily a problem with the number of tables and amount of data in one environment versus another.

Can anyone provide additional insights? And is there a way to accomplish the same thing as these procedures while getting more consistent performance?


texts: Why does Informatica hang and say "Not Responding" when connecting to ODBC Data Source

texts:
Informatica just hangs when trying to open this dialog.  The ODBC connection exists in Control Panel > Administrative Tools > ODBC Data Sources.

Source Analyzer > Sources (menu) > Import from Database


texts: SQL Server 2012 AlwaysOn performance best practice

texts:
Is it best practice to have 2 Primary then 1 Secondary for SQL Server AlwaysOn Availability Groups?

My purpose for having 2 Primary instances is to balance the database load. (e.g. my db1 and db2 is on 1st Primary Server then my db3 and db4 is on 2nd Primary Server, then i will configure my AG to add db1 and db2 to 2nd Primary as passive and db3 and db4 to my 1st Primary Server as passive).

Will this improve the performance of my SQL Server?


texts: SQL Server process only using half of the memory on host

texts:
We doubled the memory on host that has a SQL Server Enterprise 2014 instance on it. After a reboot of the server we noticed that the SQL service is only using half of the memory.

A little background info:

The instance is running in compatibility mode for SQL 2008
We went from 64 gb to 128 gb
On the instance in Properties > General > Memory it shows 131072 MB
On the instance in Properties > Memory > Max Server Memory it shows 2147483647 MB

Do you have any idea why the service is not taking up all of the available memory?


texts: Linked Server ODBC statement missing delimiters on reserved identifier

texts:
INSERT INTO SOMESERVER...[SomeTable] ([ID], [Timestamp]) VALUES (746, 1504500000)

This statement is missing delimiters for the identifiers in an ODBC trace.

"INSERT INTO SomeTable ( ID,Timestamp) VALUES(?,?)"

Therefore the statement results in an error due to the reserved word Timestamp. How can we add the required delimiters?

Microsoft SQL Server Express (64-bit) Version 12.0.5207.0
Microsoft OLE DB Provider for ODBC Drivers
4D v13 ODBC Driver 64 bits

Update: as a workaround, we created a service to insert the missing delimiters by intercepting the network communication between the ODBC driver and the 4D database.


texts: Meaning of 'SET' in error message 'Null value is eliminated by an aggregate or other SET operation'

texts:
I saw the above 'ANSI warning' message today when running a colleague's script (and I don't know which of the many statements caused the warning to be shown).

In the past I've ignored it: I avoid nulls myself and so anything that would eliminate them is a good thing in my book! However, today the word 'SET' literally shouted out at me and I realised I don't know what the meaning of the word is supposed to be in this context.

My first thought, based on the fact it is upper case, is that it is referring to the SET keyword and means 'assignment', as in

UPDATE <table> SET ...

...ON DELETE SET NULL...

SET IDENTITY_INSERT <table> ON

According to the SQL Server Help, the 'ANSI warnings' feature is based on ISO/ANSI SQL-92, the spec for which makes just one use of the term 'Set operation' in a subsection title, hence in title case, in the data assignment section. However, after a quick Googling of the error message I see examples that are SELECT queries with seemingly no assignment involved.

My second thought, based on the wording of the SQL Server warning, was that the mathematical meaning of set is implied. However, I don't think that aggregation in SQL is strictly speaking a set operation. Even if the SQL Server team consider it to be a set operation, what is the purpose of putting the word 'set' in capitals?

While Googling I noticed a SQL Server error message:

Table 'T' does not have the identity property. Cannot perform SET operation.

The same words 'SET operation' in the same case here can only refer to the assignment of the IDENTITY_INSERT property, which brings me back to my first thought.

Can anyone shed any light on the matter?


texts: MySQL performance issues with occasional complete freezes

texts:
we have a fairly large production DB currently at ~50GB but with constant growth. In the last couple of weeks, we are struggling with performances as some queries are getting very slow even if indexes are used. Furthermore, once a week or so, MySQL completely stops for no particular reason found in the log files. To be honest, we have not spent much time trying out different settings, but we tried tweaking a few with no success.
After some investigation, a few months ago we had to change some of the default settings, and that looked like it was good, but not for long. Our current my.cnf:

[mysqld]

innodb_buffer_pool_size=12288M # this
innodb_lock_wait_timeout=120 # and this
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" # per design

After examining performance_schema, I am pretty sure some of these values are bad, but because of some unclear explanations for on MySQL docs and here, I am not sure which should be changed.
E.g. Created_tmp_tables is currently at 86331 with Created_tmp_disk_tables 25743, which seems very high. MySQL documentation states that increasing tmp_table_size and max_heap_table_size should help, but many answers say that that would only hide other issues behind this.
As we are all new to MySQL optimization, I would be grateful if someone could just give us some points in which direction we should look.
Here are global_status and global_variables entries.
Our current system spec is 8 physical CPUs and 64GB of RAM, with SSD discs. MySQL version is 5.7.33.


texts: Fetching nth document based on integer array in MongoDB

texts:
so let's say I have a random array of n integers [23, 2, 78, 99, ... ] where  0 < n < collection.count(), and each element is within this range as well.
In my collection, it is currently indexed by _id, and say it contains the following data:
{
    "_id" : "1293jnqke",
    "name" : "name_string",
    "other_data" : [{
        "data" : ...
    }]
}

How should I go about grabbing the data, in chunks of 50, which correspond to each element in the array? So with the example integer array I have, I will slice it down to the first 50 elements, and then I wish to run a find() on the collection, extracting the names of the 23rd, 2nd, 78th, 99th, etc elements, based on the natural ordering of the collection (no need for any sorting beforehand). So the output should look like this:
{
    [ {"name" : "23rd name"},
    {"name" : "2nd name"},
    {"name" : "78th name"},
    {"name" : "99th name"},
    {"name" : "53rd name"},
    {"name" : "11th name"},
    ...]
}

One way of course is by looping through the array with findOne() and skip() but I don't think that is very efficient, is it possible to execute this in a single query? Or are there any better methods?
Appreciate any help, thank you!


texts: Multiple Similar Tables vs One Master Table

texts:
I'm a web app developer and less experienced in database design, so would appreciate anyone's input.

When I first built this web app, the app managed one type of object (table) with a handful of properties (columns). As the app grew, there were additional types of very similar objects, so I created additional tables for each object type.  (You could say that all the object types inherit from the same class/prototype.) I'm finding that my tables for each object type are all nearly identical because each object type shares ~ 90% of the same property types.  For example, the tables may have nearly the exact same column names (& data types), but one table may have 1-2 extra columns to refer to properties specific to that object type.

Since all the object types are so similar (in the same way that Dog, Cat, and Mouse all inherit from the Animal class), would it be wise to put these all in the same table and simply allow some columns to contain NULL values when the columns are not relevant to that type?  Or is there some other alternative I'm not considering?

For reference, here are some similar posts I've already reviewed:

1) What are the design alternatives when having multiple "type" tables

2) https://stackoverflow.com/questions/9021838/database-design-multiple-tables-vs-a-single-table

3) Database design: Dividing multiple identical tables, good or bad?


texts: Slow execution of a subquery with 0 matches

texts:
In MySQL, I have two basic tables - Posts and Followers:

CREATE TABLE Posts (
  id int(11) NOT NULL AUTO_INCREMENT,
  posted int(11) NOT NULL,
  body varchar(512) NOT NULL,
  authorId int(11) NOT NULL,
  PRIMARY KEY (id),
  KEY posted (posted),
  KEY authorId (authorId,posted)
) ENGINE=InnoDB;

CREATE TABLE Followers (
  userId int(11) NOT NULL,
  followerId int(11) NOT NULL,
  PRIMARY KEY (userId,followerId),
  KEY followerId (followerId)
) ENGINE=InnoDB;

I have the following query, which seems to be optimized enough (based on the EXPLAIN output):

  SELECT p.*
    FROM Posts p
   WHERE p.authorId IN (SELECT f.userId
                          FROM Followers f
                         WHERE f.followerId = 9
                      ORDER BY authorId)
ORDER BY posted
   LIMIT 0, 20

When followerId is a valid id (meaning, it actually exists in both tables), the query execution is almost immediate. However, when the id is not present in the tables, the query only returns results (empty set) after a 7 second delay.

Why is this happening? Is there some way to speed up this query for cases where there are no matches (without having to do a check ahead of time)?


texts:
I have a select query that should return a result with about 200 rows from 3 tables:

wb_mod_deposit_out - 700.000 rows
wb_mod_deposit - 100.000 rows
from_generale_java - 1200 rows.

The query is:

SELECT

    DATE(a.created) AS DATA_MANCANTE, a.stagione,
    c.status, a.modello, a.commessa, a.qnt_prod,
    a.fabrica, a.CODE, a.color, a.measure,
    ROUND(a.qnt,2) AS qnt_not_enough,
    a.dist_base, b.qnt AS qnt_dept,
    DATE(a.created) AS DATA_PRELIEVO,
    C.`DATA_PREV_INI_TAG`, C.`DATA_PREV_INI_CONF`

FROM

 wb_mod_deposit_out a,

 (SELECT CODE, color, ROUND(SUM(qnt),2) AS qnt
  FROM wb_mod_deposit
  GROUP BY CODE, color
 ) b,

 from_generale_java C

WHERE
      a.TYPE = 2
  AND a.code = b.code
  AND a.color = b.color
  AND b.qnt <= 0
  AND A.modello = C.MODELLO
  AND A.COMMESSA = C.COMMESSA
  AND A.STAGIONE = C.STAGIONE;

It takes about 2 min to return the result.

I have found a way to check myself with EXTEND:
https://www.slideshare.net/phpcodemonkey/mysql-explain-explained


texts: Risks of manual failover in Always On Availability Groups

texts:
In my environment, I have SQL 2014 with Always On Availability Group for databases in asynchronous with manual fail over.

We have 1 primary and 2 secondary replicas--secondary1 and sec2.

I have to do failover from primary to secondary1. I know some data loss will happen as it is a forced failover.

My questions:

What will be prerequisite for this?
Can we do in busy hours or after busy hours?
Is there any risk involved during failover.

Appreciate your suggestions as first time I will do failover.


texts: Including the substring "_id" in column names

texts:
I have a table of Teams. Each Team has an ID.
I have another table that holds Players. Each Player is assigned to a team.

So my tables might look something like

CREATE TABLE players (
   id        INTEGER   PRIMARY KEY
   name      STRING
   team_id   INTEGER   REFERENCES teams(id)
)

CREATE TABLE teams (
   id        INTEGER PRIMARY KEY
   name      STRING
)

Does writing _id make it any more clearer that the column is meant to hold an ID of a team (as opposed to maybe a team name)?

If I saw that team was linked to a key on the teams table I would assume the column is meant to hold an ID.

I find that if I started following a convetion where I tack on _id I'll end up with a lot of columns with _id in them, but am not sure whether it adds any value.


texts: Why is my maintenance plan save overwriting job ownership?

texts:
I'm slowly untying all the system dependencies to the previous DBA's system account and moving them to a service account. The old DBA had ownership of all the jobs in our maintenance plan. In SSMS I can change the ownership directly and verify the changes have been applied with this query:

USE MSDB
GO
SELECT GETDATE() AS 'ExecutionTime'
GO
SELECT @@SERVERNAME AS 'SQLServerInstance'
GO
SELECT j.[name] AS 'JobName',
Enabled = CASE WHEN j.Enabled = 0 THEN 'No'
ELSE 'Yes'
END,
l.[name] AS 'OwnerName'
FROM MSDB.dbo.sysjobs j
INNER JOIN Master.dbo.syslogins l
ON j.owner_sid = l.sid
ORDER BY j.[name]
GO

However when I modify the maintenance plan and save the ownership reverts to the old DBA account. I've combed through the GUI for a reference to his account but I can't find one.

Why is this happening and what I do I need to change to keep the maintenance plan from overwriting Job ownership.


texts: How to set Password for sqlplus /as sysdba

texts:
I am new to Oracle. I would like to know how to set login password for sqlplus / as sysdba in oracle 11g.

I want to set the password for my next login. I don't want to login as sqlplus / as sysdba since anyone can log in using sqlplus / sysdba and can easily access the data or change password for the existing user.I need to restrict the login.

Say in a company 5 peoples are working and they are accessing the server with Admin userid and password to login the system and if some-one changes some modifications in the server using sqlplus / as sysdba it will affect the rest of the peoples right so in-order to avoid we need to restrict the login by setting password.


texts: SQL Server :: how to move country code column to another table?

texts:
I have 2 tables:

tbl_Countries: 250 values, with columns like CountryName (Afghanistan), TwoCharCountryCode (AF), ThreeCharCountryCode (AFG).

Like this:
CountryName    TwoCharCountryCode ThreeCharCountryCode
Afghanistan    AF                 AFG
Aland Islands  AX                 ALA
Albania        AL                 ALB
Algeria        DZ                 DZA
American Samoa AS                 ASM
Andorra        AD                 AND
Angola         AO                 AGO

and

population_by_country_2020: 235 values which has the column Country_or_dependency (Afghanistan) but is missing the 2 and 3 CHAR country code.

Like this:
CountryName    TwoCharCountryCode ThreeCharCountryCode
Afghanistan    NULL               NULL
Albania        NULL               NULL
Algeria        NULL               NULL
American Samoa NULL               NULL
Andorra        NULL               NULL
Angola         NULL               NULL
Anguilla       NULL               NULL

...yeah, because they have different values they will never match perfectly but I can do those who miss by hands.
What I want to do overall is to move AF and AFG from tbl_Countries to population_by_country_2020 in the row 'Afghanistan'.

How to archive that?


texts: Cannot find aria_s3_copy utility in mariadb 10.5.5

texts:
I am not able to find the Aria utility - aria_s3_copy under bin. I use mariadb official docker image and I do find aria_chk but not aria_s3_copy.
Can you tell me where I can find it?


texts:
I am having a strange issue when trying to connect to my SQL Server instance remotely from my client computer. I am able to do this on my other instances by opening up a new Database Engine Query and PREFIXING ADMIN: in front of my server name. It only doesnt work on one instance. When I open up the Error Log, it states that "Dedicated admin connection support was established for listening remotely on port 55632." SQL Server Browser is running (and Restarted). When I try to connect to DAC on this one instance, I get an error saying to look in my SQL error logs.this is where I am getting confused:

I had installed a Monitoring solution from SolarWinds (DPA Free) last week. I didnt find it very useful so I uninstalled it and deleted the database where it stores the metrics (ignite_DBAnalyzer). I double-checked that this DB does not exist on this instance. This is the same DB, ignite_DBAnalyzer, it is claiming it cannot explicitly open (See line 1 of error log). I checked my user Mappings and that DB is not anywhere to be found. I checked my default DB to connect to when i start a new connection and that is also fine.

Has anyone seen this kind of behavior before? Is there cache somewhere I need to clean out so it forgets this DB and can verify that it has been deleted?


texts: oracle use left join to match one of the fields

texts:
I have a simple question but for some how I'm stucked :
I have two tables as shown :
with cte1 as (
select 'John' name , 'Smith' family from dual
UNION
select 'Adam' name , 'Hart' family from dual
UNION
select 'Matt' name , 'Harley' family from dual
)
,cte2 as (
select 'John' name , 'Adam' family from dual
UNION
select 'Adam' name , 'Hart' family from dual
UNION
select 'Bob' name , 'Harley' family from dual
)

I need to match both name and family from both tables and list the matching ones (even if only one of the 2 field is matching). so the output must be similar to the below:
name     family     name_1      family_1
--------------------------------------
John     Smith      John         NULL
Adam     Hart       Adam         Hart
Matt     Harley     NULL         Harley

So I tried left join , but in case one of the fields is not matching , all fields on the second table are not shown :
select *
from cte1 c1 left join cte2 c2 on c1.name=c2.name and c1.family=c2.family

the result is:
name     family     name_1      family_1
--------------------------------------
John     Smith      NULL         NULL
Adam     Hart       Adam         Hart
Matt     Harley     NULL         NULL


texts: How to see entire longtext column in MySQL client

texts:
Is there any way to view the entire text of a LONGTEXT column when we select it using the MySQL command line client - mysql?
This query:
select query from statement_analysis;

only shows 64 characters of the query.
This is on 5.7.25.
example from 5.7.33:
--------------
select max(length(query))
from statement_analysis
--------------

+--------------------+
| max(length(query)) |
+--------------------+
|                 65 |
+--------------------+
1 row in set (0.00 sec)

--------------
select query
from statement_analysis
where
length(query) > 60
--------------

+-------------------------------------------------------------------+
| query                                                             |
+-------------------------------------------------------------------+
| SELECT `DIGEST` , `CURRENT_SCH ... . `events_statements_history`  |
| SELECT COALESCE ( SCHEMA_NAME  ... ) - INTERVAL ? SQL_TSI_SECOND  |
| SELECT `sys` . `format_stateme ... cy` , `sys` . `format_time` (  |
| SELECT `performance_schema` .  ... IMER_WAIT` AS `avg_latency` ,  |
| SELECT COALESCE ( SCHEMA_NAME  ... statements_summary_by_digest`  |
+-------------------------------------------------------------------+
5 rows in set (0.00 sec)

If you run this command with 1000 or more it expands this to 1000:
set @sys.statement_truncate_len = 1000

But I would like to get the full query. It looks like the general log has the full length queries for what that is worth.
Bobby


texts: How to determine efficacy of index on child side of foreign key

texts:
Given a parent-child relationship between two tables, such as:

table ORDER
    order_id PK (already indexed)
    order_name

table ITEM
    item_id PK (already indexed)
    order_id (FK to ORDER.order_id)
    amount

I want to determine whether there would be any benefit (performance improvement to SELECT queries) in adding an index on ITEM.order_id.

What information should I gather that would help me determine this? (such as structure of the queries my application runs against these tables, etc.) I do know that my application regularly runs queries that perform a simple inner join between these two tables. Are there any general principles that apply to index tuning for this kind of scenario?

I am using Oracle 11g.


texts: mysql run multiple selects on the same table at the same time

texts:
We have 25 queries, which are simple select statements, that we need to run concurrently on the same table, to test load.

How do I run multiple select statements at the exact same time?
I have tried a bash script, but it runs the selects in order, and not at the same time


texts: Compute new records based on existing ones (e.g., sum of 2 records)?

texts:
Background
I have an inventory where I register the number of fruits I have in stock — both expected and actual quantities.

For some fruits (e.g., here Banana and Cherry), I might not have both records.

My table looks then like this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |

This table gets partially updated over time (i.e. I might only change the Apple | Actual quantity, or add the Banana | Expected record).

Question
➥ Is it possible to automatically compute new records based on the existing ones? If yes, how?

In our example, I might want to compute the Variation between the actual and the expected value: i.e. add the {fruit} | Variation | [{fruit | Actual}(Qty) − {fruit | Expected}(Qty)] record when I have both Actual and Expected records for a given {fruit}.

In our case, I'd like that my table becomes this:

| Fruit  | RecordType | Qty |
|--------|------------|----:|
| Apple  | Expected   |  13 |
| Apple  | Actual     |  17 |
| Banana | Actual     |   8 |
| Cherry | Expected   |  42 |
| Durian | Actual     |   0 |
| Durian | Expected   |   1 |
| Apple  | Variation  |   4 | ← newly computed record
| Durian | Variation  |  -1 | ← newly computed record

You can see that there is no Variation record for Banana or Cherry, because they are missing their Expected or Actual record, respectively.


texts: How to apply filter at runtime in SSRS 2008 R2

texts:
In my report in SSRS 2008 I have created a expression for the CasesShipped column like this:

=IIF(Fields!Current_Product.Value= "Match"," ",Sum(Fields!Cases_Shipped.Value))

It sums the Cases_Shipped value having same distributor item code. Now I want to apply  a filter for the Cases_Shipped drop down list value to report records after previously mentioned activity.

How can I do this?


texts: How can I search a column in MySQL containing unix timestamps with a query referencing a different format?

texts:
I have a mysql table that contains something like:

+-------------+-----------------+
| FOO1        | FOOTIME         |
+-------------+-----------------+
|        blah |   1234567890000 |
+-------------+-----------------+

FOOTIME uses unix timestamp.

I want to run a query that essentially says "how many rows contain foo time older than January 1st 2012?

I thought I'd want to do something like select count(*) from foodb.FOOTABLE where FOOTIME <= TIMESTAMP('2012-01-01'); but that didn't work.

What am I missing?