texts:
I have two tables articles and topics and I am using MySQL 5.6

Articles

id    topic(fk on Topics.id)   featured
1     1                        1
2     2                        0
3     3                        1
4     4                        1
5     5                        1

Topics

id    parent(fk on Topics.id)
1     NULL
2     1
3     2
4     3
5     NULL

My question is, I need to get all featured articles in specific root topic

i.e when I query about featured articles in topic id 1, I get article 1, 3, 4


texts:
This is my experience with TempDB. The Algorithm works better when
the TempDB File sizes are the same size.
We were seeing heavy TempDB contention on a TempDB file that was larger than the others. We needed that file larger in the case of run away queries (caused by people not logging off their machines properly - not the SQL Server) which
occasionally caused TempDB to fill.
We switched to 16 TempDB files (16 CPU's) on RamDisk all the same size with No Autogrowth.
We setup a job to kill the spid using the most TempDB resources, once TempDB was 80% full.
In working with Microsoft, their technician tested if a TempDB file was created on one TempDB file it would overflow to a second TermpDB file if it needed too (I was concerned about this).
Our SQL Server is a Read-Only AlwaysOn and we run some pretty big reports
that use large amounts of TempDB. Our new TempDB design has been in production for over 6 months, with no issues. Occasionally, we have a spid cancelled due to TempDB over 80% full, but not very often.
The question
Curious, is there some SQL code I can use to quickly determine if files are crossing multiple TempDB files? We are on SQL Server 2017 CU 12.


texts:
I have a JSONB field called data with a few keys and there is a field like:

{school_id: [nil, 123456]}

I'm filtering all records where second element is not null using query:

"data -> 'school_id' ->> 1 IS NOT NULL"

How can I apply an index on that field ?
I've tried:

"CREATE INDEX data_school_id ON events ((data -> 'school_id'));"

but when I use EXPLAIN I do not see that this index is in use.

=> EXPLAIN for: SELECT "events".* FROM "events" WHERE (data -> 'school_id' ->> 1 IS NOT NULL)
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on events  (cost=0.00..211043.04 rows=2096733 width=555)
   Filter: (((data -> 'school_id'::text) ->> 1) IS NOT NULL)
(2 rows)


texts: Does oracle database have a mechanism to replace specific character when storing data

texts:
I'm using oracle 11g database, does oracle database have a mechanism to replace specific character with other characters over all tables when storing data. For example i want to go to specific configuration place in oracle and specify these characters ﻙ, ﻱ to be replaced with these ک, ی that are Arabic characters.

I'm beginner in oracle database.


texts: Azure Database for PostgreSQL and Event Triggers

texts:
I am using Microsoft Azure Database for PostgreSQL with PostgreSQL 10 installed. As I'm trying to work on future tables together with other users I want to enable the other users to alter my tables as well.

I've created a role pgpublish and all users are members of the role. For a new table, which I created, I altered the table owner to the role pgpublish. Now everyone with the role pgpublish is able to alter the table:

ALTER TABLE "MYcoolSchema"."CoolNewTable" OWNER TO pgpublish;

To make this more automatic/generic, I created a trigger function and tried to create an event trigger as explained here.

Unfortunately I can't create the event trigger (The trigger function works fine), as it is stated:

ERROR: permission denied to create event trigger
"trg_create_set_owner" HINT: Must be superuser to create an event trigger.
SQL state: 42501

Is there a workaround for creating event triggers on Microsoft Azure Database for PostgreSQL? How can this look like?

I could run a cron job on another system to scan for new tables and alter the owner of these new tables to pgpublish but this is not cool at all.


texts:
I've installed WAMP on my new Windows 8 machine and for some reason when I use PHPMyAdmin to import an sql file (it's only about 5mb), it takes several minutes before timing out. And then when I check I can see it's only imported a fraction of the tables.

I've tried other sql files and it's the same story. These same sql files import perfectly fine (and in a few couple of seconds) on my Windows 7 machine also running WAMP.

I've since uninstalled WAMP and tried XAMPP, and the problem still exists. Even just browsing the databases with PHPMyAdmin takes a long time between page loads.

I am guessing it's a problem with MySQL. Any suggestions would be helpful.


texts: Rewrite query to not use CONCAT function in SQL Server 2008 R2?

texts:
I know that the CONCAT function does not exist in SQL Server prior to 2012. How would i optimize my code to not use the CONCAT function?

         SELECT  dbname = DB_NAME(),
                  file_name = name,
                  TotalMB = CONVERT(decimal(12,1),size/128.0),
                  UsedMB = CONVERT(decimal(12,1),FILEPROPERTY(name,'SpaceUsed')/128.0),
                  FreeMB = CONVERT(decimal(12,1),(size - FILEPROPERTY(name,'SpaceUsed'))/128.0),
                  Command = CONCAT('USE MASTER; ALTER DATABASE ', DB_NAME(), '; MODIFY FILE (name = ',
                        [name], ', size = ',
                        convert(int,round(1.15 * FILEPROPERTY(name,'SpaceUsed')/128,-1)), 'MB)')
           FROM sys.database_files WITH (NOLOCK)
           WHERE type_desc = 'ROWS'
           ORDER BY file_id;


texts: Automatic Version increment on Data-Tier Applications

texts: In the project settings of a database project I can set the version number form my data-tier application, however setting this manually is a bit inefficient. I would like to automatically increment the build number, as with other VS projects. How can this be done ? At the same time I would like to see this version number in the generated dacpac file.


texts:
I'm new to Oracle, so I apologize for such a simple question, but I've already wasted several hours on it.

I have the following stored procedure:

create or replace procedure MyTestProc (outparam OUT VARCHAR2) AS
begin
   select 'test' into outparam from dual;
end;

How do I call this stored procedure, and insert the value of outparam  into a variable? I'd like to be able to do this so I can then use the result for other things, e.g., printing it to output.

Using functions, I was able to do this as easy as:

variable tmpString VARCHAR2(100);
call MyTestFunc('test') INTO :tmpString;
print tmpString;

How do I do this with stored procedures?


texts: How to see restore history of a deleted database?

texts: For example, I had a database and I want to see which physical backup file was used to restore into it. The problem is that the database was deleted. How can I see the record?


texts: Need to setup a sql alert on "waiting tasks" - similar to "waiting tasks" in Activity Monitor

texts:
My manager has asked me to work upon a request. I am not sure if it is reasonable or weird but he wants me to setup an alert based on the "waiting task" that we see in the activity monitor. Basically, if the value in the "waiting task" goes above 10 then we need to be alerted upon. I am not sure as to how I would replicate the same information that we see in Activity monitor as this seems to be live data. Closest DMV and the column I could think of is "waiting_tasks_count" in sys.dm_os_wait_stats. However, this does not seem to be in line with what he is looking for and the column consists of cumulative values over a period of time since last time SQL was restarted. Can someone shed some light on where I can find a query or a counter that I can alert upon?

I have thought about a query at this time but I think I can't calculate the delta correctly it always shows me 1. Can someone help me out exactly where I am going wrong here.

select  so1.wait_type,
        so1.waiting_tasks_count as no_of_waits,
        so1.waiting_tasks_count - so2.waiting_tasks_count as delta_wait_count,
        so1.signal_wait_time_ms,
        so1.wait_time_ms - so2.signal_wait_time_ms as resource_wait_time,
        getdate() as sample_time
from  sys.dm_os_wait_stats as so1
inner join sys.dm_os_wait_stats as so2
on so2.waiting_tasks_count = so1.waiting_tasks_count - 1


texts: MySQL database getting connected with any username and password

texts:
We have a website running on an custom Ubuntu server managed and hosted by the client himself, since last two days the website database is getting hacked and someone deletes all the db tables from the database.

Upon checking the Db connection I come to know that database is getting connected with any username and password, is there any configuration issue in mysql? I am not able to find anything on google or stackoverflow.

Any help would be appreciated. TIA.

EDIT: I checked mysql users table and there are three users in the table as listed below,

root
mysql.sys
mysql.session


texts: Same Query takes 0 seconds on Server A, 7.5 minutes on Server B (same db/hardware/config)

texts:
I have a query (sql below) that is nearly instantaneous on 1 server, but takes 7.5 minutes on another virtually (key point) identical server.  The estimated query execution plans have one obvious difference;  the plan on the slower performing server contains an "Index Spool (Eager Spool)" where the plan on the faster performing server does not.

Server F (fast) - A snap of Server S that is refreshed monthly.  It is used by only a handful of users for intensive analysis and has been separated so day to day users don't have to suffer the performance hits.

Server S (slow) - Receives daily data updates and is generally used for ad-hoc data mining and a small amount of user workflow data updates.  There is no user activity or process running when this sql takes 7.5 minutes to run.

The hardware and configurations are identical.  One difference is that the indexes on Server B are only rebuilt/reorganized once per week.  They are rebuilt on Server F just after restoring the snap.  I don't think this is a problem though because on Server S all index fragmentation on the large IB Charge table is < 5%.

The sql may be complex or ugly, but it runs quickly on 1 server.  The index fragmentation does not appear to be much higher on the slower server than the fragmentation on the slower server.

What is causing such a drastic difference?  I'm struggling with deciding what to investigate next.

select
row_number() over (order by t1.service_date) as rank,
sum(t1.total_charge) total_charge,
sum(t1.service_units) service_units,
t1.service_date service_date,
(   select sum(x.total_charge)
    from ib_charge x
            inner join ib_header y on x.ib_header_fk = y.ib_header_pk
    where x.patient_account_fk = ###
            and x.record_type = 1
            and x.voided = 0
            and y.replaced = 0
            and x.revenue_code not in (274, 275, 276, 278)
        and (x.service_date is null or x.service_date <= t1.service_date)
    ) running_total_by_day
from
ib_charge t1
inner join ib_header t2 on t1.ib_header_fk = t2.ib_header_pk
where
t1.patient_account_fk = ###
and t1.record_type = 1
and t1.voided = 0
and t2.replaced = 0
and t1.revenue_code not in (274, 275, 276, 278)
group by t1.service_date
order by t1.service_date

Execution Plan Server F
Server_F.sqlplan

Execution Plan Server S
Server_S.sqlplan

SERVER F statistics io

(55 row(s) affected)

Table 'IB_Header'. Scan count 0, logical reads 60611, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'IB_Charge'. Scan count 56, logical reads 121156, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SERVER S statistics io

(55 row(s) affected)

