texts: SQL nested select (query rows after variable), running slow

texts:
Have large table (Sybase) rows = 6276225. I'm pulling call status and my query is very slow.

In effect what I’m trying to do is pull data for any event after my variable. While querying for just one acct, the query takes around 15 sec, anything larger and my query runs forever.

How can I speed up my query?

declare
@RSP varchar (2)
SET
@RSP = 'WN'

select
a.contact_list_name,
a.agent_login_name,
a.account_number,
a.ov_phone_number,
a.time_of_contact,
a.response_status

from table a

where  a.time_of_contact >=
                      (SELECT
                       b.time_of_contact
                      FROM table b
                      WHERE  a.account_number = b.account_number
                             and a.ov_phone_number = b.ov_phone_number
                             and a.time_of_contact >= b.time_of_contact
                             and b.contact_list_name ='Prime_list'
                             and b.response_status = @RSP)


texts:
I'm creating a database with a few different schemas.
99% of users connect using Excel, and when they do they see all the system stuff:

This just confuses the users and I would like to configure SQL Server so that they only see UK_data and UK_reports.
So far I tried denying select on the things that I want to hide and only allowing select on the UK_ schemas, but it didn't work.
Is there a way to hide them from users?
Thanks!


texts: SQL Server: Using Dual Databases for Performance?

texts:
We have a SQL database that stores application usage logs for about 3000 PCs. These PCs send their usage data to the SQL server around 10-20 times per day. We used to store only the most recent 60 days of application usage, but the customer asked us to no longer purge data. Now that we have about a year's worth of data (about 6,000,000 rows), the SQL database is suffering from some performance issues. Not significant, mind you, but far more than any other database we have. There are a significant number of records added each hour (application open records), and within a few hours at most that record will be updated just once with the associated application close. It is these updates that you can see via SQL Activity Monitor that are taking considerable time to complete.

That UPDATE query is simple:

SELECT TOP 1 f_ID
from tb_applicationusage
WHERE f_application = 'xxxxxxx' AND
      f_computername = 'xxxxxxxxx' AND
      f_endtime IS NULL
ORDER BY f_starttime DESC

Effectively, it finds the most recent matching application start for a specific machine that doesn't yet have an associated application close. I can't think of a more efficient way to run the query, so I'm considering the following alternative:

Move to two databases:

Working database with only the most recent 24 hours worth of records
Final database with all other records

I'm no SQL guru, so I'm probably missing some drawbacks of this method. The goal would be to just have a SQL Agent job move the completed records over to the final database every night. Then, when the customer wants to run their monthly reports, I can just have that report query only the final database and not the working database. With only maybe 10,000 records to query in the working database instead of 6,000,000 it would seem logical that it would work faster. But again, it seems so simple I'm probably missing something obvious.

Version: Microsoft SQL Server 2008 R2


texts:
In an SQLite DB I have a table with a station integer field and an at date field.

How can I combine these two values to obtain, in a WHERE clause a date which is at plus station months?

I.e. for a record with at = '2019-01-01' and station = 3, my WHERE clause should look like WHERE before <= '2019-04-01'

I have looked at several resources online, including https://www.sqlite.org/lang_datefunc.html but no one refers to using filed value to compute a date.


texts: MSSQL. Tree structure fetching with conditions

texts:
I need to make a tree structure
Table:

create table Dirs
(
    Id           uniqueidentifier primary key not null default (newid()),
    Name         nvarchar(30)                 not null,
    CreatedDate  datetime                     not null default getdate(),
    ModifiedDate datetime                     not null default getdate(),
    IsCollapsed  bit                                   default dbo.randbit(newid()),
    ParentId     uniqueidentifier                      default null
)

I want to fetch all child nodes from specific node (by id for example), but filter out closed directories' children keeping them itself in output?

e.g. given structure

root
   - Dir1 (v)
      -Dir2 (v)
         -Dir3(x)
            -Dir4(v)
               -Dir5(v)

where x - means that directory is collapsed, v - means that directory is expanded.

Expected output is:

Dir1
Dir2
Dir3

I have written such query:

create procedure GetNodes @parentId uniqueidentifier = null
as
begin
    with dirsrec as (
        select *
        from Dirs
        where Id = @parentId
        union all
        (
            select d.Id, d.Name, d.CreatedDate, d.ModifiedDate, d.IsCollapsed, j.id as ParentId
            from Dirs d
                     join dirsrec as j on d.ParentId = j.Id
            where d.IsCollapsed = 'false'
        )
    ),
         dirsec2 as (
             select *
             from Dirs
             where Id = @parentId
             union all
             (
                 select d.Id, d.Name, d.CreatedDate, d.ModifiedDate, d.IsCollapsed, j.id as ParentId
                 from Dirs d
                          join dirsrec as j on d.ParentId = j.Id
                 where d.IsCollapsed = 'true'
             )
         )
    select *
    from dirsrec
    union
    select *
    from dirsec2
end

It uses 2 recursions. The firstis fetching all opened nodes from given one, the second fetching all closed nodes and then output gets distincted
It works, but I dislike 2 separated queries with almost identical code. I guess it might be done much better

I tried to make a side-effect (Inserting collapsed nodes from within union all( subquery into temp table and then just union it with final result, but it looks like sql doesn't allow us to make some logic within subqueries)


texts:
So I'm running MySQL on a high availability cluster with the MySQL data directory on a GlusterFS.

I want to:

- Disable the query cache (--query-cache-size=0)

- Switch on external locking (--external-locking=TRUE)

- Disable delays to writing (--delay-key-write=OFF)

How would I express these settings in a my.cnf file?

I would like to edit /etc/mysql/my.cnf to my taste and then do service mysql start


texts: Table partitioning and updates / inserts

texts:
I'm planning to partition a product table on a website based on the status of the product: 0 or 1 (disabled or active). But I didn't quite understood if the partitioning will work after inserts / updates, as some disabled products may become active and vice versa, plus new active products will be inserted.

Will all the products go automatically to the designed partition after inserts / updates?


texts: How to group data and write it's group id for each row?

texts:
I have sample data:

CREATE TABLE #T (Name varchar(5), GroupId int NULL)
INSERT INTO #T (Name)  VALUES
('A'),
('A'),
('A'),
('B'),
('B'),
('C'),
('D'),
('D')

Name  GroupId
----- -----------
A     NULL
A     NULL
A     NULL
B     NULL
B     NULL
C     NULL
D     NULL
D     NULL

How can I group that data by name and after it, write groupId (sequential, maybe identity)? That is what I want to get:

Name  GroupId
----- -----------
A     1
A     1
A     1
B     2
B     2
C     3
D     4
D     4

The table to group has about 15m rows. How to do that better? Thanks!


texts:
I am trying to update 1 column in all rows in a table to have an appropriate count value. Sadly, the SELECT query I am using won't let me join on a field in update table.

The query looks like this (trimmed for this example)

UPDATE people SET files_count = (
    SELECT COUNT(files.id)
    FROM messages
    JOIN threads et
        ON messages.thread_id = et.id
        AND messages.user_id = people.owner_id /* Gives error of unknown fields owner_id */
    /* other joins here */
    WHERE
        ep.person_id = people.id /* No problem with using the people table here */
);

As you an see in the comments in the query, I am getting an error when I try to join the threads table with a value from the people table. But, when I use the people table in the WHERE clause, all is fine. If I replace people.owner_id with an actual number, there is no error (but obviously this gives incorrect results).

How can I get this JOIN to use my parent table?


texts:
I was going through one of our databases in order to answer a question from the boss, and I peeked at a stored procedure.  As usual, I right-clicked it in SSMS, clicked Script stored procedure as... then create, and to my surprise, all I found was the CREATE PROCEDURE statement:

To double-check, I had someone else look and they got the same thing.  They were signed in as database owner, I was signed in as sysadmin.  I next ran
select
m.definition
from sys.procedures p
left join sys.sql_modules m on p.object_id=m.object_id
where p.name in ('spPUpdateContactUserLockout', 'spPLINK_SYNC_CONTACT');

and got this (note I included another stored proc for comparison purposes)

I've not seen this before.  Ever.  I didn't think one could create a stored procedure without defining a body, and there are several such procedures in multiple databases on this instance.  At this point I'm concerned, but I'm not sure if I don't have to be or if I should be more concerned than I currently am.
This instance is SQL Server 2017 Standard x64 RTM-CU11, and I'm using SSMS 17.9.  Any insight is appreciated!


texts: Are Availability Groups feasible for 4 TB of data?

texts:
Currently we have a database which is 4 TB in size. We would like to put it in a 2014 Availability Group to scale out reads. Data gets changed a lot inside this database.

I have following questions:

Is this size okay for Availability Groups (we are looking to have 2-3 replicas)?
If network dies for 1hour, how fast can we get the replica in sync?
Do you guys have any consideration I should be following before using Availability Groups?


texts:
Environment: SQL 2016 SP1, AG with 2 replicas, sync commit, no readable secondary. 1 file in the PRIMARY FG, 1 for the index FG.
Problem: How to move the files for a database in this AG, from the current disk to a new disk, with no downtime, not taking it out of AG ?

I'd try to add a file to each file group, on the new disk, run shhrinkfile emptyfile, remove the empty files from the old disk, all on the primary. Log file will stay on the old disk. Not sure how to do it on the secondary.

Can someone please confirm I'm on the correct path, or improve on it, please ? Thank you.


texts:
What exists today to do Microsoft SQL Server Load Balancing on Windows Server and being transparent to any programmatic access. Is this built in or does a solution have to be purchased?

I'm trying to find out if there is a solution today that makes this very easy from the view point of the web servers or other database access to SQL Server.


texts: Postgres 9.6.6 on Ubuntu 16.04, lots of errors

texts:
We're running perfsonar on a group of our servers, and while doing some maintenance work found that we

get giant logfiles under /var/log/postgresql
table bloat on the database of the perfsonar scheduler

I assume the toolkit is using table locking when it does ANYTHING on the database, and always cancels the autovacuum run:

2019-04-07 06:33:43 NZST [471-1] ERROR:  canceling autovacuum task
2019-04-07 06:33:43 NZST [471-2] CONTEXT:  automatic vacuum of table "pscheduler.public.run"
2019-04-07 06:34:33 NZST [981-1] ERROR:  canceling autovacuum task
2019-04-07 06:34:33 NZST [981-2] CONTEXT:  automatic vacuum of table "pscheduler.public.run"

A manual run of vacuum analyze roughly halved the use of disk space under /var/lib/postgresql/9.6/main/base/  ...

I've found several web pages talking about autovacuum tuning, but I'm wondering if it wouldn't make sense to disable autovacuum and run a vacuum analyze from a cron job.

Is there a best practice/recommendation for this kind of situation?

And maybe ask the perfsonar project to clean up their code in the long run ;}


texts: SQL Server 2012 Always On AG - DBEngine Service account locking

texts:
I am having a strange issue and am hoping you guys might be able to help!

Problem: I have a 2 node SQL Server 2012 Availability Group Cluster utilising a FSW. Both nodes are using the same DBEngine Service account (as is normal in these setups).

There are currently 3 Availability Groups on this cluster, and it's been working fine for quite some time.

Today I restarted the passive node DBEngine account (as I was adding an SSL Certificate for a new application, new availability group). When the node came back up, it was no longer synchronising with node 1. The state of the replica was disconnected, and I could see lots of login failures on Node 1 (active node) SQL Logs.

I found that the DBEngine service account had locked. I had it unlocked, but it soon locked again.

I removed the SSL Certificate and restarted the passive node again (so it was in the same state I started with) and it started up fine, but within a minute, the service account had locked out again.

Steps I tried:

created a new service account to rule out the account being used elsewhere, started both nodes under the new account.... account locked out when node 2 started
unlocked the account, stopped node 2. restarted node 1. Account fine... waited.. account still fine. Started node 2 service... account locked out.
recreated mirroring endpoints on both nodes and reapplied connect permissions to the dbengine service account. - this didn't fix it.
restarted both VM's
removed the node 2 replica from the availability group, removed all databases (from node 2) and dropped the mirroring endpoint on node 2. restarted node 2 service. - at this point both nodes were happily running under the same service account.
tried re-adding node 2 as a replica using the wizard. It added it, backed up the database, restored to node 2, and got to the very last step where it connects it, and the password locked out again!

Has anyone got any ideas? Any input would be greatly received!

Sam


texts: Need proper mysqlfrm command to get into ibdata file to restore a database

texts:
I'm attempting to restore a database made up of only .ibd and .frm files. Why I'm doing this: An uninstall of my WAMP installation left only .ibd and .frm files, and was using MySQL 5.6.12. A reinstall of WAMP has MySQL 5.6.17. The WAMP people said if I restore the database in the 5.6.12 then I'll be able to choose the 5.6.12 setup from the Aestan tray menu.

I'm trying to use mysqlfrm to to do this but my command execution appears to be off.

My WAMP database is located at C:\wamp\bin\mysql\mysql5.6.12\data\wordpress.

Here is the path I tried: C:\wamp\bin\mysql\mysql5.6.12\data\wordpress>c:\utilities\mysqlfrm --server=root:rootpassword@localhost --port=3307.

I got this set of responses: WARNING: Using a password on the command line interface can be insecure. Usage: mysqlfrm --server[user[:<pass>]@host[:<port>][:<socket>]:<login-path>[:<port>][:<socket>]] [path\tbl1.frm:db:tbl.frm]

and mysqlfrm: error: Nothing to do. You must specify a list of paths or files to read. See --help for more information and examples.

Someone shared that according to the error message, I am using the command line arguments incorrectly and that "The messages even shows you the correct options".

I'm basing my command on another post's example here. So I'm not understanding how socket would be involved. Could someone post what my command should look like? I'm pretty new at this so any advice would be greatly appreciated. Thanks.

By the way, my port number is a guess based on what someone else did. I could use some help if my port number should be different. I've done a LOT of reading here on how to do this and have been at it for several weeks. Reference: Restoring a database with just .idb and .frm files (That's my question earlier in the process. I'm slowly working my way toward a solution. Just trying to get answers to get this to work. Each time I ask I'm getting clues, but there's still missing info that I find I don't have each step I take.)


