texts: When is running a graph database like OrientDB in cluster mode required?

texts:
OrientDB can run in a distributed environment, supports ACID transactions and peer to peer replicaiton.

According to the book "NoSQL Distilled: A Brief Guide to the Emerging World of Polyglot Persistence" by M. Fowler and P. Sadalage, graph databases work best in a single server configuration (chapter 4.1).

What is the reason for this statement and when should I use a clustered version for a graph database?


texts:
I am attempting to fix my barman backup for a particular database cluster:
When I attempt to start a backup it fails with:
ERROR: Impossible to start the backup. Check the log for more details, or run 'barman check pgdb'

-bash-4.2$  barman check losrlcmp10
Server losrlcmp10:
...
continuous archiving: FAILED

In the server log for pgdb I see the following barman query:
            (last_failed_wal IS NULL OR last_failed_wal LIKE '%.history' AND
              substring(last_failed_wal from 1 for 8) <= substring(last_archived_wal from 1 for 8) OR
              last_failed_time <= last_archived_time) AS is_archiving,
              CAST (archived_count AS NUMERIC) / EXTRACT (EPOCH FROM age(now(), stats_reset)) AS current_archived_wals_per_second
              FROM pg_stat_archiver

and the result of that query is:
archived_count                   | 1596
last_archived_wal                | 000000010000002600000034
last_archived_time               | 2021-02-18 15:59:50.65477-06
failed_count                     | 1
last_failed_wal                  | 000000010000002A00000015
last_failed_time                 | 2021-03-24 12:22:41.598057-05
stats_reset                      | 2020-12-01 08:29:20.854759-06
is_archiving                     | f
current_archived_wals_per_second | 0.000104813032288719

How do I solve this?
I have also verified that my archive_command does work, so that doesnt seem to be an issue.


texts: How can I redirect database requests in my postgres server

texts: So I have a list of(lot many) scrapers which push lot many data into one of my database location. Unfortunately I have a requirement that my database location  have to be changed for some performance issues. Rather than changing my so many scripts config can I have some mechanism implemented in my db server so that database requests coming from certain IP(machines) I would redirect  them to other database/server?


texts:
Location of MySQL configuration file (ie: my.cnf) not specified

How can I fixed this thing?
I been trying to reinstall mysqlserver 5.5 / 5.7 and workbench countless of time. But I still not able to fix this issue.


texts:
I have no maintenance plans yet I see indexes being created in the background (low spid). I have no idea what indexes are being created or why. Using Standard SQL Server 2017 - a VM on Azure with SQL Installed.

I checked and did not see anywhere with automatic index management.  You can configure backups, etc. but I cannot find a spot for indexing.

I tried EXEC sp_WhoIsActive @show_system_spids = 1; and got this result:

(542701ms)RESOURCE_SEMAPHORE

I think what is happening is I have a "messed up index" and the system is trying to fix it.  I cannot drop the index whatsoever or rebuild it.  At least that is my guess.


texts: MySql How to rollback my database to a certain point in time the fastest way?

texts:
I'm currently working in a testing database and we are not cleaning up after test because of our current configuration and we probably aren't changing that configuration soon, we are currently using a server to apply some UI testing on a staging server.

So I tried mysqldump but the restore time was slow, then I tried mysqldbcopy and the copy time is better, but I read something about incremental backups with innobackupex of percona xtrabackup and I think it could be faster.

I would like to know what is the best option to rollback a database to a certain point in time in the faster way possible.


texts: How to find all records in MongoDB whose primary key is a user defined character string

texts:
I would like to know the command to find all records in MongoDB whose primary key (_id) is a user defined character string, or find all records where all primary keys (_id) are not the 12-byte ObjectID BSON type.

For example, in SQL the query might be similar to:

select id from restaurants where id not like "%ObjectId("574d35bda039438e02236caf")%"

or

select id from restaurants where id in $regex[a-z];


texts: SQL Server System Objects and Intellisense

texts:
I have created two stored procedures that I want to be available from all databases on SQL Server 2017. I created them in the master database, used sp_ as the prefix for both of them (e.g. sp_MyProc1 and sp_MyProc2), and then ran sp_ms_marksystemobject on them to make them system objects.
I can indeed call them from any database and they work as expected.
The problem is that Intellisense doesn't know that they're there unless master is the default. It does recognize built in system procedures like sp_HELP. Is there another setting I need to change to make Intellisense recognize them? We're using SSMS v17.9. I tried putting them in the sys schema, but apparently SQL Server doesn't allow that on master.
Thank You,
Robbie


texts: Turn off Error AND Usage Reporting SQL Server

texts:
Unfortunately, SQL Server usage internet bandwidth and us activity in SQL, for R&D. You can find in your server, this service is called: SQL Server error and usage.

This service is active without any privacy permission, in installation process.

If you don't like this future can be turn off this activity. Like below:

But, I have a problem in SQL Server 2019: we use two SQL instances on one server - both SQL Server 2017 and SQL Server 2019.

I can turn-off SQL Server 2017 error and usage reporting service as shown in the screenshots, but when I open error and usage reporting service for SQL Server 2019 in a cmd window, and then close automatically and don't show anything:

How to turn off error and usage reporting SQL Server 2019 now?


texts: SQL Server 2012 Installation Has No Option To Uninstall

texts:
I've recently inherited a laptop for testing from a client, unfortunately it wasn't a fresh image so it has some software already installed that's causing me some grief.

The short version is their IT department has had some issues, I think the only person still working there is the guy that makes the tea, and they've asked me to setup an easy to deploy SQL Server 2012 installation and instance (easy enough, this is scripted and ready to go) the issue is that SQL Server 2012 is already installed on the laptop and there's no option to uninstall it through Add/Remove, so I can't do a test install for them to look at.

I'm thinking that the installation is corrupt, and I'm going to have to either A) Remove it all manually and hope I get all the registry keys and associated files, or B) Send it back to the client and wait for them to send me another laptop (This one's not preferable, like I said they're having a number of issues with their IT department and I'm pretty sure I'm just going to get the same laptop back as it is now)

Am I right in thinking this is a botched/corrupted installation, or have I missed something? It's windows 7 professional, logging in with a local admin account. I've been able to uninstall some other applications.

I checked some other posts on here and it sounds similar to ;
How to uninstall SQL server 2012 not showing in control panel's "Programs and features"

Thanks for your opinion.
Neil


texts:
I am getting following error message when i write sqlplus on command prompt

SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Here are the path details


texts:
Given the below CTE:

with seekResults as
(
select DISTINCT
 trx_id_fk,
 account_FK,
 customer_cif,
 customer_tin,
 TIN_Type,
 ID_NUMBER ,
 SEARCH_NUMBER
 FROM transactionsOP
)

 select trx_id_fk
  ,case when account_FK is not null then (select count(1) from seekResults a where a.account_fk = s.account_fk and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   when customer_cif is not null then (select count(1) from seekResults a where a.customer_cif = s.customer_cif and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   when customer_tin is not null then (select count(1) from seekResults a where a.customer_tin = s.customer_tin and a.TIN_Type = s.TIN_Type and a.SEARCH_NUM = s.SEARCH_NUM)
   when ID_NUMBER is not null then (select count(1) from seekResults a where a.ID_NUMBER = s.ID_NUMBER and a.SEARCH_NUMBER= s.SEARCH_NUMBER)
   else (select count(1) from seekResults a where a.SEARCH_NUMBER= s.SEARCH_NUMBER)
       end as 'transaction_period'
from seekResults s  ;

I noticed that it is taking a lot of time doing the calculation because it is calling the seekResults CTE over and over again, mostly in the select count(1) section:

select count(1) from seekResults  a

Is there a better way to improve that part ? So far I haven't found anything to replace it with.


texts:
SQL developer is giving me errors of

Network adapter could not establish the connection error

Telnet is working from the local machine to the target (scan is in dns)
nslookup from local machine is also working. It is resolving the IPs.
Checked with netstat -anp | grep 1522 it is listening and port is open.
Tried connecting using Basic connection and as well as TNSNAMES.ora using instant client in my Windows machine. I get this error.
While trying sqlplus from my local machine, it gives me

ORA-12170: TNS:Connect timeout occurred

I have set the following in sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT, HOSTNAME)
SQLNET.INBOUND_CONNECT_TIMEOUT=360

But no-luck!

Does sql-developer needs any tweaks for connecting to RAC scan-ip's?

I have tried all the possible the answers in SE and googled. Thing is i'm trying to connect to the database through one PC but not every PC. What could be the possible issues? How to go about debugging?


texts: What is the getdate() equivalent for MongoDB?

texts:
I have a query to update and set some values in some fields.
Obut Now I need to update these field and in adition, I need to set DateOfModification to the current date and time.
What is the equivalent to GETDATE() from sql server to use in a $set?
I tried this:
db.col.updateMany({
     "field.field":"zzzzzzzz"
},{$set:{
        "field2" : {
        "field3" : ObjectId("5d4dc2c2b3e6de001b934ec6"),
        "field4" : "xxxxxxxxxxxxxxxxxxxxxxxxxx"
        },
        "DateOfModification: "$$NOW"
    }
}})

but It seems the $$NOW only works with mongoDB 4.2 and I'm still using 4.0
Thanks
Edit:
I tried $currentDate but still getting errors:
db.testeDBA.update(
        {},
          {$set: {"item":"anything",$currentDate: {"modifiedOn":true}}
        })

'The dollar ($) prefixed field '$currentDate' in
'$currentDate' is not valid for storage.+


texts: How can I fix an issue where I can't start an incremental database backup?

texts:
When I'm trying to parse the following instruction in RMAN:
backup incremental level 0 database plus archivelog delete input;
It creates the backup properly but when the backup starts, I'm getting this error:

I tried to find this file, yet when I checked - it doesn't exist in directory neither in database.
select name, ts# from sys.ts$;

I also tried incremental backup with skip anaccessible attribute
backup incremental level 0 database plus archivelog delete input skip inaccessible
I get more described error message:

The same was for skip offline attribute:
backup incremental level 0 database plus archivelog delete input skip offline

Of course, I had database in archivelog mode on:
select log_mode from v$database;

What can I do to solve this problem?


texts:
I cannot create master key in oracle 19c...
I set parameter in sqlnet.ora like as:

WALLET_LOCATION=
  (SOURCE=
    (METHOD=file)
    (METHOD_DATA=
       (DIRECTORY=/opt/oracle/product/19c/db_home/admin/ALF/wallet/tde)))

Also set parameter TDE_CONFIGURATION:

ALTER SYSTEM SET TDE_CONFIGURATION="KEYSTORE_CONFIGURATION=FILE" scope=both;

After I restart oracle and performed following commands:

alter session set container=CDB$ROOT;
administer key management create keystore identified by "mypassword";
administer key management set keystore OPEN identified by "mypassword";

