texts: MySQL - Optimal indexing for a lookup table. HASH index, BTREE index or composite PK?

texts:
Let's say I have a very large lookup table that looks like this:

   CREATE TABLE `MyLookup` (
      `FKToTableA` bigint(20) NOT NULL,
      `FKToTableB` bigint(20) NOT NULL,
      `Count` bigint(20) NOT NULL,
      `Bytes` bigint(20) NOT NULL,
      `Packets` bigint (20) NOT NULL,
   ) ENGINE=InnoDB;

The first 2 attributes are FKs to Table A and B respectively. But doesn't need to be. the FKs can be dropped if it's more optimal not to have it. FKToTableA and FKToTableB is the candidate key for this table. Thus FKToTableA and FKToTableB could be a composite primary key if needed.

My question is what's the most optimal way of indexing this table? Storage size and insert times are not a concern.
Most of the time my queries will be joining on to MyLookup on both FK columns and SUM the Count, Bytes and Packets columns.

select
    a.something, b.something, SUM(c.Count), SUM(c.Bytes), SUM(b.Packets)
from
    A a
    inner join MyLookup c on a.Id = c.FKToTableA
    inner join B b on b.Id = c.FKToTableB
where
    a.something = 1 and
    a.Time >= 'blah' and
    a.Time <= 'blah'
group by
    a.something, b.something

I see three options.

1) Put a HASH index on FKToTableA and FKToTableB.

2) Put a composite BTREE index on all columns.

3) Put a Primary Key on FKToTableA and FKToTableB.

I'm kind of leaning towards 1. From what I gather HASH indexes are great at equality comparisons and that's all joins are is a big equality comparison is it not? Still has to go hit the table again to sum the other columns though which may be a bottle neck. Not sure.

Or maybe it's possible to have a hash index with Count, Bytes, Packets? Not sure how composite hash indexes works though.

Has anyone dealt with this type of thing before and can shed some knowledge and suggestions?

EDIT:
Here's my schema for Table A and B to make it a bit more clear

CREATE TABLE `B` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `CustomerIdent` int(11) NOT NULL,
  `MetaData1` varchar(256) NULL,
  `MetaData2` varchar(256) NULL,
  `MetaData3` varchar(256) NULL,
  `MetaData4` varchar(256) NULL,
  `MetaData5` varchar(256) NULL,
  `Time` datetime NOT NULL
  CONSTRAINT `PK_A` PRIMARY KEY (`Id` ASC)
) ENGINE=InnoDB;

CREATE TABLE `B` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `SourceIp` bigint(20) NULL,
  `DestinationIp` bigint(20) NULL,
  `SourcePort` int(11) NULL,
  `DestinationPort` int(11) NULL,
  CONSTRAINT `PK_B` PRIMARY KEY (`Id` ASC)
) ENGINE=InnoDB;

Basically the lookup table is in charge of telling me how many times an row in B happened for one row of A.


texts: ssrs how to make the report available to my local machine

texts:
I have been struggling in Configuring Report Server URLs for a certain reporting services server.

when I am in the server itself, the url works fine as you can see on the picture below.

but trying to access it from my local machine via internet explorer I get the following:

this has all been set up by a consultant and I am not sure if it is all right.

what do I have to do to make these reports on this particular server available to my users?

or at least, is there a default procedure to start troubleshooting?

Below is the view of my reporting services configuration manager.

the service account

web server URL

database

report manager URL


texts: Add Decimal Point after First Digit in a Numeric Value

texts:
Can any one here please advise how to add a decimal point after first digit while selecting numeric value from database?

  Example: Output is 8250 should be 8.250

I tried this:

convert(decimal(10,3),ti.Rate/1000) AS Amount

but the output returns the wrong value of 8.000. It should be 8.250.


texts: Postgres WAL logs-- point in time recovery with (time) gaps in the logs?

texts:
I'm setting up continuous archiving/ point in time recovery on Postgres and I've noticed some odd behavior.  Here's the sequence of events:

Save a base backup
Archive WAL logs during normal DB operation
Turn off the database for a little while
Turn on database
Archive WAL logs during normal DB operation
Run a complete restore

I expected to recover all the data, including that which occurred after the database downtime.

What I'm observing is that only data from before the downtime is being restored.  In fact, Postgres never even asks for/looks for the WAL logs from after the downtime.

(To be clear, there is no missing data, just a period of time in which the DB was not running and thus no WAL logs were shipping.)

Is this the intended behavior?  I was unable to find any info on the specific logic by which Postgres decides which WAL logs to apply.

If so, does that mean a new base backup should be taken after any DB downtime?


texts: Delete records from one table using join with another table?

texts:
Delete records from one table using join with another table.
Ok, the mission is to delete all employee records if in the title of their department can be
found word "sales". It can happen in the real life. Imagine that newlly elected CEO decides
to close all sales operations.

this my Query:

DELETE *
FROM Employees
WHERE DeptNo IN
  (SELECT DeptNo
  FROM Departments
  WHERE LCase(DeptName) LIKE '*sales*')

but i got error. SQL Syntax error.Please help me.


texts: Can AWS Aurora High Availability allow different Schema on Replicas?

texts:
Our company is thinking of migrating from SQL Server to Amazon AuroraDB.

In SQL Server, there are AlwaysOn and Transactional Replication - two different things.

AlwaysOn is utilized more for disaster recovery, it sends whole database to another cluster, providing database level protection. The data is sent directly with a  transaction log redo thread. Microsoft AlwaysOn
In Replication, data is first sent to a distribution database then to a subscriber. Replication allows for only certain tables to be sent, with different schema and indexes on subscriber, which can be different from Publisher. Microsoft Replication

Is AWS High Availability similar to MS AlwaysOn Disaster Recovery or Replication? It seems more similar to AlwaysOn.

If so, does it have a Replication type technology? We want to make schema changes on the subscriber. (add indexes, remove certain tables not required in reporting env)

Stack


texts:
I am following https://github.com/griddb/c_client to install griddb under Quick start (Windows)
Should I use Using source code or using Using MSI for installing Griddb on windows?
Please help.