texts: Are there any guidelines for setting innodb_stats_persistent_sample_pages in MySQL?

texts:
The default value (20) seems to produce fairly accurate index cardinalities for small tables, which in turn results into good execution plans. But from what I've seen, accuracy starts falling off once your table grows.

It sounds reasonable, at least in my mind, that the number of pages to sample should be a percentage of the total number of rows.

I tried reading the manual and also tried searching for related literature, but so far I haven't found anything.

I could always go the trial and error route, but surely I'm not the only one with this conundrum.

Thanks in advance!


texts: 2008R2 Cannot Disable Non-Clustered Index

texts: We have identified a handful of superfluous non-clustered indexes and I would like to disable them for a few days before straight dropping them.  However when using the GUI in SSMS (right click > disable) OR running ALTER INDEX DISABLE it says Successful but the index is still online.  I cannot seem to disable it.  Even in AW2008R2 I cannot disable an index.  Help!


texts: Is it possible to achieve zero downtime when upgrading database schema using this method?

texts:
I am looking for a solution in achieving zero downtime schema upgrade with the assumption that every schema upgrade is backwards-compatible with the previous application version which makes things easier. Since the database is already huge, the upgrade may take more than 2 days to complete and the business cannot afford to stop their operations that long.

I found a candidate solution in
https://stackoverflow.com/a/40365479
where its steps are comprise of:

Create a temp table
Creates triggers on the first table (for inserts, updates, deletes) so that they are replicated to the temp table
In small batches, migrate data
When done, rename table to new table, and drop the other table

But my concern is would not it cause a potential race condition problem in the last step where for instance, we have the original table 'users' and the new table 'users_new', and our goal is to replace the original table with the new table? Because to accomplish that, won't it be needed to execute two separate DDL operations such as

DROP TABLE users;
ALTER TABLE users_new RENAME users

And by doing so, what if a query was executed against the users table during the time between the execution of 1st and 2nd DDL commands?


texts: Possible INDEX on a VARCHAR field in MySql

texts:
I am working in a MySql database, with a table like this:

+--------------+
|  table_name  |
+--------------+
|    myField   |
+--------------+

...and I need to make a lot of queries like this (with 5-10 strings in the list):

SELECT myField FROM table_name
WHERE myField IN ('something', 'other stuff', 'some other a bit longer'...)

There will be around 24.000.000 unique rows

1) Should I use a FULLTEXT or and INDEX key for my VARCHAR(150)?
2) If I increase the chars from 150 to 220 or 250... would it make a great difference? (Is there any way to calculate it?)
3) As I said, they are going to be unique, so myField should be a PRIMARY KEY. Isn't it rare to add a PRIMARY KEY to a field which is already a VARCHAR INDEX/FULLTEXT?


texts: external authentication of database users in Db2

texts:
For a future project, I am looking into authenticating Db2 database users via LDAP and PAM. However, it seems that LDAP server is deprecated in RedHat:

https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/7.4_release_notes/chap-red_hat_enterprise_linux-7.4_release_notes-deprecated_functionality

and it is suggested to migrate to Redhat Identity Management or Red Hat Directory Server. Before setting that up myself I'm curious if anybody knows if authentication for Db2 via any of these replacements work, and what steps that are required in Db2 to switch from LDAP to Identity Management or Red Hat Directory Server?


texts: Query for a month works faster then for a week

texts:
I have strange situation when query for a month works faster then for a week:

There is a request for a month (2016-06-01 - 2016-07-08) that works quickly and uses proper index:

SELECT  `t1`.* FROM `t1`
  INNER JOIN `t2` ON `t2`.`t1_id` = `t1`.`id`
  WHERE
   `t1`.`type` IN ... AND
   `t1`.`site_id` = ... AND
   (t1.created_at >= '2016-06-01 07:00:00') AND
   (t1.created_at <= '2016-07-08 06:59:59')
   ORDER BY `t1`.`id` DESC LIMIT 30 OFFSET 0;

returns 48 rows in 85ms

it uses INDEX: site_id, type, created_at, subtotal

The same request for a week (2016-07-01 - 2016-07-08 or 2016-06-01 - 2016-06-08) work in 1000 times slower and does not use index

returns 7 rows in 420 sec
returns 13 rows in 420 sec

Table has ~170M records

MySQL 5.6.23
analyze table t1, t2 has not fixed the issue
USE INDEX(...) solves the issue, but it's not a solution for me, I'can't manage indexes manually instead of MySQL
if remove LIMIT 30 OFFSET 0 it works fast, but I need it for pagination
ORDER BY t1.created_at works faster, but not enough - 1-10 sec. It also uses wrong index

Is there any solutions?

UPDATE:

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `subtotal` decimal(25,2) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
...
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Update 2:

There is explain for a week (slow query):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: PRIMARY,index_t1_on_site_id_and_c1_and_type_and_c2,index_t1_on_created_at_and_site_id,index_t1_on_type,index_t1_on_site_id_and_type_and_created_at_and_subtotal,index_t1_on_site_and_type_and_c3_and_created_at
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8991557
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: eq_ref
possible_keys: i1,i2
          key: i1
      key_len: 4
          ref: production.t1.id
         rows: 1
        Extra: Using index

There is explain for a month (fast query):

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: PRIMARY,index_t1_on_site_id_and_c1_and_type_and_c2,index_t1_on_created_at_and_site_id,index_t1_on_type,index_t1_on_site_id_and_type_and_created_at_and_subtotal,index_t1_on_site_and_type_and_c3_and_created_at
          key: index_t1_on_site_id_and_type_and_created_at_and_subtotal
      key_len: 88
          ref: NULL
         rows: 7709
        Extra: Using index condition; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
         type: eq_ref
possible_keys: i1,i2
          key: i1
      key_len: 4
          ref: production.t1.id
         rows: 1
        Extra: Using index


texts: Import data to existing schema from dmp file

texts:
ORA-39083: Object type ALTER_PACKAGE_SPEC failed to create with error:
ORA-01775: looping chain of synonyms
Failing sql is:

ALTER PACKAGE "XZB"."P_IATA"   COMPILE SPECIFICATION     PLSQL_OPTIMIZE_LEVEL=  2    PLSQL_CODE_TYPE=  INTERPRETED
PLSQL_DEBUG=  FALSE    PLSCOPE_SETTINGS=  'IDENTIFIERS:NONE' REUSE
SETTINGS TIMESTAMP '2015-07-24 05:12:12'

I get above error when I try to import schema which is already existed.


texts: select distinct one column with all instances of another column in a second column

texts:
If I have a table with the following information:

ParentID | ChildID
---------+--------
     6   |      6
     6   |      7
     6   |      8
     6   |      9
     6   |     10

I need to display the distinct parent with all instances of child in the second column possibly concatonated with a comma e.g.

ParentID | ChildID
---------+--------
   6     |     6, 7, 8, 9, 10

Thanks in advance!


texts:
What is a "leaf expression"?

Partition-clause expressions are limited in the following ways:
....

You can specify leaf expressions, functions, and operators in the partition clause expression.
All leaf expressions in the partition clause must be either constants or columns of the table.

Source https://my.vertica.com/docs/6.1.x/HTML/index.htm#18125.htm


texts: Why do uncommitted transactions rollback instead of redo after a crash in ARIES?

texts:
Since all actions within a transaction are idempotent in crash recovery algorithms like ARIES that use write-ahead logging why do we rollback after a crash during an uncommitted transaction to get back to consistent state rather than just re-doing the entire transaction?

This way we would complete the transaction and we don't need to deal with any undo.


texts: SQL 2008 Developer Edition, not using all CPU´s

texts:
My SQL Server doesn´t want to use the second CPU for our Vault database.  I tried to use different configurations for the CPU affinity in SQL Management Studio, but doesn't seem to fix it.
I've read an article about different values for affinity mask but I'm not sure which values are correct, the article was about 4 CPU´s and someone suggested to set the config value to 15.
My Values for affinity mask are min: -2147483648; max: 2147483647; config_value = 0; run_value = 0;
The server is virtual server.

Thanks in advance


texts: How to manage dynamic tables in Visual Studio 2010 Sql Server Database Projects

texts:
I am migrating a common set of databases in to a VS 2010 Sql Server Db project so we can keep it in source control, and deploy common changes etc.
The databases are for the most part the same, except for a couple schema changes based on the current environment.
The schema changes generally consist of altering how computed columns are calculated, or how stored procedures and functions calculate things (converting euro's to usd for monetary calculations when the envrionment is in the EU would be an example)

Currently the database has a set of stored procedures that make a few DDL changes based on data in the database.  I need to add some DDL changes based on the environment, irrespective of data.

My question is, can I (or should I) manage the DDL differences in the db project post deploy script via if clauses and alter statements or should I migrate all of the changes to one time use stored procedures?


texts:
On a SQL Server 2005 machine, I have DBMail setup and correctly configured. I am able to use "Notify Operator" and EXEC msdb.dbo.sp_send_dbmail to queue emails.

But the problem is the queued mails never go through. I see 10 emails in the queue when I query

SELECT * FROM msdb.dbo.sysmail_unsentitems

But if I restart the SQL Server service, all mails go through and I receive emails in my inbox.

So far this is the only pattern I could find.

So my question is: is there a way to troubleshoot DBmail? I read some steps on MSDN, but they are not of much help. Any URLs to articles that could help are much appreciated.


texts: What's the use of assigning foreign keys on MySQL

texts: Aside from indexing the value of the foreign key, what is/are the advantage of assigning foreign keys to databases? I've seen a lot of databases that does not assign foreign keys, but is relational by their schema. One example is the database of the wiki (mediawiki). I've studied their schema and although their tables are interconnected using the primary key of one table, they aren't assigning any foreign keys.


texts: how would i merge a column where matches a different column?

texts:
It's very similar to the below post

How to merge column in mysql depending on match

table user

I would like to Merge customer_id from table user to table cust where customers_firstname AND customers_name match

table cust


texts: Unable to view Registered Servers on Central Management Server

texts:
I have a MS-SQL instance configured to be a CMS for 50+ instances of MS-SQL Server 2008-2017. When checking the msdb.dbo.sysmanagement_shared_registered_servers_internal I see a list of the servers and their associated groups.
However when I connect to the instance remotely (from my Desktop via SSMS), the Registered Servers list only displays an offline SQLExpress instance in "local server groups" associated with my local PC - and nothing at all under "Central Management Servers".
When RDP'ing to the CMS directly, in SSMS the Local server group correctly shows the CMS instance under "Local Server Groups" but it's empty - and again, "Central Management Servers" is empty.
Any help on how to usethe existing CMS server list would be appreciated, as rebuilding it would take some time and I'm unsure on any side effects of essentially overwriting another CMS configuration on top of the existing one.
Note: I didn't build the existing CMS nor have I used it previously.


texts: MySQL 5.6.17 running out of memory when opening table with many partitions

texts:
We are currently setting up a MySQL database (version 5.6.17) on an Amazon db.m1.large RDS instance for storing time series data. Each time series property of an object is stored in a separate InnoDB table (e.g. inside temperature of building 42 is stored in table building__inside_temperature). There are about 100 time series tables and each has three columns:

timestamp in milliseconds as bigint
ID of object (as char(22))
numerical value (as double)

The primary key is (timestamp, ID). We use range partitioning on the timestamp column with one partition per day for 2.5 years and key sub-partitioning on the ID with 8 sub-partitions. This results in about 800 partitions with 8 sub-partitions each per table.
We use innodb_file_per_table=OFF since there would be too many open files otherwise. The other MySQL parameters are left untouched.

When connecting with 100 threads (and connections) in parallel and opening a time series table, the MySQL instance runs out of memory. The process list shows all threads in 'Opening table' state before crashing. With less partitions or less parallel connections opening the table, the problem disappears.
We use the following mysqlslap command for that:
mysqlslap --create=create_timeseries.sql --delimiter=";" --query=insert_datapoint.sql --number-of-queries=1 --concurrency=100

Content of the files is here: http://pastebin.com/x4YMMF3h

The error is the following:

2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1208.

2014-08-12 06:25:53 2b119160d7002014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 1248.

2014-08-12 06:25:53 2b11913c4700 InnoDB: Assertion failure in thread 47354451085056 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
06:25:53 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.

key_buffer_size=16777216
read_buffer_size=262144
max_used_connections=102
max_threads=604
thread_count=102
connection_count=102
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 333908 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x13aeb5a80
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
2014-08-12 06:25:53 26178 [ERROR] InnoDB: InnoDB: Unable to allocate memory of size 3784.

2014-08-12 06:25:53 2b1192035700 InnoDB: Assertion failure in thread 47354464130816 in file ha_innodb.cc line 17080
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.

http://dev.mysql.com/doc/refman/5.6/en/table-cache.html just tells, that each MySQL thread opens a table independently.

Why is opening a table with many partitions so memory-intensive?
How can I see how many times a table is opened and how much memory this consumes? show open tables from mysqlslap; does not give me per-thread information and no memory consumption.


texts: Does it matter which database you connect to when querying across multiple databases?

texts:
I'm building a Sharepoint application with Nintex workflow that runs a single SQL query over multiple databases (on the same MS SQL server). Does it matter which database I specify in the connection string in terms of speed?
So if my query looks like this:
SELECT col1, col2 FROM db1.table UNION ALL
SELECT col1, col2 FROM db2.table UNION ALL
SELECT col1, col2 FROM db3.table
would it make any difference if my connection string looks like this:
Server=***;Database=db1; Integrated Security=SSPI; Connection Timeout=900
or this
Server=***;Database=db2; Integrated Security=SSPI; Connection Timeout=900
or this?
Server=***;Database=db3; Integrated Security=SSPI; Connection Timeout=900
The table from db1 has more records than db2, which has more records than db3.
EDIT: My query is actually more complex than what I wrote above, I just simplified it because I didn't know that would matter. The real queries have a WHERE clause and a LEFT JOIN on a fourth database (db4).
The compatibility level of db2 and db3 are SQL Server 2008 (100), for db1 and db4 it's SQL Server 2017 (140).


texts: Executing try catch block in mysql stored procedure

texts:
In my mysql stored procedure I want try catch block but it gives error in my mysql stored procedure .

I want my select query to be in try block
and if it executes correctly I want other query to execute.

code statement for mysql

BEGIN
 begin try
     select userId into user from users ;
 end try
 begin catch
    select 1;
 end catch
 begin finally
   select postId from photo where userId=user;
 end finally
END

but it gives error is there any try catch block for mysql.


texts: Is there any difference between these two version of MySQL - 5.1.73-community-log Vs 5.1.73-community

texts:
I have installed MySQL 5.1.73 in Oracle LINUX 5 server. Just after RPM installation, I checked system variables and found that version is 5.1.73-community.
When I copid  /usr/share/doc/MySQL-server-community-5.1.73/my-medium.cnf to /etc/my.cnf
I got 5.1.73-community-log as value of version variable.
I am curious to know about what is the difference between
5.1.73-community and 5.1.73-community-log


texts:
Hi guys trying to convert

16JAN2014 --> 16/01/2014.  Any ideas??? i'm trying to avoid the horrendous expression of ? JAN : 1 ? FEB : 2 etc. etc.

SSMS will quite happily do SELECT MONTH('16JAN2014') to get me the number of month however MONTH( "16JAN2014" ) will not work in SSIS.

hmmm... tried code but keep getting error below...


texts: Select COUNT of days between two dates except weekends

texts:
I am trying to get the number of days between two different dates, except the weekends.

I don't know how to achieve this result.


texts: Using Oracle Directories names as same of path

texts:
We, at our company, have been using a small hack on Oracle Directories names since some time ago.

Before Oracle Directories we have to explicitly set the directory on pfile, by the utl_file_dir parameter. To add a new directory we had to reboot the instance.

After Oracle Directories, everything become easier!

But some legacy scripts still have been using the old style, using utl_File.Fopen('/path',...) instead of utl_File.Fopen('DIRECTORY',...)

The small hack is create a directory in which its name is the exactly path of the desired directory. In a simple example:

create or replace directory "/tmp" as '/tmp';

select * from dba_directories ;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ---------------------
SYS                            /tmp                           /tmp

So, the users can normally use a command like that:

l_file_handle := UTL_FILE.FOPEN('/tmp','file.txt','W');

That's really nice.

But I'm facing a problem when using such a directory as the output of a EXPDP dump. EXPDP parser seems to ignore any quotations marks, and is parsing the directory name in uppercase.

$expdp \"/ as sysdba\" parfile= parfile.par

Export: Release 10.2.0.3.0 - 64bit Production on Thursday, 05 April, 2018 10:50:19

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name /TMP is invalid

[oracle@brux0009 P00OMS /EXPORT/spool]$ cat parfile.par
DIRECTORY='/tmp'
DUMPFILE=dual.dmp
LOGFILE=dual.log
TABLES=SYS.dual

Any tips?


texts: Paritioning performance for full table scan on non-paritioned index

texts:
We have a table in Postgres that we are considering partitioning. We occasionally query an indexed field that is separate from one with the partition key.

My question is: Can partitioning the table make this query faster?

My instinct says it no. Instead of scanning one index we will now be scanning multiple indexes. However, another developer I work with insists that the query will be faster because instead of scanning one large index, the database will scan multiple smaller tables in parallel.


texts:
I had a specific relational database with different tables. what I wanted to do is actually to copy two tables from different DB located on different server to my database using export feature in Microsoft SQL Server Management Studio 2017. The new tables have been imported to my current database. However, the problem is that the tables that have been exported from the other database into mine don't have primary and foreign keys. So what I did, I deleted the old tables in my current database and now working on adding the primary and foreign keys to the freshly copied one.

The issue is that I was able to create the primary keys without any problems however, when I tried to create the foreign key to the second table to link it to the first table I Got this error:

  The ALTER TABLE statement conflicted with the FOREIGN KEY constrain

I know the reason of the error that I have unmatched number of items in my first and second tables. I ran this query:

select UUT_RESULT from STEP_RESULT
WHERE UUT_RESULT NOT IN
(SELECT ID from UUT_RESULT)

I was able to see that there is 883 entries in my second table doesn't have match in the first table.

How can I solve this problem? I can't clean the tables because they have 15 million entries so is there a way that I can delete these 883 from my second table and then I can match the two tables?


texts: How to best store the Google Web Ngram data?

texts:
This is a continuation of How to best store Google ngrams in a database?, which covers how to store the Google Ngram Book data.

I'm looking to store the Google NGram Web data, which is slightly different in format (no page/year info; just counts):

...
ceramics collectables collectibles 55
ceramics collectables fine 130
...
serve as the incoming 92
serve as the incubator 99

Since this is a very simple data structure, what is a good method for storing this data that is reasonably quick to import, and quick to retrieve the count by a particular ngram?

I like the idea of a relational db, simply because of the common methods for accessing it, but I'm guessing most of these other non-relation db's (e.g. tokyo hashtable) have pretty common methods as well.

Update

Examples of a queries:

# primary query
> SELECT ngram_count FROM ngram_table WHERE ngram = 'ceramics collectables fine';

ceramics collectables collectibles 55
ceramics collectables fine 130

# secondary query (not needed, but nice if have option)
SELECT ngram_count FROM ngram_table WHERE ngram LIKE '%collectables%';

ceramics collectables collectibles 55


texts: SQL Table Indexing - Database Overloaded With Queries

texts:
I'm a bit of a newbie to SQL who has been thrown in on the deep end with a server that gets about 2 million queries an hour.

I'm trying to optimise our database and I noticed this in our database runtime information:

  Select_full_join  789  The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Obviously that is far from optimal. And other red flags are showing too which are probably related to this root problem I believe.

Now, we have a database structure that is not exactly well indexed. We have a unsigned INT as primary key on our tables and then just unindexed columns.

I wonder how I should approach this. Do I need to add indexes to every column? Would that even help? How should I approach this problem?


texts: Am I able to create table using EM Express Oracle 12c

texts: Please if someone can let me know if I could create table using Oracle 12c EM Express or also known as dbconsole? In 11g there user will go to schema -> db objects -> tables, but I don't see schema in 12C .


texts:
Possible Duplicate:
  Is it better to store images in a blob or just the url?

I need to recover images from an XML file stored on a remote server and save it to my database. Not sure how I should do so though, what type of field should the image be stored in?

Thanks!


texts: lock-ddl-per-table option not working as expected in Xtrabackup

texts:
I've recently been getting the following errors on some of my Percona XtraBackup backups:

InnoDB: An optimized (without redo logging) DDL operation has been performed. All modified pages may not have been flushed to the disk yet.

However, it was my understanding that this should be avoided by including the --lock-ddl-per-table option.

The script I use is as follows:

xtrabackup ${MySQLConnectionString} --backup --parallel=${CPUCount} --compress --compress-threads=${CPUCount} --throttle=${throttle} --slave-info --extra-lsndir="${LogsDir}" --target-dir="${CurrentDir}" --lock-ddl-per-table

xtrabackup ${MySQLConnectionString} --backup --parallel=${CPUCount} --compress --compress-threads=${CPUCount} --throttle=${throttle} --slave-info --extra-lsndir="${LogsDir}" --target-dir="${NewDir}" --incremental-basedir="${BaseDir}" --lock-ddl-per-table

My Databases are all MySQL 5.7.28, and they are slave databases. They are all running on Debian 9, and using PXB version 2.4.16.

Have I misunderstood the usage of this option?


texts: MySQL hidden files on ubuntu taking up space

texts:
I have a weird issue where my system (ubuntu 19) is showing that MySql 8 is using up 600gb of space, when it should be closer to 250gb.

I ran ubuntu's baobab utility in sudo mode, and it as well, is showing that MySQL is using up 250GB .  Any idea what could be taking up the other 250GB?

I also ran this query via SQL, to show table sizes - same story ~250GB, so not sure what else I could try.

SELECT
     table_schema as `Database`,
     table_name AS `Table`,

     round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
FROM information_schema.TABLES
ORDER BY (data_length + index_length) DESC;

.. I do have a few indexes set up, but most of them on columns containing 0/1s

Thank you in advance for suggestions.


texts: How can I cancel a single transaction log quickly?

texts: First of all I'm trying to disaster scenario for our db. My DB Recovery model is FULL now. And I have full backup. I can do recover from a time point or TransactionID. But I wanna cancel or undo only one TransactionID on SQL Server. How I can do this without third party software? Thanks in advance.


texts: Applying (log backup) LSNs to the secondary node of an Availability Group

texts:
I have a test instance configured as always-on go down today due to a restart of the primary node and the file-share going down together. I had a bunch of problems in order to get the secondary functioning again during which I had to remove it from the AG and add it again. During this time (the time when it went down till now) the log backup was running on the primary and when I tried to apply these backups to the secondary I got the following error:

  This backup set cannot be applied because it is on a recovery path that is inconsistent with the database. The recovery path is the sequence of data and log backups that have brought the database to a particular recovery point. Find a compatible backup to restore, or restore the rest of the database to match a recovery point within this backup set, which will restore the database to a different point in time. For more information about recovery paths, see SQL Server Books Online.

When I looked at sys.dm_hadr_database_replica_states:

last_hardened_lsn: 43000043815500001

When I looked at msdb.dbo.backupset (backup time):

backup time: 9:50 am
first_lsn: 43000043783000001
last_lsn : 43000043887200001

My question is, would there be any way to start applying this log backup starting from LSN 43000043815500001?

I am ok with restoring everything and then connecting my AGs since this is a very small instance, just want to know if there was an easier way as opposed to doing everything from scratch. I looked up online and did see the option for stopbeforemark or stopatmark but I am looking for something like stopaftermark.


texts:
Of course when you are making your first application for fun it doesn't matter that much how you deal with things. But when you need everything to be up to the current standards you don't always know where to find that precious useful information.

At the moment I'm making an application which deals with a DB connection.
And I'm not sure when I want my functions to execute any queries do I pass the already made connection as a parameter to that function, or do I store the connection as a global variable, or do I just make a function for temporary connection establishment and then run that function every time before my query.

This applies to any language I guess, but at the moment I'm using Python & PostgreSQL.
Just would like to know where to find this and similar useful info which all programers probably use on daily basis. I guess that not all of you are reading 300+ page books to find this info, so there probably should be some other places.

Thanks to you all in advance!

P.S. An example:

So rather than having something like this:

global somevar
randomfunc2()

def randomfunc1():
    _do_something_with_somevar

def randomfunc2():
    _do_something_with_somevar
    randomfunc1()

To have this:

somevar
randomfunc2(somevar)

def randomfunc1(somevar):
    _do_something_with_somevar

def randomfunc2(somevar):
    _do_something_with_somevar
    randomfunc1(somevar)

I assume the second one is better?

Are there any places where I can find these tips/guidances? :)