And tried to create master key:

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'master key' IDENTIFIED BY "mypassword" WITH BACKUP USING 'masterbackup';

And I always get following error:

ERROR at line 1:
ORA-28362: master key not found

Do you know, how I resolve this issue? Although, I performed commands by instruction...
Thank you.


texts:
I'm not sure what happened but I logged in on friday and did psql -d name to access a db that was perfectly running just fine. Then I log in this monday morning and try to access the same database with psql -d dbname and this is what I got:

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "johndoe", database "db", SSL off

I try sudo su postgres then I got this:

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

After running

 sudo find / -name pg_hba.conf

I get:

/var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/9.2/data/pg_hba.conf

Modifying the 1st pg_hba.conf and allowing access to postgres allowed me to login through psql.
With a \l command there showed no databases or users.
This confirms my suspicion that the postgres install I did last Friday did not update the old version I had but installed a new copy of it onto the server.

IS there anyway of logging into the old postgres server or retrieving those databases?
I know for a fact they did not get wiped with the new install of postgres as there are two pga_hba.conf files on the box.

If it did get wiped, is there any way of restoring my Centos 6.5 server to a past date?

EDIT:

Afer stopping the new running server with:

service postgres stop

I type postgres into the terminal and I get:

postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.

So I point to the correct server config with:

postgres -D /var/lib/pgsql/9.2/data

after switching with su postgres and I get this error:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 8.4.13.

This used to work where it would just give me a warning saying that the db version was 8.4 and server version was 9.2 so some features might not work.


texts: Extract and combine multiple values from a jsonb column

texts:
I am looking to extract multiple values from a jsonb column in Postgres, and am running into an issue where some values are coming back null.

fiddle

Setting up schema:

create table jsonb_test (test jsonb);
insert into jsonb_test values('{
"title": "test",
"tags": [
    {"tag": 1},
    {"tag": 2}
]
}'::jsonb);

Query that I am running:

select jsonb_path_query(test, '$.title'::jsonpath) as title,
       jsonb_path_query(test, '$.tags.tag'::jsonpath) as tag
from jsonb_test

The result that I am looking for should be:

| title | tag |
|-------|-----|
| test  | 1   |
| test  | 2   |

and what I am getting is:

| title | tag |
|-------|-----|
| test  | 1   |
| null  | 2   |

For the query, I have tried everything I can think of to make the null go away, selecting the second set of values from a cross join as opposed to the same table, aggregating it into a regular array and then using unnest, etc, but I can't seem to get it to work, and more importantly, I don't understand what is causing that second null.

What is the easiest way (performance is not expected to be an issue here) to get the result that I am looking for?


texts: Need MySQL configuration options like 'first time'

texts:
When I installed MySQL-5.1.49-win32 for the first time I got a window asking 'root password' and 'confirm' in 'mysql server instance configuration wizard'.

Now I uninstall the software and install the same setup. It always asks me for the current root password even after I remove the 'instance' then configure it.

Seems a file containing password is left behind after uninstallation.

I remember my old password and am able to set new root password to anything of my choice. But I don't want MySQL to ask me for the old password while configuring.


texts:
I remember reading this one article about database design and I also remember it said you should have field properties of NOT NULL.  I don't remember why this was the case though.

All I can seem to think of is that, as an application developer, you wouldn't have to test for NULL and a possible nonexistent data value (for instance, an empty string for strings).

But what do you do in the case of dates, datetime, and time (SQL Server 2008)?  You'd have to use some historic or bottomed-out date.

Any ideas on this?


texts: Oracle's GV$SQL_MONITOR - Uniquely identify execution of a SQL statement

texts:
I'm playing around with the data from GV$SQL_MONITOR and I found something that I don't understand. The oracle documentation says that SQL_EXEC_START, SQL_ID, and SQL_EXEC_ID combine to "uniquely identify one execution of the SQL statement". However, I see the below data in the table. Many records have the same SQL_EXEC_START, SQL_ID, and SQL_EXEC_ID, but a unique SID.

Does anyone here have insight into why the documentation and my observations contradict each other?

Possibly relevant: I observed that all of these queries finish at the same time.

SQL_EXEC_START        SQL_ID        SID SQL_EXEC_ID ELAPSED_TIME STATUS              LAST_REFRESH_TIME
--------------------- ------------- --- ----------- ------------ ------------------- ---------------------
2017-09-20 14:27:56   gncss37ygprra 196    16777218      1847766 EXECUTING           2017-09-20 14:27:58
2017-09-20 14:27:56   gncss37ygprra 3148    16777218       498598 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 3329    16777218        96863 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 480    16777218      1849199 EXECUTING           2017-09-20 14:27:58
2017-09-20 14:27:56   gncss37ygprra 3727    16777218      1848618 EXECUTING           2017-09-20 14:27:58
2017-09-20 14:27:56   gncss37ygprra 498    16777218       513823 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 2578    16777218      1955707 EXECUTING           2017-09-20 14:28:00
2017-09-20 14:27:56   gncss37ygprra 3541    16777218      1848522 EXECUTING           2017-09-20 14:27:58


texts: How to solve MySQL “The table is full” error 1114 with Amazon RDS?

texts:
I am trying to add a new column in a table and the query giving me this error 1114.

My RDS instance size is small. 1.7 GB memory, 1 ECU (1 virtual core with 1 ECU) Table i am trying to modify is having 5 Million rows and 7GB in size. Disk size of instance is 15GB (40% free).

Its critical time for us as we are stuck, AWS RDS not allowing access to all parameters of mysql configurations. How do i get rid of this problem?


texts: Configuring SQL Server for SSL Encryption

texts:
I'm new to the DBA role and I've been tasked with configuring a SQL Server install for a client, with the requirement that databases will be distributed across two instances.  Connections to one of the instances require encryption under an SSL certificate so I'm hoping that I could gather some advice on how best to implement and test the encryption.

I've obtained a trusted certificate from a Certification Authority and installed this on the server, and set the properties on INS1 protocols to force encryption using the new cert.

When I query the Exec Connection DMV the encrypt_option value shows as True for all active SPIDS.  However, I'm able to query the instance under an encrypted connection via SSMS on my local machine, and I don't have a copy of the certificate installed locally.  I was under the impression that without a corresponding copy of the certificate client side my connection would be refused as I would need access to the public / private keys in order to decrypt / encrypt any data exchanged over the connection.

Questions

What is the expected behaviour if the client does not have a copy of the certificate installed and ForceEncryption=True on the server?
Should a connection to the instance be refused, or should an unencrypted connection be allowed assuming the client has the required credentials to access the instance?
Could it be that I'm allowed to establish an encrypted connection without a copy of the certificate installed client side, due to the fact that the issuing CA is a Trusted Root CA rolled out as part of our Group security policy?

Considerations

Additionally; external clients will be connecting to the instance via a Web Server in our DMZ, then routed to an internal app server which communicates with the DB server.  In this example would each device in the chain need a copy of the certificate installing (client machine, web server, app server, database server)?

Unfortunately as an inexperienced DBA there's probably a whole host of things I haven't even considered, so any advice that can be provided will be greatly appreciated. It would be good to have input from people who've implemented a similar solution if possible.

Response to comment

  Can you elaborate on "that databases will be distributed across two instances"?

The client has requested that the databases that will be hosted on this server are split across two instances, with the first instance hosting databases that require encrypted connections and the second instance hosting databases which allow unencrypted connections.

Many thanks


texts: NDB Cluster seems to ignore foreign_key_checks=OFF