Table 'IB_Header'. Scan count 0, logical reads 60706, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 55, logical reads 384302241, physical reads 12, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'IB_Charge'. Scan count 2, logical reads 1686771, physical reads 4143, read-ahead reads 1684432, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    UPDATE >> I also put this question out as an office challenge, and on Monday someone congratulated me on having solved the problem - but I hadn't done anything!  A weekly statistic refresh and index rebuild had run over the weekend, but the particular index stats on the tables involved don't look much different.  On further investigation, I realized that depending on which particular KEY is used in the ### portion of the query the results will take either 0 seconds or 5-8 minutes.  Just editing the KEY also causes the estimated execution plan to include/exclude the index spool which seems to be the primary delay in the query.  Currently pursuing a more holistic investigation of the bpool.


texts: How do I correctly design this database?

texts:
I am not a database designer by any means.  I am a desktop programmer, but I am currently looking at implementing a website idea which will need a (possibly very large) back-end database.

In my site I will have users who can create and join "groups".  The number of users could be rather larger, as could the number of groups, and users could obviously be in more than one group if they so desire.

I am struggling with how to properly define this on the database end.

I could create a "groups" table with all the group information, and then another table that associates each user with groups (so each row in that table would have a user id and then a group id, and the user id could appear in multiple rows since a user could be in multiple groups).  Such a table would grow rather large with a large number of users, especially if users were involved in multiple groups.

A friend of mine suggested having a separate table for each group instead.  So every time a user creates a group, we create a table in the database which then gets populated with the user ids of each user that is in that group.  I wasn't so sure about this, because it seems like it would be a lot of overhead to create a new table for each group.

Also, coming from a desktop programmer's perspective, I really just want to create a class called "Group" and give it an array of user ids...but obviously I can't do that in the relational database world.

So, what can I do?  What's the best way to design this?  What will run most efficiently on a server?  Help enlighten a programmer who is wanting to learn a little bit about database design. :-)

Thanks!


texts: Upgrade SQL 2008R2 to SQL 2016 on windows server 2012R2

texts:
I am checking for expertise opinion for IN PLACE UPGRADE from SQL 2008R2 to 2016. We have tested side by side which looks good in dev env's.

Considering production can i have some opinions on how feasible and safe is to use in place upgarde:

for standalone server upgrade
for cluster SQL FCI upgrade


texts:
I want to get the high-level domain name (not sub-domain) from the hostname.
The idea is to "guess" the ISP name from the user's hostname. Ideas?

// no ISP when it's an IP
115.20.106.216 => null

// in these cases the domain name should be the ISP name
108-81-122-61.lightspeed.frokca.sbcglobal.net => sbcglobal.net
61-22-86-252.static.trlk.ca.charter.com => charter.com
c-28-8-222-102.hsd1.ca.comcast.net => comcast.net
CPE-128-122-18-181.hdbq1.win.bigpond.net.au => bigpond.net.au
dhcp-108-168-82-106.cable.user.start.ca => start.ca
ip68-100-26-186.dc.dc.cox.net => cox.net
ip-108-85-1-22.web.vodafone.de => vodafone.de
pool-68-86-186-222.washdc.fios.verizon.net => verizon.net
s51865a4f.adsl.online.nl => online.nl

// in these cases it's not very obvious
c-208-168-15-86.wifi.ties.k12.mn.us => k12.mn.us ?
cpe-66-181-81-222.tx.res.rr.com => res.rr.com ?

Note: All IP addresses are random.
Something like bigpond.net.au is 3-level. The main domain is sometimes 2-level and sometimes 3-level.


texts: Table name both exists and does not exist in the same query

texts:
I'm trying to send some data to an Oracle database.  The parameterized query looks like this:
INSERT ALL
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
INTO mySchema.myTable (c1, c2, c3) VALUES (?, ?, ?)
SELECT 1 FROM DUAL

When I run it, I get a truly bizarre error message:

Error: 942, Position: 506, Error msg: ORA-00942: table or view does not exist

Position 506 is the fourth iteration of mySchema.myTable in my query.  But they're all the same table name!  How is it that the first three copies of the table name exist just fine, and then the fourth one is nowhere to be found?
I know Oracle has a reputation for being difficult to deal with, but this is just straight-up insane!  Has anyone seen this before, who might know how to get it working?


texts: Parsing, Planning and Optimizing Prepared Statements

texts:
PostgreSQL (2nd Edition) by Douglas and Douglas notes:

  Use server-side procedures (triggers and functions) to perform common operations. A server-side procedure is parsed, planned and optimized the first you use it, not every time you use it.

When using a prepared statement, is it parsed, planned and optimized only on the first time it's used (similar to triggers and functions per the quoted text), or each time it's used?


texts: Can EDB's edb_audit be configured to not audit entries from pgAdmin or Postgres Enterprise Manager (PEM)?

texts:
Summary:
I have enabled auditing on a few EDB databases from the PEM 'Audit Manager'. The audit files have been created and auditing everything... everything. Now the audit logs files are growing at a higher rate than expected. I want to reduce some of the 'noise'.
Details and research:
Running EnterpriseDB v9.6.x and Postgres Enterprise Manager (PEM) v7.7
For pgaudit, this is known concept for as pgaudit has an option to turn off and ignore entries from monitoring applications like pgAdmin or PEM. In fact, I enabled pgaudit on a few systems (postgres v9.6) at the same time I enabled edb_audit and have successfully cut down on the noise from the PEM and pgAdmin queries on the postgresql servers. This gave me hope that edb_audit would have something similar. However, I have yet to find anything helpful.
Is there an equivalent in edb_audit? Or is there a more detailed document than the setup guide on EDB's website? Or... is edb_audit stuck at auditing all these monitoring queries?
What I have tried:
I have not been able to discover any action item to take. My google searches only point me to the very basic EDB documentation on how to enable 'Audit Manager' (no troubleshooting section, no finer details on what it's doing, etc.).
I have compared the postgresql.conf file on systems where edb_audit was enabled on one and can see (a bit) what edb_audit is doing. However, no documentation on settings that could be changed manually.
The main pages I have found are:
https://www.enterprisedb.com/edb-docs/d/edb-postgres-enterprise-manager/reference/online-documentation/7.7/audit_manager.html (EDB's official documentation)
https://blog.dbi-services.com/auditing-with-edb-postgre-enterprise/ (a great article on how to configure edb_audit from the command line.)


texts:
Is there any difference between updating the statistics of a table using sp_updatestats with out resample and updating stats of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE)

  exec sp_updatestats vs update statistics tablename

updating the tables using sp_updatestats with default value NO will update the statistics with default sampling rate.

similarly updating statistics of table using UPDATE STATISTICS without sample options(FULLSCAN,SAMPLE PERCENT,RESAMPLE) will also update the table statistics with default sampling.

So is there any difference between both methods? Am I missing anything here ?

Update :

I know that sp_updatestats runs on all the tables but using UPDATE STATISTICS we can update statistics of specific table.


texts: System SPID running for hours filling up the TempDB Log

texts:
I've been trying to track down the source of my tempdb log filling up every few weeks where the tempdb log space used will continue to grow until it reaches 99% and stays there for hours while the log file grows.  After adding in some additional logging and email alerts, I think I identified the source of the problem, at least for this incident, not sure if the cause is the same in past incidents.
dbcc opentran in the tempdb reported this:
Oldest active transaction:
    SPID (server process ID): 47s
    UID (user ID) : -1
    Name          : sort_init
    LSN           : (41:3017:42)
    Start time    : Dec 19 2020 10:03:20:410AM
    SID           : 0x01
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The spid is 47s which I discovered is a system spid.  I ran sp_WhoIsActive with @show_system_spids = 1 which brought back data on the process.  After running sp_WhoIsActive over the course of an hour, tempdb_allocations ended up at 4,221,088, tempdb_current was 4,128,656, open_tran_count was 1 (throughout the entire incident) and sql_text was always null.  After running out of ideas, I ended up restarting SQL Server.  Several times prior to restarting SQL server, I issued a checkpoint in tempdb but that didn't help, likely since the transaction from the system process was still active.
The Start time from the dbcc opentran output is 10:03am and it was 1:00pm when I restarted SQL Server.  I ran dbcc opentran a few times over the course of an hour and the same output was returned each time.
In this particular case, the server was rebooted and it restarted at 10:02:54 -- 26 seconds before this long running system process started at 10:03:20.  Past incidents don't appear to have happened immediately after a system reboot. I don't have many details on past incidents, but at least the last time this occurred 9 days ago, in the sp_WhoIsActive output I collected (without @show_system_spids set to 1) there were no long running processes captured which likely means it was a similar system process running / filling up the tempdb log.  This 99% full tempdb log incident 9 days ago was ongoing for 7 hours.
Nothing stands out in the SQL Server log and Windows logs.  I'm running Microsoft SQL Server 2017 (RTM-CU15-GDR) (KB4505225) - 14.0.3192.2 (X64).
Is there a way to determine what a system SPID is doing?  Or any tips on how I can better troubleshoot this problem when it happens next time? The only clue from this incident is the Name value of sort_init in the dbcc opentran output which could be helpful but seems generic at the same time.


texts: A pre-existing hierarchial table as relational