texts:
I have an SSIS package that populates an Excel file. The package is run from a SQL Server Agent job.

It all runs fine, but now I have added a Macro to the Excel file. This macro causes the Package to fail when it is run.
Are there any permissions I need to add to allow the SQL Agent account to run the macro?

This is the error:

  Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. --->
  System.IO.FileNotFoundException: Could not load file or assembly
  'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral,
  PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The
  system cannot find the file specified.

After installing Excel, the error is as follows:

  The file name or path does not exist. • The file is being used by
  another program. • The workbook you are trying to save has the same
  name as a currently open workbook. at
  Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object
  UpdateLinks, Object ReadOnly, Object Format, Object Password, Object
  WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin,
  Object Delimiter, Object Editable, Object Notify, Object Converter,
  Object AddToMru, Object Local, Object CorruptLoad)


texts: How Can I Isolate A Merge Replication Timeout

texts:
I am using merge replication in SQL 2012 with web sync.

When I create my subscription I am getting a timeout. The error message is not clear about cause of the timeout, but what I do know is if I increase the QueryTimeout parameter on the merge agent from default of 300 to 1200 it works.

How can I find the SQL that is timing out? I have used SQL profiler, but it isn't clear which query is timing out. In SQL profiler I selected the option 'Attention' under 'Errors and Warnings', but didn't see anything about a timeout.


texts:
How to design this simple DB?

I need to add some things to a DB I use to keep track of servers.

Table looks something like this.

Matrix
| Name        | Description    | Etc  | Etc    |
------------------------------------------------
| Server01    | First Server   | Data | Data   |

I want to add a table called Updates. It should look something like this:

Updates
| Name        | Q1 Updates | Q2 Updates | Q3 Updates |
------------------------------------------------------
| Server01    | Done       | Incomplete | Incomplete |

What is the best way to "link" these tables? I need everything in the Name field in Matrix to be in the Updates table as well. I'm a fundamental noob when it comes to databases, so I need some 101 help with this.

I have some triggers set up for another table that keeps track of other information and that works very well... I could just add more triggers for the table I am wanting to create... but I'm not sure if that's the best method.

What would you do?

EDIT: I want the Name field from Updates to always be an exact replica of what is in Matrix.


texts: Using Sync Orchestrator to Syncronize two SQL CE 3.5 Databases