texts:
According to MySQL documentation on the local foreign_key_checks variable

  Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB tables—previously, the setting was ignored and all such checks were enforced (Bug #14095855).

I should be able to import a unordered InnoDB dumpfile with foreign keys.

My NDB version

# ndbd --version
MySQL distrib mysql-5.6.21 ndb-7.3.7, for Linux (x86_64)

But when I am trying to add a structure-only dump beginning with

SET foreign_key_checks=OFF;

--
-- Table structure for table `aclAktion`
--

DROP TABLE IF EXISTS `aclAktion`;
CREATE TABLE `aclAktion` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '',
  `editMask` set('read','write','create','delete','super') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'read,write,create,delete',
  PRIMARY KEY (`id`),
  UNIQUE KEY `aclAktion_uIdx_name` (`name`)
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `aclGroup`
--

DROP TABLE IF EXISTS `aclGroup`;

SHOW VARIABLES LIKE 'foreign_key_checks';
CREATE TABLE `aclGroup` (  -- this is line #63 in the dumpfile
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aclRole_id` enum('gast','kunde','owner','owner_boss','red_ext','red_ext_boss','red_int','red_int_boss','verlag_boss','sysop','root') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'gast',
  `numUsers` int(11) NOT NULL DEFAULT '0',
  `name` varchar(64) NOT NULL DEFAULT '',
  `aktiv` tinyint(1) NOT NULL DEFAULT '0',
  `login` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `aclGroup_uIdx_name` (`name`),
  KEY `aclGroup_idx_aclRole_id` (`aclRole_id`),
  CONSTRAINT `aclGroup_fk_aclRole_id` FOREIGN KEY (`aclRole_id`) REFERENCES `aclRole` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

--
-- Table structure for table `aclRole`
--

DROP TABLE IF EXISTS `aclRole`;
CREATE TABLE `aclRole` (
  `id` enum('gast','kunde','owner','owner_boss','red_ext','red_ext_boss','red_int','red_int_boss','verlag_boss','sysop','root') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'gast',
  `numGroups` int(11) NOT NULL DEFAULT '0',
  `login` tinyint(1) NOT NULL DEFAULT '0',
  `checkNumLogins` tinyint(1) NOT NULL DEFAULT '0',
  `defaultPrivileges` set('read','write','create','delete','super') CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT 'read',
  `beschreibung` varchar(192) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=NDBCLUSTER ROW_FORMAT=DYNAMIC;

I get an error:

ERROR 1215 (HY000) at line 63: Cannot add foreign key constraint

The error log on the API node shows that NDB actually tries to handle the foreign key correctly

2014-11-03 17:50:05 [NdbApi] INFO     -- Flushing incomplete GCI:s < 10865/8
2014-11-03 17:50:05 [NdbApi] INFO     -- Flushing incomplete GCI:s < 10865/8
2014-11-03 17:50:05 22485 [Note] NDB Binlog: starting log at epoch 10865/8
2014-11-03 17:50:05 22485 [Note] NDB Binlog: ndb tables writable
2014-11-03 17:50:05 22485 [Note] NDB Binlog: Node: 2, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:05 22485 [Note] NDB Binlog: Node: 3, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:08 22485 [Note] NDB Binlog: Node: 2, unsubscribe from node 4, Subscriber bitmask 00
2014-11-03 17:50:08 22485 [Note] NDB Binlog: Node: 3, unsubscribe from node 4, Subscriber bitmask 00
2014-11-03 17:50:16 22485 [Note] NDB Binlog: Node: 2, subscribe from node 4, Subscriber bitmask 010
2014-11-03 17:50:16 22485 [Note] NDB Binlog: Node: 3, subscribe from node 4, Subscriber bitmask 010
2014-11-03 18:01:29 22485 [Note] NDB Binlog: CREATE TABLE Event: REPL$database/aclAktion
2014-11-03 18:01:29 22485 [Note] NDB Binlog: logging ./database/aclAktion (UPDATED,USE_WRITE)
2014-11-03 18:01:29 22485 [Note] NDB FK: Created mock table 'NDB$FKM_20_0_aclRole' referenced by 'aclGroup'

No errors on the ndb node

It seems, the variable foreign_key_checks = off is ignored here.
Any hints?


texts: Is there an easy way to SELECT FROM another session's temp table?

texts:
Is there an easy way to select the data from another session's temp table? I need to do so ad-hocly to debug an issue that I have limited visibility on otherwise.
E.g. if I have access to TEMPDB, is it possible to access the table object itself?

I saw this post from Paul White on one way to do it, but it's a lot more involved then I was hoping for: Viewing Another Session's Temporary Table

I cannot switch the code to use a global temp table for debugging purposes.


texts: Retrieve parent row when key appears in either one of another two tables

texts:
My actual data and sql is more complicated, but I've stripped it down to the following. I have 3 tables.

match
+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
| 2  | match 2 |
| 3  | match 3 |
| 4  | match 4 |
+----+---------+

home_players
+----+----------+--------+
| id | match_id | player |
+----+----------+--------+
| 1  | 1        | Angus  |
| 2  | 1        | Bertie |
| 3  | 1        | Carl   |
| 4  | 1        | Dave   |
+----+----------+--------+

away_players
+----+----------+--------+
| id | match_id | player |
+----+----------+--------+
| 1  | 2        | Angus  |
| 2  | 2        | Bertie |
| 3  | 2        | Carl   |
| 4  | 2        | Dave   |
+----+----------+--------+

I want to know the match.id and match.title for all matches that Dave has played in, so expecting two rows:

+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
| 2  | match 2 |
+----+---------+

I've tried the following:

SELECT DISTINCT m.id, m.title
FROM match m
INNER JOIN home_players h ON m.id = h.match_id
INNER JOIN away_players a ON m.id = a.match_id
WHERE h.player = 'Dave' OR a.player = 'Dave'

But this only ever returns "match 1" and misses "match 2", which Dave has also played in.

+----+---------+
| id | title   |
+----+---------+
| 1  | match 1 |
+----+---------+

It's actually returning a number of rows, but the DISTINCT sorts that out.

What's the sql I need here?


texts: Restoring a tablespace to a different database

texts:
Any ideas as to how we can re-create a tablespace on a different server?

We have a cold backup, so we can copy the datafiles to the target server.
Can we just create a new instance on the target server with the same tablespace name and the same path structure and plug-in the original datafiles?

Just a bit more background, we just want to get a table back from cold backup without restoring the whole database, so we were looking to restore the tablespace's files into a dummy database, then exporting the table were after.

Unfortunately it is an old version of Oracle - 9.2.0.8 and we don't use RMAN.
Any info would be appreciated.


texts: MySQL Slave SQL Running State - "Waiting for dependent transaction to commit" confusion

texts:
I'm trying to debug a replication lag issue.

We have a job that we run over the weekend, where we delete a large number of rows (potentially millions) from a table, currently in batches of 100. While this is running, we find that the replica can fall quite far behind our master database.

The query for the deletes is a simple DELETE FROM table WHERE id IN (...) - we use GUID's for the IDs in this table.

We have slave_parallel_workers set to 2 and slave_parallel_type set to LOGICAL_CLOCK, however binlog_group_commit_sync_delay is currently set to 0.

Use SHOW FULL PROCESSLIST; to see what is happening on the replica, it seems that only one of the workers is actually doing anything -

I can see that one worker is processing a DELETE query, and has the state updating, the other worker is doing nothing, with the state Waiting for an event from Coordinator.

Meanwhile, the Coordinator thread has the status Waiting for dependent transaction to commit. I'm a little confused as to what this means, and I can't see this status in the list of thread states in the MySQL documentation.

Does the "dependent transaction" mean the Delete transaction currently running on the other worker thread on the replica (does this mean the Delete statement is locking the table)?

Or does the "dependent transaction" refer to something occurring on the Master that the replica is waiting for?

It's probably worth noting that while these Delete queries are being run, the replica is still processing a large number of Select queries.

Thanks in advance!


texts: Select and sum data from a oracle table containing duplicates

texts:
I have a table containing data where some are identical in given columns but different in other columns. I will try to describe this table as follwing:

table1                               table2           table3
+---+------+-----+----------+-----+  +-----+------+  +------------------+
| id|sub_id|block|block date|valid|  | id  |active|  |rec_id| messageId |
|---+------+-----+----------+-----+  +-----+------+  +-------+----------+
|123|123_1 |  0  |          |false|  | 123 |   1  |  | 123   |a123kckcdf|
|123|123_2 |  0  |          |false|  | 321 |   1  |  | 123   |ffsw2234sd|
|123|123_3 |  1  |01.07.2019|true |  | 456 |   0  |  | 123   |kk3jd563sf|
|321|321_1 |  1  |28.06.2019|true |  | 654 |   1  |  | 654   |89hhrlidlf|
|321|321_2 |  0  |          |false|  +-----+------+  | 456   |jf335698ld|
|456|451_1 |  0  |          |false|                  | 321   |fkd003llds|
|456|456_1 |  0  |          |false|                  | 123   |jdjd234lds|
|654|654_1 |  0  |          |true |                  | 654   |djd34lld23|
|654|654_2 |  1  |01.04.2017|true |                  | 453   |jf33lfrd2l|
+---+------+-----+----------+-----+                  +-------+----------+

This what I want to achieve:

Summarize number of records sent to rec_id IF:
 * Block_date has to have occured more than 24 hours ago (presume I will run this query at '01.07.2019')
* The value 'id' in table1, table2 and rec_id in table3 is identical (and can be used in a join)
* The value for "valid" in table1 is can be 'false' for all occureences of sub_id (set manually by mistake in application gui)
* The value for "blacklisted" in table1 must be '1'
* The value for "active" in table2 must be '1'
* The time in "block date" in table1 is in timestamp format really and is not truncated (as it seems like in this example)
* Each message in table3 is a unique message

I would then want to end up with a list like this:

+---------------------+
|  Id  | Num messages |
+------+--------------+
|  321 |     1        |
|  654 |     2        |
+------+--------------+

I have tried with something like this but I have not managed how to get hold of the date period yet and neither how to fully cope with the fact that an records in table1 can be both valid and false and I only want the false ones

SELECT t3.rec_id AS"Id",
COUNT(t3.rec_id) AS"Num messages"
FROM TABLE3 t3
INNER JOIN TABLE2 t2
ON t2.id=t3.rec_id
INNER JOIN TABLE1 t1
ON t2.id=t1.id
WHERE t2.active='1'
AND t1.block='0'
AND t1.valid like 'false'
GROUP BY t3.rec_id
ORDER BY COUNT (t3.rec_id) DESC

(This is to detect faulty messages not beeing detected by our existing routines)


texts: Log report 'bad password', but still grants access

texts:
Original symptom: User sa locked out, too many bad attempts.

Problem was sudden (no issues before), and pervasive (couple of fails in one day).
As this was a new roll out of SQL backend to Sage300, Crystal Reports and Misys inventory package, we could reboot to recover.
(We have since confirmed a Windows Login will allow us to unlock account. No more reboot.)
After seeing the recommendation on how to "disable enforcement policy", we halted the lockouts.
(And yes, there was a windows update, and SQL "uses the policy of the underlying OS" - did Windoze something change?)

Ahh! But here the mystery deepens!
Our log indicates a login failure for user sa at each Crystal/Sage report generated.

Login failed for user 'sa'. Password did not match that for the login provided. [CLIENT: <localhost> -or- 192.168.x.x of machine]
Error: 18456 Severity: 14 State: 8

The report IS generated and delivered to the user. No indication of an issue/error.
We tried to intentionally enter a bad password; exact same error in the logs, BUT the user gets a failure dialog box.
Please note: with the exception of occasional fat-fingered logins, we had no login failures in the logs up until this started. The verbose message is present (duplicated) in both the WindowsNT:Application and the SQL server logs.

So the problem is still there! But the user doesn't see it. But it is in the logs.
WHY? I expect this will come to bite me later, if I don't address today.
Since this is new roll-out, and end-user expects some teething pain, I have some leeway to try things, now....
Maybe not so much, down the line, if/when this blooms into a real issue.
Any suggestions??


texts: Upgrade SQL Server 2014 AG to SQL Server 2017 AG

texts:
Is it possible to migrate SQL Server 2014 AG (Windows 2012, SQL Server 2014) to SQL Server 2017 AG (Windows 2016, SQL Server 2017) using rolling upgrade method?

What I mean is

Build 2 SQL Server 2017 instance.
Add the above nodes to the existing cluster.
Add the new nodes to the existing AG as an additional replica (Async)
Failover to 2017 server
Remove the 2014 nodes from Ag
Remove the 2014 nodes from the cluster.

Would this work or do we have to build everything from scratch?.


texts: How to transfer a few databases from MariaDB master to another master

texts:
I must transfer databases (everything without system's databases) from one replication server to another replication server.
Everything is on MariaDB (10.0.20) and i need to do it with minimal downtime.
On source servers I have master (serverA1), slave (serverA2), slave (serverA3) replication.
On destination server I have the same situation - master (ServerB1), slave (ServerB2), slave (ServerB3).
Now I must move databases from servers A to servers B, with minimal downtime and not to ruin replication.
Server A and B have different databases.
In future Server A, will be offlined.


texts: normal Linux user for oracle database dumps

texts:
is it possible to allow normal linux user " neither root nor oracle " to be able to execute expdp and impdp and to have access to  dpdump directory with rw access on oracle database 12c ?
i know this doesn't sound logic , however there is business need to avail it if possible
appreciate your support in advance


texts: How to store external IDs from different sources?

texts:
I have a table of customers with a PK that we can use as an identifier for our internal system. These customers may belong to different companies, each of which may have an external ID in some arbitrary structure to identify the customer. Furthermore, customers and companies have a many to many relationship.

My dilemma is whether I should store the external ID in the linking table itself or via some external table (because the external IDs may be of arbitrary form). We need to be able to find customers for a particular company via internal or external ID.


texts: How can I disable mysql_native_password and mysql_old_password on MariaDB / MySQL?

texts:
I only want to use unix_socket as the sole authentication method.
Both mysql_native_password and mysql_old_password are installed and enabled by default, but I'd like to disable them.
I'm unable to uninstall them:
MariaDB [(none)]> uninstall plugin mysql_native_password;
ERROR 1619 (HY000): Built-in plugins cannot be deleted

I've followed the instructions here:
https://mariadb.com/kb/en/mysql_plugin/
I've created disabled_plugins.ini in /etc/mysql with contents:
disabled_plugins
mysql_old_password

Then:
MariaDB [(none)]> mysql_plugin disabled_plugins DISABLE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysql_plugin disabled_plugins DISABLE' at line 1

This is on WSL1 and 10.3.27-MariaDB-0+deb10u1-log Debian 10
Can someone help?
Thanks


texts: Disk usage not decreased after the full and log backup in SQL Server

texts:
I am using SQL Server 2012. I created the backup (full and transaction log backup) using maintenance plan and scheduled the job using SQL Server agent.

After the success of the backup, I cannot see why the C partition on my disk is not reduced. What I know is that after the backup of logs, the disk will be reduced.

Forgive me for the ignorance but I did my search. Plus, I am coming from Oracle database background.

Is there anything wrong? Under what circumstances the disk size is not reduced after log backup?


texts: Mysqldump --flush-logs results in small backup files?

texts:
New to MySQL and just learning how to setup a daily full backup (binary logs enabled). My goal is to run a daily full backup of the databases/schemas and reset the binary log.

I'm currently using an edited script that creates individual backup files for each schema which is great.

Each run through the loop executes this against a database and all looks great except the binary log does not reset.:

%mysqldumpexe% --user=%dbuser% --password=%dbpass% --routines --master-data=2 --databases %%F --log-error=%errorLogPath% > "E:\MySQLBackup\Database\%%F.%backuptime%.sql"

When I add the --flush-logs parameter to the line and run it, it:
1) Creates separate binary logs for each database (currently I have only one)
2) Creates full backup files that are 1-2kb with no data?!

What am I doing wrong here? I want the full backup to be a complete dump of the database at the time and also reset the binary log so it contains the changes just after the full backup.

Thanks in advance


texts: SQL Concurrent Inserts and Automatically Find Active Flag Record

texts:
What is the proper database design to Instantaneously find Active Flag for an OLTP table: with many rows being inserted into a parallel, multithreading environment while allowing optimization, and prevent racing conditions, locking, deadlocks, etc?
Note: This table only has  inserts, no deletes or updates.

The active flag is determined by shops sending order files ( max(LastModified) column). With customer modifications and technical issues, sometimes shops can send old files later. So last file being sent may not always be current and have an earlier [LastModified] date.

Should we use :

(1) One table design with active flag,

(2) Or Two table design (transaction table and a current pointer table) in high insert environment?

We receive around 5000 inserts/sec and 100 million inserts in week time span. Consumes around 200GB of data, SQL2016 in 50 Cores, 200GB of RAM.

Method 1: One Table Method with Current Flag

create table dbo.CustomerOrder
(
    CustomerOrderId bigint primary key identity(1,1),
    CustomerId int,
    CurrentFlag bit,
    FileLastModified datetime,  -- actual shop submittal date
    Productsku varchar(25),
    Quantity int,
)

Method 2: Two Table Method, Pointer Rows to Original Table

create table dbo.CustomerOrderCurrent
(
    CustomerOrderCurrentKey bigint primary key identity(1,1),
    CustomerId int,
    CustomerOrderId bigint foreign key references  CustomerOrder(CustomerOrderId) -- refers to historical table
)


texts:
I am getting the following error while starting the sqlplus (I am using Oracle XE 11.2.0)

ORA-01033: ORACLE initialization or shutdown in progress
Process ID: 0
Session ID: 0 Serial number: 0

So, I did the following..

SQLPLUS> CONN AS sysdba

and then..

SQLPLUS> alter database mount

I am getting the following error for the above command.

alter database mount
*
ERROR at line 1:
ORA-00227: corrupt block detected in control file: (block 3, # blocks 1)
ORA-00202: control file: 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\CONTROL.DBF'

I have the following files along with CONTROL.DBF in the same folder.

CONTROL.DBF
SYSAUX.DBF
SYSTEM.DBF
TEMP.DBF
UNDOTBS1.DBF
USERS.DBF

I also tried disk check on the C:\ drive, and Windows said it repaired the CONTROL.DBF file but still, I am getting the same error.

Thanks in advance. Hope you will reply as soon as possible.


texts: CREATE FUNCTION inside BEGIN TRY and END TRY

texts:
I want to use CREATE FUNCTION in a block that will "swallow" errors, but this
BEGIN TRY

CREATE FUNCTION test (@ID int)
RETURNS int
AS
BEGIN
    RETURN(2 * @ID)
END

END TRY
BEGIN CATCH
END CATCH

results in

Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword
'FUNCTION'. Msg 137, Level 15, State 2, Line 7 Must declare the scalar
variable "@ID".

Why?


texts: How should I design a specific needs mysql table

texts:
I am planning out a mysql table to record page views. None of my options seem very efficient. I plan to have the pages send a +1 update to the table when loaded. What I want to be able to do is query the table for view count for the last 30 days, or a different query for view count last seven days. The best plan I can come up with is to insert a new record for each view with a date/time stamp. This would require that each of the two possible queries (30 days or 7 days) to scan the whole table and use a conditional clause. I would also need to remove rows older than 30 days old with a cron job or something.
It seems that there should be a much more efficient way to do this.
I use freelancers to do my work and have learned from past experience that I need to be specific in how I want things done.
If you can suggest a better way to accomplish what I need in a more efficient way than I described please do so.


texts: What's the easiest way to automate Mongodb backups, for offline storage?

texts:
So, I'm almost finished building my first major production web app, and am wondering how to manage backups.
Weekly cold snapshot backups via my vps online admin seem ideal - but weekly downtime sounds awful for UX. Hot backups seem pointless as they cannot be trusted to not be corrupt
Perhaps I don't need weekly snapshots - just a single one, when the server is fully set up? Of course the project/app code will be versioned in the repo.
My server runs Debian.
Is there a way to SSH in and clone the filesystem on my local machine, before encrypting with Veracrypt, before mailing it off-site?
I suppose I don't need to backup the entire fs on a daily basis, but definitely Mongodb.
What's the easiest way to automate a mongodb backup?
Thanks for reading :)


texts: Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

texts:
I wrote this script without COMMIT in a new Query window (Query1):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3

and in another query window I write this script without COMMIT (Query2):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 1

Everything is OK, when I run Query1 and then Query2 I can select row with RegioID=1. But if I write the first query this way (Query3):

BEGIN TRAN
SELECT [RegionID], [RegionDescription]
FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK)
WHERE RegionID = 3 OR RegionID = 4

...and add RegionID = 4 in result, I can't run Query2. Why wheras Query2 result does not has intersect with Query3 I can't run it?


texts: Is fragmentation at non-leaf levels a problem?

texts:
SQL version: 2017 Standard x64
OS: Windows Server 2016 Datacenter (10.0)

I've just created some minimal indexes on ETL tables which were previously heaps with no indexes.  Both R and W performance have improved.  Thought I'd keep on eye on index fragmentation, so I'm looking at sys.dm_db_index_physical_stats: in DETAILED mode simply to learn more about this area.  I'm an
experienced DWH developer rather than a DBA, so my questions may be very beginner to DBAs (there is no DBA here, or they'd be taking care of this).

The index I'm looking at is a clustered unique index.  This table has 1.3m rows.  At the leaf level (0) I see .89% fragmentation: good.  At higher levels though there is fragmentation.  Index has levels 0-3.  At level 2 fragmentation is 52%, fragment_count=23, record_count=2781.

This is confusing me, especially as I only created the index yesterday.

Is fragmentation at non-leaf levels a performance concern?
How can there be so much fragmentation at level 2 already?
In a clustered index, what do non-leaf (>0) levels in the B-tree actually store?  It can't be row data as in level 0: is it a pointer to a set of lower index entries?
If I'm right about 3, it would seem a trivial job to reorganise/rebuild an upper level of a B-tree, e.g. one with only 2781 records, without going near the heavy-duty work of reorganising the leaf level.  But I can't see any mention of a way to do this.
What does fragment_count actually mean, especially at higher levels?  The documentation only says that logical fragmentation is "the percentage of out-of-order pages in the leaf pages of an index".  Is fragment_count a count of contiguous areas of the index, which are in-order internally?


texts: How do I restrict relations between entities to a parent entity?

texts:
I have a multi-vendor database for my e-commerce application. Below is a simple interpretation of my table and relation structure.

Table: stores

store_id (PK, AutoIncrement)

Table: categories

category_id (PK, AutoIncrement)
store_id (FK)

Table: packages

package_id (PK, AutoIncrement)
store_id (FK)

Table: categories_packages

category_id (PK, FK)
package_id (PK, FK)

I need to restrict relationships between package and category to their respective store. For example with my current table structure store A categories can have store B packages, this is not allowed in my application.

My question:
Can I adjust my database design to restrict such behavior, or do I continue to use my application to conduct validation checks?


texts: What happens when sga and pga reach limits?

texts:
I have the following doubt, I have a server with Oracle 9i and another with Oracle 12c, in both cases I have the doubt that happens when Oracle reaches the limit imposed by the defined memory parameters (SGA and PGA)? ... Example hypothetical:
I have a server with 32GB of RAM in which I only have running an instance of oracle 12c which has assigned the following parameters:

sga_max_size = 19327352832
sga_target = 19327352832
pga_aggregate_target = 10737418240
pga_aggregate_limit = 12884901888

If it were to reach the limit of those parameters that would happen ?, the operating system (RedHat) would begin to swap?


texts:
I have tried using the system preferences and then shutting down MySQL there, but to no avail. I also tried typing killall -9 and -11. Both failed :(.

I also tried /usr/local/mysql/bin/mysqladmin -u root -p shutdown. The last thing I tried is in the following screenshot... it also did not work.

I am using yosemite, if that matters.


texts: mongodb set feature compatibility to 3.4 fail

texts:
I want to enable the features of my Mongodb 3.4 after upgrading. I have sharded cluster enviroment And after I ran the commands:

use admin
db.adminCommand ({setFeatureCompatibilityVersion: "3.4"})

In mongos instance I got the following output:

  {"ok": 1}

But when I try to check with it succeeded with command:

db.adminCommand ({getParameter: 1, featureCompatibilityVersion: 1})

I get the following output:

  {"ok": 0, "errmsg": "no option found to get"}

And when I check in ops manager I see that the command did not work


texts:
I have to fix an issue on a VM for a website that use Mariadb technology. When I type "systemctl start mariadb", it shows :
Job for mariadb.service failed because the control process exited with error code.
See "systemctl status mariadb.service" and "journalctl -xe" for details.

Here is what I have in error.log:
 2020-12-23 20:17:52 0 [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded
2020-12-23 20:17:52 0 [ERROR] mysqld: Can't lock aria control file '/var/lib/mysql/aria_log_control' for exclusive use, error: 11. Will retry for 30 seconds
2020-12-23 20:18:23 0 [ERROR] mysqld: Got error 'Could not get an exclusive lock; file is probably in use by another process' when trying to use aria control file '/var/lib/mysql/aria_log_control'
2020-12-23 20:18:23 0 [ERROR] Plugin 'Aria' init function returned error.
2020-12-23 20:18:23 0 [ERROR] Plugin 'Aria' registration as a STORAGE ENGINE failed.
2020-12-23 20:18:23 0 [Note] InnoDB: Using Linux native AIO
2020-12-23 20:18:23 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2020-12-23 20:18:23 0 [Note] InnoDB: Uses event mutexes
2020-12-23 20:18:23 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2020-12-23 20:18:23 0 [Note] InnoDB: Number of pools: 1
2020-12-23 20:18:23 0 [Note] InnoDB: Using SSE2 crc32 instructions
2020-12-23 20:18:23 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
2020-12-23 20:18:23 0 [Note] InnoDB: Completed initialization of buffer pool
2020-12-23 20:18:23 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
2020-12-23 20:18:23 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11
2020-12-23 20:18:23 0 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2020-12-23 20:18:23 0 [Note] InnoDB: Retrying to lock the first data file
2020-12-23 20:18:24 0 [ERROR] InnoDB: Unable to lock ./ibdata1 error: 11

I have searched similar issues but solutions didn't work.
Thank you very much for your help.
Update : When I killall mysqld and I restart it, I have same issues


texts: SQL Server (Azure) split comma-separated string into multiple columns?

texts:
I have the following data in Azure SQL Server (Microsoft SQL Azure (RTM) - 12.0.2000.8 Sep 20 2018 21:40:02 Copyright (C) 2018 Microsoft Corporation)

ID    Name
1     US_Android_100x200_blue
2     UK_iOS_100x200_green

I want to split them into a table like this:

ID  Country  Device   Size     Color
1   US       Android  100x200  blue
2   UK       iOS      100x200  green

What are the ways to do it? I looked it up and it shows to use STRING_SPLIT but I haven't been able to find a clean way to do it.


texts: Join two tables based on Aggregate Function sum() result

texts:
I have a small issue related to joining two tables based on the result of SUM().Following are my tables.

Customers Table

CUSTOMERID      NAME AGE   ADDRESS       PHONE
        1        AA  33    Some Address  123654789
        2        BB  35    Some Address  123654789
        3        CC  55    Some Address  987654789
        4        DD  55    Some Address  987654789
        5        EE  25    Some Address  365754789
        6        FF  20    Some Address  365754789

Orders Table

    ORDERID  CUSTOMERID ORDERAMOUNT
    C12335    3         50000
    F12336    3         12000
    C12337    3         5000
    C12338    1         3700
    C12339    2         1100

I display the customerID,Name,Address,Sum of orders of the customers whose Sum of ORDERAMOUNT is more than 50000.

However,since I cannot use an Aggregate function like SUM() after a WHERE clause,I am in trouble here.

SELECT C.NAME,C.ADDRESS,O.CUSTOMERID
FROM CUSTOMERS C
INNER JOIN
(
    SELECT CUSTOMERID,SUM(ORDERAMOUNT) AS sumorders FROM ORDERS GROUP BY CUSTOMERID
)O ON C.CUSTOMERID = O.CUSTOMERID;

The most progressive working query I could write was this and it does not evaluate the condition of Sum of ORDERAMOUNT is more than 50000.

Is there a way that I can evaluate that condition as well? A help is much appreciated. Thanks in advance :)


texts: Cannot modify a job in SQL 2008 R2. The Modify window simply doesn't come up

texts:
I created a maintenance plan to perform daily backups with transaction log backups to be scheduled on four hour increments, however, I did not enable the job immediately, nor did I enter add the schedules.
Now I want to go into the maintenance plan and add the schedules for the full backup and the log backs, and to enable them. However, when I right click on the Maint. Plan and select "Modify" nothing happens. No dialogue box, nothing.
Any thoughts on how to resolve this buggy issue? Is this a known issue? My boss won't let me run any service packs. All their servers are straight 2008 R2 out of the box. No service packs. And I am not permitted to apply any at this time.


texts:
I was studying Drupal and asked this question:

https://drupal.stackexchange.com/questions/45443/why-timestamp-format-was-chosen-for-users-created-field/45446#45446

I decided to check, how the CMS will behave after the end of Unix epoch. Set my local date to 01.01.2040 and rebooted.

To my surprise, the site didn't work at all. And the reason was, that MySQL was down. I tried to connect manually and got an error:

>mysql -uroot -ppass
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)

I am using MySQL 5.5 on Win XP. Why such strange behaviour?


texts: Manual postgres migration for Django (python) app

texts:
I have a Django (python) data model called Message:

class Message(models.Model):
    text = models.CharField(max_length=50)
    sending_time = models.DateTimeField(auto_now_add=True)

I added sender = models.ForeignKey(User) to this model in Django's models.py code. Next, I wanted to explore doing schema changes to reflect this change in the code without invoking syncdb or south. I.e. manually updating the schema of my postgresql backend.

So I logged into psql and did the following:

ALTER TABLE message ADD COLUMN "sender" INTEGER DEFAULT 1;
ALTER TABLE message ADD CONSTRAINT fk_message_user FOREIGN KEY (sender) REFERENCES user(id);

With those two commands, I successfully added a new column with default value 1, containing a foreign key to Django's built-in User model.

After I had done that, I figured I had successfully approximated what Django does with syncdb or migrate. I ran my web app - everything looked good. But then as soon as I hit the first database transaction in my code, the following error resulted:

  DatabaseError: current transaction is aborted, commands ignored until
  end of transaction block

I'm unable to get it to work unless I delete the column I manually added (or rollback the transaction), but not otherwise. syncdb is an option, but let's exclude it for this question.

What did I miss when I manually provisioned that foreignkey field? Can someone walk me through the process? I'm assuming running the Django migration command simply automates steps one could have done manually too. Since I need to understand the anatomy of that process, I'm doing it by hand. How would you add a foreign key field manually to a pg database in the sort of scenario I've pointed out?


texts: SQL - prevent a function from being executed in a short period of time

texts:
I'm using a lot of functions to make a website up and running, almost every action in that website leads to executing a SQL functions.
Imagine it this way, almost all data processing and website logic is handled by database and partly web server, crazy right? not my fault.

But there's a problem here, there are multiple event handlers in front-end which send requests to database and in return ask for huge amount of data, like by one click asking for big JSON strings, so there's a chance that users mistakenly call these SQL functions by simultaneously clicking on these events in a short period of time, where it can lead to drop database connection or bring the server down.

So, all I need is to know is whether I can prevent SQL functions from being executed multiple times in a short period of time? or at lease to be executed once before it returns data? and how?


texts: Execute a script with SQLPlus containing blanks, semi-colons, and forward slashes

texts:
Occasionally I will get a script that will run fine in SQL Developer or Toad, but requires modification in order to be run successfully from SQL*Plus.  Here is a worst case example containing multiple statements each with blank lines, semicolons, and forward slashes:

INSERT INTO t1 VALUES ('a

;
/
');

INSERT INTO t1 VALUES ('b

;
/
');

DELETE FROM t1 WHERE c1 = 'c

;
/
';

For various reasons these statements need to be run from SQL*Plus.  The blank lines are easy to resolve with a simple...

set sqlblanklines on

I am aware that the sqlterminator can be changed and/or turned off, but both would require modifications to the code, the former moves the problem without resolving it and neither resolves the embedded slash issue.

The best answer would be a way to allow these statements to run without modification by changing the environment in some way (as sqlblanklines does).  If that isn't possible, then perhaps there is a way to programmatically modify the scripts.  I am trying to avoid manual changes.


texts: PostgreSQL rule to delete record before insertion, if the record has changed

texts:
I'm not sure it's possible to do this with a rule, but what I want to do is, when you insert a record:

If it doesn't exist, just insert it as usual.
If it exists, but each field is equal to what you're inserting, then do nothing.
If it exists, but any field differs from what you're inserting, then delete the old record and insert the new one.

The reason for this is that, in the last case, I want to use the cascade deletion productively. That is, if a record has changed, then everything that refers to it is now void and should be removed.
Let's say my table is:
create table foo (
  id  integer primary key,
  foo integer,
  bar integer
);

My first attempt was to just delete the old record regardless (before checking the difference):
create rule refresh as
  on insert to foo
  do instead (
    delete from foo where id = new.id;
    insert into foo values (new.id, new.foo, new.bar));

This doesn't work; it's an infinite loop.
Is it possible to do what I'm after with rules, or would I need a full-blown trigger?


texts: Ubuntu: upgrading postgresql 8.4 database cluster for use with 9.2 server

texts:
I have installed PG 9.2 on my machine. I have a database cluster that was created about a year ago, using initdb, using postgreSQL version 8.4.

The (8.4) data cluster consumes several Gb and so its on its own separate hard disk. I instructed the old 8.4 server to use the correct data location by passing it a -D /path/to/data/folder when the server was started.

My question is this:

I have a (v8.4) database cluster (containing about 10 databases) located at /some/path/to/datadir

I have a freshly installed postgreSQL 9.2 server.

How do I upgrade the cluster (and its databases), so that the new server can use the cluster - bearing in mind that I want the data to remain in the current data location (a physically separate device mapped to '/').

I tried running the server against the old cluster file by modifying the configuration file, and I (unsurprisingly), got the following error:

 * Starting PostgreSQL 9.2 database server                                                                                * The PostgreSQL server failed to start. Please check the log output:
2012-11-18 23:50:31 GMT FATAL:  database files are incompatible with server
2012-11-18 23:50:31 GMT DETAIL:  The data directory was initialized by PostgreSQL version 8.4, which is not compatible with this version 9.2.1.

So how can I upgrade an 8.4 cluster (in a specified data directory) to be used by a 9.2 server?


texts: OPatch in Oracle 12C - Duplicating databases

texts:
I am new to Oracle 12C.  I am trying to restore the production database with SID 'PROD' into a test server for testing purposes.  In the test server I have kept everything exactly the same as in production server.  However the production server was set up in 2017 and I am creating the test server now.  When I tried to restore the database, I got the following error:

ORA-00904: "ACDRROWTSINTCOL#": invalid identifier

Online help indicated that I need to do startup upgrade at sqlplus.  When I did the same, I got the following error.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 2.6978E+10 bytes
Fixed Size         26330680 bytes
Variable Size        1.3892E+10 bytes
Database Buffers     1.3019E+10 bytes
Redo Buffers           40775680 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/home/app/oracle/oradata/PROD/system01.dbf'

This error lead me to run recover database command.  When I ran this, I got this error.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database or pluggable database must be opened in read/write
mode

The exploration on this error lead me to believe that I have to upgrade the OPatch in oracle 12c.  When I checked for the OPatch version between production and test server, I found them to be different.

In production it was OPatch Version: 12.2.0.1.3 and in test it was OPatch Version: 12.2.0.1.6.

How do I downgrade the OPatch in my test server.  I do not want to touch production database as the data is critical.

If there is any other work around to resolve this, I am open to suggestions.


texts: How to narrow down records in SQL based on conditions

texts:
Customer   Rank      Joining_date       salary
    A         2        2017-10-12         500
    A         1        2017-10-10         800
    A         1        2017-10-20         400
    B         2        2017-05-20         200
    B         2        2017-05-15         100
    c         3        2017-06-10         600
    c         4        2017-06-05         600

Logic: For a given customer

if the Rank is 1 ,then retain all those records with rank 1 and
         drop rest

if customer has records with different rank ,select the record
  based on latest rank (order by rank desc) and drop the other

if the customer has records with same rank (other than 1),then
  select the
  record based on the lowest salary (order by salary asc)**

Expected result

 Customer   Rank      Joining_date       salary
    A         1        2017-10-10         800
    A         1        2017-10-20         400
    B         2        2017-05-15         100
    c         4        2017-06-05         600


texts: Cannot connect to database hosted at CentOS VM through SQL Developer on Windows

texts:
I've installed Oracle 12c smoothly today and was able to start lsnrctl and database.
SQL*Plus connection is okay inside VM.
I've set the IP address to be static @ 192.168.0.20 (CentOS VM).

I am able to ping the VM address from Windows but I cannot connect to the database through SQL Developer inside Windows. This is the error I got:

  The network adapter is unable to establish the connection

I suspect it may be a firewall problem? But I have opened port 1521 using firewall-cmd.


texts: How to clear the InnoDB redo log before starting mysqld for crash recovery

texts:
System: Windows Server 2012 R2, MariaDB 10.2.8

We are currently trying to recover from a quasi-crash (it was a normal shutdown, but mysqld won't start up again)
and are having problems with the redo log of InnoDB during start up. If we start with innodb_force_recovery=0,
the process gets to the point where it logs

2019-06-14  9:29:41 14580 [Note] InnoDB: Starting final batch to recover 1150 pages from redo log.

and then gets stuck there. Since there are no further log entries from that point it is not clear if it takes more
time or silently fails. We tried waiting for 22 hours, with no visible progress. The process ran with <1% CPU usage
and unchanging memory usage, so presumably it stops silently.

We then tried increasing numbers of innodb_force_recovery and at 6 the server finally starts up again by
skipping the redo log entirely. Unfortunately this also starts with read-only. Thus clearing the log by using
innodb_fast_shutdown=0 doesn't appear to do anything on shutdown.

What we need now is a way to clear the redo log with mysqld being offline. Simply deleting the log files
ib_logfile0 and ib_logfile1 doesn't seem to work either, since they somehow get recreated during
start up, which runs into the same problems as above. We're assuming the data somehow is saved in ibdata1,
but we can't delete it from there without also deleting the tablespace.

As a last resort we tried deleting the corrupted database by deleting it's directory from disk (since we can't delete
it within MySQL as it only starts in read-only) and then starting with innodb_force_recovery=3, but once again
the process tries to perform the redo log and then stops because the database doesn't exist.

It seems the best course of action is to somehow start mysqld without performing the redo log or clearing it beforehand,
then deleting the corrupted database and restoring it from a backup. But we can't get it to start in write-mode without
trying and failing with the redo log. Is there a way to clear it by directly manipulating the files?


texts: How to move a Daily Partition to a Monthly Partitioned Table

texts:
I would like to switch out a partition from a daily partitioned table and merge it into monthly partitioned table.  I am unsure as to how to go about it.  Here's what I have so far.

I have two tables, Event_Daily and Event_Archive_Monthly.  Both tables are partitioned on CreatedDate datetime with Event_Daily being partitioned by Day and Event_Archive_Monthly being partitioned by Month.

Event_Archive_Monthly has the following file groups:

Archive_Monthly_2018_05_01_FG
Archive_Monthly_2018_06_01_FG
Archive_Monthly_2018_07_01_FG

Event_Daily has the following file groups:

Daily_L_NULL_FG
Daily_2018_06_18_FG
Daily_2018_06_19_FG

I have 9 records in Event_Daily in the 2018_06_18 file group and no data on the other partitions.  I have created a SwitchOut table on Daily_2018_06_18_FG  and have issued these commands to switch out the data and merge the boundary point:

PRINT 'switching out previous days data'

ALTER TABLE [dbo].[Event_Daily]
SWITCH PARTITION 2 TO dbo.Event_Daily_SwitchOut;
GO

PRINT 'merging boundary point'

ALTER PARTITION FUNCTION Daily_PF ()
    MERGE RANGE ( '2018-06-18' )
GO

At this point I am unsure as to how to get this data into the archive table.  Ideally, after everything is said and done the file group Daily_2018_06_18_FG is gone.  One option I know I have is to just insert the data and then drop the SwitchOut table and drop the file group.

Is there a way, using the Partitioned Table, to merge the SwitchOut table that is on the filegroup Daily_2018_06_18_FG into the partition on Archive_Monthly_2018_06_01_FG?


texts: MySQL throws error while secure_installation

texts:
I am installing MySQL using link: https://www.digitalocean.com/community/tutorials/how-to-install-mysql-on-centos-7

But after installing step 3 for secure_installation sudo mysql_secure_installation.

It shows password to enter, and when I enter password or leave it blank, it shows Error:

  Access denied for user 'root'@'localhost' (using password: NO)


texts: Custom aggregation for non-leaf level nodes to avoid double counting

texts:
I have a fact table with the following columns:

WorkerName
OrderId
NumberOfPackagesPerOrder

Note that neither WorkerName nor OrderId is unique in this fact table.
Multiple users could work on a single OrderId
Also note that NumberOfPackagesPerOrder only depends on the OrderId, i.e. for every OrderId, the number of NumberOfPackagesPerOrder will be the same.

I am trying to build a cube to report hierarchical report from this data by employee hierarchy:

ManagerLevel1           [Total orders completed] [Total Packages Shipped]
    Managerlevel2
        .....
        ManagerLevelN
                  Worker

Since multiple workers could work on the same order, I need to avoid double counting  PackagesPerOrder for manager levels (non leaf nodes).

How to do this? What MDX Script do I need to properly SUM NumberOfPackagesPerOrder by summing these only after DISTINCTing these by OrderId?

Note1 - the employment hierarchy is defined recursively - the levels are not static.
Note2 - managers, those on a non-leaf nodes, could also potentially ship packages.


texts: What is the impact of temporarily setting ALLOW_PAGE_LOCKS ON

texts:
The MSDN ALTER INDEX page says "An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF."

I'm modifying our index reorg/rebuild process to be sensitive to indexes that have ALLOW_PAGE_LOCKS turned OFF.  When I detect the index has the option turned off, I perform the following steps:

BEGIN TRANSACTION
BEGIN TRY
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = ON);
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] REBUILD
        WITH (
            SORT_IN_TEMPDB = ON
            , MAXDOP = 1
            , PAD_INDEX = OFF
            , FILLFACTOR = 90
            , ALLOW_PAGE_LOCKS = OFF
            , ALLOW_ROW_LOCKS = ON
            , IGNORE_DUP_KEY = OFF
            , DATA_COMPRESSION = NONE
            );
    ALTER INDEX [MyIndex] ON [dbo].[MyTable] SET (ALLOW_PAGE_LOCKS = OFF);
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

Presuming the database is not actually "in-use" at the time this rebuild process starts, does this pattern present any issues?  Does setting PAGE LOCKS ON cause any side-effects?

Note - the options, such as FILLFACTOR = 90 are taken from the current definition of the index just prior to starting the reorg/rebuild process, and a decision is made whether to reorg or rebuild based on the number of pages in the index and the degree of fragmentation.  This question is NOT about these settings being "good" or "bad", it is ONLY about the impact of temporarily turning ALLOW_PAGE_LOCKS to `ON', to allow the index to be defragmented.

Also, I realize the ALTER INDEX ... REBUILD statement has ALLOW_PAGE_LOCKS = OFF, while the very next statement also turns it off... this is to allow the ALTER INDEX ... REORG statement, which does not have any options present, to run properly, while also resetting the ALLOW_PAGE_LOCKS setting off once it's completed.

I've wrapped the alter index statements in a transaction for only the indexes that have ALLOW_PAGE_LOCKS OFF so that if anything goes wrong during the rebuild process the index is not modified.

Also, note, we are not using Ola Hallengren's solution due to corporate policy.

EDIT:

From @Shanky's comment, I've confirmed that ALTER INDEX ... REBUILD does NOT require ALLOW_PAGE_LOCKS ON; this option is only required when reorganizing an index.  I've modified my code to do the transaction-wrapping shown above only when performing a reorganize where the index already has ALLOW_PAGE_LOCKS turned off.


texts:
I've got a table t with ~23 million rows (4248 MB in size). There's a column row_id in it, with a not null constraint. And a unique index p1 on t(row_id).

When I do select count(*) from t to count all the rows in the table, the planner tells me:

Seq Scan on t  (cost=0.00..686191.06 rows=23176906 width=0)

I would have expected a fast Index Only Scan (index p1 occupies only 698 MB - 6x less).

If I do SET enable_seqscan = off, then the planner still insists on reading the table rows:

QUERY PLAN
  ->  Bitmap Heap Scan on t  (cost=210923.32..897114.38 rows=23176906 width=0)
        ->  Bitmap Index Scan on p1  (cost=0.00..205129.09 rows=23176906 width=0)

Why is the unique index ignored in this case? What's the catch?

I am using PostgreSQL 10.4

For a clean room test I did the following:

create table tmp
(
  row_id      varchar(15) unique not null,
   <10 original cols>
);

insert into tmp (row_id, <10 cols>) select row_id, <10 cols> from t;
commit;
analyze tmp;

set enable_seqscan = on;
explain (analyze, buffers) select count(*) from tmp;
QUERY PLAN
Aggregate  (cost=744070.45..744070.46 rows=1 width=8) (actual time=5631.501..5631.502 rows=1 loops=1)
  Buffers: shared hit=209109 read=245254
  ->  Seq Scan on tmp  (cost=0.00..686128.96 rows=23176596 width=0) (actual time=0.014..3481.967 rows=23176906 loops=1)
        Buffers: shared hit=209109 read=245254
Planning time: 0.064 ms
Execution time: 5631.531 ms

SET enable_seqscan = off;
explain (analyze, buffers) select count(*) from tmp;
QUERY PLAN
Aggregate  (cost=980282.14..980282.15 rows=1 width=8) (actual time=16224.408..16224.408 rows=1 loops=1)
  Buffers: shared hit=26285 read=542015
  ->  Bitmap Heap Scan on tmp  (cost=236211.69..922340.65 rows=23176596 width=0) (actual time=10030.115..14157.288 rows=23176906 loops=1)
        Heap Blocks: exact=454363
        Buffers: shared hit=26285 read=542015
        ->  Bitmap Index Scan on tmp_row_id_key  (cost=0.00..230417.54 rows=23176596 width=0) (actual time=9929.582..9929.582 rows=23176906 loops=1)
              Buffers: shared hit=26285 read=87652
Planning time: 0.051 ms
Execution time: 16229.303 ms

No parallel index scan so far. PostgreSQL insists on accessing the table for some obscured reason.


texts: Inserting rows into other table whilst preserving IDENTITY

texts:
I'm performing ETL logic on a SQL Server table.  I'm going to be synchronizing data from one table to another.  For all of the records that need to be added to the target table based on the source table, I'm doing an insert on those rows in the target table.  The schema defines one of the columns as an identity column.  So SQL Server auto increments the id for new rows that are inserted.  Because I'm moving existing ids, I need to remove the identity, insert those rows, then re-apply the identity, then reset the seed so it matches the source table.  How can this be done programmatically?

Can I remove the identity declaration from an existing column?
Can I mark an existing column as an identity?
How can I reset the seed for an identity column?


texts: How can I enable Transparent Data encryption (TDE) on MySQL 5.6

texts:
I am looking to perform real-time I/O encryption and decryption on MySQL 5.6 Database. I have few questions to understand Transparent Date Encryption (TDE):
Note: I am using AlibabaCloud

Can I Activate / Deactivate TDE any time
Is there any key / Certificate required for Encryption / Decryption
Is there any effect on Server Memory / CPU Usage

I appreciate any assistance with this


texts: Setup failed for Microsoft Management Studio

texts:
I had a fault, the details of which I don't remember, on trying to start up SSMS. I thought I would just install it, but that turned into a real four day ordeal, with a different error on each install. Eventually I uninstalled all traces of all SQL servers from my machine (they're all just dev) and started afresh.

Everything went well, except installing SSMS. It says "A valid destination folder for the install could not be determined", and gives me a link to a huge log file, in which I can find nothing comprehensible about a folder not being determined. It ends with paths to two very similar, about as large log files.

Has anybody else experienced the same error, or could give me a clue what to look for in the log files, help will be gratefully accepted.


texts: Show rows with non-unique value in one field

texts:
I'm familiar with how to aggregate rows as shown in this answer:

Show only duplicate values

I'm also familiar with how to filter the aggregated results using a HAVING clause.

What I can't seem to grasp (so that it sticks) is how to filter rows based on values or comparisons other rows, without aggregating them.

I know the answer involves something about window functions or window clauses, and in fact I've done it successfully before.  But it just doesn't seem to stick in my mind how it works; I feel I'm missing something fundamental.

To give an example, with the info:

 fruit_name | some_field
------------+------------
 apple      |       3.25
 apple      |        6.8
 apple      |        0.7
 orange     |        2.6
 banana     |        3.5
 banana     |       2.49
 cherry     |          1
 grapefruit |        2.6
 grapefruit |        2.7

I want to get all rows with a row count (by fruit name) greater than 1, so it should look like:

 fruit_name | some_field
------------+------------
 apple      |       3.25
 apple      |        6.8
 apple      |        0.7
 banana     |        3.5
 banana     |       2.49
 grapefruit |        2.6
 grapefruit |        2.7

What is the correct idiomatic way to do this?

(I'd like an SQL standard answer if possible, and if there's a simpler Postgres-specific way to do it I'd like to know that also.)


texts: Ignore foreign keys in mysql workbench model synchronization

texts:
I've made an ERD in MySQL Workbench, where I added foreign keys to visualize the relationships. In the actual database I want to work without the foreign keys (I'll organize that in my application).

When I export a create script, there is somewhere a function called "skip creation of foreign keys" or anything like that.

Now I want to sync the model directly with my database, but it looks like there is no equivalent option in the synchronization function.

I can also not find this option in the wizard for exporting an alter script, which would also be ok.

Am I just missing it or is that impossible?


texts: MongoS clients not seeing databases after config server change to CSRS

texts:
The three config servers were migrated to CSRS and WiredTiger last week. Followed the documentation here : https://docs.mongodb.com/manual/tutorial/upgrade-config-servers-to-replica-set/

Since the change, when connected to a mongoS, we cannot see the databases after using show dbs and we cannot connect using --authenticationDatabase admin --username root --password xxxxx --authenticationMechanism SCRAM-SHA-1

There are no errors in any logs, neither mongos, on the config servers, on the mongod machines. rs.conf and rs.status are OK.
The mongos have been stopped and restarted a few times to try and rectify this, with no luck.


texts: Product of two querysets with a parameter in where clause of second result

texts:
So I have a query that returns a row of some columns.

SELECT a.id, related_a_id FROM tableA

And I have an aggregated query which'd be using each of the rows.

SELECT tc.c_field1 AS cf1, SUM(td.c_field3) AS cf3 FROM tableA ta
   INNER JOIN tableC tc ON ta.id = tc.a_id
   INNER JOIN tableD td ON tc.d_id = td.id
WHERE ta.id IN (a.id, related_a_id)
GROUP BY cf1

I want to get all the rows for the second query for each pair from the first query with a resulting queryset columns including a_id, related_a_id, cf1, cf3.

Sample data and my attempts are available at this sql-fiddle.


texts: Oracle AWR shows missing stats for SQL ID as top elapsed statement

texts: Why SQL ID has null number of executions and elapsed time / execution in AWR report. SQL ID is among top elapsed command on AWR but no details stats whoing time / execution. Would it be because shared pool got flushed or what else can cause this?


texts: How to pass data and loop in procedure Oracle

texts:
I have a procedure in an Oracle database which I use to insert users into a table. But now, I need to implement a loop, so that I use that same procedure to insert many users.

I have little knowledge of pl/sql so I don't know which is the best way to do this. Should I pass the data through a varray?

And if so, how can I implement a loop in my procedure?

CREATE OR REPLACE PROCEDURE insert_users(
     p_username     IN xx_users.username%TYPE,
     p_pass         IN xx_users.pass%TYPE,
     p_role         IN VARCHAR2
     )

IS

l_id_role    NUMBER;
l_role       VARCHAR2(200);

l_user       VARCHAR2(200);
l_pass       VARCHAR2(200);

BEGIN

l_user := p_user;
l_pass := p_pass;
l_role := p_role;

SELECT id_role
INTO l_id_role
FROM xx_roles
WHERE role_name = l_role;

INSERT INTO xx_users (id_user,
                        username,
                        pass,
                        id_role
                        ) VALUES (xx_users_id.nextval,
                                 l_user,
                                 l_pass,
                                 l_id_role
                                 );

COMMIT;
END;

I was thinking that maybe I could create another procedure which takes an array and for each element, calls the procedure insert_users but I don't think that is the most effective way to do it.


texts: Need Help Executing a Remote Task from SQL Server Agent

texts:
There is a scheduled task running on a batch server via the Windows Task Scheduler that sometimes overlaps with other scheduled jobs in the SQL Server Agent causing CPU issues.  We can solve this if we can have SQL Server Agent execute the .exe on the remote server to ensure the schedules never conflict.

Is there any way to do this with SQL Server Agent, perhaps with a Powershell command?  If so, can you please give me the steps?

The .exe on the remote server is a data importer for SQL.  It is a stand alone .exe.  We want it to run on the batch server so it doesn't cause resource contention on the DB server.  We are running SQL 2008 R2 Enterprise.

Please let me know if any additional information is required to answer this question.


texts: Help with optimizing Oracle Query - nested with count

texts:
I have 2 tables (Students and Cellphone).

Columns in Students: StudentID, FNAME, PHONEID

Columns in Cellphone: PhoneID, Phonename

I want to create this:

•  Run a query to show the most popular phone used by students

My answer is:

select D.phoneid, c.phonename, d.numberp
from
(select * from

(SELECT m.phoneid,
         COUNT(*) AS numberp
    FROM students m
GROUP BY m.phoneid
ORDER BY numberp DESC)

where rownum = 1) D, cellphone c
where d.phoneid = c.phoneid

Can this be optimized or a simpler way of achieving this?
May be using TOP ? or HAVING ? JOINS ?


texts: Is it worth to update to an x64 environment?

texts:
Situation

We have 10 to 20 Tomcat web applications with hundreds of concurrent users per application on a server.

Each Tomcat application connects using its own user to a database on Microsoft SQL Server 2008 R2 server.  This database contains loads of Stored Procedures that are executed by the Tomcat application. The Stored Procedures usually execute large joins between multiple tables (usually joins between 4-5 tables with each 100k+ records)

Currently this runs on 32-bit Windows 2008 R2 (Enterprise) with 32-bit Tomcat and 32-bit SQL Server 2008 R2. The Servers have 32 - 64GB of RAM on a multi-core processor.

Question

My gut feeling tells me we should update to x64. This will future-proof and prevent some memory issues now that might be better handled in an x64 environment. I can't find benchmarks or proof for this though. Using flags like /AWE and /PAE feels like SQL Server is artificially trying to perform better than it would out of the box, whereas this artificial layer is not necessary in the x64 environment. I can't find any "proof" for this though.

Is it worth updating this configuration to x64? Will we see a performance gain? How much? Are there benchmarks supporting this?


texts: Update: How to update the first two rows with a Y flag

texts:
I am trying to update the first two rows of a column from null to Y so that a user should be able to select two images.

Can you please tell me how to fix the query below;

DECLARE CURSOR tp_Identifier_cur
     IS
        select distinct TP_IDENTIFIER
        from IMAGES
        ORDER BY TP_IDENTIFIER ASC;
     l_tp_Identifier  tp_Identifier_cur%ROWTYPE;
  BEGIN
     OPEN tp_Identifier_cur;
     LOOP
        FETCH tp_Identifier_cur INTO l_tp_Identifier;
        EXIT WHEN tp_Identifier_cur%NOTFOUND;
        UPDATE IMAGES SET OMG = 'Y'
        where TP_IDENTIFIER = l_tp_Identifier.TP_IDENTIFIER
        and rownum <= 2
        order by Image_identifier;
     END LOOP;
     CLOSE tp_Identifier_cur;
  END;


texts: How can I insert procedure results from server 1 into table in server 2?

texts: The goal is to link our website to the database.  The database the website will connect to will be different then our production db.  In fact, a separate instance of SQL.  How can this be structure to execute the procedure in sql1 to populate table A in sql2 so the web query can pull from sql2 table A?


texts: Find unauthenticated connections to MongoDB

texts: I have many services and users that connect to MongoDB and now I need to switch on authentication. I have created some accounts and tried to ensure most users/services are now using accounts when connecting. I'd like to know if there is any way I can find connections to the database that are not using an account before I restart the mongod with --auth and enforce authentication.


texts: Unable to connect SQL Server 2008 from SQL Server 2005 DB

texts:
I have SQL Server 2005 on my work station and on my Server, we have SQL Server 2008. When I tried to connect to SQL Server 2008 from my SQL Server 2005 edition, I am receiving the following error:

  This version of Microsoft SQL Server Management Studio can only be
  used to connect to SQL Server 2000 and SQL Server 2005 servers.

Without installing SQL Server 2008 on my workstation, is there any way for me to connect to SQL Server 2008 from SQL Server 2005 ?


texts: Invalid use of side-effecting operator 'INSERT EXEC' within a function

texts:
I'm trying to create a scalar function that will return a delimited list from some sql that would be passed in.  Unfortuantly where I work the previous people responsible for the database thought it would be a good idea to put comma delimited id lists inside of some columns.  This function is the first step for me to rectify the problem without breaking the developers UI.  Can anyone tell me how to fix this error?  My code is below.

 CREATE FUNCTION fnSQLToDelimitedList ( @SQL NVARCHAR(MAX), @Delimiter NVARCHAR(3))
RETURNS NVARCHAR(MAX)
AS
BEGIN

DECLARE @OUTPUT NVARCHAR(MAX) = ''
DECLARE @VALUE NVARCHAR(MAX)
DECLARE @TEMPVALUES TABLE (VALUE NVARCHAR(MAX) NOT NULL)

INSERT INTO @TEMPVALUES (VALUE)
EXECUTE sp_executeSQL @SQL

WHILE EXISTS (SELECT 1 FROM @TEMPVALUES)
BEGIN

SET @VALUE = (SELECT TOP 1 value
                FROM @TEMPVALUES)
SET @OUTPUT = @OUTPUT + ' ' +  @VALUE + @Delimiter

DELETE FROM @TEMPVALUES WHERE VALUE = @VALUE
END

SET @OUTPUT = SUBSTRING(@OUTPUT,0, LEN(@OUTPUT) )

    -- Return the result of the function
    RETURN @OUTPUT

END
GO


texts: Is varchar(x) as fast as `text CHECK ( char_length(x) )`?

texts:
In an Twitter exchange, Simon West asks to Brandur,

  Out of interest, why do use email  TEXT CHECK (char_length(email) <= 255) rather than email VARCHAR(255)? Not a pattern I've seen before

Brandur responds,

  Excellent question!

  (1) VARCHAR and TEXT are equally performant in Postgres (see the "tip" box at https://www.postgresql.org/docs/current/static/datatype-character.html …).
  .

  (2) If you ever want to change the length, ALTER TABLE requires an exclusive lock (see https://www.postgresql.org/docs/current/static/sql-altertable.html …). Changing CHECK is instant.
  When answering a question that called into question text CHECK (char_length(email) <= 255) vs varchar(255)

Is the first claim (bolded) of the two claims made strictly true?

If interested in the second claim check out this question.


texts: SQL Server Management Studio: Could not load file or assembly 'Microsoft.VisualStudio.TextManager.Interop.8.0'

texts:
I've had this problem for a while and can't find anyone with the exact same issue.

I get the error when trying to select top 1000 rows.

I checked in the PublicAssemblies and it is not there. the lowest version there is 9.0. And I can't find a copy anywhere else.

Thanks for any help.


texts: Read performance when indexing one column vs indexing multiple columns

texts:
(MySQL, InnoDB)

I have a table that has columns A, B and C. That table will have a lot of rows, let's say a million rows. I have a query that will be executed very often and will search the table by the A column, so I added an index on the A column.

I have another query that will not be executed as often as the first one but still often enough to qualify for a speed increase. That query will search by all the three rows.

My question is, will the second query have any speed increase from the index on column A or would I have to add indexes on all three columns in order to increase read speed? In other words, in a query where I have three WHERE conditions, will MySQL first search by the indexed column? Because if it does, out of those million rows, the first column would filter out maybe 100 or so, and querying those by the other two columns would be somewhat trivial and wouldn't require an index.


texts: Preferred database solution for storing sensor data

texts:
As a hobby project I am developing a system which stores data from different kind of sensors around the house. Currently it is implemented using ASP.Net MVC 6 and MongoDB for the database. (I choose MongoDB because I was curious about how it works) Every sensor peroidically posts data to the server on it's own.

For the sake of not having to store the same information possibly hundreds of times I am using references for redundant data. This data includes the type of a sensor, the data fields used by the sensor(and information about fields like it's name, etc).

Here is an example in JSON format:

// Sensors collection
{
    _id : 1,
    TypeId : 1
}

// SensorTypes
{
    _id : 1,
    GenericName : "Test sensor",
    FieldIds : [1, 2]
}

// Fields
[{
    _id : 1,
    Name : "field1",
    DecimalPlaces : 2
},
{
    _id : 2,
    Name : "field2",
    DecimalPlaces : 3
}]

Questions:

Is this good practice? Am I using correct and preferred way to avoid redundancy in my database?
"Schema" of my documents can be displayed like a graph. Does a graph database like Neo4j would be more suitable for my problem?

Thank you for your help!


texts: MySQL Tuner OPTIMIZE and Defragmentation

texts:
I have just started using mysqltuner to help keep my mysql server fast and healthy. However, I am having a little trouble optimizing my tables. When I execute mysqltuner, it all works fine and returns the results. It states that there are 317 fragmented tables and that I should run 'OPTIMIZE' table to repair them.

So, I wrote a script that executes each week to 'OPTIMIZE' every table in the databases on the local server. The script seems to work fine because of the results that are returned. Despite this, when I run mysqltuner again, it still says there are 317 (actually, to be fair, it has reduced by 4 to 313) fragmented tables and that I should OPTIMIZE them still.

Is there something else I should do? Is my script right?

Here is my OPTIMIZE script:

mysqlcheck -u'admin' -p`cat /etc/psa/.psa.shadow` --auto-repair --optimize --all-databases;


texts:
I am looking for a design pattern that would allow me to capture metadata relating to columns on a table. More specifically, here is my situation.

I have a database with fields in a field table, crops in a crop table and prediction models in a model table. Each field can grow one crop and subscribe to multiple models. Each model offers different kind of predictions for a specific crop.

Now, each crop allows a particular set of inputs from the farmer. For instance, all crops will expect a planting date while only some of them will expect a flowering date. Therefore, I create a generic_input table and crop-specific tables (potato_input, soy_input, etc.) to capture that data. Each column of those table is an input that needs to be captured.

Here comes the interesting part. Each model also specifies a  set of required crop inputs. That is, inputs that need to be set in order for that model to be able to compute its predictions. That means I would essentially need to reference particular columns of my input tables which is not really possible.

So far, the only real solution I found would be the Entity-Attribute-Value pattern. However, I've read so many posts warning against its use (example) that I am quite reluctant to go down that road. Another option would be to create an enum or a table listing all my inputs but then if new inputs need to be added or if we add a new crop to the system, we need to update that list as well: that results in some sort of data duplication or poor relational consistency.

Are there any other pattern that could support my use case? What would be an appropriate solution? Is this a case where EVA would actually be a good solution?


texts: System Memory (RAM) for Windows Server 2012 Standard with SQL Server 2012

texts:
We are using Windows server 2012 standard server with 2.4 GHz and 16 GB RAM. SQL Server 2012 is installed in it and also our primary application is installed on the server.

Using transaction replication to get the latest data b/w the servers.

The server performance hits badly sometimes when SQL Server holding the entire memory of the server.

Will the performance of the server improve when increasing the RAM to 32 GB?


texts:
I have a memory table and I am updating it somewhere around 3000 updates per second. I setup a another table in Innodb and then created a Trigger so that all data can be send to the innodb table as a backup since memory table can disappear if server reboots.

My problem is I get errors like this Waiting for table level lock, from my show process list. I am unable to update Memory table anymore and all my operations blocked.

As soon as I delete these triggers no more locks and I can continue updating the tables.

Can anyone help with this, do I need to create triggers differently or some setting need to be changed ?


texts: What is the impact of building Postgres with --disable-thread-safety option?

texts:
I am a newbie in the database field. Recently I have been trying to install Postgres 9.3.4 on AIX 6.1. When I was building the code with the configure command, I ran into this error:

checking thread safety of required library functions... no
configure: error: thread test program failed
This platform is not thread-safe.  Check the file 'config.log' or compile
and run src/test/thread/thread_test for the exact reason.
Use --disable-thread-safety to disable thread safety.

I am only able to compile successfully after using the -disable-thread-safety option. As such, I would like to know what is the impact of using this option towards the database reliability. What does 'client libraries' mean in terms of using the database with this option?

I would also like to know how to compile the thread_test.c and run it manually. The manual says:
        - run "configure"
        - compile the main source tree
        - compile and run this program

But I haven't been successful in compiling it.

*Edit:
Here are the steps that I took to compile thread_test.c:
1. I first ran configure with the --disable-thread-safety option to get the source tree.
2. Then from the build directory, I ran "gmake" to compile main source tree.
3. When's that's done, I moved into the src/test/thread folder and tried to run "gmake".
I got this error:

`gmake: *** No rule to make target `thread_test.o', needed by `thread_test'.  Stop.

If I run "make", I get many errors like this:

"../../../src/Makefile.global", line 45: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 48: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 52: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 72: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 84: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 85: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 87: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 88: make: Dependency line needs colon or double colon operator.

"../../../src/Makefile.global", line 91: make: Dependency line needs colon or double colon operator.

make: Equal sign not found in macro substitution.

I might be doing something wrong here...Can someone be kind enough to point out my mistakes in compiling the src/test/thread/thread_test.c file?

Thanks for all your patience with me. :)


texts:
I received this error while deploying a SQL Server DBProject

  DBProj.dbschema(0,0): Warning SQL01271: The unnamed foreign key
  between tables [dbo].[Match] and [dbo].[Team] will be checked by
  checking the table.  Name the foreign key to avoid checking the entire
  table and only check the new constraint.

My table definition is

CREATE TABLE [dbo].[Match]
(
    MatchID         int identity(11,2) PRIMARY KEY,
    TournamentID    int not null REFERENCES Tournament(TournamentID),
    Team1_ID        int not null REFERENCES Team(TeamID),
    Team2_ID        int not null REFERENCES Team(TeamID),
    MatchDate       date,
    MatchTime       time
)

What does this error means? What is the implication?

update

  actually, am concerned about the warning that it will check the
  entire table. Is it at creation time or every time? how does this
  affect performance?