texts:
Currently the database contains something like so (these are the main fields but there's a large amount more):

+----------+----------------+----------+
| Agency   |     parent     | main_term
+----------+----------------+----------+
|        1 |  Office Staff  | Supplies |
|        1 |  Supplies      | Postings |
|        1 |  Supplies      | Artwork  |
+----------+----------------+----------+

The program would spit this data in a list like so, looking at the parent and main_term for proper indentation:

Agency #1:
Office Staff
--Supplies
----Postings
----Artwork

Right now the data seems redundant and creating a program for average users to edit or restructure the lists would turn out very confusing.

Is there a better way to structure this using multiple tables?

Thanks in advance.


texts: How to add multiple query results into one csv file?

texts:
By the help of the members of this community I have created a script which shows recent T-log backup for each database in the server. Instead of sp_MSforeachdb I used a customized script written by Aaron Bertrand.

Here is my actual command which gets recent LOG backups for each database in the server:

EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command =
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg''
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN ''Yes'' ELSE ''No'' END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = ''L''
WHERE sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'

Now, the problem is I want to send all these query results into email in CSV file but I am not able to paste all these results. I tried creating a new table and storing all query results there, unfortunately I got an error. I need an alternative solution.

I would be glad to hear your ideas.


texts: I broke replication. Could this have been fixed?

texts:
I ran mysql_upgrade on both my master and slave after upgrading to a later version of mysql. Somewhere during this process replication broke for the mysql table. I was able to fix it manually, but after resetting slave I was greeted with more issues causing the SQL thread to abort. The following includes the first example right after I've fixed the mysql table and executed reset slave.

141006  8:53:08 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.020292' at position 54986, relay log '/var/log/mysql/relay.010967' position: 38174
141006  8:53:08 [ERROR] Slave SQL: Error 'Duplicate column name 'character_set_client'' on query. Default database: 'mysql'. Query: 'ALTER TABLE proc ADD character_set_client
                      char(32) collate utf8_bin DEFAULT NULL
                      AFTER comment', Error_code: 1060
141006  8:53:08 [Warning] Slave: Duplicate column name 'character_set_client' Error_code: 1060
141006  8:53:08 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.020292' position 54986
141006  8:53:08 [Note] Slave I/O thread: connected to master 'replication@mysql07:3306',replication started in log 'mysql-bin.020293' at position 2146
141006  8:54:03 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
141006  8:54:03 [Note] Slave I/O thread killed while reading event
141006  8:54:03 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.020293', position 2146
141006  8:56:45 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/log/mysql/relay.000001' position: 4
141006  8:56:45 [Note] Slave I/O thread: connected to master 'replication@mysql07:3306',replication started in log 'FIRST' at position 4
141006  8:56:45 [ERROR] Slave SQL: Error 'Duplicate entry '323812407' for key 'PRIMARY'' on query. Default database: 'im_production'. Query: 'INSERT INTO messages
                         (timestamp, seq, conversation_id, sender_id, body)
                         VALUES (1410872583925, 194935,
                           (SELECT id
                           FROM conversations
                           WHERE owner_id=123456
                           AND with_user_id=1245678), 3069096, 'lol')', Error_code: 1062
141006  8:56:45 [Warning] Slave: Duplicate entry '323812407' for key 'PRIMARY' Error_code: 1062
141006  8:56:45 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.019466' position 1568
141006  8:59:23 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.019500', position 72441800

At this point I panicked and immediately brought production down and began transferring tablespace files from master to slave.

After looking at these logs and doing some more research, I'm starting to think that was very silly of me. I'm seeing that after I fixed the original replication issue, I executed reset slave. That seems to have reset the slave's log position back. It went from using mysql-bin 2020292 at position 54986 to trying to start the I/O thread at mysql-bin.019500 at position 72441800. It now seems like the correct thing to do would have been to reset master to the last known good position before replication broke again, instead of panicking and recovering all of our tablespace data.

Thoughts? Was as I as silly as I'm thinking I am now?


texts: Can I use the "import" tool on PHPMyAdmin multiple times?

texts:
I am moving databases between a couple of shared hosting accounts, and both of them only give access to the MySQL databases through phpmyadmin.

The importing account's upload limit is 50MB. I would like to export the database in parts (by selecting only a few tables to export at a time), using the export tool, then use the import tool more than once to get around the limit. Is this possible?


texts: Paging in SQL Server: Is an order by clause required? (currently, rows are being missed)

texts:
We have some code that pages through a SQL result. (Currently running on SQL Server 2008)

We notice that when paging is done, some rows are not returned. Let me clarify this a bit:

nHibernate generates SQL queries.
We are doing paging.
If we page by 100, the way nHibernate generates the successive SQL queries is:

TOP 100  // gives us first 100
TOP 200  // gives us 2nd 100 of this block
etc

The above, without an ORDER BY / sorting at nHibernate level, has the end result that some rows never surface to nHibernate. We surmise this is due arbitrary sorting by SQL, so that rows are "moving around" within the pages (and thus "hide" from our application code).

If we do the nHibnerate query as a single shot (returning all rows), we see all the data. (This query below is generated by nhibernate.)

Will adding an order by clause (aka nHibernate sorting) help?

SELECT top 33
 ... <field list>
FROM
  salesOrder this_ left outer join [Item] Item2_ on this_.ItemId=Item2_.ItemId
WHERE this_.AccountId = @p0
and this_.ModifiedAt > @p1
and this_.ModifiedAt <= @p2


texts:
I have a database called FDB. I have created this database on my PC with SQL Server Express Edition and added one table. I made a backup of this database. The SQL Server version on the server is 2005.

I copied this backup to the server and tried to restore it, but I get this message:

  The media family on device C:\Program File\Microsoft SQL Server\MSSQL.1\MSSQL\Bacup\FDB.bak is incorrectly formatted. SQL Server cannot process this media family. RESTORE Headeronly is terminating abnormally. Microsoft SQL Server error 3241


texts: Configure sp_add_schedule to run on the first day of every X months/weeks

texts:
I need to configure my jobs scheduling dynamically.

The jobs will always run on the first day of the week once every X weeks. Same for months.

Looking at the documentation,

I see that for weeks option I would need to use freq_type=8 (weekly) with freq_interval=1 (Sunday). But which parameter should be used to mark that the repetition should be once every X weeks?


texts: MySQL tar.gz backup keeps getting bigger but MySQL folder size is the same

texts:
Our production site runs on a LAMP stack and we have a daily mysql backup that produces a tar.gz file. I recently reduced the size of some tables by deleting old rows after 3 months rather than 6, and the daily tar.gz file got bigger. The next day I removed an inefficient TEXT field and replaced it with a VARCHAR(3) field...and the daily tar.gz file got bigger. After unpacking into folders, all the individual files and folders (including the ibdata file) in the daily backups appear to be the same size - but on a day by day basis the tar.gz files get ~14mb bigger each day. According to the table size info I get from MySQL, I have reduced the size of my db.

Nothing is actually broken here - site is running fine and the db is responsive - but I cannot find any explanation as to why my backups keep getting bigger even though the db is getting smaller and it's frankly doing my head in. If anyone can offer an explanation that would be great.


texts:
PostgreSQL 9.5.16

I'm writing a query which contains several aggregate columns and many ungrouped columns. I've condensed the core logic.

SELECT min(table_b.aggregate_column),
table_a1_key,
table_a2_key,
table_a1_column
FROM api.view_a
JOIN api.table_b USING (table_a2_column)
GROUP BY table_a1_key, table_a2_key;

view_a joins table_a1 and table_a2 and exposes table_a1.table_a1_key + table_a2.table_a2_key. But I get this error when executing:

ERROR:  column "view_a.table_a1_column" must appear in the GROUP BY clause or be used in an aggregate function

Am I required to include other columns in the GROUP BY clause, even though they are functionally dependent on my grouped columns?

From docs:

  When GROUP BY is present, or any aggregate functions are present, it is not valid for the SELECT list expressions to refer to ungrouped columns except within aggregate functions or when the ungrouped column is functionally dependent on the grouped columns, since there would otherwise be more than one possible value to return for an ungrouped column. A functional dependency exists if the grouped columns (or a subset thereof) are the primary key of the table containing the ungrouped column.


texts: Is there a way to email SSRS execution reports

texts:
I have an SSIS package that runs daily, and we are now starting to add error handling into it.

However, I was wondering that if instead of adding email tasks into SSIS to notify us of errors, we could simply have the execution report of the job sent to us via email, preferably only if the job failed. Is this possible?

EDIT: Actually it would be really useful if I could email the report if the Job succeeded but there has been a warning (e.g. no files found to process at a given step). Is that possible, or would I have to add such tasks in the SSIS package itself?


texts:
I am aware of Brent's post Why Nobody Ever Patches Their SQL Servers in this regard.....and with one of my friend, please assume one such scenario, where nearly 100 SQL Servers are never patched. Assume that no bugs or performance issues have ever been reported by any user. So, is there a cool way for my friend to identify across all the servers - where he is unaware - and few of the servers may already be hitting some bugs? In other words, how to pro-actively find the bugs across 100 SQL Servers?

One plan is to read release notes of those bugs where bug could be identified using a server trigger on occurrence basis (may be) .... capture that in local tables....SSIS those at a central location.

But, does this plan seem worth?

Special interest on those bugs where some impact on data by any chance. (FYI, DBCC CheckDB with purity runs error-less). Or some possible loss to business.

And finally assuming that, indeed, some bugs detected in some SQL Servers - so, how and why to treat them as "threat" when no issue reported by users ever from many years?


texts: C# Windows Service task fails due to a SP timeout, but works fine after executing the SP in SSMS

texts:
I have a Windows Service app running and sometimes a task which has a method call to a stored procedure (with a Select operation) is keep failing due to a timeout.

But when I execute that SP with same parameters in the SSMS directly, at the first time it takes a while to complete (80 seconds) but then the Windows Service app task starts to work fine.

I checked the actual exection plan, and it shows a 90% cost for a Clustered Index Scan (To a table with 180 million rows).

What I can do to stop this failure without executing the SP in the SSMS every time?


texts: 250 billion rows - computing interconnectedness on a really huge scale

texts:
I have a table of approximately 700k rows, each identified by a unique itemnumber.

Each row/item can be associated with any of the other rows/items in the table by calculating a single numerical value indicating the strength of the association or the mathematical "distance" between two items, with no association/infinite distance represented by "0" while the numerical value "1" indicates the same item/no distance.

These association numbers are difficult to calculate/computing intensive and are based on data stored in a separate database. Therefore, precalculating them once for all row combinations, and then just for new rows as they are added (<4k new rows per year) seems to make sense.

The resulting table of associations might look like this:

itemnumber    |  associatedwithitem        | associationstrength

23920390293   |   12356456885              | 0.12255888644888

45468411516   |   44565464884              | 0.91155684161123

45648855222   |   98956221818              | 0.00000000000000

45468411516   |   23920390293              | 0.46813185844468

The size of such a table, however, would be immense:
((700,000 x 700,000) - 700,000)/2 = 244.999.650.000 = approximately 250 billion rows,
even after throwing out all self-connections (-700,000 in formula) and storing each connection only one-way (the divide by two in the formula).

I will be running only one type of query as follows:

"Given a list of itemnumber (see table example above), calculate the average (mean) association number."

Each list of itemnumber run against the association table will generally contain < 1k itemnumber, but may rarely be as large as 50k. But because each number in a list of 1k itemnumber will be associated with 700k other itemnumber, such a query would extract 700,000 x 1000 = 700,000,000 association numbers and would then need to have the mean of those 700m association numbers calculated.

Any ideas for the following:

Best data management system to hold this table
Structure (250 billion rows vs 700k rows with blob containing association data for each)
Best way to extract data and calculate means

Any input would be helpful.


texts: If I add an additional column to handle special values, how will this impact my table size?

texts:
I have a table TABLE1 with 400,000 row (by now) that has 20 columns.
I have a column DETAIL that usually stores 100 to 300 characters, that's why its size is 1024, but I have an special case. I need to store 1900 chars for one row only.
The solution that I found is creating another column, DETAIL2 to store remaining characters, but I'm afraid that this will cause my table size to increase a lot (just one row would have DETAIL2, others will be NULL).
Is this the right solution or is there a better way to store the special case?


texts: How Restore backup as overwrite on a database in use in SQL Server?

texts:
I Want to restore a backup with overwriting on a database in use
for this problem I want to create a query base on these steps :
1- Remove All Connections [MyDB]
2- Create a backup from [MyDB]
3- Restore a Backup as an Overwrite on [MyDB] from specifying a path
Thank you very much for your help


texts:
As per below thread from SQL Server: Can I log ship from SQL Server 2014 --> 2016?

  Always log ship UP as it's a basic supported upgrade process. you can't log ship DOWN. The question you might be concerned with is how do you roll back and not lose data if you need to go back to 2014 as you can't restore 2016 DBs to 2014

We need to set this up for our environment to log ship from older version to newer one, i.e. from 2008R2 to 2016 and getting following error:

Error: This backup cannot be restored using WITH STANDBY because a database
upgrade is needed. Reissue the RESTORE without WITH STANDBY.

Would you please advise whether this can be done at all? If so, what we are supposed to do to get it working?


texts: SQL query for next occurrence of specific date

texts:
I am working on a function for our database that contains contract data. Each contract expires on a certain date, and then each contract has an expiration type that determines what happens after that primary expiration date.

For example, one expiration type is Year-to-Year. So, if the contract expires on 6/1/2016, after that date, the contract again comes up for expiration on 6/1/2017. My query that handles this expiration type is as follows:

SELECT next_dt
  FROM (SELECT add_months('1-JUN-2016', rownum * 12) next_dt
      FROM all_objects
     WHERE rownum <= 500
     ORDER BY 1)
 WHERE next_dt > trunc(SYSDATE)
   AND rownum = 1;

However, I have one expiration type that says after the initial expiration, the contract again expires the next June 1 or December 1. So, if the contract initially expires on 10/1/2015, the contract comes up for expiration again on 12/1/2015. If it expires on 12/5/2014, it comes up for expiration again on 6/1/2015.

I'm struggling with how to do this. Again, this is a function so we're not limited to a single query to return the desired result.

Any help would be appreciated. Using Oracle 11g.


texts: Creating plan guide for query called through sp_executesql

texts:
To make a long story short, I have a view called vwRelatives which uses CTE recursion to build family trees. It's meant to be queried for a single person at a time.

This runs in about a quarter second:

SELECT * FROM vwRelatives WHERE person_id = 5

This (the way the query is executed from the application) takes more like 4.5 seconds:

exec sp_executesql N'SELECT * FROM vwRelatives WHERE person_id = @P1',N'@P1 int',5

(Note that I've simplified the query a bit. The real thing has an explicit column list and an ORDER BY, but the WHERE semantics are the same. I get the same symptoms with either version.)

Most likely, SQL Server is able to take person_id = 5 into account when creating an execution plan for the first query, but parameterizing it is causing the whole view to be run and then filtered by person_id.

So I figured I'd create a plan guide. And now I have two problems.

These are the steps I'm taking, which appear to have no effect.

First, run the 'good' query to get it into the plan cache...

SELECT * FROM vwRelatives WHERE person_id = 5

...then perform the standard steps to turn it into a plan guide...

--Get the 'good' plan
SET @xml_showplan = (
    SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
        CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
        WHERE st.text LIKE N'SELECT * FROM vwRelatives WHERE person_id = 5'
)

--Apply a plan guide to the meat of the sp_executesql query
EXEC sp_create_plan_guide
    @name = N'vwRelatives_Test_Plan_Guide',
    @stmt = N'SELECT * FROM vwRelatives WHERE person_id = @P1',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 int',
    @hints = @xml_showplan;

This completes successfully, but when I run the original sp_executesql statement again, it still takes 4.5 seconds. I have Profiler running, and both the Plan Guide Successful and Plan Guide Unsuccessful events are selected. Neither of those events ever shows up in the trace.

What am I doing wrong that's preventing SQL Server from seeing this plan guide as a match for the sp_executesql query?


texts: MySQL: Optimize query with a range and distinct count

texts:
I have a query on a large table with millions of rows that looks like this:

SELECT
    COUNT(
        DISTINCT clicks.tx_id
    ) AS unique_clicks_count ,
    clicks.offer_id
FROM
    clicks
WHERE
    clicks.offer_id = 5
    AND created_at > '2014-11-27 18:00:02'
;

Created_at is a timestamp. I have a compound index on (offer_id, created_at) which gets used. The following is the explain:

|  1 | SIMPLE      | clicks | range | clicks_created_at_index,clicks_offer_id_created_at_index | clicks_offer_id_created_at_index | 8       | NULL | 215380 | Using index condition; Using MRR |

Keeping in mind the range, what kind of index would I need to be able to count the distinct tx_id's efficiently, most likely which covers tx_id as well?
What would the index look like without specifying clicks.offer_id = 5, and instead doing GROUP BY offer_id?


texts:
I have a table like this:
person_id food   data
1         bread   xxx
1         bread   xxx
1         fruite  xxx
2         bread   xxx
2         fruite xxx

food can have only three values (bread, fruits), I want to get the percentage of bread each person has eaten, and there percentage of fruits each person has eaten.
I tried to do:
select count(*) from table
group by person_id, food

and that gives me:
player_id, food, number

but how can I continue from now?
I am on hadoop


texts:
I have a set of .csv files many times larger than my hard drive which I would like to load into database tables using postgresql.  (I must keep it locally on an external hard drive due to Data Use Agreement restrictions.)

I would like to tell Postgresql to store the tables I load on the external drive, due to size constraints.

I think I can do this with initdb -D '/Volumes/My Book/2020/', where /Volumes/My Book/2020/ is the path to the folder on the external drive.

I get the error: FATAL:  data directory "/Volumes/My Book/2020" has invalid permissions 2021-01-23 11:21:58.864 EST [4390] DETAIL:  Permissions should be u=rwx (0700) or u=rwx,g=rx (0750).

But ls -l "/Volumes/My Book/" returns (among other lines) drwxrwxrwx@ 1 my_username  staff  1048576 Jan 19 13:29 2020 which looks like the right set of permissions to me.

I tried sudo chown my_username "/Volumes/My Book/2020/", but I get the same error as before.

I tried sudo chmod a+rwx "/Volumes/My Book/2020/", and still get the same error.

Can anyone advise me about how to set the permissions correctly?  (Or failing that: maybe I am taking the wrong approach to this problem?)
Thanks!


texts:
I am facing Memory issue in our server. While debugging I came to know that, many number (millions) of temporary tables are created. Is there any way to identify which databases are creating this temporary tables? We have around 50 databases and many of the databases using create temporary table statement in its procedures. In /tmp directory I could see the .ibd, .frm and few .MYI & MYD files. Is it possible to find which database contributing more to creation of temporary tables? Please let me know, if you need any other information.

Thanks in advance.


texts: How to recalculate and adjust the entire row of time in and time out in sql server?

texts:
In SN = 1, I have an original schedule of 09:15:59 with my first time in is 09:50:57 so that I am late about 34 mins. In SN=2, 13:27:05 is my time out. In SN=4, I time in again of 15:40:13 that gives me a missing time of 133 mins as you can see in SN=3. My problem now is how can I readjust or update all the table row I insert an official business time between 13:30:00 AND 15:00:00 like the output in table below...

This will be the output if I insert the OB time of 13:30:00 TO 15:00:00


texts: Unable to upgrade SQL server 2008 r2 to 2012

texts:
I am trying to upgrade SQL server 2008 r2 to SQL server 2012 shows no instances are displaying for upgrade.
my SQL Instance versions is 10.53.6000.34
I followed the following steps.

Run the SQL Server 2012 Setup
choose Upgrade
passes all test
while selecting the features for upgrade, it displays none of the features I installed in 2008 r2, any one face this issue before?

thanks in advance,
Arun


texts: How to identify writeable/partitioned views?

texts:
Assuming all the necessary prerequisite requirements are met, Sql Server will make partitioned views writable. How can I identify what views are in this state? A followup question would be- does the engine make this determination at the time the view is created and store it as an attribute of the view or is it determined dynamically?

If the latter, I would assume there is no way to verify correct operation without writing to the view. Is this correct?


texts: SQL 2016 - NU/NC Index Missing From sys.partitions

texts: Is there a reason that a non-unique, non-clustered index would have 0 corresponding records in the sys.partitions view?  There is a CI on the same table and there is a record for it in the view.  My understanding is that any NCI should have at least one record in sys.partitions.  Thanks.


texts: SQL (Postgres): Subquery in WHERE clause with IN operator is slow

texts:
I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
    select tp.task_id
    from task_participant tp
    where tp.person_id = p.id )
and p.id = 1234

The EXPLAIN (ANALYZE, COSTS) generates the following result:
Aggregate  (cost=15850181.01..15850181.02 rows=1 width=8) (actual time=25155.942..25155.943 rows=1 loops=1)
  ->  Nested Loop  (cost=0.28..15849814.75 rows=146504 width=0) (actual time=973.619..25155.778 rows=143 loops=1)
        Join Filter: (SubPlan 1)
        Rows Removed by Join Filter: 286635
        ->  Index Only Scan using personxpk on person p  (cost=0.28..4.30 rows=1 width=8) (actual time=0.018..0.021 rows=1 loops=1)
              Index Cond: (id = 1234)
              Heap Fetches: 1
        ->  Seq Scan on task t  (cost=0.00..13952.08 rows=293008 width=8) (actual time=0.006..53.361 rows=286778 loops=1)
        SubPlan 1
          ->  Bitmap Heap Scan on task_participant tp  (cost=4.63..103.38 rows=26 width=8) (actual time=0.013..0.078 rows=143 loops=286778)
                Recheck Cond: (person_id = p.id)
                Heap Blocks: exact=38705207
                ->  Bitmap Index Scan on task_participantx1  (cost=0.00..4.62 rows=26 width=0) (actual time=0.007..0.007 rows=143 loops=286778)
                      Index Cond: (person_id = p.id)
Planning Time: 0.186 ms
Execution Time: 25155.994 ms

I don't understand why the sub select is being executed 286778 times. I think that Postgres should execute the subselect only 143 times, because the condition is clear based on the outer query. What am I missing?
EDIT:
It is a generated query. The desired result (and performance) could also be achieved with:
select count(*)
from person p
join task_participant tp on tp.person_id = p.id
join task t on t.id = tp.task_id
where p.id = 1234

However, I cannot easily rewrite the current query to this better approach. I'm hoping to find a way to achieve a similar execution time without completely changing the query.


texts: SQL Server transaction log growing without control

texts:
We have a database growing uncontrolled and filling the hard drive every 2 days. Database maximum log size is set to 6 gb but id doesn't seem to be working because it continues growing beyond 6 gb.

The only way to "solve" it somehow is backing up and truncating after that.

First, wow's that possible database continues growing if maximum log size is set to a specific size?

Second, if transactions are opened and closed properly, we know it's working well, why my log file grows uncontrolled?


texts:
I'm in the process of migrating a set of databases from code set ISO8859-1 to UTF-8, STRING_UNITS=CODEUNITS32. I suspect that quite a few indexes will no longer fit in there current tablespace page size (SQL0614N). Is there a formula that can be used to determine the minimum page size for each index?

I've found the following article by Serge Reilau:

https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/getrowsize?lang=en

that I guess I can modify to determine the size of the index, but I'm not entirely sure whether the same formula holds for indexes.

Any pointers?


texts: Any gotchas at all with converting from MyISAM to InnoDB?

texts: I'm ready to move from MyISAM to InnoDB but wanted to know if there was a full list of things to look for? For example, I haven't seen any list mention that running DISABLE KEYS on an InnoDB table will throw a warning, except the manual page for ALTER TABLE. It's that kind of thing I need to know about before converting over. I thought I'd be fine with my queries but apparently not.


texts: What happens on the server when a MySQL client connection is lost?

texts:
Imagine the following situation:
You have a MySQL server S and two clients, A and B.
Both clients connect to S (e.g. using the CLI).
A starts a transaction and then locks a table with WRITE permissions and inserts a row into the table.
Now B tries to select all rows from the table and - of course - has to wait until A has committed AND unlocked the table.
Imagine that A's connection is interrupted before it is able to commit or rollback. In my special use-case, the connection might be interrupted at any point in time.
I tested this scenario using three virtual machines which are connected to a common virtual bridge. Using brctl I can simply remove individual VMs in order to simulate connection interruptions.
The result
Client B is stuck, because it waits for the table to get unlocked.
Typing SHOW PROCESSLIST in the CLI of the server shows me  this:
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+
| Id | User | Host            | db       | Command | Time | State                           | Info                       | Progress |
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+
|  8 | root | B:38129         | foo      | Query   | 1676 | Waiting for table metadata lock | select blockname from root |    0.000 |
|  9 | root | A:54806         | foo      | Sleep   | 1679 |                                 | NULL                       |    0.000 |
| 12 | root | localhost       | NULL     | Query   |    0 | init                            | show processlist           |    0.000 |
+----+------+-----------------+----------+---------+------+---------------------------------+----------------------------+----------+

So the server thinks that the connection from A is still alive, while it already was lost minutes ago. Having a look at netstat also tells me that the connection is still considered to be ESTABLISHED and still alive.
I also observed the bridge: Not a single packet from S to A.
Client B is stuck forever. I have to restart the whole server to unlock the table.
What I expected
I want my server to detect (after some predefined time) that the connection to A is lost even if A did not send any more queries. Then I want my server to rollback any uncommitted transactions and finally to unlock the table.
How can I realise this?
Why aren't there any TCP keepalives from S to A?
Is it possible to define any kind of timeouts?

I played around with the variables wait_timeout, innodb_lock_wait_timeout, and interactive_timeout.

interactive_timeout is not interesting because my productive system will not use the CLI.
innodb_lock_wait_timeout seems not to be respected in any way.
wait_timeout indeed improves my situation: idling sessions are killed after the timeout. But I only want them to be killed in case of connection interruption. I want to keep the connection alive to improve performance (save tcp handshakes, SQL logins, db selection...)


texts: Using pgsql_fdw in a function result “ERROR: cache lookup failed for type 0”

texts:
Now we want to use pgsql_fdw to select a table of  remote postgresql database，When we
select the table in a session it is okey ,but when we use the foreign table in a funciton
it turns out "ERROR:  cache lookup failed for type 0" ， anybody knows it ,thanks !

--1 base informaiton

skytf=> \d ft_test;
       Foreign table "skytf.ft_test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Server: pgsql_srv

skytf=> \des+ pgsql_srv
                                                List of foreign servers
   Name    | Owner | Foreign-data wrapper | Access privileges | Type | Version |                Options
-----------+-------+----------------------+-------------------+------+---------+----------------------------------------
 pgsql_srv | skytf | pgsql_fdw            |                   |      |         | {host=127.0.0.1,port=1923,dbname=mydb}
(1 row)

--2 destination table
mydb=> \d test
             Table "mydb.test"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 id     | integer               |
 name   | character varying(32) |
Indexes:
    "idx_test_1" btree (id)

--3 function
 CREATE or replace FUNCTION  func_sync_bill() RETURNS INTEGER  AS $$
    BEGIN

     begin
      insert into test_tf (id,name) select id,name from ft_test;
      return 1;
     end;
    END;
  $$ LANGUAGE 'plpgsql';

--4 it works in a session
skytf=> create table test_tf(id integer,name varchar(32));
CREATE TABLE

skytf=> insert into test_tf select * from ft_test;
INSERT 0 1990000

--5 function call error
skytf=> truncate table test_tf;
TRUNCATE TABLE

skytf=> select func_sync_bill();
ERROR:  cache lookup failed for type 0
CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
PL/pgSQL function "func_sync_bill" line 5 at SQL statement

when I call the function func_sync_bill() which will select a foreign table , it turns out the error.
  Is this a bug of pgsql_fdw?

--verbose meeage

    skytf=> \set VERBOSITY verbose
    skytf=> select func_sync_bill();
    ERROR:  XX000: cache lookup failed for type 0
    CONTEXT:  SQL statement "insert into test_tf (id,name) select id,name from ft_test"
    PL/pgSQL function "func_sync_bill" line 5 at SQL statement

LOCATION:  getTypeOutputInfo, lsyscache.c:2441


texts: Why does max_connections in my.ini not match concurrent connections in Mysql instance config wizard?

texts:
MySQL 5.1.x | Windows Server 2003

Can someone please clarify why max_connections in my.ini is much larger than the manual value I specify in the Concurrent Connections Dialogue?

For example, if I set concurrent connections to 800 in the dialogue window, I see max_connections=1023 in my.ini.  Why? Is this normal?


texts: Insufficient privileges as a definer of package

texts:
I cannot run a procedure within a package with a user that also defined it unless I specify "authid current_user" for some reason. Here is my original code that produces insufficient privileges error

create or replace package hr.table_pkg
is
    procedure make ( p_table_name varchar2, p_col_specs varchar2);
end  table_pkg;

create or replace package body hr.table_pkg is

  procedure  make  ( p_table_name varchar2, p_col_specs varchar2) is
    sql_stmt varchar2(32767);
    begin
      sql_stmt := 'Create table '|| p_table_name||' ('||p_col_specs||')';
      dbms_output.put_line(sql_stmt);
      execute immediate sql_stmt ;
  end make ;
end table_pkg;
/

So the user that defines the package is HR. when I query view USER_OBJECTS I can see the package and the body aswell

but when I, as the HR user try to run

execute table_pkg.make('my_contacts','id number(4), name varchar2(40)');

I'm gonna get an error that I cannot create the table. It also does not work when I change the procedure in the body to create the table in a specific schema HR:

sql_stmt := 'Create table hr.'|| p_table_name||' ('||p_col_specs||')';

the call to the procedure only works when I recompile the header of the package to

create or replace package hr.table_pkg
authid current_user
is

Can please anyone explain to me what am I missing, I do not get why the definer ( HR user) cannot run the procedure with definer rights

Thanks a lot!


texts:
I have two tables:

Employee Table with columns employee_id (primary key) and employee_name.
Company Table with columns company_id (primary key) and company_name.

The companies allows its employee to work for other companies. So an employee can work in many companies and a company can have many employees(MANY TO MANY relation).

Say I have 3 employees and the companies they work for with the respective start and end time for a day.

employee_name | company_name | hours they work |
Akash            A               09:00 - 11:00
                 B               12:00 - 02:00
                 C               04:00 - 07:00

Sunny            D               09:00 - 11:00
                 C               12:00-  04:00
                 D               05:00 - 07:00

Vishal           B               09:00 - 12:00
                 A               12:00 - 05:00

How should I design the database?
How do I find the employee who worked the most hours for a given company?


texts: Parent Child Relation in same Table . Select Query to get only parent records and Records not comes under parent child relation

texts:
I have a table with Parent-Child Relation in the same Table. Some Sample Data

  id │ name                 │ parent │ is_active
 ════╪══════════════════════╪════════╪═══════════
  1  │ Company A            │ null   │ true
  2  │ Child of A           │ 1      │ true
  3  │ 2Child of A          │ 1      │ true
  4  │ 3Child of A          │ 1      │ true
  5  │ 0 Single Company     │ null   │ true
  6  │ 1 Single Company     │ null   │ true
  7  │ 2 Single Company     │ null   │ true
  8  │ Parent Company B     │ null   │ true
  9  │ 0 Child Company of B │ 8      │ true
  10 │ 1 Child Company of B │ 8      │ true
  11 │ 2 Child Company of B │ 8      │ true
  12 │ 3 Child Company of B │ 8      │ true

I want to write two queries

get all the parent records (only parent records)
Example: id 1,8
Get all the single records (only records does not come under parent-child)
Example: 5,6,7

Please help to write select queries.


texts: What are some best monitoring tools available for mysql?

texts: I have a setup a linux mysql server and would like to monitor it. I found percona plugins for nagios and cacti. Are there any other tools available?


texts: Can multiple instances of procedure create same #Temp simultaneously?

texts:
I have a procedure which inserts data into #temp table.
After performing certain manipulations to the data, it stores data from #temp table to some other table.
I cannot run multiple instances of this procedure simultaneously. Why?
We create #temp table with the same name in multiple tabs. Then why shouldn't we be able to do the same for multiple instances of temp table?
Any solutions?
Example:
--exec dbo.testing_temp_table
CREATE PROCEDURE [dbo].[testing_temp_table]
AS
BEGIN

    SET NOCOUNT ON;

    -- Insert statements for procedure here
    IF OBJECT_ID('tempdb..#temp_1') IS NOT NULL drop table #temp_1
    select  * into #temp_1 from dbo.Emp

END


texts: Spatial Index ignored when using additional non-spatial conditions

texts:
Why does SQL Server ignore my spatial index when I use additional conditions? If I add an INDEX hint it will use the correct index, and runs a lot faster. I would like to avoid using the hint if possible.

Here is my SQL:

        PRINT CHAR(13) + 'Distance (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (No Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK)
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

        PRINT CHAR(13) + 'Distance + Gender (Hint)'

        SET STATISTICS TIME ON;

        SELECT p1.profileId, p2.profileId
        FROM profiles AS p1 WITH (NOLOCK)
            LEFT JOIN dbo.profiles AS p2 WITH (NOLOCK INDEX(IX_location))
                ON p1.location.STDistance(p2.location) < 50 * 1609.344
                AND p1.isMale = ~p2.isMale
        WHERE p1.profileId BETWEEN 1000 AND 1010

        SET STATISTICS TIME OFF;

And here are the results:

Distance (No Hint)

(2206 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 295 ms.

Distance + Gender (No Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 3323 ms,  elapsed time = 9183 ms.

Distance + Gender (Hint)

(1000 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 234 ms,  elapsed time = 307 ms.


texts: How can I forward odbc/oledbc connection through DMZ

texts:
I have an Oracle database separated from our BI system by two network zones (different sides of a DMZ). Company policy dictates that firewall openings may only pass to an adjacent zone. Therefore it is not possible for the BI system to directly talk to the Oracle database.

What is the best way to get the information to the BI system? Our enviroment is (almost) entirely Windows-based.

I have thought of adding a server inside the DMZ to forward the data from the Oracle database to the BI system, something like an ODBC/OLEDBC proxy, however I have found no software that does this.

Another solution might be to translate database requests using some kind of Web API, but I have found no such solutions either (other than building it myself, which I would really like to avoid). Is there any such software available for free or purchase?


texts: Does it make sense to order condition in where query?

texts:
We have a clustered index in table Tbl with order of A,B,C.
Does it make senses to write a query to have

WHERE A = @a, B = @b, C = @c

or it'll be as same as

WHERE C = @c, B = @b, A = @a

Does the order of query make sense for clustered index?


texts: MySQL - INSERT DELAYED <- deprecated What now?

texts:
I've just been looking at the MySQL doc's https://dev.mysql.com/doc/refman/5.7/en/insert-delayed.html

I was intending to use an INSERT DELAYED, but I see that this has been deprecated.

I still need this functionality for compatibility with some PHP code that is handling some very large data transfers. I'm quite happy to update my PHP but I have no idea how to achieve the same functionality now.

How would we achieve the same outcome as traditional INSERT DELAYED now that it's deprecated?


texts: Query that can benefit from index, cluster or hash-cluster

texts:
I'm trying to find a equi-join query that shows a decent performance bump when I use an index, cluster or hash-cluster structure on my data. I initially need to run the query on unstructured data first and the execution time should be significant so that I can see a performance boost with any of the 3 structures. The issues I am having is that if I use a query that utilizes an index column the search is too narrow and so I get too few rows returned and then my baseline query's execution time is too fast so I can't measure the time later.

It seems the only queries that have any major effect on my baseline queries execution time are those that cause a full table scan of the table with the most rows, but then that makes using an index structure useless as it won't use the index. Do clusters or hash-clusters benefit from full table scans - in general I don't know what queries benefit from clusters/hash-clusters.

My table has 500,000+ rows and some of the queries I have tried:

SELECT c.Cust_name, s.total_price
FROM Sales s, Customer c
WHERE s.Cust_id = c.Cust_id
ORDER BY c.Cust_name;

SELECT count(*)
from Sales s, Customer c
WHERE s.Cust_id < 500
    AND s.Cust_id = c.Cust_id;

SELECT c.Cust_name, s.total_price
FROM Sales s, Customer c
WHERE s.Cust_id = c.Cust_id
    AND c.Cust_name LIKE '%A';

The last query was returning 34k rows and was still forcing the hash-join and full table scan over using the index. Is there any query I can run that is best suited for either indexes or clusters such that without these structures I would get a slow execution time on the baseline?


texts: Logging Postgres startup errors on Windows

texts:
I have been experimenting with Postgres configuration parameters and noticed that when I use settings that prevent Postgres from starting nothing shows up in the pg_log directory logs. I have

log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'

Is there a way to cause Postgres to log startup errors to pg_log on Windows 7 and Postgres 9.3?


texts: Implenting Min Max memory configuration in Alwayson

texts:
I have never changed this setting for an AlwasyOn setup. What is the best practise to do this? Should I change the setting on the Secondary node first and then the primary? Please suggest . The current setting is set to the default max value and that is something not which I want. Each of the server has 24gb of memory. The total size of the databases on each node is 105gb

Thanks


texts: Can PostgreSQL databases be attached/detached on the fly?

texts:
Is it possible to attach/detach databases to PostgreSQL on the fly, i.e. while the server is running without having to restart the service? I can't find anything related to this in the documentation.

What I am trying to achieve is to have the user be able to select different data directories from an archive location and then have them loaded into PostgreSQL, and later removed when the user is finished.


texts: SSRS reports resets the parameters to default after I click on "View Report"

texts:
We are using SSRS version 16(build 13.0.x) in Native mode.
I built a report using Report Builder with 2 parameters(independent). Everything works as it should when I run the report in Report Builder.
When I publish it to WebPortal - some random set of users have the following issue:

Through WebPortal URL, they click on the report to open in browser, we have always used chrome.
They populate the parameters and click on "View Report".
The report starts "Loading" and immediately the parameters are reset to default(or blank) and the report area goes blank. They couldn't even see the column header or the menu bar in the report area.

Apart from a handful of seemingly random users, everyone else have no issues.
We use SQL accounts for authentication and all users have permissions to fetch data from tables. I checked if this issue is only with certain kind of parameters, but there seems to no common denominator.
I've come up dry on PowerBI, Technet forums. I appreciate any help/troubleshooting steps. Thanks :)


texts:
I’m a DBA that is fairly new to SQL Server coming over from a UNIX/Informix.

I have a question about tempdb growth:

From what I can see on regular databases if I start with a database that is 10 GB and then put in a bunch of data into it to make it 100 GB, and then delete 50 GB out of it I have to perform a shrink to reclaim space on the hard drive. Until I do that the space is allocated to the database

How does the tempdb work?

If an initial size is 10 GB and then some query grows tempdb to let’s say 100 GB and then terminates does the space become available for future tempdb queries/temp tables etc..?

Or does it release the space to the operating system?


texts: How to create Polygon from Point datatype in MySQL?

texts:
I have a table like this:

CREATE TABLE `aois` (
  `aois_id` int(11) NOT NULL DEFAULT '0',
  `WS_A` point DEFAULT NULL,
  `WS_B` point DEFAULT NULL,
  `WS_C` point DEFAULT NULL,
  `WS_D` point DEFAULT NULL,
  `DB_A` point DEFAULT NULL,
  `DB_B` point DEFAULT NULL,
  `DB_C` point DEFAULT NULL,
  `DB_D` point DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now i want to create a polygons from the points:

SELECT aois_id,
polygon((WS_A, WS_B, WS_C, WS_D, WS_A)) as geom
FROM aois;

But i always get the error message:

Operand should contain 1 column(s)

(I'm using MySQL Server Version 8.0.16 on Windows 10, if that is of any importance.)

Can anyone point me in the right direction?

Thanks a lot!


texts: Installing SQL Server 2012 SP2 on instance with Cumulative Update #10

texts:
We have Cumulative Update #10 installed on SQL Server 2012 SP1 that we need because of a specific fix to DQS.

Since the recently released SQL Server 2012 SP2 only contains fixes from CU1 through CU #9, if we decide to update to SP2, will that overwrite and remove the fixes from CU #10? And if so, could we re-apply CU#10 to it? Or would we have to wait until another CU is made for SQL Server 2012 SP2?

http://support.microsoft.com/KB/2958429
http://support.microsoft.com/kb/2954099


texts: SSAS Tabular model Date Dimension with Time possible?

texts:
I am in the process of building my first SSAS Tabular model and thought everything was going well, until trying to create a measure by DateTime.

In my warehouse, I have a DIm_Time dimension, which has a DateTime column, with a row for every 5 minutes for the last 2 years.
5 Minutes is the granularity that we require. I addition, there is a TimeID Identity column on the table. IN my ETL, I assign a Time ID to each fact table depending on what 5-minute range it slots into.
So the end result, is a relationship between the 2 tables on the ID, with let's say 10 facts per TimeID.

Now I am trying to do a simple count of rows per time range. For example, how many facts for the current hour. The problem I'm finding is that I am seeing no data in either PowerBI or Excel when testing my measure, and I'm 90% sure it's related to the fact that my model is filtering my DateTime as a date when calculating the measure.

Fcts by Date:=
CALCULATE ( COUNTA(Fct_Table[IDColumn]), Dim_Time[DateTime])

Please, can someone help point me in the right direction as I am struggling to find anything with regards to working with a DateTime dimension.
Thank you very much.

I am, using SQL/SSAS 2017


texts: Connecting to MySQL database from Analysis Services

texts: I need to know whether it is possible or not to get data from a MySQL database and create a Cube in Analysis Services 2008. If so, tell me how to connect to MySQL Database from Analysis Services.


texts:
I have a really hard SQL question! I've inherited a system that allows people to enter dog shows. I have inherited the database with its present architecture, and since there are office systems that rely on the database, I can't unfortunately change the architecture.

I have the following tables:

users
event
event_rules

The first two tables are self explanatory. The third table ('event_rules') contains the following columns:

event_id,
targetTable,
targetField,
allowableValues,
min_value,
max_value

The table basically records rules stating who can and can't enter a sports event. There may be more than one rule per event. So an example from the table might be:

__event_id__|__targetTable__|__targetField__|___allowableValues___|__min_value__| __max_value__|
      1     |     users     |      age      |                     |     12      |              |
      1     |     breed     |      name     | labrador, chihahua  |             |              |
      1     |     dog       |      age      |                     |             |      6       |

So for the above event, the rules are that the dog handler must be over 12 years old, the dog must be a labrador or chihahua and the dog must not be older than 6.

All the relations are in place, so for example, dog.breed_id=bred.id and so forth. I can pass in all required variables.

I want to know, for a given user, which events they can enter. My problem is this: it would be computationally huge to iterate each row in order to check which events can be entered for a given set of criteria. It would also involve (in my limited knowledge) evaluating the strings in 'targetTable' and 'targetTable'  columns and then using the values as table and column names. Can anyone suggest an approach that might complete in a reasonable timeframe? The database is running on MySQL but could be changed to PostgresQL if required.

I'm afraid I can't alter the existing schema as the current systems rely on it, but I could add columns or tables where required.

Do any experts out there have any suggestions?

Thanks
Matt


texts:
I have looked and cannot find how to show the last # months in a pivot table.  Everything I seem to run across codes months statically.  I'm seeking a solution that shows something similar to the below result:

The columns headers are a unique identifier for the month(year*12+monthnum).  This was the only way I could think of for progressing year and month in a linear manner.

 Partnumber 24180   24179   24178   24177   24176

 813453     0       2       4       6       3

Is this possible?  Am I thinking too hard?  Each month the columns would naturally shift, and this is where I can't figure out how to dynamically do this.

Any suggestions or assistance is appreciated.

The table holding the data looks like this:  Of course the actual table hold much more historical data for thousands of parts.

PN      Year    Month   Usage   UID
813453  2014    12      0       24180
813453  2014    11      2       24179
813453  2014    10      4       24178
813453  2014    9       6       24177
813453  2014    8       3       24176


texts:
I have 2 tables

Table A
address_id email
12341      aba@mail.com
12342      abb@mail.com
12343      abc@mail.com
12344      abd@mail.com
12345      abe@mail.com
12346      abf@mail.com
12347      abg@mail.com
12348      abh@mail.com
12349      abi@mail.com

Table B
address_id email
12342      xyz@mail.com
12344      xyy@mail.com
12349      xyx@mail.com

I want to update emails in table A with emails in Table B where the address_id matches.

I have tried the below query :

update TableA A
set email=(select email from TableB B where A.address_id=B.address_id)

But its updating all the emails to null where the address_id is not matching in TableA.

Appreciate your help..


texts: How to add a step in all existing jobs in sql server

texts:
IF (SELECT ars.role_desc
    FROM sys.dm_hadr_availability_replica_states ars
    INNER JOIN sys.availability_groups ag
    ON ars.group_id = ag.group_id
    AND ars.is_local = 1) <> 'PRIMARY'
BEGIN
   --We're on the secondary node, throw an error
   THROW 50001, 'Unable to execute job on secondary node',1
END

Set this step to continue with the next step on success, and to quit the job reporting success on a failure.

Can you  please explain how to add this step in all existing SQL server jobs?


texts: HAVING STATEMENT error SQL command not properly ended

texts:
I have updated this question sicne I have made progress, my HAVING statement is still not working correctly. I am getting a ORA-00933: SQL command not properly ended..

I believe its my HAVING clause. Any Ideas?

RECAP:

I am trying to capture progress of customers who did not have purchasing activity between 2013-2015 and track their buying activity from Oct-2015 forward to track salesman activity..

SELECT INV.COMPANY
     , INV.CUSTOMER_ID
     , INV.ADDRESS_ID
     , INV.NAME
     , INV.ADDRESS1
     , INV.ADDRESS2
     , INV.CITY
     , INV.STATE
     , INV.ZIP_CODE
     , INV.COUNTRY
     , CICM.CLIENT_VALUE as contact_type
     , CICM.VALUE
     , CICM.DESCRIPTION
     , INV.REGION_CODE
     , INV.SITE
     , LD.LSTINVDATE
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END AS TWO_YEARS
     , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-15') AND SYSDATE THEN SUM(INV.SALES) ELSE 0 END AS PROGRESS
     , SUM(INV.SALES) AS SLSAMNT

FROM IFSINFO.HB_INVOICING_ALL INV
LEFT JOIN IFSINFO.HB_LAST_INVOICE_DATE LD
    ON (INV.COMPANY = LD.COMPANY)
    AND (INV.CUSTOMER_ID = LD.IDENTITY)
LEFT JOIN IFSAPP.CUSTOMER_INFO_COMM_METHOD CICM
    ON (INV.ADDRESS_ID = CICM.ADDRESS_ID)
    AND (INV.CUSTOMER_ID = CICM.CUSTOMER_ID)
WHERE INV.CUST_GRP = '4'
AND INV.COMPANY = '20'
HAVING SUM(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') = 0
--HAVING MAX(LD.LSTINVDATE) < (SYSDATE - INTERVAL '2' YEAR)
GROUP BY INV.COMPANY
       , INV.CUSTOMER_ID
       , INV.ADDRESS_ID
       , INV.NAME
       , INV.ADDRESS1
       , INV.ADDRESS2
       , INV.CITY
       , INV.STATE
       , INV.ZIP_CODE
       , INV.COUNTRY
       , CICM.CLIENT_VALUE
       , CICM.VALUE
       , CICM.DESCRIPTION
       , INV.REGION_CODE
       , INV.SITE
       , LD.LSTINVDATE
       , INV.INVDATE


texts: how can I ensure database connectivity on a spotty network? (and allow for entirely offline connectivity)

texts:
My place of work has a pretty spotty network... we have quite regular (though usually brief) outtages.

Not too long ago, my team was logging application data to a SQL Server owned and managed by IT (I am a programmer outside of IT, so I don't control what they do)... some time ago, due to the spottiness of the network and this SQL Server in particular, the applications got changed to point to an instance of MySQL.  The data getting stored in MySQL then gets sent to the SQL Server periodically when we can ensure connectivity.

This has improved reliability greatly, however, there are still some annoying blips due to the spotty network issues...

Due to the poor network conditions, and the desire to potentially operate software in an entirely offline state, I have been tasked with looking into local database options...

We are using MySQL currently... I am entirely unaware of a local MySQL option that's simple to configure.  I also don't want to be in the business of managing potentially hundreds of MySQL instances... But the data we are recording locally will eventually be migrated to either a MySQL database or SQL Server.

What options are out there for a very lightweight/easy to configure database?  What about options for easily migrating this data without a lot of overhead?

Honestly the only thing that comes to mind is to have a bunch of local databases (potentially something like SQLite), and run scripts that talk to the local databases on some given frequency to push the data out to a database server on the network, but this seems like a lot of overhead that we'd need to manage...

I'm afraid this whole setup is feeling quite hacky and I personally feel the responsibility of the data storage should fall on the application itself (the application should know the network is down and wait until it can try to send the data again)... I also feel that more pressue should be put on IT to improve the reliability of the network and the SQL Server... But I am trying to see what options if any are available that might be simple to impement on my end.


texts: always-on availability group readable secondary disconnected

texts:
I have AOAG that hosts many remote sites ( more than 10 each has its own databases) databases, in a centralized data center the customer required if one site disconnected from that centralized Datacenter, to have a copy of the last database locally for reading only. I thought I could have the architecture as below the centralized data center to have like 10 AOAG on 2 nodes add a third node for each AOAG on each site respectively.
my question is: in case of disconnection occurred, I won't make failover to this node, but will this node be readable ???


texts: How comes that login with no rights cam give himself db_creator?

texts:
I just created a new login using the GUI, which generates this script:

USE [master]
GO
CREATE LOGIN [tester] WITH PASSWORD=N'tester', DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master]
GO
GRANT CREATE ANY DATABASE TO [tester]
GO

This login was granted to a person who wanted to make a new database. I didn't want to give him db_creator rights, so I used the code above. However, after he installed his application (in which the cration of a database is included), I check the login again, and now it has the db_creator server role! Somehow he has granted this to himself, and theoretically he could delete my databases now!  I've tested this 3 times to make sure what this is indeed what is happening. I've made a trigger to prevent the deletion of databases, which he can't access, but I'm still worried.

How is this even possible and how can I prevent it?


texts: Generate & Email report from Oracle Forms 11g

texts:
I generate a report in PDF through passing of parameters from Oracle Forms 11g. I have a requirement to email the same report automatically as well. Following is the part of forms where I have passed on the parameters.

pl_id := Create_Parameter_List('tmpdata1');
add_parameter(pl_id, 'FDT', TEXT_PARAMETER,  :a);
add_parameter(pl_id, 'TDT', TEXT_PARAMETER,  :b);
add_parameter(pl_id, 'VC', TEXT_PARAMETER, :e);
add_parameter(pl_id, 'SYS_DATE', TEXT_PARAMETER, :f);
ADD_PARAMETER(pl_id,'DESTYPE',TEXT_PARAMETER,'FILE');
ADD_PARAMETER(pl_id,'DESFORMAT',TEXT_PARAMETER,'PDF');
ADD_PARAMETER(pl_id,'DESNAME',TEXT_PARAMETER,'C:\Downloads\abc.pdf');

Now a copy of report is saved in C:\Downloads folder as well as its shown in browser.

I use the following procedure to send current data shown in Forms via emails.

create or replace procedure send4 (p_sender IN VARCHAR2, p_recipient IN VARCHAR2, p_subject IN VARCHAR2, p_message IN VARCHAR2)
IS
crlf         VARCHAR2(2)  := chr(13)||chr(10);
l_mailhost VARCHAR2(255) := <IP ADDRESS>;
v_connection             UTL_SMTP.connection;

BEGIN
V_CONNECTION := utl_smtp.open_connection(l_mailhost, 25);
utl_smtp.Helo(V_CONNECTION, l_mailhost);
utl_smtp.Mail(V_CONNECTION, p_sender);
utl_smtp.Rcpt(V_CONNECTION, p_recipient);
utl_smtp.Data(V_CONNECTION,
'Date: '   || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: '   || p_sender || crlf ||
'Subject: '|| p_subject || crlf ||
'To: '     || p_recipient || crlf ||
'MIME-Version: 1.0'|| crlf ||   -- Use MIME mail standard
'Content-Type: multipart/mixed;'|| crlf ||
' boundary="-----SECBOUND"'|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
'Content-Transfer_Encoding: 7bit'|| crlf ||
crlf ||
p_message|| crlf ||
crlf ||
'-------SECBOUND'|| crlf ||
'Content-Type: text/plain;'|| crlf ||
' name="file.txt"'|| crlf ||
'Content-Transfer_Encoding: 8bit'|| crlf ||
'Content-Disposition: attachment;'|| crlf ||
' filename="attachment.txt"'|| crlf ||
crlf ||
p_message|| crlf || -- Content of attachment
crlf ||
'-------SECBOUND--'         -- End MIME mail
);
UTL_SMTP.quit(v_connection);
EXCEPTION
WHEN utl_smtp.Transient_Error OR utl_smtp.Permanent_Error then
raise_application_error(-20000, 'Unable to send mail', TRUE);
END;

Can I email the Report generated directly? I tried passing the parameter 'MAIL' and 'Email address' in destype and desname but I am guessing there is some settings need to be done?

OR

How can I use the email procedure to send the said report as attachment?

OR

How can I copy the report pdf generated in user's PC to a specific directory at server? Because I have one separate procedure for email where I can mail PDFs from server directly. User has Windows PC while server is LINUX.

OR

Generate report in PDF format at server directly so I could use the procedure (#3 point) to email it?

I am posting this here in Database Administrators because I believe there needs to be a database level configuration done (developers have not been able to help). Sorry if its a wrong forum to post.


texts:
I'm new to postgres. It might sound a silly question, due to some security requirement I need to remove default superuser 'postgres' and replace it with any other super user (for example:abc) created by me.

To test this, I created a database user 'abc' and grated all superuser permission(like postgres user) to this user. However, when I'm trying to connect to postgresql prompt from my linux machine with below command:

    sudo su - abc -c "psql"

It wasn't working. I know there is a default database with postgres user 'postgres' in postgres and it's template database.
Postgres is open source and I believe, there should be a possibility to accomplish what I want to. Anyone could please let me how can I make it happen?


texts: How to speed up SQL Server 2014 Spatial functions

texts:
I have some normalized tables with about 1,2M records that I collect in a view in a SQL Server 2014 database.
Note that I have simplified the names of the tables and columns.
To illustrate I have created this query on the source tables:
SELECT geom, dateFrom, dateTo
FROM requestsTable
INNER JOIN geomTable ON requestsTable.requestId = geomTable.requestId
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

This requests returns 54 rows within 1 second.
The problem is that as soon as I extent this query with spatial functions, the query gets slow.
Example 1: Added spatial filter:
SELECT geom, dateFrom, dateTo
FROM requestsTable
INNER JOIN geomTable ON requestsTable.requestId  = geomTable.requestId
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')
AND geom.Filter(geometry::STGeomFromText('POLYGON ((-791308.53524129 5350657.6892069, -791308.53524129 6762958.489206901, 2357696.2647587 6762958.489206901, 2357696.2647587 5350657.6892069, -791308.53524129 5350657.6892069))', 25832)) = 1

This requests returns the same 54 rows, but now it takes 23 seconds.
The execution plan shows 67% at the clustered index seek on the PK of the geometry table.

https://www.brentozar.com/pastetheplan/?id=S1l1ivsBv
Example 2: Even just adding the SRID takes a lot of extra time:
SELECT geom, dateFrom, dateTo, geom.STSrid
FROM requestsTable
INNER JOIN geomTable ON requestsTable.requestId  = geomTable.requestId
WHERE (dateTo >= '2020-09-10 00:00:00' AND dateFrom <= '2020-10-09 00:00:00')

This requests returns the same 54 rows, but now in 12 seconds.

https://www.brentozar.com/pastetheplan/?id=BJS8swsSw
The geometry in geomTable does have a spatial index:
CREATE SPATIAL INDEX [spatial_idx]
ON [geomTable] ([geom])
USING GEOMETRY_GRID
WITH (BOUNDING_BOX =(440000, 6040000, 900000, 6410000), GRIDS =(LEVEL_1 = HIGH,LEVEL_2 = HIGH,LEVEL_3 = MEDIUM,LEVEL_4 = MEDIUM),
CELLS_PER_OBJECT = 16, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]

The question is: how do I speed up the spatial functions?
UPDATE
I simplified the question a bit to make it more understandable. The real tables have a lot of indices that might mess up the query optimizer. Here is the table and index definition:
https://pastebin.com/8GkSkN2h
And the query:
SELECT the_geom, graveperiode_fra, graveperiode_til FROM graveforespoergsel
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id = graveforespoergsel.graveforespoergsel_id
WHERE
 ("graveperiode_til" >= '2020-01-10 00:00:00' AND "graveperiode_fra" <= '2020-11-05 00:00:00')
 -- Adding the filter below adds a lot of time to the query
 AND "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527.96559412003 5928957.9586343, 434527.96559412003 6282033.158634299, 1214815.9655941 6282033.158634299, 1214815.9655941 5928957.9586343, 434527.96559412003 5928957.9586343))', 25832)) = 1


texts: What is the maximum number of users in MS SQL

texts:
What is the maximum number of users on a single database that one can define?

I cannot seem to find this information on Maximum Capacity Specifications for SQL Server


texts: How to perform conditional insert based on row count?

texts:
I'm using Postgres 9.3, and I need to prevent inserts into a table based on a count of specific rows already in the table. Here's the table:

                                      Table "public.team_joins"
     Column      |           Type           |                            Modifiers
-----------------+--------------------------+---------------------------------------------------------
 id              | integer                  | not null default nextval('team_joins_id_seq'::regclass)
 team_id         | integer                  | not null
Indexes:
    "team_joins_pkey" PRIMARY KEY, btree (id)
    "team_joins_team_id" btree (team_id)
Foreign-key constraints:
    "team_id_refs_teams_id" FOREIGN KEY (team_id) REFERENCES teams(id) DEFERRABLE INITIALLY DEFERRED

So, for example, if a team with id 3 only allows 20 players, and SELECT COUNT(*) FROM team_joins WHERE team_id = 3 is equal to 20, no players should be able to join team 3. What's the best way to handle this and avoid concurrency issues? Should I use a SERIALIZABLE transaction to insert, or can I just use a WHERE clause like this in the insert statement?

INSERT INTO team_joins (team_id)
VALUES (3)
WHERE (
  SELECT COUNT(*) FROM team_joins WHERE team_id = 3
) < 20;

Or is there a better option that I'm not considering?


texts: Estimated hardware requirement for a planned "mostly-write" database

texts:
I was asked to create a server application that needs to receive and store relatively simply messages (about 7 fields, uints, strings and a timestamp), but a lot of them and at a high frequency. The messages fly in at an average rate of about 100 messages per second, with peaks going up to about 400 per second. The database will mainly consist of one large archive table and some reference tables with station names etc.

As I do not have experience with Postgres, I'm having a hard time defining the required hardware for the use case, is there some rule of thumb or some lookup table that you know of? Of course, bigger and faster is always "better", but I would like to get a feeling for what will do the job with a good performance and reserve without going directly to the top of the shelf.

Here are some more details:

The data need to be accessible for at least 5 years before it is acceptable to export and remove them from the DB
the data need to be filtered by time ranges and some additional fields
Search and filtering results should be reasonably fast (from a GUI user's point of view who knows nothing about the amount of data), so maybe some milliseconds or seconds
From what I already know, new messages do not need to be written to the DB instantly, it is ok to collect them in intervals of 1 to 2 seconds and bulk put them into the database
99% of the time, nobody will look at the data, so there's usually just the server application pumping messages into the archive, from time to time a user starts a client application to look at some values, so there are very few concurrent connections to the DB

Here's a message definition from my current server application written in Go:

type Message struct {
    Sender     string   `json:"sender"`
    MessageID  int      `json:"message_id"`
    Text       string   `json:"text"`
    Priority   int      `json:"priority"`
    Parameters []string `json:"parameters"`
}

MessageID references a Messages table, for the Parameters string array I would like to use a Postgres string array field.

What do you think is a good hardware platform in terms of CPU & cores, disk space, memory? Thanks in advance for your estimation.


texts: Filter rows if value exists in different columns

texts:
We're on SQL Server 2016 and 2019
I have a data-set where I'm rolling up a parent/child relationship. I'm doing this because some of the parent rows will return a SalesValue of 0 when their children will the value we're looking for.
Here's my query:
SELECT ART.StockCode
     , BOM.Component
     , ART.QtyInvoiced
     , ART.Invoice
     , ART.SalesValue
     , ART2.SalesValue
  FROM TABLE1 ART
  JOIN TABLE2 IM
    ON IM.StockCode = ART.StockCode
  LEFT OUTER JOIN TABLE3 BOM
    ON BOM.ParentPart = ART.StockCode
   AND IM.KitType = 'K'
  LEFT OUTER JOIN TABLE4 ART2
    ON ART2.StockCode = BOM.Component
   AND ART2.Invoice = ART.Invoice

And the data it returns
StockCode   Component   QtyInvoiced    Invoice     SalesValue   SalesValue2
315128      NULL        1.000000             1         154.32          NULL
317324      NULL        1.000000             1          68.31          NULL
317350      NULL        1.000000             1         428.90          NULL
318482      NULL        1.000000             1         171.24          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
900550      NULL        1.000000             1        2396.08          NULL
900572      NULL        1.000000             1        1448.84          NULL
900581      NULL        1.000000             1        2017.96          NULL
901012      900581      1.000000             1           0.00       2017.96
901012      317350      1.000000             1           0.00        428.90
901012      317324      1.000000             1           0.00         68.31
901062      900572      1.000000             1           0.00       1448.84
901062      318482      1.000000             1           0.00        171.24

What I'm trying to do is exclude any StockCode that has a value in the Component field that has the same Invoice number
In the example above, we're looking to exclude stock codes 900581, 317350, 317324, 900572, and 318482 because they already exist in the Component field.
Here's what I'm going for:
StockCode   Component   QtyInvoiced    Invoice     SalesValue   SalesValue2
315128      NULL        1.000000             1         154.32          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
320058      NULL        1.000000             1          28.58          NULL
900550      NULL        1.000000             1        2396.08          NULL
901012      900581      1.000000             1           0.00       2017.96
901012      317350      1.000000             1           0.00        428.90
901012      317324      1.000000             1           0.00         68.31
901062      900572      1.000000             1           0.00       1448.84
901062      318482      1.000000             1           0.00        171.24