texts:
I am trying to set up synchronization between two SQL Compact 3.5 databases using SqlCeSyncProvider.  I am able to create a scope for the two databases, which creates a meta table.  When I try to run SyncOrchestrator.Synchronize() though, I get the following error and am not able to get the two databases to synchronize when I insert data:

"An error occurred when initializing adapters for scope 'Person'. Ensure that the scope name specified on the provider exists in the scope metadata table, and that the correct base tables are specified for that scope."

When I run the following code, three tables are created for each database: Sync_Person_tracking, Sync_scope_config, and Sync_scope_info. Both databases contain the same scope name of Person in the Sync_scope_info table.

    SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning();
    SqlCeConnection ceConn = (SqlCeConnection)syncProvider.Connection;
    string scopeName = syncProvider.ScopeName;
    if (!ceConfig.ScopeExists(scopeName, ceConn)) {
        var description = new DbSyncScopeDescription();
        description.ScopeName = scopeName;
        var table = new DbSyncTableDescription("Person");
        var pkCol = new DbSyncColumnDescription("ID", "int");
        pkCol.IsPrimaryKey = true;
        table.Columns.Add(pkCol);
        table.Columns.Add(new DbSyncColumnDescription("Name", "nvarchar 50"));
        table.Columns.Add(new DbSyncColumnDescription("Height", "int"));
        table.Columns.Add(new DbSyncColumnDescription("Weight", "int"));
        description.Tables.Add(table);
        ceConfig.PopulateFromScopeDescription(description);
        ceConfig.SetCreateTableDefault(DbSyncCreationOption.Skip);
        ceConfig.ObjectPrefix = "Sync";
        ceConfig.Apply(ceConn);
    }

In the above code, syncProvider is a SqlCESyncProvider object with a connection string attached to the specified database.

Here is the code I use to set up synchronization between the databases using SyncOrchestrator:

    _syncOrchestrator.LocalProvider = localSyncProvider;
    _syncOrchestrator.RemoteProvider = remoteSyncProvider;
    _syncOrchestrator.Direction = SyncDirectionOrder.DownloadAndUpload;

    try {
        var sync = _syncOrchestrator.Synchronize();
        Console.WriteLine(string.Format("Sync Downloads: {0}\nSync Uploads: {1}\nSync Start Time: {2}\nSync End Time: {3}",
            sync.DownloadChangesTotal.ToString(), sync.UploadChangesTotal.ToString(),
            sync.SyncStartTime, sync.SyncEndTime));
    } catch (Exception ex) {
        Console.WriteLine(string.Format("Sync Orchestrator threw exception: {0}", ex.Message));
    }

Thanks in advance for any help!


texts:
I'm currently working on a mobile app powered by a web platform, and the mobile app will have some offline capabilities. The application has a small amount of DB entities, and only about 3 of them will be written by the mobile app, and not by the web platform.

Since Firebase provides a good solution for offline data sync, my plan is to use it only for the data that actually needs the sync, instead of using it for every data on my app. So, when a user logins to the app, it will load some basic info from my API, loaded from MySQL, and then sync the specific data from Firebase.

I'm sticking to MySQL in the web app because it has several relational data, such as user specific data, profile and reports, so I believe it fits better in a SQL database. Besides that, I don't need all of it to sync with the mobile app.

My question here is whether I should concentrate all my data on a single DB engine, or my plan to mix the two engines is a good way to go?

I never used Firebase before, although I've used similar engines such as Redis. It just seemed like a good option for the offline sync. I'm open to suggestions for this as well.

Thanks


texts:
I recently faced a weird problem where I cannot use/log into any Oracle user nor even System account as well as not being able to access my database.
I have a Windows 10 64 bits Home edition, and I did multiple new installation of both Oracle XE 11g release 2 and Oracle XE 18c.
When I installed the XE 11g R2, and when trying to connect to SQLplus using the command line I get the following error

So searched for this error and I found that it is probably related to the database as it is not being mounted correctly everytime:
I did the following:

Now I get the ORA-00119 & ORA00130 ERRORS, and I honeslty don't know where is the problem especially because all of these problems do not occur when I'm using an Internet Connexion using WIFI at home.
If anyone got any idea on how I can fix this please share!
Here are the following settings I currently have for Oracle:

LSNRCTL status:

the C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\listner.ora

  SID_LIST_LISTENER =
    (SID_LIST =
      (SID_DESC =
        (SID_NAME = PLSExtProc)
        (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )
      (SID_DESC =
        (SID_NAME = CLRExtProc)
        (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
        (PROGRAM = extproc)
      )
    )

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
        (ADDRESS = (PROTOCOL = TCP)(HOST = ASUS-AlexLutinNoir)(PORT = 1521))
      )
    )

  DEFAULT_SERVICE_LISTENER = (XE)

**the C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN\tnsnames.ora **

  XE =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ASUS-AlexLutinNoir)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = XE)
      )
    )

  EXTPROC_CONNECTION_DATA =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      )
      (CONNECT_DATA =
        (SID = PLSExtProc)
        (PRESENTATION = RO)
      )
    )

The services that are running for oracle :

My envirement variables:


texts: Oracle SYS views missing from DB instance

texts:
Hi i have a question but not sure what the problem was and how it happened though.

The initial error when i logged in the DB dev machine was ORA-00942: table or view does not exists. Some of the system views that we tried to access gave a ORA-017775: looping chain of synonyms.

Then we noticed some of the ALL_* and DBA_* views and functions were missing, which were causing alot of the dependencies and system packages to throw errors.

We also noticed that the SYSTEM tablespace has reached 100% utilized space.

So my question is, is it possible when the SYSTEM tablespace runs out of space, it would randomly drop the SYS views and functions

We also checked the alert logs, seems like it happened during a non-working period. Or is there any logs that i could further check on what happened that caused SYS views and functions being dropped suddenly ?

Regards,
Thanks


texts: Writing data to new database - collation question

texts:
We will be changing the collation of our databases in a few months. I am setting up an environment, to test the new collation with my application.  I have created a database with our new collation and want to write data into the new database.

Database1: SQL_Latin1_General_CP1_CI_AS
Database2: Latin1_General_CP1_CI_AS_WS

If I run the following query:

INSERT INTO Database2.table1
SELECT * FROM Database1.table1

...will SQL use the collation of Database2 or Database1 to write the data?

Both databases are on SQL Server 2012.

The collation for Database2 is Latin1_General_CP1_AS_WS


texts: postgresql grant user privilages to dynamically created tables

texts:
We have 2 postgresql users. One user (dbworkeruser) for creating tables dynamically (from C# worker) and other user (injestworkeruser) for inserting data into these tables (from another C# worker as well).
The main issue that we have is that, every time we do drop/create new tables dynamically (on runtime) we get exception that the user injestworkeruser doesn't have enough permission to select or insert data from/into these newly created tables. This is the created user:
CREATE USER injestworkeruser WITH
  LOGIN
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

Is there anyway to grant the user permissions to apply CRUD operations to any newely created table?
This one didn't work for me as this applies to the existing tables:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO injestworkeruser;


texts: MySQL table not working after a few days of use

texts:
I have the following tables on MySQL server, 5.5.46:

CREATE TABLE `locking` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `origin` varchar(255) NOT NULL,
  `system` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `filename` (`filename`,`origin`)
) ENGINE=InnoDB AUTO_INCREMENT=74882007 DEFAULT CHARSET=utf8;