texts: MySql 5.6 error (Table 'mysql.user' doesn't exist) when started as a daemon

texts:
We can't seem to get the MySql daemon to start when using the included init script...
A clean install on linux (ubuntu 14.04) was done using the generic MySql binary tarball. The following post-install steps were successful: (NOTE: installation locations and owner account were customized)

$ mysql_install_db --user=mysql-owner --basedir=/mysql-path --datadir=/mysql.data-path
$ mysqld_safe --user=mysql-owner --basedir=/mysql-path --datadir=/mysql.data-path
$ mysql_secure_installation

After those steps were completed (the last resulting in a changed 'root' password and some additional security locks), the mysqld service was stopped.
Subsequently, the 'mysql.server' init script was modified accordingly:

basedir='/mysql-path'
datadir='/mysql.data-path'

Along with my.cnf:

user=the_mysql_owner
basedir='/mysql-path'
datadir='/mysql.data-path'

After manually executing the init script:

./mysql.server start

The service fails to start and produces the following output in the hostname.err file:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'mysql.user' doesn't exist
mysqld_safe mysqld from pid file /mysql-path/run/mysql.pid ended

All of the references to this error seem to be based upon incorrect ownership/privileges and/or corruption due to an upgrade routine. Well, this is a clean install (on a new server), and the service seemingly runs fine when manually started from the command line, so I don't see it being an ownership issue. It "feels" like the init script is dropping a configuration setting, but which one...?


texts: querying a table of events for reporting

texts:
The background (masking a real-world problem behind a word problem)
Farmer Brown has a peach orchard in California and ships hundreds of
thousands of peaches all across the country. When a shipment of peaches
reaches its destination, the deliveryman inspects the peaches and keeps
all the peaches that fail his inspection.

When the peaches that failed inspection arrive back at Farmer Brown's farm,
the peach defects are repaired at Farmer Brown's Peach Hospital and
get re-shipped with the next batch of peach shipments. And if the original peaches
fail inspection again, they go through the same process, as long as the peach is salvageable.

Peach Hospitals are expensive, and so is the cost of shipping,
so Farmer Brown starts thinking, "For the peaches I shipped within any time range, how many total did I ship? Of that total, how many shipped for the 2nd time? for the 3rd (or more) time?”

At a high-level, the peach shipments tracking table (on a SQL Server 2000 database) has 9 million+ records with different kinds of shipments, and looks (grossly) akin to:

ShipmentDate   PeachID    Shipment Type
8/10/2005         7112      Shipment to Customer
7/15/2007         8798      Shipment to Customer
4/3/2009          8798      Shipment to Customer
4/15/2009         8798      Shipment to Customer
4/21/2009         8798      Shipment to Customer
4/21/2009         145751    Shipment to Customer
4/22/2009         7112      Shipment to Customer
4/22/2009         12121     Shipment to Customer
4/25/2009         8798      Shipment to Customer
5/12/2009         8798      Shipment to Customer

If we attempted to return a sample of what Farmer Brown was looking for,
it would be results like this:

given the following Parameters:
 StartDate = 4/1/2009,   EndDate = 4/30/2009

Results
-- The easy part
Total Peaches Shipped to Customer: 100,000
(PeachID 8798 contributes 4 to the total. PeachID 7112 contributes 1 to the total.)

-- The messy part
Total Peaches Shipped for the 2nd time: 5,000
(PeachID 8798 contributes 1 to the total. PeachID 7112 contributes 1 to the total.)

Total Peaches Shipped for the 3rd (or more) time: 800
(PeachID 8798 contributes 2 to the total.)

The issue
What would be the most efficient way of building a holding table(s) of this data (refreshed once a day - real-time is not important), so that when retreiving Farmer Brown's requested data, the data can be returned speedily? I'm thinking that if the shipments starting from the beginning of Farmer Brown's opening day to present were aggregated by day into a holding table(s), then performing an aggregate on whatever the date range parameters are, the proc will be run faster than querying the Shipments table and its millions of records.

Apologies if I'm not explaining the situation clearly enough. I'd really appreciate an outside perspective on this.


texts:
I'm trying to build a SQL query that will show me all the products that have products.description IS NULL but also have a corresponding description in the descriptions table.   So code is always a substring of info.

The products.code is a substring of descriptions.info, so I'm not sure how to pull this.

products

id |  code   |  description
--------------------------
1    12345-1      NULL

descriptions

id |  info
---------------------------
1     12345-1_Hammer


texts: Export roles *with* privileges under PostgreSQL 9.3

texts:
New to backups under PostgreSQL. I've been backing up my database with the general commands, and it works well. Except no users or privileges are stored with it. I understand that I need to do that separately.

I've been using the following command to dump the users on the cluster:

pg_dumpall -U backups -r > /tmp/globals.only.dump

Either the -r or the -g return the same thing. What I end up with in the file is just:

CREATE ROLE backups;
ALTER ROLE backups WITH SUPERUSER NOINHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION PASSWORD '0000000000000000000000000000000000';

It seems that all the privileges are missing, but there are 15 of them that I had setup through NaviCat.

Recreating all the privileges by hand is obviously not viable for production. How do you force the privileges to be exported with the role? Subsequently, how might I import them into another PostgreSQL server?


texts: Why is the backup sizes of AlwaysON replicas different?

texts: We have SQL Server 2014 Always ON set up with a primary and just one secondary replica. I have been using Dell LiteSpeed to take Database backups (full & tlog) on secondary replica in order to off load that traffic from Primary. We had one developer truncated an important table so we had to restore the primary database from the full backup and tlog backups. I took a full database backup on primary first before restoring and noticed that it is just 34 GB as opposed to the backup on secondary which is almost 70 GB. After restore, people complaint that performance is slow. My question is that why the database backup on secondary replica is double the size of primary. TIA.


texts: adding rules to MySQL user to have prefix when they created database

texts:
I am working on a project that combine multiple MySQL ports under the same port; each port has a shared databases with names (database name and tables), so I though of the following idea. if I created user1, user2, and user3, on the new machine and separate all user databases by adding prefix (userid_) to the database name `.

example:

port (3306) one is located under machine 1.1.1.1, if I use the following command I will get the following result:

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| customers          |
| meetings           |
| mysql              |
| performance_schema |
| test1              |
| training           |
| feedback           |
+--------------------+

port (3307) is located under 2.2.2.2 if I use the following command I will get the following result:

show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | customers          |
    | management         |
    | mysql              |
    | performance_schema |
    | meeting            |
    | employee           |
    | feedback           |
    +--------------------+

port (3308) is located under 3.3.3.3 if I use the following command I will get the following result:

show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | customers          |
    | meetings           |
    | mysql              |
    | performance_schema |
    | test1              |
    | training           |
    | feedback           |
    | management         |
    +--------------------+

the new machine 4.4.4.4 port (1111) should have (note its already done):

show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| user1_customers    |
| user1_meetings     |
| mysql              |
| performance_schema |
| user1_test1        |
| user1_training     |
| user1_feedback     |
| user2_customers    |
| user2_management   |
| user2_meetings     |
| user2_employee     |
| user2_feedback     |
| user3_customers    |
| user3_meetings     |
| user3_test1        |
| user3_training     |
| user3_feedback     |
| user3_management   |
+--------------------+

My question here can I make a rule for the three user when they created a database should start with their name ( if user3 create a database the database will have user3_ as prefix) or to add it automatically.


texts: How can I set a default session sql_mode for a given user?

texts:
I want to have a default session sql_mode that is different from the global sql_mode.

I can set it manually, but I'd like to do it automatically.

I thought I could add it to .my.cnf, but that doesn't work. I tried adding it to the [mysql] group in .my.cnf, and it does not throw an error.

Yet, when I connect my session sql_mode, the session still inherits the global sql_mode.

I'm using MySQL 5.5.16.


texts:
I have the following SQL, generated from ActiveRecord in Rails 4 (partly with the Geocoder gem)

problem is, that even though DISTINCT is used, hopefully to get only unique items, the items returned duplicated. The reason for the duplication is that they can occur in several shops or brands (which is why I added the distinct, which doesn't quite help)

SELECT DISTINCT items.*,
                <calculate distance from shops coordinates> AS distance,
                <calculate bearing> AS bearing,
                items.*,
                shops.id AS closest_shop_id
FROM "items"
INNER JOIN "collection_items" ON "collection_items"."item_id" = "items"."id"
INNER JOIN "galleries" ON "galleries"."id" = "collection_items"."gallery_id"
LEFT OUTER JOIN brands ON brands.id = galleries.galleriable_id
AND galleries.galleriable_type = 'Brand'
INNER JOIN shops ON shops.brand_id = brands.id
LEFT OUTER JOIN shops AS othershops ON galleries.galleriable_id = othershops.id
AND galleries.galleriable_type = 'Shops'
WHERE <shops coordinate are within some bounds>
ORDER BY updated_at DESC,
         distance ASC LIMIT 10

*things in < > are calculation simplification to make query less messy

How can I make this query return distinct items?

Update:

Checking with PGAdmin, I notice the results are distinct, but on everything, see data

So, to make these distincts, I need to decide which row to ignore.

Is there a way to take only the first occurance (by the order specific) of the id column?

such that it will take the first row of id 23171?


texts:
I lost a lot of data in my postgresql database and do not have a dump file to restore backup from, but I heard that in /var/lib/postgresql/9.4/main there are pg_xlog and pg_clog and that those directories could be used to restore previous points in the db. Could someone clarify this and how to do it? The importance is major.

FYI everything is stopped. What happened is that there is only a very old dump from February since then a lot has changed in the database and in 3rd of July at 2PM executing a python script it changed a lot of the data in the database(data changed is the clients assigned to a specific shop). There is no data loss but it would be needed to revert those transactions. In the pg_xlog folder the transaction logs that have not been archived are from 2hours before that moment it all changed.

I desperately need to revert those changes. Is there any possible way to revert changes?

I have a system backup of that virtual machine that is moments few hours after and can try solutions many times.


texts:
I have an Oracle 12c database running in OEL 6.. yesterday due to some OS related issues, my Oracle home software mount-point got erased completely.

The only thing that is left is the pfile and data files. I have got that.
I do not have backups as well.

What are the options I have got? If I just install the New Oracle home and start with the pfile and with the data files, will I be able to start the database as it is?


texts: Why scan clustered index instead seek nonclustered index?

texts:
There is SQL script:

CREATE TABLE users
  (
     id        INT,
     firstname VARCHAR(50),
     surname   VARCHAR(50)
  );

CREATE CLUSTERED INDEX ix_users_id
  ON users (id);

CREATE NONCLUSTERED INDEX ix_users_firstname
  ON users (firstname);

SELECT firstname,
       surname
FROM   users
WHERE  firstname = 'John';

I do not understand why for the above SELECT request, Engine of SQL Server 2019 selected follows Execution Plan:

Why the Clustered Index is scanned? I thought, faster is:

seek Nonclustered Index;
move on a Clustered Index by Clustered Index Pointer, which
storing in Leaf Node of Nonclustered Index;
and take rest surname value from there.


texts:
I have experienced a problem with flashback and the solution is to recreate the control file (the error was ORA-01207).

So to do this I tried

 alter database backup controlfile to trace as '/tmp/trace.sql';

This creates a file that instructs one to either use set one or set two. i.e. with or without resetlogs.

How does one determine what set is the most applicable? Is there any tests that one needs to perform to find out?


texts: Switch-SQLAvailability error during DB Restore in AlwaysON Group

texts:
Can anyone share your inputs on below and how to fix the issue and possible causes of failure?

I am trying to restore a DB using PS script and at one step it is failing during the role change test and during failover.

All Databases are Synchronized and BCP is Synchronizing and alwayson AG is in green and Healthy.

But I am still facing the below issue.

Verified the SQL Server logs and can see below point:

  A connection timeout has occurred while attempting to establish a
  connection to availability replica '' with id
  [xxxxxxxx]. Either a networking or
  firewall issue exists, or the endpoint address provided for the
  replica is not the database mirroring endpoint of the host server
  instance.


texts: Performance impact of Extended Events sessions looking at the exact same event type

texts:
I have created an extended events session that watches the module_start event type and filters down based on the object_name: equal_i_sql_unicode_string]([object_name])
The purpose of this session is to simply record basic information whenever a proc in the filter list is called so that I can answer developers questions with 99.9% guarantee whether or not a proc is still called in production. The idea is to run this for ~1 month 24/7 (yes it doesn't account for things that are ran annually, but it is what it is).
The issue I am running into is that the list of procs the developer gave me is about 90 or so long and the filter list of an EE session is limited to 3,000 characters. The only idea I have come up with in order to increase the rate at which we can track the procs is to have 2 separate EE sessions that are identical except the filter predicates are different.
I am not asking "how much of a CPU impact will this be", but more or less is their any concern with running 2 of the same EE sessions with different filter predicates? It is odd to me that Microsoft would limit the filter list to 3,000 characters when 'more filtering == better performance' because the way EE is built into the engine it is very optimized unlike a trace that acts more like a proxy than a "trigger based off an event".
Is it safe to assume that whatever the performance impact of running 1 session is I can multiply it by 2 or is their further concerns I am not considering?


texts: MySQL Enterprise backup (mysqlbackup). Log progress into file but don't report it to stderr

texts:
When using MySQL Enterprise Backup I am interested in keeping a record of the progress in the MEB-<timestamp>-<operation>.log file, as usual, but I don't want to get the same output via stderr. (If possible to reserve stderr to actual warnings and errors)

Is there a way to accomplish this?


texts: Exclusion constraint on ENUM errors: data type dow has no default operator class for access method "gist"

texts:
Following in the style of this, I'm trying to create an exclusion constraint over an ENUM,

CREATE TYPE dow AS ENUM (
  'Monday', 'Tuesday', 'Wednesday', 'Thursday',
  'Friday', 'Saturday', 'Sunday'
);

CREATE TABLE available (
  dow      dow,
  tsrange  tsrange,
  EXCLUDE USING gist (tsrange WITH =, dow WITH &&)
);

Returns

ERROR:  data type dow has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.


texts: Read ancient Oracle database into current Oracle version

texts:
An old computer with Oracle 8i is dead.  We have the daily backup of the database (C:\oradata).  We are unable to install this old Oracle version anywhere because we don't have the install CDs for it.

Can we read this database with, for example, Oracle Express? If yes, how?


texts: Replacing the removed database diagrams feature

texts:
I have a SQL Server database and want to create an ER diagram for it, to analyze tables and relationships.

The option to create a diagram is no longer present in SSMS 18.

Is there any free tool that would help me to create such a diagram, or another workaround?


texts: .NET error when trying to create snapshot for transactional Replication

texts:
I encountered a bizarre prob today while creating a transactional publication on a SQL 2012 server running on Windows Server 2008R2.

I had just had some success creating and initializing some Merge publications (including generating snapshots) on this same server.

With my transactional publication though, around the time the snapshot agent goes to lock tables, it gives me this:

  A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_scriptdropinsreconciliationproc_sqlclr":

  System.TypeInitializationException: The type initializer for >'System.Data.SqlClient.SqlConnection' threw an exception. ---

Here are some things I tried:

Google: surprisingly few results. A reference to KB2840628, which is
not on the system.
Search here: nothing found
Manually create subscription thru the GUI (had
been using a script) - fail
Same as above but publish from a
different database - fail

In these tests I am using ridiculously
   powerful accounts to try to rule out permissions issues.

It's very strange. Any helpful hints would be appreciated.


texts:
I have some legacy mysql files of the format frm, MYI and MYD that i wish to export to csv. Using sequel pro on OSX, i created a new database and copied those files over.

All the data can be accessed fine but i have major issues with the query results due to unknown encoding. I have no idea what format the previous programmer used.

This database contain English, Chinese, Japanese and Korean song titles but as i have no idea what format these names are encoded in, so i get weird characters using utf8 format:

ÄÐÉù
ÂèÂèºÃ+Éµ´ó½ã+ÆÃË®¸è
Àä¿ÕÆøµÄ¶À°×
À¶É«µÄÃÎ

Most of them begins with A with an accent though, i wonder if they do provide any clue as to which encoding format the programmer used.

I've tried putting these text onto safari/chrome browser and ran through the various encoding options but to no avail.

Will anyone please help me?


texts: How to efficiently sort a column from two tables?

texts:
In our app, we have assignments. We create this by unioning two other tables with a due_date column: checklists and tasks. We want to order assignments by the due_dates. Right now, we are doing something like this:

select * from (
  select a.type, t.id, t.due_date from assignments a
    inner join tasks t on t.id = a.task_id
  where a.type = 'Task' and a.user_id = 'X'
  union
  select a.type, c.id, c.due_date from assignments a
    inner join checklists c on c.id = a.checklist_id
  where a.type = 'Checklist' and a.user_id 'X'
)
order by due_date desc
limit 20

Obviously, this goes very slow because it needs to generate all the task and checklist assignments (which can be in the 100000s) and then sort them and grab the top 20.

Adding an index doesn't help because the due_date field is in separate tables.

We looked at table inheritance, but it seems to just be syntactic sugar for unions, so doesn't really help.

Also, because there are so many assignments, it seems like materialized views wouldn't help because they'd have to be refreshed every 100ms or so.

What is a good way to optimize this type of query?


texts: SQL Server target/total server memory is not equal to sum of database size in dm_os_buffer_descriptors

texts:
I am having an issue in my SQL Server like high memory paging rate and more compilation/sec which lead me to a different observation in memory, so first i checked what is total size of memory of system and what are the threshold levels set in SQL server i.e. min server memory 4096 MB and max server memory 51200 MB, i checked few performance counters total server memory is 64 GB, target server memory is 53 GB and other performance counters as shown below images

When i checked sys.dm_os_buffer_descripters in SQL Server there are very less dirty pages, but when i check total size of memory occupied by all databases is 39 GB as shown in below images

My questions is why and how 14 GB of memory is additionally being occupied by SQL Server and the same i can in resource monitor or performance counter like below image.

Can any one tell me did i miss some thing to check, Total databases are occupying only 39 GB but if i check perfmon or resource monitor it shows that SQL Server is occupying total 53 GB, how 14 GB can be used internally.


texts: sqlplus works specifying the parameters but fails when using tnsnames.ora

texts:
I've been turning google upside down and couldn't find an answer. I can connect to my oracle instance like this:

sqlplus 'myuser@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=msoracle.cnujcn8mkxea.us-east-1.rds.amazonaws.com)(PORT=1521))(CONNECT_DATA=(SID=ORCL)))'

But I can't make it work if I create a TNSNAMES.ORA at /usr/lib/oracle/12.1/client64/network/admin

ORCL=
(DESCRIPTION =
  (ADDRESS_LIST =
    (ADDRESS=(PROTOCOL=TCP)(HOST=msoracle.cnujcn8mkxea.us-east-1.rds.amazonaws.com)(PORT=1521))
  )
  (CONNECT_DATA=
  (SERVICE_NAME=ORCL)  -> I tried SID as well. No luck.
  )
)

I tried to

export ORACLE_SID=ORCL
export PATH=$PATH:$HOME/bin:/usr/lib/oracle/12.1/client64/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/12.1/client64/lib
export ORACLE_HOME=/usr/lib/oracle/12.1/client64
export TNS_ADMIN=$ORACLE_HOME/network/admin

And nothing. The following message always comes up:

ERROR:
ORA-12162: TNS:net service name is incorrectly specified

EDIT:
This is the output of the tests in the comments:

[franklin@ip-10-148-17-253 client64]$ sqlplus myuser@ORCL

SQL*Plus: Release 12.1.0.2.0 Production on Fri Aug 5 10:13:39 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name: myuser
Enter password:
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Enter user-name: ^C


texts: How to add 'root' MySQL user back on MAMP?

texts: On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?


texts: How to Insert records from remote database to local database using dblink in postgresql 9.3

texts:
I have a scenario like i have a two DB, one resides in remote server and other resides local. Now i have to fetch the records from remote DB when new records are inserted on tables (in remote DB) and insert into local DB (Both DB have a same table structure).

I have many tables on it and for each and every table i have to do this activity. For this we wrote a function like on below flow.

create dblink for remote server and map user.
create foreign tables
get the new records count from remote table through foreign table option and store it in table (contain table_name and no of records).
And based upon this table if i have a count >0 then call another function which sends query to fetch records from remote server and create insert query for local DB (record wise inserting) execute insert query.
Then create loop for log table to generate inserted records details and insert it into log table.

My Questions:

Since my team is asking to reduce the total time i increased the following parameters in
postgresql.confg file (windows machine) as per 'pg-tune' suggested,

shared_buffers
work_men
maintenance_work_men
effective_cache_size

but i cant find any performance improvement.

What i'm missing here? can any one suggest on this?

To find where my function taking more time to perform i used the module 'auto_explain' (for session) and noted the execution plan,
auto_explain settings

load 'auto_explain';
set auto_explain.log_min_duration=0;
set auto_explain.log_nested_statements=ON;
SET auto_explain.log_analyze = true;

as observed i added the index on required field. Sample is given below,

log Bfr adding index

2014-12-09 10:37:33 IST LOG:  duration: 129.007 ms  plan:
    Query Text: SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00'
    Aggregate  (cost=178.27..178.28 rows=1 width=0) (actual time=129.001..129.001 rows=1 loops=1)
      ->  Foreign Scan on res_users_ft j0  (cost=100.00..175.42 rows=1138 width=0) (actual time=128.882..128.887 rows=29 loops=1)
2014-12-09 10:37:33 IST CONTEXT:  SQL statement "SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00'"
    PL/pgSQL function fn_get_update_row_counts() line 36 at EXECUTE statement
    SQL statement "SELECT fn_get_update_row_counts()"
    PL/pgSQL function fn_pull_hq_to_hqstag_batch_process() line 7 at EXECUTE statement

Log After adding index

2014-12-10 10:46:29 IST LOG:  duration: 0.762 ms  plan:
    Query Text: SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00'
    Aggregate  (cost=178.27..178.28 rows=1 width=0) (actual time=0.759..0.759 rows=1 loops=1)
      ->  Foreign Scan on res_users_ft j0  (cost=100.00..175.42 rows=1138 width=0) (actual time=0.746..0.750 rows=29 loops=1)
2014-12-10 10:46:29 IST CONTEXT:  SQL statement "SELECT count() FROM res_users_ft j0 WHERE j0.write_date > '1970-01-01 00:00:00'"
    PL/pgSQL function fn_get_update_row_counts() line 36 at EXECUTE statement
    SQL statement "SELECT fn_get_update_row_counts()"
    PL/pgSQL function fn_pull_hq_to_hqstag_batch_process() line 7 at EXECUTE statement

But overall time is still remaining the same Since time got reduced on above log.

What i'm missing here? can any one plz suggest on this?

And also please suggest me instead of record wise insert can i do bulk insert? if yes suggest me how can i do that without using third party tool?

Note: For inserting records i didn't getting any information (duration) on log. How can i do that to view duration?

thanks in advance.


texts: how many cores uses gbak (firebird) on restoring?

texts:
I'm restoring db, and I see that firebird uses only one core of my CPU for gbak and fb_smb_server:

Is it normally? I've 4 cores in my CPU.
CpuAffinityMask parameter in firebird.conf is commented out.


texts:
I have two tables, first the table food and Second is Activity:

INSERT INTO food
    (`id`, `foodName`)
VALUES
    (1, 'food1'),
    (2, 'food2'),
    (3, 'food3'),
    (4, 'food4'),
    (5, 'food5'),
    (6, 'food6'),
;
CREATE TABLE Activity
    (`id` int,`place` varchar(14),`food_id` int,`timing` TIME,`date_and_time` DATETIME)
;
INSERT INTO Activity
    (`id`,`place`, `food_id`,`timing`,`date_and_time`)
VALUES
    (1, 'place1', 1, '10:30am','2013-05-01'),
    (2, 'place1', 1, '12:30pm','2013-05-01'),
    (3, 'place1', 1, '04:30pm','2013-05-01'),
    (4, 'place2', 2, '10:30am','2013-05-02'),
    (5, 'place2', 2, '12:30pm','2013-05-02'),
    (6, 'place2', 2, '4:30pm','2013-05-02'),
    (7, 'place1', 2, '10:30am','2013-05-02'),
    (8, 'place1', 2, '12:30pm','2013-05-02'),
    (9, 'place1', 2, '4:30pm','2013-05-02'),
    (10, 'place2', 3, '10:30am','2013-05-03'),
    (11, 'place2', 3, '12:30pm','2013-05-03'),
    (12, 'place2', 3, '4:30pm','2013-05-03')
;

For now I'm using the following query:

SELECT
    a.activity_type AS Activity,
    COUNT(DISTINCT p.id) AS Products,
    CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8)
      AS Categories
FROM
    food AS p
  JOIN
    ( SELECT activity_type
           , prod_id
      FROM activity
      WHERE activity_type <> ''
      GROUP BY activity_type
             , prod_id
    ) AS a
    ON p.id = a.prod_id
GROUP BY
    activity_type
ORDER BY
    Products DESC ;

Could you please help me, I need output in the below format:

place | food_id | Timings             |                              |
      |         |---------------------|Date                          |
      |         |Time1 |Time2 | Time3 |                              |
---------------+----------+------------------------------------------|
place1 | 1      | 10:30am| 12:30pm| 4:30pm |2013-05-01(MAX timestamp)|
place2 | 1      | 10:30am| 12:30am| 4:30am |2013-05-01(MAX timestamp)|


texts: Pass a table array as a parameter to an upsert function in postgresql

texts:
i have an UPSERT function which works well but i update and insert records in batches, can this function be modified so that i just pass all the records to this function as an array then it will insert/update ALL the records at once as ONE TRANSACTION? if record number x fails,it should roll back undo any records that had been inserted/updated before x?
here is my upsert function:

CREATE OR REPLACE FUNCTION save_weights(rec tbl_weightment)
  RETURNS text AS
$BODY$
DECLARE
myoutput text :='Nothing has occured';
BEGIN

    update tbl_weightment set
    vname=rec.vname,
    iquality=rec.iquality,
    vhooks=rec.vhooks,
    tstamp=rec.tstamp
    vtare=rec.vtare,
    vgross=rec.vgross
    WHERE id=rec.id;

    IF FOUND THEN
        myoutput:= 'Record successfully updated';
        RETURN myoutput;
    END IF;

    BEGIN
        INSERT INTO tbl_weightment SELECT(rec).*;
        myoutput:= 'Record successfully added';
    END;
 RETURN myoutput;

    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

can this function be modified to be like save_weights(rec tbl_weightment[]) ??
any help will be highly appreciated


texts: Log to record activity against a database

texts:
I've been assigned to create a general log.

I need a log that captures what actions were taken, by whom, to the database. Regardless of whether it is a SELECT or an ALTER or etc.  We need to keep track of everything that users have been doing to the database.

I have already created a general log to my localhost as a "mouse lab".  However, there are no records inserted into the general log table.

What can you suggest I do?  I'm using MySQL.

I'm losing myself on this, and I have no idea what to do anymore.  I'm just a freshie, not even graduated.


texts:
My query is:

SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM
"tema".MMT WHERE BRAND_GROUP IS NULL AND SUB_BRAND IS NULL GROUP BY
BRAND,BRAND_GROUP,SUB_BRAND

UNION
SELECT BRAND,BRAND_GROUP, SUB_BRAND ,SUM(INCOME) AS TOTAL_INCOME FROM
"tema".BGT WHERE BRAND_GROUP IS NULL AND SUB_BRAND IS NULL GROUP BY
BRAND,BRAND_GROUP,SUB_BRAND;

and my output is :

BRAND BRAND_GROUP SUB_BRAND TOTAL_INCOME
----- ----------- --------- ------------
GBS   NULL        NULL             10000
SWG   NULL        NULL             10000
GBS   NULL        NULL             20000
STG   NULL        NULL             20000
GTS   NULL        NULL             30000

The problem is that i have 2 categories of BRAND and I want to have just 1. Like this :

Brand       Brand_Group     Sub_brand       Total_Income
GBS                  -          -           30000
STG                  -          -           20000
GTS                  -          -           30000
SWG                  -          -           10000

Can someone help me with an ideea?


texts: Best practice to query administrative views for instance level elements

texts:
I want to query a monitor element at instance level (for example the memory used for the instance) via a query like this:

SELECT MEMBER, MAX_MEMBER_MEM, CURRENT_MEMBER_MEM, PEAK_MEMBER_MEM
FROM TABLE(SYSPROC.ADMIN_GET_MEM_USAGE()) AS T

However, in order to execute the query I need to be connected to a database. What method do you use to query this kind of elements (instance level) and establishing a connection (database level):

Connect to the first database available in the db directory.
Create another database just for monitoring purposes.

1) For this scenario, I have to give the necessary grants in at least one database of each instance.

db2 list db directory | awk '/alias/ {print $4}'
db2 connect to $i
db2 -vf query.sql

2) In this scenario, I have to create a database for monitoring purposes in each instance of each server, and I give the necessary grant in this database.

db2 connect to monitodb
db2 -vf query.sql

I have hundreds of databases, distributed in many instances on different servers. And for this reason, the way to do a query creates a great impact in the set of database I am currently administering.
|


texts: Is it safe to call pg_ctl reload while doing heavy writes?

texts:
I am running Postgres 8.4, and I would like to change the authentication method for a user. It's my understanding that I have to do this in pg_hba.conf, and then reload the configuration files with pg_ctl reload (or, in this case, service postgresql reload) for the settings to take effect.

However, I am also importing a ton of data from flat files using multiple processes.

Is it safe to do this?


texts:
I have developed an application which connects to a database over a WAN.

As long as I'm using the application on my local network, everything works fine.
When I connect over the WAN, the database request takes several seconds. The user experiences long load times when using the application from his location. The loading times are so long, that it isn't workable.

Most of the time, the query selects all the data from a table. Sometimes it only selects one line.

I think this is due to latency on the WAN. Does anyone know how I can make my queries "latency-proof"?


texts: How can I generate all trailing substrings following a delimeter?

texts:
Given a string that may contain multiple instances of a delimiter, I want to generate all substrings starting after that character.

For example, given a string like 'a.b.c.d.e' (or array {a,b,c,d,e}, I suppose), I want to generate an array like:

{a.b.c.d.e, b.c.d.e, c.d.e, d.e, e}

The intended usage is as a trigger to fill a column for easier querying of domain name parts (i.e. find all q.x.t.com for query t.com) whenever another column is written to.

It seems like an awkward way to solve this (and it very well may be), but now I'm curious how a function like this could be written in (Postgres') SQL.

These are email domain names so it's hard to say what the maximum possible number of elements is, but certainly the vast majority would be < 5.


texts: How to optimize query by preventing repetitive subquery

texts:
I have query that uses a subquery (select min/max(date) from stage.stage_net_subscription_010_filter_and_net_pds) to find min and max dates of a table.
It seems that the subqueries are repeated very often.

Is there a way to rewrite the query without CTEs such that the subqueries do not get executed for every loop
One other option to speed up the query is see, would be to use generate series in stead of selecting the dates from a table with dates. Any clue if that will make a difference?

Query:
SELECT
         dim_date.date AS date
         , stage_net_subscription_020_classes.client_id AS client_id
         , stage_net_subscription_020_classes.product_id AS product_id
  FROM star.dim_date dim_date
  CROSS JOIN stage.stage_net_subscription_020_classes stage_net_subscription_020_classes
  where date >= (select min(date) from stage.stage_net_subscription_010_filter_and_net_pds)
  and   date <= (select max(date) from stage.stage_net_subscription_010_filter_and_net_pds);


texts: MySQL Workbench, Windows Server 2019, MySQL not showing Performance, Information of MySQL Schemas!

texts:
This evening I installed MySQL on a new clean install of Windows 2019 server. When the install finished, the installer offered the possibility of opening Workbench so I said yes.

Then I noticed that there were schemas missing - as per the question, the performance, information and mysql schemas were missing (see the screenshot below1)

But, when I log in using the mysql client, I get this

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

In Workbench, I can access the "missing" schemas via queries like so

SELECT host, user FROM mysql.user;

host,user
%,xxx
localhost,mysql.infoschema
localhost,mysql.session
localhost,mysql.sys
localhost,root

Can anybody explain to me what's going on here? How can I get the missing schemas to appear in MySQL Workbench?

1 I know that screenshots are normally not a good idea in this forum, but this time, it's the only way to show what I mean.

>mysqld.exe --version
\mysqld.exe  Ver 8.0.18 for Win64 on x86_64 (MySQL Community Server - GPL)

and

Workbench 8.0

and

Windows Server 2019 Standard


texts: What size of hardware/VM for database with over 1,000,000 posts, 20GB?

texts:
I am running a Rails application with a Postgresql database. I am exited to say that I am scaling rapidly, and I now have over 1,000,000 posts, not including the User table, in Postgresql(approx 22GB). What instance size should I purchase? I currently have an 8x core, 8GB, SSD cloud server, and it is sh^tting a brick. Reviewing both cloud and dedicated hardware solutions.

Price, for the most part, is not a factor.

Specs:

Rails 4
Postgresql 9.1

Any advice is humbly welcome.

Cheers,
Jake


texts: How should I handle measurement errors in a timeseries database?

texts:
I have a table used to record measurements sampled at regular intervals on different sensors. Each row records the time, the identifier of the quantity being measured, and the value itself.

Now and again measurement errors occur and garbage is being recorded in the value field. How should I deal with these errors:

Delete the offending rows entirely, losing the information that there had been an error;
Keep the row as is, and asking of client code to deal with the errors;
Replace the values with NULL, losing the original erroneous value?

Or is there another option that I have not considered?


texts:
I trying to fetch data form database which is have 3 column unique_id, parent_id & node_name, but this query (Given in below) is return only two rows.

Condition: every parent has two child.

DROP PROCEDURE `treeTbl`;
CREATE DEFINER=`bizsol`@`localhost` PROCEDURE `treeTbl`(IN `childId` INT(255)) DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
 DECLARE tempId INT DEFAULT 0;
 SELECT *, unique_id as id FROM t_users WHERE 1 AND parent_id = childId;
 IF(tempId>0)
  THEN
    SET tempId = id;
    CALL treeTbl(tempId);
 END IF;
END

And call form here,

CALL treeTbl(3);


texts: How should I organize my mdf, log and tempdb on a 3 physical disk server

texts:
Windows Server 2012, 48Gb ram, SQL 2016 Standard with:

1 disk SAS for Windows and SQL binary (C: )
2 SSD disk for data (D: and E: )

Talking about performance, what is the better way to put tempdb, mdf and log of my OLTP Database (about 50Gb, about 100 simultaneous users)?

Maybe:

C: - OS and sql binary
D: - MyDB and Tempdb data file
E: - MyDB and TempDB log file

(D: and E: are physical disk, i don't think it's useful for performance creating more Logical partition to divide objects, or not?)

It's the "better" way?


texts: Can you view the Oracle listener status from OEM?

texts: Is it possible to view/monitor the listener status from Oracle Enterprise Manager (OEM)?  If so, how is this done?


texts: MYSQL: Values of a column where two other columns have same value

texts:
I have a table say table1 with three columns lets name them col1 col2 and col3. I have a suspicion that col2 and col3 have some values that are identical i.e. to say there are some rows where col2 = col3. In case, if for these rows col1 is the same I will have no problem in my application. But how to check for those where col1 = col2 but they don't point to the same col1 value.

I mean SELECT * FROM table1 WHERE col2 = col3 gives me records for col2 = col3 in same rows. what I want for each col2 value search col3 if it is same send those records

Something like this (this is pseudo SQL) but in MYSQL

SELECT DISTINCT col2 FROM table1
foreach col2
    same_records = SELECT DISTINCT col1 FROM table1 WHERE col2 = col3
    records UNION same_records
end foreach
SELECT records

Is there is a way to get these results (if they exist)? I believe that I have to use self-join on the table.


texts: combining 2 sql queries based on different conditions

texts:
I have a table :

Person :

(integer) (varchar)  (Date)         (date)
  id       state       d1            d2
  1       'ALIVE'   '2015-08-07'  '2015-08-08'
  2       'DEAD'    '2015-08-08'  '2015-08-10'
  3       'DEAD'    '2015-08-08'  '2015-08-08'

And so on..

What I want is the count of all people when :

  person is alive and d1 <=someDate
  person is dead and d2 > someDate and d1 < = someDate.

So, in the above case, if someDate = '2015-08-08', then the output should be 2. i.e, id 3 should not be considered.

I can of course do this as 2 seperate queries and add the result. How to do this in just one query.

Note : This is an example of what I am trying to do. The table names, columns are different in my code.


texts: Monitor stored procedure statistics with extended events

texts:
I want to log the duration and time of user defined stored procedures each time they are invoked. I believe using extended events would be the way to go to achieve this.
Can you please help me in defining the session and in querying the results?
I am not quite sure about which event to add (sqlserver.rpc_completed?) and which target to choose (synchronous_event_counter,asynchronous_file_target or ring_buffer?).
I also need help in querying the results (For example: grouping the result set so that each sp will show from top execution duration and execution time.)
We use SQL Server 2008 R2.


texts:
I need to integrate a daily XML file into my system.

The XML file has an schema similar to this:

create table txml(id int identity, data xml);
insert into txml (data) values ('
<order>
    <trans DTrans="20170102" HTrans="10:52"></trans>
    <head Id="552665566"></head>
    <lin headId="552665566">
        <dLin Id="00001" CoArt="1111111" CoArtCust="05220001" NameArt="Product1" dateVal="20161115" />
        <acum DCalAcm="20170101" DIniAcm="20161115"><qtyAcm Qty="1101163.00" /></acum>
        <lastOrd Id="95767" QtyLastOrd="12000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="20000.00"/><dIni Date="20170109"/><dEnd Date="20170112"/></pLin>
        <pLin LType="4"><uni Qty="24000.00"/><dIni Date="20170113"/><dEnd Date="20170116"/></pLin>
    </lin>
    <lin headId="552665566">
        <dLin Id="00002" CoArt="2222222" CoArtCust="05269958" NameArt="Product2" dateVal="20161101" />
        <acum DCalAcm="20170101" DIniAcm="20161101"><qtyAcm Qty="552652.00" /></acum>
       <lastOrd Id="49956" QtyLastOrd="5000.00" DLastOrd="20170101" />
        <pLin LType="4"><uni Qty="2200.00"/><dIni Date="20170104"/><dEnd Date="20170108"/></pLin>
        <pLin LType="4"><uni Qty="3000.00"/><dIni Date="20170109"/><dEnd Date="20170116"/></pLin>
    </lin>
</order>');

As you can see for each <lin> section you can find an unknown number of <pLin> lines. By now I'm doing this job using C# and a .Net console app, but I'd like to do it by using SQL.

Till now I can get a rowset of <lin> tags using the next query:

SELECT  T2.lin.value('(./dLin/@Id)[1]', 'int') Id,
        T2.lin.value('(./dLin/@CoArt)[1]', 'varchar(20)') CoArt,
        T2.lin.value('(./dLin/@CoArtCust)[1]', 'varchar(20)') CoArtCust,
        T2.lin.value('(./lastOrd/@Id)[1]',  'varchar(20)') lastOrderId,
        T2.lin.value('(./lastOrd/@QtyLastOrd)[1]',  'decimal(18,2)') QtyLastOrd
FROM    txml
CROSS APPLY data.nodes('/order/lin') as T2(lin);

That produces next result:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd
-: | :------ | :-------- | :---------- | :---------
 1 | 1111111 | 05220001  | 95767       | 12000.00
 2 | 2222222 | 05269958  | 49956       | 5000.00

But I should add nested lines corresponding to <pLin> tags and produce the next result:

Id | CoArt   | CoArtCust | lastOrderId | QtyLastOrd | DIni       | DEnd       | NextQty
-: | :------ | :-------- | :---------- | :--------- | ---------- | ---------- | ---------
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 04/01/2017 | 08/01/2017 | 24000.00
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 09/01/2017 | 12/01/2017 | 20000.00
 1 | 1111111 | 05220001  | 95767       | 12000.00   | 13/01/2017 | 16/01/2017 | 24000.00
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 04/01/2017 | 08/01/2017 | 2200.00
 2 | 2222222 | 05269958  | 49956       | 5000.00    | 09/01/2017 | 16/01/2017 | 3000.00

I've set up a dbfiddle here


texts: SQL Server: Slow restore from SQL 2014 DB, but Fast from SQL 2016 DB via SSMS

texts:
We are chasing an odd problem.

We see a significant perf delta when restoring via SSMS, but do NOT see the delta when restoring via TSQL.

When we restore, using SSMS, on SQL 2016 from a DB created on a SQL 2014 instance, the restore is really really slow.

But, on the same SQL 2016 DB server, when we restore from a SQL 2016 instance, again in SSMS, the restore is super fast.

The SQL 2016 system is a super fast system, all-SSD, etc. It should always be fast.

To address, we applied all OS and SQL patches and updates to the target system. Problem was not changed.

Why would restoring a SQL 2014 DB be so slow?


texts: Constructing Circularly Referenced Tables

texts:
I'm attempting to construct two tables in Oracle, A and B; each of these tables reference each other. In my script, I have the foreign key constraints 'in-line' of the create table statements. Of course, both the create table A and create table B are failing  because neither table has been created yet.

Is it possible to keep the foreign key constraints in-line, or must they be added only after the table(s) have already been created by means of an alter table statement?


texts: InnoDB has a transaction running, but "SHOW PROCESSLIST;" shows nothing

texts:
I have a replicated database setup, mostly Innodb databases/tables.  The query that I believe caused the issue ran in about 20 minutes on the master database. This is where it gets weird. Show processlist has nothing listed, except for my connection and the replicant connection. Innodb has a transaction running according to show innodb status.  The transaction has been running for 7+ hours.

Replication seems to have stopped, despite the slave_io and slave_sql both running. The Exec_Master_Log_Pos hasn't budged and cpu usage is at 100% (1 core).

Is there any way to see what query it's actually running as well as maybe a way to figure out how long the transaction may take? I'm kind of lost here and about to blow away the slave and resetup replication if I can't get anywhere soon.


texts: Why does UPDLOCK cause SELECTs to hang (lock)?

texts:
I've got a select in SQL SERVER that locks the whole table.

Here's the setup script (make sure you don't overwrite anything)

USE [master]
GO

IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = 'LockingTestDB')
DROP DATABASE LockingTestDB
GO

CREATE DATABASE LockingTestDB
GO

USE [LockingTestDB]
GO
IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'LockingTestTable')
  DROP TABLE LockingTestTable
GO

CREATE TABLE LockingTestTable (
  Id int IDENTITY(1, 1),
  Name varchar(100),
  PRIMARY KEY CLUSTERED (Id)
)
GO

INSERT INTO LockingTestTable(Name) VALUES ('1')
INSERT INTO LockingTestTable(Name) VALUES ('2')
GO

Open a new query window and run the following transaction (which has a wait in it):

USE [LockingTestDB]
GO

BEGIN TRANSACTION
  SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '1'
  WAITFOR DELAY '00:01:00'

COMMIT TRANSACTION
--ROLLBACK
GO

USE [master]
GO

And another one that will run (make sure they run at the same time):

USE [LockingTestDB]
GO

SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '2'

USE [master]
GO

You'll notice the second query will be blocked by the first one. Stop the first query and execute the ROLLBACK and the second will complete.

Why is this happening?

PS: Adding a non-clustered index (with full coverage) over Name will fix it:

USE [LockingTestDB]
GO

CREATE NONCLUSTERED INDEX [IX_Name] ON [dbo].[LockingTestTable]
(
  [Name] ASC
)
INCLUDE ( [Id]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Again why?


texts: How to import sql file in stored procedure in MySQL?

texts:
What I want to do is as follows.

In a stored procedure:

Access the DB to retrieve some record of a file name X
Check whether a file named X.sql exists
source X.sql; (or something similar)

Is that doable? Or do I have to go to UDF?

Thanks.


texts: MySQL : How to know if Trigger was fired

texts:
Someone can help me please , I want to log error about triggers in MySQL 5.5
I'll like to know if it's possible to know if a trigger was'nt fired ?


texts: SQL Server 2005 not collecting logins in the log file

texts:
I am currently running SQL Server 2005 Standard Edition on a Windows 2003 server machine.

I have gone to the properties of the server and confirmed that the Login Auditing is set to both failed and successful logins. For some reason though there is nothing in the logins for fails or successes in the SQL Server logs when I know there have been logins for both.

I have searched out the reason for this and have not been able to come up with anything helpful, so I am hoping that someone here could give me a little direction. This is working on my other SQL Servers, so I know where to look for the results, but there are none there.


texts:
I have a progress openedge server which I have an ODBC connection to. I can read and write to the server through ODBC with SSIS from a MSSQL database. I've used SSIS to write to a test database but because I now want to write to live I'm more hesitant I've been trying to find documentation from progress to give me a step by step, what to look out for etc.

I can't find this, I can only find Progress to MSSQL but I've checked the collation and it seems compatible iso8859-1 to Latin. I've never worked with progress before so I'm now looking at using ABL but if using SSIS is ok I would prefer to use this method.

If anyone has used this method before I would appreciate some advice. My main concern is does the ODBC connection work well the other way around.

I don't see any issues from my understanding of databases, if I can populate the fields with data that conforms to the constraints and the conversion doesn't drop 0s or any other corruption once the data is there an application will pick it up.

The main issue I've read about is if the collation is different or incompatible I need to make sure it's converted but I'm confident this isn't an issue.

Any responses are welcome, I'm probably over thinking this.


texts: Postgresql trigger to update a column in a table when another table gets inserted or updated

texts:
Basically I have table_a and table_b. table_b is made with features from table_a and in common they share section_id column and status.

table_a's section_id is primary key so it's unique, but table_b can have multiple section_ids, but they all share same status

Users insert and update table_b and I want to capture the status changes back in table_a

CREATE TRIGGER table_b_aiu
AFTER INSERT OR UPDATE
ON table_b
FOR EACH ROW
WHEN (((new.status = 100) OR (new.status = 200)))
EXECUTE PROCEDURE table_b_aiu();

CREATE OR REPLACE FUNCTION table_b_aiu()
RETURNS trigger AS
$BODY$
BEGIN
UPDATE table_a a
SET status = 100
FROM table_b b
WHERE (b.status = 100 or b.status = 200)
AND a.section_id = b.section_id;
    RETURN new;
END;
$BODY$
 LANGUAGE plpgsql VOLATILE
 COST 100;

The problem with this, is that instead of updating just the newly updated or inserted row, it updates the ENTIRE dataset, which is not intended. How to update only new rows? I've tried putting arguments on the triger but it doesn't accept new.section_id


texts: Change IDENTITY_INSERT to ON in SQL server

texts:
How can I insert an ID into an identity column in SQL Server? I'll make a special item into my table and I will quickly detect that item by the ID. So because all other ID's are bigger or equal to one, I will give that special item the ID zero.

The problem is now, when I've created that table I've place the identity of the column ID on true. If I insert now the special item with code below, I've got next error:

INSERT INTO MyTable(ID, Name, Description)
VALUES (0, 'Special title', 'special item');

Error:

  Cannot insert explicit value for identity column in table MyTable when IDENTITY_INSERT is set to OFF.

How can I change this table, column or property so I can create a record with ID zero?


texts: I am using SQL Server 2012 Audit and when I look through the captured events I can see that event_time does not show the correct time. Actually it shows 5 hours earlier than time on the server. Do you have any ideas how can I fix that ?


texts: All queries are so slow on mysql NDB cluster

texts:
Here is my situation:

I got over 10G of web logs (around 30 million rows) and I mainly search it with various conditions.

e.g.:

select * from tbl
where ip = '123.123.12.3'
  and agent = 'mozillar'
  and body like '%script%'

As data is getting bigger, a simple query takes minutes, so I am trying to create some distributed database with MySQL cluster.

I have built 1 management server, 1 SQL (api) server and 4 data nodes on a Google cloud VM. They have 2 CPUs and 8 GB per each instance.

PROBLEM:

When I try to restore data from an .sql file, each insert statement takes almost 2~3 seconds!

3 sec * 30m row = FOREVER!

The select query also disappointed me, it takes almost 100 times longer than normal InnoDB.

Am I doing something wrong?

Is there any better solution for my purpose?

config.ini

[ndbd default]
NoOfReplicas=2
DataMemory=5G
IndexMemory=128M
MaxNoOfConcurrentOperations=1000000
MaxNoOfLocalOperations=1100000
MaxNoOfConcurrentTransactions=327680
NoOfFragmentLogFiles=256
FragmentLogFileSize=32M
SharedGlobalMemory=512M
DiskPageBufferMemory=512M
DiskIoThreadPool=8

[ndb_mgmd]
NodeId=1
hostname=10.142.0.7
datadir=/var/lib/mysql-cluster/mgmt

[ndbd]
NodeId=11
hostname=10.142.0.8
datadir=/var/lib/mysql-cluster/data1
[ndbd]
NodeId=12
hostname=10.142.0.9
datadir=/var/lib/mysql-cluster/data2
[ndbd]
NodeId=13
hostname=10.142.0.10
datadir=/var/lib/mysql-cluster/data3
[ndbd]
NodeId=14
hostname=10.142.0.11
datadir=/var/lib/mysql-cluster/data4

[mysqld]
NodeId=100

All the nodes are connected to management server as expected

-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     4 node(s)
id=11   @10.142.0.8  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0, *)
id=12   @10.142.0.9  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 0)
id=13   @10.142.0.10  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 1)
id=14   @10.142.0.11  (mysql-5.6.29 ndb-7.4.11, Nodegroup: 1)
[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.142.0.7  (mysql-5.6.29 ndb-7.4.11)
[mysqld(API)]   1 node(s)
id=100  @10.142.0.2  (mysql-5.6.29 ndb-7.4.11)

Additional info:

    Create Table: CREATE TABLE `tbl` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `rule_name` varchar(50) DEFAULT NULL,
  `ip` varchar(20) DEFAULT NULL,
  `detection_time` datetime NOT NULL,
  `uri` text,
  `site` varchar(500) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `body` text CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `detection_string` text,
  `detection_type` int(11) NOT NULL,
  `action` int(11) NOT NULL,
  `category` int(10) NOT NULL,
  PRIMARY KEY (`no`),
  KEY `no` (`no`),
  KEY `ip` (`ip`)
) ENGINE=InnoDB AUTO_INCREMENT=21682043 DEFAULT CHARSET=utf8
1 row in set (0.03 sec)


texts: Structure of database with billions of rows of data

texts:
I have started working on a system setup, that includes a database, that collects around 6-7 billions of rows of data each year.
I'm abit confused over the design of this database, and I'm not able to discuss it with the designer of this database.

Its a factory, that has alot of high interval sensors monitoring all kinds of things.

Each year they create a new database, to recieve the data from that year.
So they have db names like this:

  FactorySensors2017

  FactorySensors2018

In addition to yearly databases, they also create daily tables, for the sensors data, that looks like this:

  SensorData01012018

  SensorData02012018

  SensorData03012018

They have a reporting tool, that fetches data for a timespan that they define, and for the sensors/sensortypes that they want, that then selects that data. So I'm guessing that this kind of structure with the daily tables, will make the querys faster, since it has to scan less rows.

It is however in big contrast to what I know about database normalization, and therefore I initially considered it bad practice. But because of the .bak files being smaller and more manageable with yearly databases, and the daily tables faster to fetch data from, I guess that there is some reasoning behind the choices.

Is this kind of design/structure a proper way of dealing with this amount of data? When loading times and .bak backupfiles are taken into consideration. Or should it have been dealt with in a different way?

Update:

The sensor data tables, are only being used for the initial insert of data, and then later on data will be fetched from them, with select statements.
There may be some rare cases of updating sensor data, if a sensor was malfunctioning, but the performance of these updates doesn't matter, since they will be rare.

The sensordata tables looks like this:

CREATE TABLE [dbo].[SensorData01012018](
    [RegisterId] [uniqueidentifier] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [AvgValue] [float] NOT NULL,
    [NoAggregations] [int] NOT NULL,
    [MinValue] [float] NOT NULL,
    [MinValueTime] [datetime] NOT NULL,
    [MaxValue] [float] NOT NULL,
    [MaxValueTime] [datetime] NOT NULL,
    [Value] [float] NULL,
    [ValueTime] [datetime] NULL
)

And an example of a select query, could look like this:

SELECT AvgValue, Value, ValueTime
FROM SensorData01012018
WHERE RegisterId = 367a5c7f-3c88-44f9-b3d8-7576556bc199
    AND StartTime >= 2018-01-01 3:30:00.000
    AND EndTime < 2018-01-01 18:30:00.000
ORDER BY StartTime ASC


texts: Best fill factor for GUID clustering key

texts:
We have a database that uses GUIDs for most of their columns in a data (non sequential). Does anyone have any best recommendations for fill factors on a GUID database in an hgih insert/update database?

Regarding maintenance windows, we have downtime every night, and are usually closed on weekends.


texts: How can I change the sequence_name parameter after renaming a sequence (PostgreSQL)

texts:
I have renamed a sequence with the following command:

ALTER SEQUENCE TableIdSeq RENAME TO NewTableIdSeq;

however, when I issue the \d NewTableIdSeq command, I get the following output:

       Sequence "dev.newtableidseq"
    Column     |  Type   |        Value
---------------+---------+---------------------
 sequence_name | name    | tableidseq            <--------------- HASN'T CHANGED!!
 last_value    | bigint  | 3
 start_value   | bigint  | 1
 increment_by  | bigint  | 1
 max_value     | bigint  | 9223372036854775807
 min_value     | bigint  | 1
 cache_value   | bigint  | 1
 log_cnt       | bigint  | 30
 is_cycled     | boolean | f
 is_called     | boolean | t

As you can see, the sequence_name property of the sequence is still set to the old name. How can I change the name here as well?


texts: sql server instance using maximum cpu usage

texts:
I have a SQL server 2005 instance installed on the server machine with two vm

Server Specification are
*Processor:* Xeon
Ram : 6GB
OS: window server on vm ware

It is running asp.net application deployed on the IIS and sql server database instance . This application is used by around 500 users daily. after every 22 days the processors CPU Usage Reaches to 100% to 95% . and the most used process (in task manager) is the instance of the sql server that is taking the maximum(all) the processing of the processor. Ram is only used up to 3 - 4 GB

I executed the below task but of no use

Recreated all the table indexes but of no use
Restarted the sql server database engine instance but of no use

But when the server(machine) was restarted. The sql server was working fine and CPU Usage was back to normal.

WE DO NOT WANT TO RESTART THE SERVER MACHINE. is this possible to performance tune the sql server so that after 22 days it does not choke up the server processing or what can i do to avoid it.


texts: Resource Governor not limiting CPU / RAM

texts:
I have an application that is consuming a lot of resources, actually this application drives CPU to. 99% which makes the server unusable (I think there is nothing I can do about this as is a third-party application).

So I decided to create a Resource Pool specific to this Application.

After creating the Resource Pool I limited this application to 2 cores and 25% CPU / RAM, it worked perfectly, CPU of the server was at 25-26% all the time when before it was at 99%...

Then I decided to remove the MAXDOP for the Resource Group and then the application went to 99% again... I have seen that the application query opened 8 parallel processes so I assumed that puting the resouce pool at MAX 10% would do the job (10*8 = 80% CPU) but if I don't limit the Cores then I'm getting again a 99% CPU consumption...

What am I doing wrong?...


texts: For creating sum columns in a table that requires immediate reflection, is it better to have persisted columns or triggers?

texts:
Is it better to have persisted columns or triggers if you would like to have auto-updating sum/avg/etc... columns?

Like if I have this table:

    CREATE TABLE [dbo].[tbl_shipping_boxes]
    (
        [boxid]     [int] IDENTITY(1,1) NOT NULL,
        [costperten][numeric](10,2),
        [weight]    [int] NOT NULL,
        [length]    [numeric](10,2) NOT NULL,
        [width]     [numeric](10,2) NOT NULL,
        [height]    [numeric](10,2) NOT NULL,
        [volume]    [numeric](10,2),
        [dimwgt]    [numeric](10,2),
        [costperone][numeric](10,2)
    )

I would like to calculate the volume, dim weight, and cost per one. Which would offer me better performance? More reads on this table than writes.

Is one preferred over the other for constant reads, and low writes? What about if I add a quantity field that I would decrement or increment to? Does that change the model I should have?

I am not having luck with persisted columns not recompiling on each query even when marked persisted and using deterministic values and functions. I used database tuning wizard, and it is faster, but I am concerned about the space this will eventually take up.

Thanks


texts: SQL Server Management Studio does not load

texts:
I am working on SQL Server 2012 for the past 15 days it was working perfectly but now I am having an issue:

  Cannot find one or more components. Please reinstall the application


texts: Threadsafe/non-clobbering foreach file container for SSIS?

texts:
I'm attempting to modify a SSIS package in Visual Studio's "File Reader Integration Services" to process multiple ForEach File Enumerators at the same time (each for different file sub-types), but it seems that setting e.g. @User::ProcessFile in one sets it so that in the "global" (package-wide) scope, it clobbers the value previously set in another simultaneously executing ForEach File Enumerator. The result of this is that my Connection Manager within the enumerator attempts to process a type of file that's inappropriate given that enumerator.

I see that there's a way to set the variable scope, but it doesn't work as desired. Plus it's a PITA.

Open the Variables tab
Click on the "Grid Options" icon (far right)
In the Filter section click "Show variables of all scopes"
Make sure "Scope", "Data type" and "Namespace" (at the very least) are clicked in the Columns section.
Click "OK"
Repeat the following for each ForEach File Enumerator (we'll call each "ffe" when it's the one selected)

"Add Variable"(upper-left icon) and name to "ProcessFile"
Select the "Data Type" dropdown and select "String"
Set the "Namespace" column to "lcl"
"Move Variable" (second-from-left icon)
Expand "Executables"
Select "ffe" from the list
Click "OK"

Now for each ffe container

Open the Foreach Loop Editor
Click on "Variable Mappings"
Set the value of Index 0 to "lcl::ProcessFile"
Click "OK"

There doesn't appear to be a Copy Variable option (either via icon, mouse/context menu or keyboard shortcut), so there doesn't seem to be an easy way to have the variable represented in the global space as well as in each sub-container scoped to only itself.

However, as the "Package Explorer tab shows "Connection Managers" as a separate group from the "Executables", variables made local to the scope of the Foreach File Enumerator executable aren't visible to any of the Connection Manager entries.  The error given is The variable "lcl::ProcessFile" was not found in the Variables collection. The variable might not exist in the correct scope, as it's not a GLOBAL variable.

Is there a way to move the connection managers to be children of the executables, or a way of specifying the parent scope for each Connection Manager?

Is there a way to set a variable value for a connection manager without depending on either a constant value or a reference to the global variable space? Can "variable variables" be made to e.g. use a different prefix for each subcontainer?


texts: What is the best way to unpack JSON objects in SQL Server?

texts:
Part 2 of my foray into JSON, I'd like to add a user to multiple groups: insert an JSON array into a table.

Ideally, the JSON would look like this:

    '{
    "Email": "WMogh@starfleet.gov",
    "Prefix":null,
    "FirstName": "Worf",
    "MiddleInitial": "",
    "LastName": "Mogh",
    "Suffix": "Son Of",
    "Title" :"Commander",
    "Groups": [{"0", "1", "5"}]
    }'

Currently, I can do it with JSON like this:

    '{
    "Email": "WMogh@starfleet.gov",
    "Prefix":null,
    "FirstName": "Worf",
    "MiddleInitial": "",
    "LastName": "Mogh",
    "Suffix": "Son Of",
    "Title" :"Commander",
    "Groups": "1,2,3,4"
    }'

then "unpack" it with the following ditty:

    declare @groups varchar(1000)

    select @groups = Groups from openjson(@json)
                    WITH
            (

            Groups nvarchar(100)        '$.Groups'
            )

            print @groups

    select value from string_split(@groups, ',')

which returns a nice little table like so:

Value
1
2
3
4

Problem This is bad JSON and the Web developer will make fun of me.

Question How do you propely unpack a JSON array in SQL Server?


texts: osm2pgsql: select relation member by role

texts:
I have a standard postgresql OpenStreetMap database that was filled with data via osm2pgsql. There are different tables for different types (points, lines, polygons, relations) that need to get combined for my specific query:
I want to select all relations with the tags boundary=administrative and admin_level=6. The results need to be searched for a member with the role admin_centre.

Here are my steps by now:

Select member-role list:

SELECT unnest(members)
FROM planet_osm_rels
WHERE ARRAY['admin_level','6']<@tags AND ARRAY['boundary','administrative']<@tags;

Result for one example:

    unnest
--------------
 n240045827
 admin_centre
 w185481610
 outer
 w191987461
 outer
 w166286278
 outer
 w166290573
 outer
 w65481875
 outer
 w166278211
 outer
 w166278224
 outer
 w166278225
 outer
 w166278227
 outer
 w185148605
 outer
 [...]

The n stands for nodes, the w for ways, followed by the unique id that can be found within the respective table (planet_osm_nodes or planet_osm_line). In this example, the admin_centre is the only node, so we can find members that are nodes with LIKE:

SELECT name
FROM planet_osm_point
JOIN (
    SELECT ltrim(member, 'n')::bigint AS osm_id
    FROM (
        SELECT unnest(members) AS member
        FROM planet_osm_rels
        WHERE ARRAY['boundary','administrative']<@tags AND ARRAY['admin_level','6']<@tags) u
    WHERE member LIKE 'n%') x
USING(osm_id);

Here my question: It is possible that there are several nodes, but with different roles. How could I manage to get only the node that has the role admin_centre? Here it would be n240045827.

The result from above should be sufficient for my question without going too deep into detail about the database structure. The solution would be to take every nth and nth-1 element as a 2D array in order to have the mapping between member and role.

Here is the named relation with its admin_center just for clarification:
https://www.openstreetmap.org/relation/62383


texts: How do I preserve leading zeros for varchar field

texts:
When saving a number with leading zeros into a string variable or column, leading zeros are truncated.
When I run this query:
declare @varchar as nvarchar(50)
set @varchar = 011
print @varchar

The result I get is 11.
Is there any way to get 011?


texts: What situation RDBMS need to vm or docker virtualization?

texts:
Usually  RDBMS just have only one primary server for writing, so what's the point of virtualization?
It cannot make multi-DB, and one additional layer of encapsulation does not mean that the speed will be slowed.
But I know there's a reason for exist, what are the scenarios that are particularly suitable for database virtualization?


texts: ORDER BY that doesn't contain repeating values

texts:
Relevant SQL Fiddle (postgres 9.2)

How do I state a SELECT .. ORDER BY .. that avoids having repeated values for a column?

That is, for rows of the column "rank" (1,3,3,1) how do I get a result so that rows of that column are ordered as (1,3,1,3), garuanteeing that a row with column value 1 is never follow by a row with column value 1, and similar for other rows values.


texts:
I just maked an huge mistake.I overwrite database with my old database backup which was 3 months older.
Is there any way that i can recover my orignal database with there record ??
please help me


texts: Need to Bring Back most Recent Departure Date from Sales table for Customers in Recipient table

texts:
Looking at 3 Tables - Recipient, HouseholdScores, Sales.

I need to bring back the most Recent tsDepart (departure date) from sales for the iCustomerId in the Recipient Table. There is no Depart date information in the Recipient Table so i can only join the two tables on iCustomerID.

What I have Tried (Using - 'Microsoft SQL Server Management studio 17':

SELECT DISTINCT

iRecipientId as RecipientID,NmsRecipient.iCustomerId as Cust_Num,sFirstName as FirstName,sLastName as LastName,sEmail as Email,
iBlacklistPostalMail as NoMail,iBlacklistEmail as NoEmail,iBlacklistMailGCCL as DontMailGCCL,iBlacklistGCCL as DontEmailGCCL,
iBlacklistMailOAT as DontMailOAT,iBlacklistOAT as DontEmailOAT,sLastBrand as LBT,sFrequencyDetail as Frequency,sRecency as Recency,GccTbl_HouseholdScores.iNumTripsBkd as Trips,
GccTbl_Sales.tsDepart as Departure_Date

FROM ((neolane.NmsRecipient

Inner Join [neolane].[GccTbl_HouseholdScores] ON NmsRecipient.iRecipientId = GccTbl_HouseholdScores.iHouseHoldId)
Inner Join [neolane].[GccTbl_Sales] ON NmsRecipient.iCustomerId = GccTbl_Sales.iCustomerId)

WHERE iIrateFlag= 1
    AND iOperationalFlag= 1
    AND iEmployeeFlag= 0
    AND iDeceased= 0

Order By iblacklistPostalMail,iBlacklistEmail,iBlacklistMailGCCL,iBlacklistGCCL,iBlacklistMailOAT,iBlacklistOAT;

Results are bringing back Duplicate iCustomerID where i only want 1 instance for each with the Most Recent Departure date


texts:
my question is really simple, and does not need any particular documentation.
When I run expdp command like this using Oracle Database 11g Release 11.2.0.4.0 - 64bit:

expdp system/******* directory=DATA_PUMP_DIR dumpfile=SYSAUX.dmp logfile=sysaux.log TABLESPACES=SYSAUX

I see that no table about SYSTEM schema is present.
I know for sure that tables in SYSTEM schema belong to SYSAUX tablespace, like DBA_SEGMENTS and DBA_EXTENTS tell me, but I don't see any log row like this:

.. exported "SYSTEM"."TABLENAME"                       120.2 GB 8396874 rows

No error message with expdp, just missing tables.....
Could anyone please give me suggestions about this?


texts: HeidiSQL solution for SQL Server error 18488?

texts:
I'm connecting to a remote database using HeidiSQL client but looks like the administrator has activated the enforce password change policy (Error 18488).

Is there a workaround to solve this with HeidiSQL? Or I need to download and install SQL Server Management Studio?


texts:
I am trying to insert a new record into a table, but only if another record of the same table meets some conditions. Sadly it does not work I alwys get the following error: ERROR:  duplicate key value violates unique constraint "stackacos_pkey" Even though the id is not yet present in the db. Any idea how to avoid this issue ?

INSERT INTO stackacos (id, created_at, updated_at, owner_id, stack_id, admin_full, admin_create, admin_read, admin_update, admin_delete, "create", read, update, delete)
SELECT 'notyetindbisforsureunique', '2018-09-18 14:34:57', '2018-09-18 14:34:57', 'test_user_222', 'KZROLZYQbSeHbMtZNFCpMlaKJyfaKFMdcbwGklCiUQBuIiZSptBSlVcuUGofnjMM', 'false', 'false', 'false', 'false', 'false', 'false', 'true', 'false', 'false'
FROM stackacos
WHERE stackacos.owner_id = 'test_user_1' AND
    stackacos.admin_full = true OR (
        stackacos.admin_create >= 'false' AND stackacos.admin_create >= 'false' AND
        stackacos.admin_read >= 'false' AND stackacos.admin_read >= 'true' AND
        stackacos.admin_update >= 'false' AND stackacos.admin_update >= 'false' AND
        stackacos.admin_delete >= 'false' AND stackacos.admin_delete >= 'false'
        ) RETURNING *


texts: Count down from 0 to 5 seconds and repeat

texts: What query can I use to trigger a count down from 0 to 5 seconds whenever executed? When I execute a query the output has to be 0 second to 5 second and once 5 seconds are done again it should go back to 0. When I execute again it should start from 0 to 5 seconds. Is it possible to achieve something like this using sql query?


texts: I found that auditing can be done on oracle schema objects like procedures and views. Is there any possibility to do the auditing on Types?


texts: SET CONTEXT_INFO for tracking specific SQL requests

texts:
I've got a problem where I'm seeing some very strange lock waits, with the process at the top of the lock wait tree in the 'sleeping' 'AWAITING COMMAND' state.  I can see the machine name and application, and I'm pretty sure it's a client-side transaction that's blocked on the client-side after locking some records that's causing the problem, but I haven't been able to find through code examination what it could be doing.  I've already ruled out GC, because the same application is responding to other requests (we have a request to dump the status of all pending requests, each with a unique ID, and that returned just fine while the lock remained).  By the time we are notified of this issue, request are backed up to the tune of several thousand per SQL client machine, so sifting through all these requests is very painful without writing custom code just for that.

I'd like to add some info to the SQL connection information to track which request is the one at the top, not just which machine and application (and I think this may prove useful in diagnosing future issues as well).  I could append something to the application name in the connection string, but I think that would likely do bad things to the connection pooling.

I read this post: Can I Set The Value of App_Name() AFTER Login?
and this one: http://www.sqlservercentral.com/articles/T-SQL/2765/ which seem to indicate that I might be able to use SET CONTEXT_INFO to get data into sys.dm_exec_requests.context_info and then I would be able to use that data to identify the exact request the bad transaction is coming from.

We're not using SET CONTEXT_INFO for anything else in the application (and the SQL servers are dedicated to this application).

Is there some reason I would not want to do request identification this way, or some better way to track requests through to the SQL layer?


texts:
I have a very challenging scenario at hand and would like to hear suggestions on the solution I am considering implementing.

I have several databases that have similar (non-identical) structures and I am centralizing on one base, but these databases have their own ID structures (PKs will be the same), but by bringing it to a centralized base I will obviously have conflicts on account of 2 IDs with the same value (example company 1 with customer id 100 and company 2 with customer id 100).

First approach: renumber
I can renumber (it was my first approach) but as I start the next company, I realize that the ids are already generating conflict again (as I centralize and put into production, so the last id I generated for example would be 5000 already no longer worth because the production base is already at 7000).
Using this approach is the simplest, I simply pass a new number to auto increment and the process continues with a simple
ALTER TABLE tablename AUTO_INCREMENT = value;

Second approach: key by company
I thought of creating my own key, something like company id * 1000000 + original id, example company 1 id 100 becomes 1000100 but my auto-increment field should be extinguished and instead a trigger would be entered, but this trigger would be complex because it wouldn't be just put a sequence table, and at the same time i should know the company (something like select max (table) as x where company = NEW.id_company).
This approach would be by far the safest as ids come to have their relativity with the company and because of this I never generate conflicts but I will have to keep generating the id for the rest of the process life.
My question is if this will be performative

Tables don't have a lot of data, in the worst case scenario I'll be talking about 50,000 row tables, but by centralizing all the small parts on a single basis my performance may be compromised.


texts: MYSQL Calculate ranking of row that is not yet inserted

texts:
I've got a table similar to this data:

|     id    | username | value |
|-----------|----------|-------|
| 1         | bob      | 46    |
| 483       | alice    | 90    |
| 176       | sue      | 3001  |
| 82        | harry    | 0     |
| 10493     | bill     | 327   |

I have this query that returns me the ranking of a user based on their id

SELECT
    username,
    value,
    rank from
    (
        SELECT
        tp.username,
        tp.value,
        tp.id,
        @curRank := @curRank + 1 AS rank
        FROM table tp,
        (SELECT @curRank := 0) r
        ORDER BY tp.value DESC
    )
as temp
WHERE id = 483;

So for the above query, I would get a ranking returned of 4 for the id 483.

Let's say I want to insert the following row into the table:

|   id   | username | value |
|---------------------------|
|    2   |  sally   | 2000  |

Is there any way to know what rank this row will have after it is inserted, without actually inserting it?

I.e. sally would have a rank of 2 from the above query if inserted.

The reason I am curious if this is possible is that I'm attempting to insert the row into the database and only have this one transaction, rather than having to insert the row, then re-run the rank query.

Thanks!


texts: "AT TIME ZONE" with zone name PostgreSQL bug?

texts:
I was answering this stackoverflow question and found strange result:

 select * from  pg_timezone_names where name = 'Europe/Berlin' ;
     name      | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
 Europe/Berlin | CET    | 01:00:00   | f

and next query

select id,
  timestampwithtimezone,
  timestampwithtimezone at time zone 'Europe/Berlin' as berlin,
  timestampwithtimezone at time zone 'CET' as cet
from data ;
 id  | timestampwithtimezone  |       berlin        |         cet
 -----+------------------------+---------------------+---------------------
 205 | 2012-10-28 01:30:00+02 | 2012-10-28 01:30:00 | 2012-10-28 00:30:00
 204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00
 203 | 2012-10-28 02:30:00+02 | 2012-10-28 02:30:00 | 2012-10-28 01:30:00
 202 | 2012-10-28 02:59:59+02 | 2012-10-28 02:59:59 | 2012-10-28 01:59:59
 106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00

I'm using PostgreSQL 9.1.2 and ubuntu 12.04.
Just checked that on 8.2.11 result is the same.

According to documentation it doesn't matter if I use name or abbreviation.

Is this a bug?
Am I doing something wrong?
Can someone explain this result?

EDIT
For the comment that CET is not Europe/Berlin.

I'm just selecting values from pg_timezone_names.

select * from  pg_timezone_names  where abbrev ='CEST';
 name | abbrev | utc_offset | is_dst
------+--------+------------+--------

and

select * from  pg_timezone_names  where abbrev ='CET';
        name         | abbrev | utc_offset | is_dst
---------------------+--------+------------+--------
 Africa/Tunis        | CET    | 01:00:00   | f
 Africa/Algiers      | CET    | 01:00:00   | f
 Africa/Ceuta        | CET    | 01:00:00   | f
 CET                 | CET    | 01:00:00   | f
 Atlantic/Jan_Mayen  | CET    | 01:00:00   | f
 Arctic/Longyearbyen | CET    | 01:00:00   | f
 Poland              | CET    | 01:00:00   | f
 .....

During winter Europe/Berlin is +01. During summer it is +02.

EDIT2
In 2012-10-28 timezone has change from summer time to winter time at 2:00.
This two records have the same value in Europe/Berlin:

204 | 2012-10-28 02:00:00+02 | 2012-10-28 02:00:00 | 2012-10-28 01:00:00
106 | 2012-10-28 02:00:00+01 | 2012-10-28 02:00:00 | 2012-10-28 02:00:00

This suggest that if I use one of abbreviations (CET or CEST) for big data range (summer time and winter time) result will be wrong for some of records. Will be good if I use 'Europe/Berlin'.

I changed the system time to '2012-01-17' and pg_timezone_names has changed also.

select * from  pg_timezone_names  where name ='Europe/Berlin';
     name      | abbrev | utc_offset | is_dst
---------------+--------+------------+--------
 Europe/Berlin | CEST   | 02:00:00   | t


texts: Query For Where Table 1 (A,B,C) for each distinct A, has rows matching all rows in Table 2 (B,C)

texts:
I'm trying to optimize a query here:

Given Table 1 (A,B,C) and Table 2 (B,C)

Given Table 1 contains many unique tuples (B,C) for each A

Select (1.A, 1.B, 1.C)

Where for a given set of rows where 1.A is distinct, the set of rows must collectively satisfy all of the rows in 2 where 1.B = 2.B and 1.C = 2.C

Example

TABLE 1

| A | B  | C  |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |
| 3 | B1 | C1 |

TABLE 2

| B  | C  |
|----|----|
| B1 | C1 |
| B2 | C2 |

RESULT

| A | B  | C  |
|---|----|----|
| 1 | B1 | C1 |
| 1 | B2 | C2 |
| 2 | B1 | C1 |
| 2 | B2 | C2 |
| 2 | B3 | C3 |

(Only 3 was excluded because it failed to match both rows of table 2)

Current implementation is dynamic SQL

select Result.A
from
(
SELECT vtab1.A
FROM table1 vtab1
where vtab1.[B]= 'B1' and vtab1.[C] = 'C1'
union all
SELECT b.VaultObjectId
FROM table1 vtab2
where vtab2.[B]= 'B2' and vtab2.[C] = 'C2'
) AS Result
Group By A
HAVING
COUNT(A) = 2

However this is all generated dynamically, and doesn't remerge my A with the matching B (which I suppose is easy enough)

Related: Is there any way to "name" a query to be used later (not computed, but deferred?)


texts:
I have two MySQL servers, one is the master and other one is slave, that's all good.

The problem I have is that the slave is also set up as a master. I would like to disable or remove the master role from the slave server.I have commented everything out in my.ini that relates to master.

The way we set this up was a copy of a database which was a master and copied it across to the new server. So the master configuration was already there. I set up slave replication and that all works, it talks to the original server.

Set up is like this: Server 1 is a master. Server 2 is the slave. Server 2 replicates with server 1. Server 2 also has Master replication configured. Is it possible to remove or disable the master configuration on server 2?

The server is Windows 2008 R2. The data sits on WAMP server with phpmyadmin.


texts: Dealing with CXPACKET waits - setting cost threshold for parallelism

texts:
As a follow-up to my previous question on perf troubleshooting a Sharepoint site, I was wondering if I could do something about the CXPACKET waits.

I know the knee-jerk solution is to turn off all parallelism by setting MAXDOP to 1 - sounds like a bad idea. But another idea is to increase the cost threshold before parallelism kicks in. The default of 5 for the cost of an execution plan is fairly low.

So I was wondering if there's a query out there already written that would find me the queries with the highest execution plan cost (I know you can find those with the highest duration of execution and so on - but is the execution plan cost retrievable somewhere, too?) and that would also tell me if such a query has been executed in parallel.

Does anyone have such a script at hand, or can point me in the direction of the relevant DMV, DMF or other system catalog views to find this out?


texts: How to efficiently design Firebase database for Home service Booking application?

texts:
I'm planning to build my own app. It's a home service App to link users with other providers.
I'm recently used a NoSQL database, Firebase, and I am stuck with the design of the database for my app. The question is how to structure the databases in this way with NoSQL, like a JSON tree?

Like

{
"uID":"01",
"username":"John",
"Booking":[{
       "Bid":"01",
       "BDetails":"Cleaner Rooms",
       "Baddress":"America",
        "providerID":"023",
        "timestamp":"1-Mar-2019"
       ....
       ....
      }]
}

Is that right?


texts: Performance improvement by placing tables on a virtual RAM disk

texts:
I am trying to improve performance of queries by placing the tables in a virtual RAM DISK. I am using in Oracle 11.2 Standard edition with 4G of SGA and 2G of PGA.

I created a Virtual RAM DISK of size 140GB using imdisk. Then I moved only one table, which has 350 million rows and 32gb of size, to this RAM DISK.

Now I am accessing this table with simple straight forward query.

SELECT PRICE, QUANTITY
FROM TEST_TABLE;

It is taking around 5-6 minutes to read the entire table.

There is no difference in performance whether the table is in hard disk (SSD) or RAM disk. When RAM read speed is many times faster than hard disk, why is this not getting reflected in the query performance?

Is something is missed here?

Any help would be appreciated.

Explain Plan
Plan hash value: 2982911821

-------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   348M|    11G|  1311K  (2)| 04:22:14 |
|   1 |  TABLE ACCESS FULL| TEST_TABLE |   348M|    11G|  1311K  (2)| 04:22:14 |
--------------------------------------------------------------------------------

This is the output of stats:

| Id | Operation| Name| Starts | E-Rows | A-Rows |A-Time|Buffers|
|0   | SELECT STATEMENT| |1 | | 13 |00:00:00.04 | 4 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY_CURSOR |      1 |    136 |     13 |00:00:00.04 |  4 |

I am trying here to see if RAM disk can really improve the performance, as this is full table scan and it has fetch so much of rows, I thought this would be appropriate use case to effects of RAM disk with Oracle.

If moving tables in RAM is going to work then, We are planning to keep our entire database in RAM (our database size is 500GB), so that we can avoid the I/O bottleneck or I/O bound and we also make sure that we have the best hardware in place that cares of I/O bound, there is nothing more can be done for the I/O. Right now I am focusing only on I/O cost and thought moving tables to RAM is going to solve those problems.

More over we already have a mechanism in place to handle the volatility of RAM, so that's not a problem for me right now. Also, the entire database is in SSD (still we are not satisfied with the performance). The data must be fetched far better from RAM than SSD. Only question that I am not able to get answer is why that is not reflected here (even though the sample query is a I/O bound). This is an exercise that I am doing to take some vital decision on using RAM and fine tuning other queries.


texts:
I am creating a relational database - a library management system. I want to show an example where I normalize the data from 2NF to 3NF. My example of 2NF looks like

The definition of third normal form would be:
'all non-key attributes are not dependent on any other non-key attributes.'

In this instance, the non-key attribute ISBN determines the value of the primary key SKU (stock keeping unit). Does this define a transitive dependency, when SKU is the primary key?

And further, would it be correct to divide the uploaded table into a subtable (Book) with the attributes (isbn, title, author_first_name, author_middle_name, author_last_name) and the subtable (Copy) with the attributes (sku, isbn)?