CREATE TABLE `locking_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `timeout` int(11) NOT NULL,
  `origin` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

And I have the following SQL which is executed periodically from different machines (simultaneously or not):

DELETE l FROM locking l,locking_rules r WHERE l.origin=r.origin AND r.name!='*' AND ABS(TIME_TO_SEC(TIMEDIFF(l.timestamp,NOW())))>r.timeout

In this table there are permanently inserted new rows. The table is in general at about 20000 rows size. I insert on DB using OpenJPA ORM, and remove using the provided "native query".

The problem:

Sometimes, once at few days, delete queries on locking table are just waiting and nothing happens anymore to this table. Can anybody give me some hints on which part should I look in order to see where the problem sits?

If I run show engine INNODB status, output doesn't have anything related to locking table locks or deadlocks.


texts:
I have the following queries:

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'',[PhoneNum]
    ,[PhoneExt]
    ,COUNT(2) as CNT
    FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')
        UNION ALL
SELECT  *
FROM    OPENQUERY([SERVERB],
    'SELECT ''SERVERA'',[PhoneNum]
    ,[PhoneExt]
    ,COUNT(2) as CNT
    FROM [DB].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')

I am getting the error Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in UNION ALL operator.

I have checked the collation of the columns and database involved and they are all Latin1_General_BIN.  The difference is SERVERA has a collation of SQL_Latin1_General_CP1_CI_AS and SERVERB has a collation of Latin1_General_CI_AS.  I have tried adding COLLATE SQL_Latin1_General_CP1_CI_AS to the column names but this has not worked.

Can anyone advise how to resolve this?

Added exact query

SELECT  *
FROM    OPENQUERY([SERVERA],
    'SELECT ''SERVERA'',resourcephone.[PhoneNum]
    ,resourcephone.[PhoneExt]
    ,COUNT(2) as CNT
    FROM [rtc].[dbo].[ResourcePhone] WHERE PhoneNum like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')
        UNION ALL
SELECT  *
FROM    OPENQUERY([SERVERB],
    'SELECT ''SERVERB'' COLLATE Latin1_General_CI_AS, resourcephone.[PhoneNum] COLLATE Latin1_General_CI_AS
    ,resourcephone.[PhoneExt] COLLATE Latin1_General_CI_AS
    ,COUNT(2) as CNT
    FROM [rtc].[dbo].[ResourcePhone] WHERE PhoneNum  like ''+44%''
    OR PhoneNum like ''+41%''
    GROUP BY PhoneNum, PhoneExt
    HAVING COUNT(2) > 1;')

This query gives the following message:

Msg 457, Level 16, State 1, Line 1
Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "Latin1_General_BIN" in UNION ALL operator.


texts: Returning setof derived from for loop in plpgsql

texts:
I have the below function.  It is intended to loop through every schema and return all user IDs from a table in that schema.  The schemas have the format: users[0-100].

$body$
declare
  r varchar;
  sql text := 'select userid from $1.users';
begin
  for r in select schema_name from information_schema.schemata where schema_name ~ '^users'
  loop
    return query select format('select userid from %s.users', r);
  end loop;
  return;
end
$body$

I have tried a few different ways, but I haven't been able to return a set.  Where am I going wrong?


texts: Get the total usage (in percent) between two specified dates

texts:
I have table at SQL server 2017:

Number 1 represent the test equipment, it is connecting and 0 is disconnected.
need your help to define query that can give me the amount of usage (in percentage) to :

Connect Statistics In accordance with the selected range of DateTime in
specific "DeviceName" and "PcName"
Running Statistics In accordance with the selected range of DateTime
in specific "PcName"

Thank you for your help

 USE [LabDevices]
  GO
  INSERT INTO [dbo].[Statistics]
               ([DeviceName]
               ,[ConnectStatistics]
               ,[RunningStatistics]
               ,[DateTime]
               ,[PcName])
         VALUES
               (<DeviceName, varchar(50),>
               ,<ConnectStatistics, bit,>
               ,<RunningStatistics, bit,>
               ,<DateTime, datetime,>
               ,<PcName, varchar(50),>)
  GO
  INSERT INTO [dbo].[Statistics] (DeviceName,ConnectStatistics,RunningStatistics,DateTime,PcName)
    VALUES ('Regatron_1', 1 , 0 , '2019-02-27 08:00:00','PC1'),
           ('Regatron_1', 1 , 1 , '2019-02-27 08:10:00','PC1'),
           ('Regatron_1', 1 , 0 , '2019-02-27 08:50:00','PC1'),
           ('Regatron_1', 1 , 1 , '2019-02-27 09:20:00','PC1'),
           ('Regatron_1', 0 , 0 , '2019-02-27 09:40:00','PC1'),
           ('Regatron_2', 1 , 0 , '2019-02-27 08:00:00','PC2'),
           ('Regatron_2', 1 , 1 , '2019-02-27 08:10:00','PC2'),
           ('Regatron_2', 1 , 0 , '2019-02-27 08:50:00','PC2'),
           ('Regatron_2', 1 , 1 , '2019-02-27 09:20:00','PC2'),
           ('Regatron_2', 0 , 0 , '2019-02-27 09:40:00','PC2')

Hi

First thank for your help

Let me clarify the question and expected result:

The query should generate the used time for each device in percent

In case the selected time is between '2019-02-27 08:00:00' to '2019-02-27 09:40:00' -> time range is 1.4

Regatron_1_ConnectStatistics -> from 8:00 to 09:40 = 1.4

Regatron_1_RunningStatistics -> from 08:10 to 08:50 and 09:20 to 09:40 = 1

Regatron_2_ConnectStatistics -> from 8:00 to 09:40 = 1.4

Regatron_2_RunningStatistics -> from 08:10 to 08:50 and 09:20 to 09:40 = 1 The answer:

Regatron_1_ConnectStatistics(%) = (1.4/1.4)*100

Regatron_1_RunningStatistics(%) = (1/1.4)*100

Regatron_2_ConnectStatistics(%) = (1.4/1.4)*100

Regatron_2_RunningStatistics(%) = (1/1.4)*100

Can you help to define the correct query?

Thanks


texts: multiple MYSQL full text indexing for innodb and memory + disk efficiency

texts:
I have the following table: (Create table statement)

CREATE TABLE `phppos_people` (
  `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone_number` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `address_2` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `city` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `state` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `zip` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `comments` text COLLATE utf8_unicode_ci NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `person_id` int(10) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`person_id`),
  KEY `phppos_people_ibfk_1` (`image_id`),
  FULLTEXT KEY `full_search` (`first_name`,`last_name`,`email`,`phone_number`),
  FULLTEXT KEY `first_name_search` (`first_name`),
  FULLTEXT KEY `last_name_search` (`last_name`),
  FULLTEXT KEY `full_name_search` (`first_name`,`last_name`),
  FULLTEXT KEY `email_search` (`email`),
  FULLTEXT KEY `phone_number_search` (`phone_number`),
  CONSTRAINT `phppos_people_ibfk_1` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

I have requirements for a fast search using first name, last name, email and phone number at once; but also being able to search each individual field. I also like the relevancy sorting of full text which cannot be done with Btree

The queries work well but according to my cloud database provider they recommending btree indexes for better memory and disk usage.

Do I have any other options/configurations or way to create indexes that don't use a lot of disk + memory? I have about 40 full text indexes per database that follow a similar pattern. Performance is great with these indexes and has really sped up product.

Is Innodb full text designed to use a lot of memory + disk? Here is my mysql variable settings

https://gist.github.com/blasto333/aa4241a4e37447961188356719ea6984

Here are some stats from my provider

Upon review was getting an idea of the actual data set size as stored within InnoDB.  It was a bit of a surprise when I found your dataset size much smaller than your on disk usage as listed below:

Such a discrepancy between the on disk and tablespace usage had me concerned initially that we had something orphaned, such as old temp tables, but later found this was being caused by a heavy usage of InnoDB Full Text indexes.

PHP-POS-DB-01
  On Disk:                              18GB
  Total DataSet Size:                   4.56GB
  Unused allocated Tablespace:          3.10GB  ## Fragmentation
  Total innodb used plus fragmentation: 7.66GB

PHP-POS-DB-2-01
  On Disk:                              9.7G
  Total DataSet Size:                   923.85MB
  Unused allocated Tablespace:          363.00MB
  Total innodb used plus fragmentation: 1.29GB


texts: Postgresql replication slot used by barman has a long replay_lag

texts:
I set up barman using wal streaming, I just notice I have a hug replay_lag. I'd like to keep it down to 0 but have no idea about how to do this.

I also have a database replica and it is working fine.

I have barman 2.7 and postgresl 10.8

When I check the postgresql replication status I found this:

select * from pg_stat_replication;

-[ RECORD 1 ]----+------------------------------
pid              | 23095
usesysid         | 169593
usename          | repmgr
application_name | replication_server
client_addr      | xxx.16.2.66
client_hostname  | replica_server
client_port      | 51164
backend_start    | 2019-07-05 23:03:03.194165-05
backend_xmin     |
state            | streaming
sent_lsn         | 22/30884870
write_lsn        | 22/30884870
flush_lsn        | 22/30884870
replay_lsn       | 22/30884870
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
-[ RECORD 2 ]----+------------------------------
pid              | 6689
usesysid         | 66019
usename          | streaming_barman
application_name | barman_receive_wal
client_addr      | xxx.172.16.109
client_hostname  | barman_server
client_port      | 40680
backend_start    | 2019-07-16 00:00:06.903489-05
backend_xmin     |
state            | streaming
sent_lsn         | 22/30884870
write_lsn        | 22/30884870
flush_lsn        | 22/30000000
replay_lsn       |
write_lag        | 00:00:03.01204
flush_lag        | 00:00:01.085313
replay_lag       | 583:31:21.110173
sync_priority    | 0
sync_state       | async

select * from pg_replication_slots ;

-[ RECORD 1 ]-------+----------------
slot_name           | barman_slot
plugin              |
slot_type           | physical
datoid              |
database            |
temporary           | f
active              | t
active_pid          | 6689
xmin                |
catalog_xmin        |
restart_lsn         | 22/30000000
confirmed_flush_lsn |

barman status master_server

Server master_server:
        Description: master_server - streaming
        Active: True
        Disabled: False
        PostgreSQL version: 10.8
        Cluster state: in production
        pgespresso extension: Not available
        Current data size: 11.4 GiB
        PostgreSQL Data directory: /var/lib/postgresql/10/main
        Current WAL segment: 000000010000002200000030
        PostgreSQL 'archive_command' setting: barman-wal-archive barman_server master_server %p
        Last archived WAL: 00000001000000220000002F, at Fri Aug  9 06:56:05 2019
        Failures of WAL archiver: 63 (000000010000001C00000062 at Mon Jul 15 23:59:21 2019)
        Server WAL archiving rate: 2.60/hour
        Passive node: False
        Retention policies: enforced (mode: auto, retention: RECOVERY WINDOW OF 1 MONTHS, WAL retention: MAIN)
        No. of available backups: 1
        First available backup: 20190809T063454
        Last available backup: 20190809T063454
        Minimum redundancy requirements: satisfied (1/1)

Can anyone point me to a resource I can check to figure out how to fix this Failures of WAL archiver and replay_lag?

Thanks in advance


texts: MariaDB crashes on mismatched tablespace ID for InnoDB

texts:
I'm having an issue with some corrupted InnoDB tables on a stand-alone MariaDB 10.1 server running on Debian Stretch. I've encountered this issue in the past, but never found a working solution, so I ended up just rebuilding the DB instance. I'm trying to see if there's a way to recover without a rebuild.

Here's the situation:

We have a Drupal site that uses InnoDB for pretty much all of the tables, and there are a handful of tables that are used for caching (named cache_). The contents of the tables are temporary, so the data within them doesn't matter. What happened is that multiple cache_ tables became corrupted somehow and cause MariaDB to crash whenever they are accessed (even something as simple as SHOW TABLES). My guess is the corruption occurred because of TRUNCATE operations that somehow caused a blip in MariaDB when they were rebuilt.

Some key MariaDB settings:

ignore_builtin_innodb
plugin_load = ha_innodb.so
innodb_file_per_table = 1

Sample log messages are below (trimmed for size):

[ERROR] InnoDB: tablespace id is 1479582 in the data dictionary but in file ./mydb/cache.ibd it is 1480371!
[ERROR] InnoDB: Trying to do i/o to a tablespace which exists without .ibd data file. i/o type 10, space id 1479582, page no 0, i/o length 16384 bytes
.. (above repeated 100 times) ..
[ERROR] InnoDB: Unable to read tablespace 1479582 page no 0 into the buffer pool after 100 attempts. The most probable cause of this error may be that the table has been corrupted. You can try to fix this problem by using innodb_force_recovery. Please see http://dev.mysql.com/doc/refman/5.6/en/ for more details. Aborting...
.. (skip the crash report lines) ...
InnoDB: Error: table 'mydb/cache'
InnoDB: in InnoDB data dictionary has tablespace id 1479582,
InnoDB: but a tablespace with that id does not exist. There is
InnoDB: a tablespace of name mydb/cache and id 1480371, though. Have
InnoDB: you deleted or moved .ibd files?
InnoDB: Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html
InnoDB: for how to resolve the issue.

From a technical standpoint, I understand why the error is happening (the internal tablespace ID in the .ibd file doesn't match what the global InnoDB data dictionary thinks it should be). From my research, a TRUNCATE operation on a table will recreate the table with a new tablespace ID. The MySQL manual states the TRUNCATE will not change the tablespace ID (I believe starting in 5.7, I'm having trouble finding the reference again). MariaDB doesn't seem to have this patch incorporated (you can verify by looking at the INFORMATION_SCHEMA.INNODB_SYS_TABLES table before and after a TRUNCATE).

Things I've tried which all result in a crash:

Pretty much anything on the trouble shooting page referenced in the logs (see above)
TRUNCATE cache
DROP TABLE cache
ALTER TABLE cache DISCARD TABLESPACE
Deleting the .idb and keeping the .frm file
Setting innodb_force_load_corrupted=1 in config
Doing all the above with innodb_force_recovery set anywhere from 1 to 6

Ultimately, the reason behind the corruption is not as important to me as is being able to clean up the corrupted tables. I have no problem removing them completely, but any operations on the table cause the DB server to crash. Most of the other solutions I've found either involve a different type of corruption that one of the above commands can fix, or they're using MySQL and it behaves slightly differently.


texts: How to hide SQL Server connection string and SQL login password from Developers

texts:
We are using Microsoft Azure App Services to host some of our Web Apps.
Web Apps connect to on-premises SQL Server over hybrid connections.

When new Web App needs access to the on-prem SQL Server, a dedicated SQL login is created, and the connection string (with password, etc.) is sent to the developer responsible for that app. Developer then goes to Web App -> Configuration and pastes the connection string into corresponding section, so the application could use it.

The problem with this approach is that some other developers have Contributor access in a subscription, and they can see those connection string, and subsequently they can see the SQL Server login and password.

Those SQL accounts can have elevated permissions on tables, which are normally not supposed for Developers to have, but as we can see SQL Serverpasswords are exposed, so these can be abused.

Questions:

Is there a way to put connection string "somewhere else" - not in Configuration pane of the Web App, but in some other location where application can use it, but developer (even if dev is a Contributor) can not?
Is there a way for an Azure Web App to use Integrated Security=TRUE and login to SQL Server using some domain account, so developers don't see its password in connection strings?
Any other options? I know it is also policy thing, but right now it is what it is, and I can't say Developers are doing some abuse, no, but still would like to implement some secure solution.


texts: How to force a sysadmin to store data in schema other than dbo?

texts:
I am using a GUI tool to upload data in my SQL Server 2016 database.
The user being used for above task has been granted sysadmin permission
I created a dedicated schema to store above data. This schema is owned and default to above said user.
The issue is, when i upload data it stores in dbo schema instead of dedicated schema and i couldn't revoke sysadmin priv from this user.

Kindly let me know how to upload data to this dedicated schema without revoking sysadmin priv ?

Thanks


texts:
I'm using Amazon RDS to run my Postgres database, v9.3.3.

Recently, I had 2 instances where updating the schema caused the database to stop responding and I had to restart it in order to put the site back to normal.

During the freeze, CPU Utilization levels were low (< 15%), Read/Write IOPS did not spike. The only metric that spiked was the DB Connections, which I suppose are all the queries coming in from the web servers.

The first time was to create a new table. The second time was just to add a numeric column into a table with about 10,000 rows of data.

What could be causing these freezes?


texts: Increase performance WITHOUT adding indexes

texts:
I'm having a rather large table (350MB, 1.3M rows) and there are dozens of queries arriving at this table every second. Most queries are super-fast thanks to indexes - but some others aren't.

Obviously I'm using indexes for the most common queries on this table; but I always hesitated to add indexes for queries which are only performed every few seconds or even minutes. They are just a minority compared to the more important queries.

As far as I know, adding more indexes makes inserting new rows and updating existing data slower (and UPDATE/DELETE/INSERT queries happen very frequently for this table); but adding indexes also makes normal SELECT queries slower, since MySQL has to find the perfect index first and has to check more index possibilities.
Obviously, the slightly slower SELECT statements wouldn't be a problem; but I'm more worried about the UPDATE queries.
But on the other hand: I don't want these rare queries to take 1+ seconds, either.

Is there anything else I could do?
These performance heavy queries are creating temp tables and I thought about increasing the tmp_table_size (it's currently 16MB and I'm having 6+GB of unused RAM space). But then I read that this makes things even worse.

Join/Sort buffer sizes are already at 128/256MB
(Now that I think about it: These two variables are lower than the table size itself. That might be a problem; though I don't want to increase variables without knowing exactly what I'm doing.)


texts: How to find out if rows are refenced from other tables?

texts:
I have a table named contacts (primary key id) which is referenced by a number of other tables via foreign keys. When a list of contacts is displayed in the application, I get their primary keys in an array and need to determine if these records could be deleted. This is just used for display, no actual deleting is taking place here.

Based on this answer, I can find out if one record can be deleted:

(SELECT 1 FROM case_contacts WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM case_payments WHERE contact_id = 5000 LIMIT 1)
UNION ALL
(SELECT 1 FROM invoices WHERE contact_id = 5000 LIMIT 1)
-- etc
LIMIT 1;

I could run this query for every contact ID I'm given, but that seems very inefficient.

Is there a way to send a single query for multiple contact IDs (up to ~500 at once)? Any kind of result would be fine, including only the IDs that can be deleted, or only the IDs that cannot be deleted, or a result set of (contact_id, can_be_deleted).

EDIT: This is what I'm using until I find a more efficient solution:

SELECT  c.id
  FROM  contacts c
 WHERE  c.id IN (1,10,20,1557,5000,15057)
        AND NOT EXISTS (SELECT 1 FROM case_contacts WHERE contact_id = c.id)
        AND NOT EXISTS (SELECT 1 FROM case_payments WHERE contact_id = c.id)
        AND NOT EXISTS (SELECT 1 FROM invoices      WHERE contact_id = c.id);


texts:
I'm trying to model a hierarchy of categories where a category can have multiple parents ( an overlapping tree model as described in this book )

I have the following tables

video_categories
  int     id
  string  name

video_category_links
  int     parent_id  (foreign_key to video_categories.id)
  int     child_id   (foreign_key to video_categories.id)

and an index on [ parent_id, child_id ] for uniqueness

The issue with this setup is that
A can be parent of B, and B can be parent of A at the same time

Is there a way to specify a db constraint to avoid this or should I ensure it at the application level?

Thank you very much


texts:
We're running an intensive application load (thousands of operations/second) against a SQL Server database with quite a bit of data. Some tables have billions of rows, several of these have lots of inserts and updates.
DB performance is generally quite OK, but periodically we get query performance problems; rather simple queries that previously worked just fine may take 10-100x the time all of a sudden.
This appears to be related to table/index statistics and the query optimiser - most times a statistics update will fix the problem, then again other times an update of the statistics will make the situation worse (re-running the statistics update will then typically solve the problem eventually).
What appears to be happening is that the optimiser decides to use objectively wrong indexes for some queries; all of a sudden, after having used the correct one for days and weeks.
My question is: Why is this happening and what can we do about it?
This database has been running for years with basically the same load, pretty much the same queries, and the same amount of updates. For 99.995% of the queries, there should be no reason to ever decide on different index strategies over time, regardless of the input (and - indeed - doing so will demonstrably completely destroy query performance).
As indicated above, automatically updating statistics on a schedule will often generate horrible problems - if the statistics sample gets skewed (which appears to happen at least 5% of the times) we end up in a world of pain.
Is there any way to tell SQL Server (on certain tables) that the statistics histogram and density will not change over time, so please just continue using the same query plan for queries involving this table? If not, how can we ensure a predictable result of statistics update over time (avoiding the skewed statistics problem described above)?
No stored procedures. We do have control over the SQL, so it can potentially be changed but it's a LOT of code so it would be unfortunate if we had to change every single query (e.g. adding in an additional clause).
A follow-up question: parameter sniffing only appears to be relevant for stored procedures, is that correct?


texts: Select all children from infinite parent-child relations

texts:
I have a comments table, and rows have parent child relation, which means any comment can have children (responses) and any child itself can also have children (infinitely).

All rows are inside one table, and root row's parent_id is 0.

Now I want to select all children (in any deep level) of a given parent_id

Any idea ?


texts: How to make a PostgreSQL trigger thread-safe

texts:
I want to make my PostgreSQL trigger function do something like incrementing counters etc (something complex with multiple queries etc) thread-safely (so the same function triggered concurrently won't mess things up), is it possible to do it with advisory lock like this?

BEGIN
    SELECT pg_advisory_xact_lock(1);
    -- do something thread-safely here
    ...
END;


texts:
I'm an intern stuck dealing with a database system that is as old as I am.

The Issue:

I have a perl script pulling data from a server running Oracle 8 (8.1.7) and placing the data into a .csv.  The server that runs the script is being decommissioned and I am now need to port that script into a new server running a client for oracle 11 (11.2.0.4.0).  The problem is that when I run that script in the new server I get an error:

  failed: ORA-03134: Connections to this server version are no longer
  supported. (DBD ERROR: OCISessionBegin)

All servers are linux servers

The questions:

Documentation reagarding Oracle 8 is scarce but I'm assuming the error is happening because Oracle client 11 dosen't support oracle 8.  Assuming I can find a copy of client 8 is it possible to load 2 clients on one machine?
I could easily re-write the script in Java. The issue is figuring out how to find a driver that is compatible with Oracle 8, what is the latest version of OJDBC that supports oracle 8?

I'm open to other suggestions as to how to read data from Oracle 8. Using a server running Oracle 10 as an in-between is not an option


texts:
There's a column in my database which has got this kind of value:

id|       body
-----------------------------------
1 | ...  ...
2 | ...  ...
3 |...  ....

Basically, after the = sign I can have anything. And I would like to change it for nothing.

I know if it was a static value I could use:

SELECT REPLACE(column, $$$$, ' ') FROM table

But I couldn't do it dynamically.


texts:
I have a table named 'DATAENTRY' with the following columns

Title
FName
MName
LName
Add1
Add2
Add3
City

Now I want to select records which have different (FName, MName ,LName) so I tried

SELECT DISTINCT FName, MName, LName FROM dataentry

The problem is that I want full records with all columns but they should be unique across  (FName, MName, LName)

I can't use

SELECT DISTINCT Title, FName, MName, LName, Add1, Add2, Add3, city FROM dataentry

because two records with same values in (FName, MName, LName) but with different values in Title or Add1 will be treated as distinct records.


texts: How to use a database across two availability zones

texts:
I have servers in the USA and Japan for my website. At the moment they are both connecting to a MySQL server in the USA, which is obviously perfect for my local USA clients but quite slow for my Japanese clients.

What is the best strategy for reducing latency? How can I introduce a local MySQL server for the Japanese end and sync these up?

I have considered master-master replication but this appears to be an incomplete and unstable solution. Are there any other suggestions?


texts: service mongod cant start, permission denied mongod.lock when changing dbpath

texts:
What i want to do: change dbpath of mongodb from /var/lib/mongodb to /home/user/mongodb/data

im using db version v3.4.7

what i've done

mkdir -p ~/mongodb/data
sudo chown -R mongodb:mongodb  /home/user/mongodb

change /etc/mongodb.conf

storage:
  dbPath: /home/user/mongodb/data/
  journal:

sudo mongod -f /etc/mongod.conf

this works. Also tried

sudo mongod --dbpath /home/user/mongodb/data/

command works fine

sudo service mongod start

command fails. checking the log file.

  Permission denied: "/home/user/mongodb/data/mongod.lock", terminating

what is going on? I've tried everything i know. even tried setting chmod 777 to the lock file, and still has permission denied.


texts:
While pt-table-checksum seems to work well for most tables, a few of the big ones get this:

  Error checksumming table mydb.mytable: Redundant argument in printf
  at /usr/bin/pt-table-checksum line 8106.

Versions:

  pt-table-checksum 2.2.11

Servers (MariaDB acting as both slave to MySQL and master to another MariaDB, happens in both cases):

  mysql  Ver 14.14 Distrib 5.6.36, for Linux (x86_64) using  EditLine
  wrapper mysql  Ver 15.1 Distrib 10.1.24-MariaDB, for Linux (x86_64)
  using readline 6.3


texts:
I have a complex SELECT query table that identifies relationship between event_id and attribute. Here is a simplification with a VALUES expression:

SELECT event_id, attribute
FROM (
  VALUES
    (1, '2D'),
    (1, 'IMAX'),
    (2, 'IMAX'),
    (3, '3D')
) event_attribute (event_id, attribute)

I want to include an extra (event_id, '2D') record for every event_id that is not already associated with a 3D or 2D attribute. How to conditionally append rows to a result set?

In case of the above table, the expected result would be:

(1, '2D'),
(1, 'IMAX'),
(2, 'IMAX'),
(2, '2D'),
(3, '3D')

There is also a table event with one row for each relevant id.


texts:
I have a system where users can 'like' content. There will likely be many hundreds of these likes going on at once. I'd like it to be AJAX driven so you get an immediate response.

At the moment I have a mysql table of likes which contains the post_id and user_id and I have a 'cached' counter on the posts table with the total number of likes - simple so far.

Would I benefit in any way, from storing any of this information in mongodb to take the load off of mysql?

At the moment, I click like, and two mysql queries run - and INSERT into likes and an UPDATE on posts. If I'm in a large-scale environment in heavy read/write situation what would be the best way to go?

Thanks in advance :)


texts: Database design how to track a change on a patient row and set in a log table

texts:
I need to track any update on my patients table.

The table contains the following fields:

Patient ID;
Name;
address;
phone
relative name;
relative phone
smoker;
alcoholic.

Now I need to track if a profile of a specific patient is changed in any field, so if he was smoker 2 months ago and now not, I want to see a log that the nurse changed the profile of the patient x from smoker to non-smoker.

First Approach

Now, should I create a similar table to this but it contains a foreign key connected to the initial table and each time an edit/update is made a row is added to the second table ?

Second Approach

Or I need to track every field as a table, so I will make a table for smoking history edit, alcoholic history status table and add a row into them whenever an update happened ?


texts: TempDB Best Practices For Shared Storage

texts: Microsoft recommends separating the SQL Data Files from the Temp Files to limit device contention but what about cases involving shared storage.  I have a shared environment where the data and temp files are located on the same mount point or LUN.  Is it best practice to separate them onto different?  Or will it make a difference from a performance standpoint?


texts: SSIS OLEDB connections dropping out during long-running data flows

texts:
Long-running SSIS Data Flows (OLEDB, which transfer > 4M rows) intermittently fail with error "Protocol Error in TDS Stream" and detail "An existing connection was forcibly closed by the remote host" or "Semaphore period timeout".

I have noticed that most times (but not always), this is accompanied by an event in the SQL server's OS (System) Event log "The system time has changed" (where the time was adjusted by a few seconds).  SSIS is running on an app server (not the SQL server).

Has anyone encountered issues like this?  Where (if anywhere) can I look, or what can I do to further debug the issue?  I would like to (or need to) get clear evidence of a networking problem if that is the root cause.  I also feel that the fact that the server time is adjusted almost every day, sometimes twice a day, is out of the ordinary.  Has anyone had to deal with the server time changing so often on your underlying OS and if so, how?


texts:
In a transaction, if I select results to display to the screen, I get the message Query result with x rows discarded.

How can I view those results?  Something is amiss with my query and I want to see what the values are in a temp table at a certain point.


texts: What's the easiest way to re-format CSV exports from MySQL into Excel/ flat-file fields?

texts:
I've just had some database backup problems on a website I run containing some important lead data. Unfortunately, I've now lost one of the key MySQL tables, which means my data has lost its relationship and is now hard to read and manipulate in Excel. Luckily, I did manage to keep 1 table which contains the important values. I now just need to re-format it for Excel to make it easier to work with.

Currently when I export from the DB to CSV the data looks like this:

id,lead_id,field_number,value
1,1,1,Mickey
2,1,2,Mouse
3,2,1,Minnie
4,2,2,Mouse

I would like to re-format this data into the following format:

id,lead_id,field_1,field_2
1,1,Mickey,Mouse
2,2,Minnie Mouse

I hope that makes sense, I wasn't sure how else to explain this question.


texts: Does combining columns into 1 column helps to optimize a large table?

texts:
I have user, items and user_items tables, and as usual user has many items.
Tables contains:
User: ~50K
Items: ~3.5M
UserItems: ~5M

I am using MySQL and most of my queries are selecting items of a user.
So I wonder If it helps to keep data on UserItems table as follows,

user_id item_ids
1       1,22,23,45,66...

Then after getting ids, simply parse to get item ids.
Any suggestion/experience?


texts: Cannot read from secondary availability group

texts:
I have an issue with Always On Availability Groups In SQL Server 2012.

I installed a replica secondary SQLNODE3. It was fine and everything retrievable form tables but suddenly I found I could not read anything from the database. In Activity Monitor, I see the query is suspended and the wait type is:

HADR_DATABASE_WAIT_FOR_TRANSITION_TO_VERSIONING

I wait for the updating transition for more than day but nothing happened.

I restarted the replica server and created the AG from scratch but still cannot read data.

How can I read from the replica and avoid this wait type?


texts:
I have inherited a db, and I need to transform an existing parent-child relationship into all parents.

Let me first explain the existing data.The tables have a structure something like:

Thing                  Widget
------------           ------------
thing_id               widget_id
thing_name             widget_name
thing_rank             widget_rank
                       thing_id

The data is presented to the end user as a list of Thingss, and occasionally a Thing has-a Widget, and the widget is presented as being "beneath" or "sub" to the Thing. So there's some hierarchy. The other key thing is that this presentation is sorted by the thing_rank and widget_rank.

Here's a concrete example with data:

Things:

1001  "thing blue" 1
1002  "thing green" 3
1003  "thing red" 4
1004  "thing yellow" 2

Widgets:

2001  "widget cucumber" 2  1002
2002  "widget bean" 1  1002

This would be presented with the Things ordered by rank, then the two child Widgets belong to "thing green", and ordered by their rank:

1 thing blue
2 thing yellow
3 thing green
   1 widget bean
   2 widget cucumber
4 thing red

The task is to remove the hierarchy, but preserve the rank order. For example, the above data would be assigned a new rank, and all data would belong to the same relation -- no longer a parent-child relationship. For example:

1 thing blue
2 thing yellow
3 thing green
4 widget bean
5 widget cucumber
6 thing red

And the single relation would be:

Stuff
-------------
stuff_id
stuff_type   (thing|widget)
stuff_name
stuff_rank

I'm looking for how to create a Stuff view that presents both Thing and Widget data, with new rank assignments.


texts: What is the best order of columns on a compound index?

texts:
I have a series of small 'attribute' tables (innodb) with this structure:

uuid
name (TEXT)
organisation_id (INT)

In my application, when users add a new attribute, I am first performing a lookup if it already exists, SELECT * WHERE name = '?' AND organisation_id = '?'

What would the optimal index setup be?

Bonus:

I am also displaying all attributes from an organisation so often just querying the table by organisation_id, do I need a separate independent key on the organisation_id column or will the compound be ok for this too?


texts: Inserting variables into another variable

texts:
I've been working on the code below. I'm trying to insert results of a declared variable into a sql statement. I've highlighted the section below that I'm talking about. What am I doing wrong here? I've tried a few different things. Do I need to create a temp table and import from the temp table to the live table?

Thank you for you time and help.

DECLARE @counter INT
    ,@dimtablename NVARCHAR(50)
    ,@dimKey INT
    ,@processdatekey INT
    ,@DimCount INT
    ,@sqlstmt VARCHAR(8000)

WHILE @counter <= (
        SELECT MAX(DimKey)
        FROM dbo.audit_HostDimensionName(NOLOCK)
        )
BEGIN
    SET @dimtablename = (
            SELECT DimName
            FROM dbo.audit_HostDimensionName WITH (NOLOCK)
            WHERE DimKey = @counter
            );
    SET @dimkey = (
            SELECT DimKey
            FROM dbo.audit_JHostDimensionName WITH (NOLOCK)
            WHERE DimKey = @counter
            );
    SET @processdatekey = (
            SELECT cast(convert(VARCHAR, dateadd(d, - 1, getdate()), 112) AS INT) AS datekey
            );
    SET @DimCount = '(SELECT COUNT(*) from''' + @dimtablename + ''' with(nolock)) ';
    SET @sqlstmt = 'INSERT INTO dbo.Audit_HostDimensionTracking(DateKey, DimKey, Row_Count) VALUES (''' + @processdatekey + ',' + @dimkey + ',' + @DimCount + ')'''

    PRINT @sqlstmt

    EXEC (@sqlstmt)

    SET @counter = @counter + 1
END