texts: Are columns which are not indexes, sorted on disk together with index?

texts:
Are columns which are not indexes, sorted on disk together with index, in MySQL, in MyISAM and InnoDB?

An incorrect thought that I started to write:

  I think that probably not, since they are not indexed; if they were sorted that would mean that they are indexes.

This is not correct because every index column is sorted by its own content's order, but I am asking about being ordered of every row (or of only of some columns) with its corresponding index.

To explain, I say: this would be useful to make selecting ranges of rows, which stand side-by-side, together, by their indexes, faster. For example, if I want to select * where id >1000 and id<2000 (there may be mistakes in the MySQL syntax, I do not know it well), then, the id column itself can be read from disk quickly because probably its cells from 1000 to 2000 stay together on physical disk. But other column content corresponding to id 1000 to 2000 may be written on different places on physical disk. If they are also sorted, they would be read faster. I think, maybe MySQL automatically sorts that columns on physical disk, for performance of such operations.

Are they sorted in other types of databases (PostgreSQL, etc.)?

December 27: I see from the 2 answers, that in the case when there is clustered index/primary key, the simple rows themselves are not sorted on physical disk (as I thought it might/may be), and even the clustered index is not sorted, if it is b-tree, i have read about b-tree and see that its nodes, as i understand, stay at random places on disk.


texts: How can I select data and the total to cursors?

texts:
I have a table like this:

 ID  | Name
------------
  1  | AA
  2  | AA
  3  | AA
  4  | BB

I want to select all rows where Name like 'AA' in a cursor and get the count from this result in another cursor. I also need to page the results.

Expected result (Skip = 0; Take = 2)

-- p_result

 ID  | Name
------------
  1  | AA
  2  | AA

-- p_count
   3

How can I do this?


texts: Application service down due to SQL Server error

texts:
I'm using SQL Server 2012 where in I found an error in my SQL Server error logs which says:

  Logon,Unknown,The login packet used to open the connection is
  structurally invalid; the connection has been closed. Please contact
  the vendor of the client library.

Because of this, on my application services went down.

What is the error pointing to?


texts: SQL Server 2008 R2 Query For Failed Email Notifications

texts: Is there a SQL query I can run to see if there has been any failed attempts to send database mail in the last X Minutes?


texts: Restore the data from Snapshot Backup when the data spans over multiple directories?

texts:
Problem : I have one master and various slave on EC2, all running with the innodb_file_per_table as OFF.

We have done some testing work on one of the slave say server A.and it takes a huge amount of disk space due to out test work on that slave. (Approx 300GB increased due to our test work) as dropping the database would not help us due to innodb_file_per_table OFF.

ibdata is on LVM.
datadir is on /EBSroot
innodb logs are on some other location

The total ibdata size is approx 600GB due to this test work it increased to 900GB.

Now i want to reclaim the space.I am thinking as

delete the ibdata from server A.and take the snapshot backup from the one of the slave and restore on this slave A.

but i am not getting the correct steps.For How to take the backup from one of the slave and restore on this slave A.

i have studied alot on how to take snapshot backup of MySQL
i found some Caveats as : Handling data spread on multiple volumes (DB logs on separate LV or DBs spread across multiple LVs).

So how can do this without any issues.because my ibdata/logfiles/datadir are all on the different locations.I have to also  restart the replication as well.

I need the best possible way with minimum downtime.

please help..


texts: Security risk of Enabling MS DTC Distributed transaction?

texts:
I'm doing data exchange and Insert update operations over the linked server between two database servers. To allow distributed transaction, I've changed settings in Windows to enable MS DTC services.

I want to make sure there is not security risk after enabling it for distributed transaction.

https://technet.microsoft.com/en-us/library/cc753620(v=ws.10).aspx

https://serverfault.com/questions/61269/security-risk-of-enabling-msdtc


texts: PostgreSQL 10 - secure remote logical replication (SSH?)

texts:
I am exploring Postgresql for a new project, and the upcoming logical replication feature in version 10 interests me.

I would like to use it to have a remote server automatically hold an up-to-date copy of some tables from a different server, across the internet. With proper database design this could make entire webservices unnecessary.

However, the feature requires the use of a plain connection string in the CREATE SUBSCRIPTION command. This means I cannot simply point it to a remote server, not without exposing it to the internet.

First, what secure options do I have available here?

Creating a VPN between the two servers. Safest option, most cumbersome.
Establish a SSH tunnel between the two servers. Less cumbersome, still safe as long as the SSH server is properly configured. Once the SSH tunnel is up the connection becomes just a normal SQL connection, so it should work with replication, right?
Postgresql has native SSL support. I'm not sure if the replication subscriber would automatically use it when connecting to the publisher. But more importantly, while this would protect the data in transfer, it would still require me to expose the server to the internet, use something like fail2ban to protect against intruders, and I'm sure I've missed other potential threats, so all in all it would make me uneasy.
Any other option I may have overlooked?

Second, are there any other reasons why using logical replication in such a fashion would be a bad idea?


texts: Cascading PostgreSQL triggers between three tables

texts:
I have a ‘test’ PostgreSQL 12 schema, defined with initial tables:
begin;
drop schema if exists test cascade;
create schema test;

set search_path = test,public;
create table jobs (
  job_id serial primary key,
  job_type_lu text);

create table phases (
  phase_id serial primary key,
  job_id_fk int references jobs(job_id),
  phase_type_lu text);

create table phase_steps (
  phase_step_id serial primary key,
  phase_id_fk int references phases(phase_id),
  step_type_lu text);

commit;

‘jobs’ are composed of one or more ‘phases’, which in turn have multiple ‘phase_steps’
What I want to accomplish is to fire two triggers when a new job is inserted into the ‘jobs’ table. I have these two triggers defined:
create or replace function test.setup_phases () returns trigger as $$
    begin
        insert into test.phase_steps (job_id_fk,phase_type_lu)
            values
                (new.jobs_id,'RESEARCH: JOB_INPUT'),
                (new.jobs_id,'RESEARCH: WATER'),
                (new.jobs_id,'RESEARCH: OTHER'),
                (new.jobs_id,'RESEARCH: REVISION_ATEAM'),
                (new.jobs_id,'RESEARCH: REVISION_CLIENT');
                return new;
end;
$$ language plpgsql;

create trigger setup_test_phases after insert on test.jobs
for each row execute procedure test.setup_phases();

create or replace function test.setup_phase_steps() returns trigger as $$
    begin
        if (new.phase_type_lu = 'RESEARCH')
        then
            insert into test.phase_steps (phase_id_fk,step_type_lu)
                values
                    (new.phase_id,'RESEARCH: JOB_INPUT'),
                    (new.phase_id,'RESEARCH: WATER'),
                    (new.phase_id,'RESEARCH: OTHER'),
                    (new.phase_id,'RESEARCH: REVISION_ATEAM'),
                    (new.phase_id,'RESEARCH: REVISION_CLIENT');
                    return new;
        end if;

        if (new.phase_type_lu = 'SURVEY')
        then
            insert into test.phase_steps (phase_id_fk,step_type_lu)
                values
                    (new.phase_id,'SURVEY: SCHEDULING'),
                    (new.phase_id,'SURVEY: FIELDING'),
                    (new.phase_id,'SURVEY: REVISION_ATEAM'),
                    (new.phase_id,'SURVEY: REVISION_CLIENT');
                    return new;
        end if;
end;
$$ language plpgsql;

create trigger setup_test_phase_steps after insert on test.phases
for each row execute procedure test.setup_phase_steps();

If I input data directly for just the phases (therefore invoking only the ‘setup_test_phase_steps’ trigger, e.g.,
insert into test.phases (phase_type_lu)
    values
        ('RESEARCH'),
        ('SURVEY');

The correct results are obtained:

However, if I enter input directly into the ‘jobs’ table (which then will invoke the ‘setup_test_phases’ AND the ‘setup_test_phase_steps’ triggers):
insert into test.jobs (job_type_lu)
values
('NT-TYP');
I get this error:

I’m obviously misunderstanding cascading triggers, but what?


texts:
There are some tables that I manage which contain a string value (for example, a 4 word "Type" field).

There are only a few DISTINCT strings, however
the application may insert new strings that have never been seen before, but
in the life of the application, there will never be more than 200 strings, which means the system could store this field in a single byte.

Disk space is not an issue, but the less bytes, the faster the lookups.

I have this idea that I doubt any database engines are supporting, but I wanted to check with the experts.

The strings could be mapped to IDs in a separate table, and that way you would not need to repeat these strings on every row of the primary table. Ideally the database would manage this internally for you. The unique strings would be stored as part of the table definition, invisibly, internally, and the system would work great, no need to modify the app, for all string fields that are below 255 distinct possibilities.

Is there anything like this on any database engine? I mostly use MySQL, but I am curious if this has ever been implemented, short of modifying the applications call a custom wrapper function. (like INET_ATON, except that it would transparently do an INSERT to an AUTO_INCREMENTing table to get the ID for the first time)


texts: Some folks here at my current job have made the decision that all data transformations that go into our database must be done via SSIS. This means that any old code such as views, stored procedures, functions, etc. that are used to transform data will no longer be used. The transformations they currently perform will be done via SSIS only. I am trying to come up with a list of potential issues we may face and I don't know as much about SSIS as I do about SQL Server being that I am a DBA here. One of the issues I see is attempting to look at performance issues in SSIS vs sql objects. What are the other things I am missing? We are using Microsoft SQL Server 2016 SP2 and SSIS 2016.


texts: JOIN correctly no matter which end is linked

texts:
Suppose I have the following two tables: SupportTicket and Link. I also have other tables that Link can connect between. I've created these tables and a hypothetical third table that can be linked to on SQL Fiddle.

Let's suppose I want to count the number of support tickets submitted for technical support related reasons, here is my query:

SELECT
  st.Submitting_User,
  COUNT(l.Link_ID) AS [Count]
FROM
  SupportTicket st
  JOIN Link l
    ON st.SupportTicket_ID = l.Entity_ID1
WHERE
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP
GROUP BY
  st.Submitting_User, st.Submitting_Department
ORDER BY
  st.Submitting_User

Note that this query is already set up and runs on SQL Fiddle (where you can also see the table structures, etc.).

Here's the problem: the SupportTicket can be on either end of the link. Currently this only counts those where the SupportTicket is on the first link end (Entity_ID1). This should also count it when the SupportTicket is on the second link end (Entity_ID2). How do I fix this?


texts: How to partially merge a row in Postgres?

texts:
I have 3 tables:

Customers:      Items:    Purchases:
  id              id        id
  first_name      name      customer_id
  last_name       price     item_id

I need to present data in a way that a can see all the purchases of a customer, per item, the amount (sum) of each item and the total price of this amount(again, per item), like this:

Name:   | Last Name:  | Item:     | Price:  | Amount: | Total:
John    | Doe         | Pencil    | 0.5     | 5       | 2.5
John    | Doe         | Paper(A4) | 3.0     | 20      | 60.00
Michael | Smith       | Pencil    | 0.5     | 3       | 1.5
Michael | Smith       | Paper(A4) | 3.0     | 10      | 30.00

I manage to do that with this query:

SELECT
  customers.first_name AS "Name",
  customers.last_name AS "Last Name",
  items.name AS "Item",
  items.price AS "Price",
  COUNT(items.id) AS "Amount",
  SUM(items.price) AS "Total"
FROM
  items
INNER JOIN
  purchases ON items.id = purchases.item_id
INNER JOIN
    customers ON purchases.customer_id = customers.id
GROUP BY
  customers.id, items.id
ORDER BY customers.last_name

Is there a way to do it like you would in Excel, for example, where you can merger those cells that have the first and last name, so the customer name is listed only once? I now that it is not the most relevant "problem", but I have to do this twice a week in a meeting with 5 other people and it would be great to improve this layout.


texts: Return master and master details record(s) in a single row in postgresql

texts:
I am trying to formulate a PostgreSQL query where I wanted to return all the payment table data in an array and customer data as a header in a single row.
I am writing the following query to get data in a single row, but somehow I am getting several rows with the same repetitive data. I want a single data row to be returned for the requested id. I don't want to use the "limit=1" keyword to return a single row.

WITH t as (select row_to_json(rr)::jsonb as record
from (
       select (
                select row_to_json(row)
                from (
                       select
                             c.customer_id,
                             c.first_name,
                             c.last_name,
                             c.email,
                             count(*) total,
                             sum(p2.amount) total_amount
                       from payment p2
                       where p2.customer_id = c.customer_id
                     ) row
              ) as header,
              (select array_to_json(array_agg(row_to_json(row)))
                from (
                       select
                             amount,
                             payment_date
                       from payment p1
                       where p1.customer_id = c.customer_id
                     ) row
              ) as details
       from customer c
       join payment p on p.customer_id = c.customer_id
     ) rr)

    SELECT record FROM t
    where record->'header'->>'customer_id'= '2'


texts: Is it Possible to have a database table table of morethan 3000 fields

texts:
I have a form of more than 3000 fields and i want to create a table without splitting it into two.

Is it okay? Or it will have problems?


texts:
How can I select distinct two columns not ranged correctly.
for example:
if I have this table t:

================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
|| 2   || 3   ||
|| 3   || 1   ||
|| 2   || 1   ||
================

If I choose 1 in my query the result should be:

================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
================

This table is a result of selecting the rows which has a=1 or b=1 and do not repeat a,b or the inverse.
Details:

The result of SELECT * FROM t WHERE a=1 OR b=1; will be:

================
|| a   || b   ||
=======||=======
|| 1   || 2   ||
|| 1   || 3   ||
|| 3   || 1   ||
|| 2   || 1   ||
================

I should eliminate the repeated rows normally and inversely.

|| 2   || 1   || will be eliminated because it's the inverse of || 1   || 2   ||

|| 3   || 1   || will be eliminated because it's the inverse of || 1   || 3   ||

The result was shown above.


texts: Should I split these fields or leave as one?

texts:
I want to create a database listing characters in each book of the A Song of Ice and Fire series, listing the name of the character and the page in which they appear. I could just use three fields (not including the primary key,) Name, PageIntroduced, and BookIntroduced, but the Name field isn't atomic; I could be subdivide it quite a bit. Some characters have titles (Maester, Lord, Ser,) some characters have house names (Stark, Lannister, Baratheon,) plenty have nicknames (Bull, Ned, Arry,) and there are quite a few kings differentiated by regnal numbers (there are five Aegon's!) So my table might look like the following:

CHARACTER (
ForeName
HouseName
Title
NickName
RegnalNumber
PageIntroduced
BookIntroduced
)

The question is: is this complete overkill, or should I use a single "Name" field encompassing all five attributes, even if it's non-atomic? I'm sure it's neater, especially for "King Aegon V "the Fortunate" Targaryen", but it would lead to a lot of fields not being used - many characters have only one name and nothing else, such as "Rorge", "Biter" and "Hot Pie". Is this a problem in databases? Does it matter if fields are left blank in databases?


texts: So I get how to connect Access to SQL Server, and I understand that the tables have to be linked, but my problem is security. If I link the tables from SQL Server into Access, then anyone who comes into the Access forms to enter data can ultimately get at the tables via the link can't they? I know that newer Access versions have no user security, but is there any way to restrict what data they see in some of the tables, or do I need to do that on the back end in SQL Server Express?


texts: phpmyAdmin doesn't display one table's data

texts:
Sorry if this sounds like a very basic question but I couldn't find the answer on this site or with web searches.

I have a table in my database that have this structure:

And these indexes:

It seems something is wrong in this table because I can't SELECT data from this table, from PHP code nor via phpmyAdmin.

I did drop some duplicate indexes but still I have the same problem.

I did OPTIMIZE table but still I can't select data.

When I click on the icon to show the general table structure, it looks fine. I can select data from other tables in the database. The general table seems to be locked or not working correctly.

How can I fix this?


texts:
Oracle has a limit on the arguments in the IN statement of 1000, I believe, is there a way around this? If I had a statement similar to this (with 1000 ids or more):

SELECT COUNT(*)
FROM "VERSION"
WHERE "VERSION"."VERSION_ID" IN
      (59156, 57045, 54779, 54048, 54048,
       --- long list ---
       12648, 11160, 11160, 11160, 11160)

How could I write simple count statement?


texts:
If I have a table like so:

Food
-----------------
name | price | x

Let's say I create an index on name & price, but the database has a bunch of null values for both name & price in some of the rows. Does PostgreSQL automatically skip indexing those rows? I heard this behavior might have changed in later releases.


texts: Changing Service Account password in SQL Server - Clarification

texts:
We have the SQL Server Service and SQL Server Credential using same AD Level service Account.

Because of recent security changes we need to change the Service account password at AD Level.

At this stage :

If I change my SQL Server Credential password first with new password will it get directly applied to SQL Server Service account which is using same service account ?

If by any chance:

Do we need to change the SQL Server account first in configuration manager followed by credential account password change?

Please clarify.


texts: Oracle 18 on Windows with long hostnames doesn't talk to the local listener?

texts:
Note: Initially this was about installing Oracle 18c XE on a Windows machine with a long (> 15 chars) hostname. During our further tests we then noticed that Oracle 18c XE does not work properly at all on a Windows7 or Windows 10 machine with a host name longer than 15 characters.

Listener Problem

When we switch a correctly working VM machine that has Oracle 18x XE installed from a short hostname/computername (e.g. MYHOST) to a long (> 15) name (e.g. MYVERLONGHOSTNAMEXYZ) the listener stops being able to hand off the connection to the OracleServiceXE instance.

Specifically, the error we get is:

ORA-12518: TNS: Listener konnte Client-Verbindung nicht weitergeben
ORA-12518: TNS: Listener could not hand off client connection

This error occurs with both sqlplus SYSTEM/***@XE and sqlplus SYSTEM/***@XEPDB1 - bith entries are set up in tnsnames.ora and tnsping can see them.
The Oracle XE instance is up and running.
It is possible to connect via sqlplus SYSTEM/***(without @SID)
listener.ora, tnsnames.ora do not contain any hostnames, they only
contain the (local) ip address.

TL;DR Question

Is anybody able to switch an Oracle Test machine with Oracle 18c (/ XE) running to a long Windows hostname (so that the hostame is then longer than the NetBIOS name) and still connect to the instance via the local listener?

Initial installer problem description:

We're having troubles getting Oracle 18c DBCA (XE or non-XE) working on Windows machines (both Win7 and Win10) where the machine name is longer than 15 characters (the NetBIOS limit).

It seems the Oracle 18 dbCA tool refuses to pass some checks when the machine name is > 15 characters. (see: https://community.oracle.com/message/15361496#15361496)

Now, without going into further details, I am currently investigating:

Does Oracle database 18 docs say anything about the maximum length for the computername on Windows installation?
Can someone provide a counterexample where Oracle 18c (preferrably: XE) has been successfully installed on a windows machine where the computer name is > 15 chars?

Note: This error halts the dbCA, effectively stopping the Oracle 18c (XE) setup from doing it's job: 18c XE setup will only install the binaries and the listener, no instance will be created and no instance can be created via dbCA afterwards. The same is true for the full 18c installer, although that one is more flexible in it's invocation options and one might get it to work somehow.


texts: Minimize impacts of a known Denial-of-Service in SQL Server

texts:
I am currently experiencing a situation where an application is sending several identical queries at once (I found this in SQL Profiler).  This application is buggy, it is probably in loop somewhere and the responsible team is working to solve this problem.  But the same application also provides other services that are essential to the company, so I can't just disable it.  I have to live with it, until the team solves the problem.

So far so good.  But this SQL Server instance also maintains other applications, whose performance are being penalized due this occurrence, some of them timeout their operation which makes this situation a DoS, in practice.

My question is: what can I do, in the DB side, to minimize the impact to the other applications?

I don't care if the buggy app gets penalized, but I would not like to penalize the other ones.  (I can't just disable it, but I can slow down their queries results, if possible.)


texts: Is ASYNC_NETWORK_IO wait type anything to worry about?

texts:
In looking at the list of stored procedures that take long time to execute, one stands out as causing the most wait.  However, most of that wait (81%) is ASYNC_NETWORK_IO and I know why: the stored procedure transfers roughly 400 MB of information.

In the documentation, it states that the cause of ASYNC_NETWORK_IO is that the client can't keep up with the flood of data and that is probably true.  I am not sure how to make the client keep up since all it does is call the stored procedure via an ADO.NET and then just processes the dataset.

So given this information, should I worry about the ASYNC_NETWORK_IO wait type for this procedure? Does it actually have an effect on the server performance?

Additional pieces of information:

I am on service pack 2 of SQL Server 2005.
The client app is on the same box as SQL Server (I know, i know...but I can't do anything about it).


texts: Why is my InnoDB table size so much larger than the data?

texts:
I have a MySQL table (version 8.0.13). One table is unreasonably large and doesn't correspond to the current data. I rebuilt the table by running ALTER TABLE users FORCE and OPTIMIZE TABLE users, but they did not help. The two culprits I imagine are: variable length columns causing extra storage to be allocated, or deleted rows causing fragmentation. (The column types are listed at the end.)

When I examine the data with LENGTH(col), I don't see anything amiss. From SHOW TABLE STATUS, I see:

`users`:
Engine: InnoDB
Avg_row_length: 212077938
Data_length: 25237274624
Data_free: 4194304
Index_length: 32768
Rows: 119

I know this is wrong because I copied the table as follows:

CREATE TABLE `users_copy` LIKE `users`;
INSERT INTO `users_copy` SELECT * FROM `users`;

When I examine the new table, I see the following statistics (the differing row count is because the first table is constantly being modified):

`users_copy`:
Engine: InnoDB
Avg_row_length: 132270
Data_length: 16269312
Data_free: 3145728
Index_length: 32768
Rows: 123

So why is the data length so much longer in the original table, and can I fix the table online? If the answer isn't apparent, how can I debug it? Note that the DB is running on AWS so I don't think I have access to the filesystem.

Note: SHOW CREATE TABLE gives:

CREATE TABLE `users_copy` (
  `user` varchar(100) NOT NULL,
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `token` varchar(60) NOT NULL,
  `data1` json DEFAULT NULL,
  `data2` json DEFAULT NULL,
  `data3` json DEFAULT NULL,
  `data4` json DEFAULT NULL,
  `data5` json DEFAULT NULL,
  `data6` json DEFAULT NULL,
  `data7` json DEFAULT NULL,
  `data8` json DEFAULT NULL,
  `data9` json DEFAULT NULL,
  `data10` json DEFAULT NULL,
  `data11` json DEFAULT NULL,
  `data12` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `user` (`user`,`token`)
) ENGINE=InnoDB AUTO_INCREMENT=735580 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

UPDATE: Almost a day after I ran the OPTIMIZE and ALTER commands, the size was reduced to something more reasonable (but still far larger than it should have been). I would love to know how to trigger this change predictably.


texts: How to create database, and add user linked to login?

texts:
created a login and a user in the master database and linked that user to the login.

I created a new database, then went to that database and immediately tried to create a user linked to the same login. I can't because the user dbo is already linked to the login.

In the master database, SELECT CURRENT_USER returns my WMC_SQLMGR user name which is
linked to the WMC_SQLMGR login.

In the newly created WMC_main database, SELECT CURRENT_USER returns dbo as user name which is linked to the WMC_SQLMGR login.

CREATE login   WMC_SQLMGR WITH PASSWORD= 'pwstuff'
GO
(left out addsrvrolemember commands)

CREATE USER WMC_SQLMGR FOR LOGIN  WMC_SQLMGR
GO
CREATE DATABASE WMC_main ON PRIMARY
   ( NAME=N'WMC_main', ... (left out rest for brevity)
GO
ALTER LOGIN WMC_SQLMGR WITH DEFAULT_DATABASE = WMC_main
GO
USE WMC_main
CREATE USER WMC_SQLMGR FOR LOGIN  WMC_SQLMGR
   (this fails because the login    WMC_SQLMGR is connected to user dbo.)
***********************************************************
                DB_NAME    WMC_cur_user_name      sess_user_name user_name
---------------- ---------- -------------------- -------------- ----------
after USE MASTER master     WMC_SQLMGR           WMC_SQLMGR     WMC_SQLMGR

(1 row(s) affected)

-------------------------
database WMC_main dropped

(1 row(s) affected)

-------------------------
database WMC_main created

(1 row(s) affected)

                   DB_NAME    WMC_cur_user_name    sess_user_name user_name
------------------ ---------- -------------------- -------------- ----------
AFTER USE WMC_MAIN WMC_main   dbo                  dbo            dbo

(1 row(s) affected)

------------------------------------------------------
now try to create user WMC_SQLMGR for LOGIN WMC_SQLMGR

(1 row(s) affected)

Msg 15063, Level 16, State 1, Line 13
The login already has an account under a different user name.


texts: Is there any way to recklessly drop a principal leaving orphaned objects or should I assume only data corruption could cause it?

texts:
Given that SQL Server blocks me when I try to DROP a principal that owns an object in the database, if I see an object which the owner no longer exists, should I assume right away it was data corruption or is there any other way a database principal can disappear leaving behind an object it owned?
Background Info:
Working to solve this question related to an orphaned fulltext_stoplist I failed to reproduce the problem.
All my attempts to DROP the principal (user, role or application role) that owned the fulltext_stoplist threw the following error:

Msg 15138, Level 16, State 1, Line 7  The database principal owns
a fulltext stoplist in the database, and cannot be dropped.

When I tried to DELETE FROM sys.database_principals, I got this error:

Msg 259, Level 16, State 1, Line 15 Ad hoc updates to system
catalogs are not allowed.

Another approach:

I tried connecting through the dedicated admin connection (DAC) and
deleting from the underlying system table ([sys].[sysowners]), and
got the same error. (Thanks, Josh
Darnell)

So, is there any reckless or forceful (and reproducible) way to drop a database principal leaving an orphaned object or, if I ever see such a situation, I can assume the cause was data corruption with no further investigation?


texts: Unable to connect to localhost with mysql utilities

texts:
I installed mysql utilities on my ubuntu server today, so I could use mysqldbcompare, but I just cannot connect to the mysql server with it (localhost). I created an entry into the .mylogin.cnf, using mysql_config_editor, as mentionned into the mysql utilities 1.6 documentation, and printed / tested it (with mysql), and it's ok. But if I try to run any mysql utilities command, it always fail with

ERROR: Access denied for user '****'@'localhost' (using password: YES)

(I put the stars myself instead of my user name).

I spent more than 2 hours searching the web, I have absolutely no clue of what's wrong... any idea ?

EDIT :
alright, let's do this step by step
First, my versions. Everything is on the same virtual machine : mysql server and client, and mysql utils.
My versions are :
MySQL server : Ver 14 Distrib 5.7.22
MySQL client : Ver 14 Distrib 5.7.22
MySQL utils : 1.6.1

First, using phpMyAdmin (which connects correctly to mysql), I create a user named userTest, with host % and password "bonjour!".
Then, I go to my host terminal, and type the following :
mysql_config_editor set --login-path=test --host=localhost --user=userTest --port=3306 --password
When asked, I enter the password "bonjour!"
Then, I try to connect to mysql using the login path (no user is named "test" on the database :
mysql --login-path=test
This connects me to mysql correctly :

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 17

Server version: 5.7.22-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>
Now I exit mysql and type a simple mysql utils command :
mysqlserverinfo --server=test
And I get
ERROR: Access denied for user 'userTest'@'localhost' using password: YES


texts: Import data from a CSV file without creating a table first in MySQL

texts:
I want to import CSV data into mysql database.

Additionally, I dont want to create a table first and then import. That means, if my csv file name is test.csv, and its contents are like

customer_ID,name
10000000,name1
10000001,name2

I should be execute

mysql> use database {database_name}
mysql> {some_command} test.csv

And I should be able to create a table with name test and it having headers customer_ID and name and it should be populated according to contents of test.csv. Specifically some_command is the magic bullet I am looking for.

Any idea how this can be done in MySQL. While I searched I did not find a way to import CSV data without creating a table first.


texts: Copy database wizard - any change in lsn number of source database

texts: After Copy database wizard - database restore in another server . Any change in LSN number of source database . is it same as copy only backup?


texts: Retrieve title from rows with a maximum count in other columns

texts:
Given the following table archived:

CREATE TABLE archived (
   sid       serial -- row id
   showurl   text   -- can be: btg, tmwwt, gcaf, emda
   title     text
   reference text   -- title id number
);

Sample Input:

sid | showurl | title       | reference
----+---------+-------------+-----------
1   | btg     | 01-Preface  | 01
2   | btg     | 01-Preface  | 01
3   | emda    | 26-Friction | 26
4   | tmwwt   | 13-Helper   | 13
5   | btg     | 05-Finale   | 05
6   | gcaf    | 07-Help     | 07

SQLfiddle to work with.

Desired Output:

1st selection:  showurl: 'btg'

2nd selection: reference: '01'

3rd and final selection: title: '01-Preface'

I am trying to determine from table archived which showurl has the greatest number of rows.

Then, for that 'showurl', I need to identify which reference has the greatest number of rows. Obviously, that 'reference' will be duplicated, but the 'sid' associated with each duplicated 'reference' will be unique.
Once that is determined, I wish to select/display its title.

My initial attempt (using php, postgresql 9.2: queried each showurl, then using max to identify the showurl with the greatest number of hits.):

$query = "SELECT * FROM archived WHERE showurl = 'btg' order by sid ASC";
$query = "SELECT * FROM archived WHERE showurl = 'tmwwt' order by sid ASC";
$query = "SELECT * FROM archived WHERE showurl = 'gcaf' order by sid ASC";
$query = "SELECT * FROM archived WHERE showurl = 'emda' order by sid ASC";

$max = max($numrows1,$numrows2,$numrows3,$numrows4);

Using the sample data above, $max would be 3, but showurl, title, reference, and sid are unavailable for further processing.

I have tried using pg_numrows() but I cannot find a way to refer back either to 'title' or 'reference' for the max rows.

I gather some form of UNION would be in order, but have no idea how to implement this.


texts:
I'm busy trying to migrate data between informix databases using the dbexport and dbimport utilities.

I know that in PostgreSQL I can use the following

pg_dump –a –t table database > /destination/table.sql

Which I can then import using

psql database < /destination/table.sql

Is there an equivalent for the informix dbexport and import utilities?


texts: Azure tempdb filling up while trying to convert a large table to columnstore... how to mitigate?

texts:
I have a large reporting table (approx 6B rows) taking up roughly 400GB of storage on an azure sql db.  I've been trying to convert it to a columnstore table using the following commands:
insert Logs(Message) values ('Creating SRR table...');
select top 0 * into temp.SRR from dbo.SRR (nolock);
create clustered columnstore index ix_SRR on temp.SRR with (online = off);
create index ix_SRR_JobId on temp.SRR (JobId);
insert Logs(Message) values('Populating SRR table...');
insert into temp.SRR with (tablock) select * from dbo.SRR (nolock);
insert Logs(Message) values ('Switching out SRR table...');
alter schema old transfer dbo.SRR;
alter schema dbo transfer temp.SRR;
insert Logs(Message) values ('SRR table converted.');
if (select count_big(*) from old.SRR (nolock)) = (select count_big(*) from dbo.SRR (nolock)) begin
    drop table old.SRR;
    insert Logs(Message) values('Deleted old SRR table.');
end else begin
    insert Logs(Message) values('Row counts between old.SRR and dbo.SRR do not match; retaining old.SRR.');
end

This has worked for all of our other large reporting tables, but this one (after a good 30 hours of DTU time) consistently fails with the message:
Msg 40544, Level 17, State 2, Line 195
The database 'tempdb' has reached its size quota. Partition or delete data, drop indexes, or consult the documentation for possible resolutions.

What can I do to make this work?


texts: Network design considerations for Oracle Database Appliance

texts:
With the introduction of Oracle Engineered Systems the DBA is moved somewhat closer to infrastructure design decisions, and expected to at least have some opinions of the network design requirements for the database. At least that is the situation I find myself in :)

After deploying an ODA for testing, I find myself with the current setup:

System Controller 0 has the public bonded interface (bond0) connected to a typical edge switch, a Catalyst 2960 series. A management interface (bond1) is connected to a second edge switch of the same type.

System Controller 1 similarly has it's public interface connected to the second switch, while the management interface is connected to the first switch.

This way, if one of the switches goes down, an operator will be able to reach each system controller either via the public or the management interface to facilitate diagnostics.

On the Cisco end of things, EtherChannel groups are configured for the 4 bonded interfaces of the ODA. The two switches are individually wired to the rest of the network, with no direct links between the two.

At first glance this does look like a reasonable design, but the more I think about different fault scenarios the more questions I seem to come up with.

Taking into consideration that these edge-type switches are not in themselves redundant, it seems rather important that the cluster can deal with one switch becoming unavailable due to power supply failure, or one switch failing to forward packages.

The database clients (zend server application servers in this case)  are each similarly connected with a bonded interface to only to one of the two switches. This brings up some questions with regard to load balancing: The way I understand 11gR2 RAC, simply connecting to the SCAN address will quite possibly let the client go the long way to the main network and back through the other switch, which can hardly be considered to be very efficient.

What happens if a switch fails or stops forwarding packets? Will connections find the accessible VIP listener through SCAN? Will RAC somehow detect the network fault and move the SCAN and VIP to the System Controller with a working and accessible public interface? I honestly can't see how it would.

And while clients taking the long way through the core network and back is acceptable during a failover scenario, it sure would be nice to avoid it in normal production.

I'm sure Oracle has a very clear idea of how this should all work together, but I'm afraid I just don't see it all that clearly.

Is it possible to achieve full redundancy with edge-class/non-reduntant switches? Can we somehow add some control on where client connections are routed in production and failover situations? Perhaps there is a good way to interconnect the two switches to allow traffic directly between clients on one switch and database listener on the other?

At this point I'm looking for any best practices and fundamental network design considerations that should be applied to a typical high availability ODA implementation.

Hopefully this will then be of use to any DBA that is faced with making network design decisions for their ODA :)

Update:

The ODA is configured with bonds in active-backup configuration. I think this may allow for a setup where each interface on the bond is connected to a different switch, without any switch side configuration.

Anyone know if this is the case?

[root@oma1 ~]# cat /proc/net/bonding/bond0
Ethernet Channel Bonding Driver: v3.6.0 (September 26, 2009)

Bonding Mode: fault-tolerance (active-backup)
Primary Slave: None
Currently Active Slave: eth2


texts: ORA-01722: The specified number was invalid when truncating timestamp

texts:
Query:

SELECT
    first_load_time
FROM
    v$sql;

Result:

2017-02-16/07:45:25
2017-02-17/06:47:36
2017-02-17/02:05:31
2017-02-15/17:39:13

Query:

SELECT
    to_char(FIRST_LOAD_TIME, 'YYYY-MM-DD')
FROM
    v$sql;

Result:

  ORA-01722: *Cause:    The specified number was invalid.

SELECT
    trunc(FIRST_LOAD_TIME)
FROM
    v$sql;

  ORA-01722: *Cause:    The specified number was invalid.

Why does oracle give invalid number error when truncating the timestamp? I need to do a group by with date on this view.


texts: How to get list of ids from joining two tables (one to many) whose results is not present in a list?

texts:
Say Table 1:
tID, tName
Table 3:
t2ID, tID, tSubjects

One tID can be have multiple t2ID.

What I want is list of tIDs who's t2IDs should not just have a list of t2IDs (1,5,6).

i.e They can 1,2,5,6 but not just 1 or 5 or 6 or 1,5 or 1,6 or 1,5,6 in short they tIDs should be included if they have any other t2IDs along with the ones in the list.

Thanks.


texts:
When running a repair table on a corrupted or damaged table, will this guarantee replication to break?

Documentation says "in the event that a table on the master becomes damaged and you use REPAIR TABLE to repair it, you should first stop replication (if it is still running) before using REPAIR TABLE, then afterward compare the master's and slave's copies " ..

Questions

Is this a simple STOP SLAVE; on the replica?
If so, wouldn't the binary logs just send that back once slave has started again?

tia


texts:
Running alter role db_ddladmin add member sqlloginname

gives the error message:

  Cannot alter the role 'db_ddladmin' because it does not exist or you
  do not have permission.

This statement I am executing in master db from Server admin login in azure sql db. I am not able to find any reason of that.


texts: Does disconnecting the network stop a query?

texts:
I recently executed an update query against 100,000 records. I realized I'd made a mistake while the query was running and quickly unplugged the network cable.

Does the update query

stop processing and completely rollback?
continue processing to completion and commit?
stop processing and leave only part of the target rows updated?


texts: how to show combined results of two tables?

texts:
I want to show the result as combination of two.
Table 1

SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID`
FROM au.`invoice`
WHERE au.`invoice`.`InvoiceID` > 11126
-- total results 757

Table 2

SELECT nz.`invoice`.`InvoiceID`, nz.`invoice`.`UserID`
FROM nz.`invoice`
WHERE nz.`invoice`.`InvoiceID` > 11236
-- total results 757

I want result combined as table 1 result and table 2 results

SELECT au.invoice`.`InvoiceID`, au.invoice`.`UserID`, nz.`invoice`.`InvoiceID`, nz.`invoice`.`UserID`
FROM au_._invoice`
JOIN nz.`invoice` ON nz.`invoice`.`UserID` = au.invoice`.`UserID`
WHERE au.invoice`.`InvoiceID` > 11126
  AND nz.`invoice`.`InvoiceID` > 11236
-- total results 1000+

What I want it should be the same no of results because I just assigned new invoiceId to table 2, all the users have the same no of invoices.

Can you help me to achieve this result?

Thanks
Rajesh


texts: Table variable not Recognized in stored procedure

texts:
The following stored procedure for a postgresql database attempts to insert into a temporary table 'result' of type result_load_status ,which is a table in database, and return this temporary table. When function is executed, the output window shows a message 'result does not exist'.
How to fix it or any other way it should be done?

Following is the definition of stored procedure:

create or replace function trial5(table_name text)
returns table(up_link double precision,time_stamp timestamp without time zone) as $$
declare b integer default 1;
declare c integer default 2;
declare result result_load_status;
begin
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || b into result;
execute 'select ' || table_name ||'.up_link,' || table_name || '.time_stamp from' || table_name || ' where bar2= ' || c into result;

return query select * from result;
end

$$ language plpgsql;

Error Message:

  ERROR:  relation "result" does not exist
  LINE 1: select * from result


texts: Best Way to design a table with different types of attachment

texts:
I am currently Building a table for message where user can send a message with different types of attachment,

Sender can:

send text per message
send one location per message
send one or multiple images per message
send one or multiple videos per message

Currently, this is my table design,

message
    -id
    -type (text,images,videos,location)
    -sender_id

message_text
    -id
    -message_id
    -body

message_location
    -id
    -message_id
    -latitude
    -longitude

message_images
    -id
    -message_id
    -image_path
    -image_height
    -image_width

message_videos
    -id
    -message_id
    -video_path

Am I doing things right?

My concern was user can attach multiple of images or videos but only one location.


texts: How to export SSIS (SQL Server Integration System) packages to third party data source

texts: I want to export SSIS packages into a HANA databse. Please guide me through the exact steps to achieve this.


texts: mysql slave replication doesn't work due to error 1236

texts:
My mysql master server has crashed due to storage issue. The issue has been fixed and the master is up and running. However, my slave has stopped to be replicated due to the error:
Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'

Here is my slave status:

    mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.x.x.x
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000321
          Read_Master_Log_Pos: 3962375
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000321
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 3962375
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
               Last_SQL_Errno: 0
               Last_SQL_Error:

And here is my master status:

mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000323
        Position: 103145634
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

I can see that the specific binary log (mysql-bin.000321) doesn't exist on the master. The the log file itself exist on the server, but ain't shown by SHOW BINARY LOGS;.

mysql> SHOW BINARY LOGS;
+------------------+------------+
| Log_name         | File_size  |
+------------------+------------+
| mysql-bin.000314 | 1073742675 |
| mysql-bin.000315 | 1073742958 |
| mysql-bin.000316 | 1073742098 |
| mysql-bin.000317 |  631008456 |
| mysql-bin.000318 |   18007666 |
| mysql-bin.000319 |        125 |
| mysql-bin.000320 |  327413876 |
| mysql-bin.000322 |        125 |
| mysql-bin.000323 |  109150417 |
+------------------+------------+

Please advise.


texts: Multiple IPs registered for listener when using AlwaysOn AGs w/ multi-subnet clustering

texts:
We are testing AlwaysOn Availability Groups on a WSFC multi-subnet cluster and using dynamic DNS registration. Our issue is that sometimes the IP for the Availability Group Listener (AGL) in the active subnet is registered in DNS as well as the IP for the AGL that is in the inactive subnet.

When we initially setup the cluster, by default, RegisterAllProvidersIP is set to 1 so we expect both the active IP and inactive IP of the AGL to be registered in DNS.  We followed the steps outlined by the MS AlwaysOn Pro team to change RegisterAllProvidersIP to 0 so that only the IP in the active site is registered with DNS. We performed extensive testing once we made this change and discovered the following behavior:

About70% of the time, only the active site’s IP for the AGL is registered in DNS (Good!)
About 30% of the time, both the active IP and inactive IP for the AGL are registered in DNS (Bad!)

There is no pattern to the issue. We’ve looked at DNS when multiple records are created and they both have the exact same date/time stamps so the records are obviously being created at the same time. To clean up this issue, we simply run the following command and only the active IP is registered:
Get-ClusterResource AGL_Network_Name | Update-ClusterNetworkNameResource

Is anyone successfully using multi-subnet clustering with AA AGs in a production environment? If so, I’d appreciate any help you might be able to provide.

We’ve confirmed the issue occurs running under VMware and with physical servers. The VMs and physicals are located in physically different datacenters and subnets. We have a fast pipe between them and latency is minimal.  The VMs are running Server 2012 and the physicals Server 2012 R2. SQL Server 2012 SP2 is being used.


texts: How to get rows only contains multiple ID using SQL?

texts:
Let say I have this table:

id | foo
1  | 100
1  | 200
1  | 200
2  | 300
2  | 300
3  | 200
4  | 100

I am expecting to have this result:

id | foo
1  | 100
1  | 200
1  | 200
2  | 300
2  | 300

I want to get only rows that have multiple id log, regardless of the foo value.


texts: Does the Mysql install location affect write speeds?

texts:
Up until lately, I used a 32 bit version of Mysql 5.7, bundled with a WAMP server (EasyPHP) on Windows for local development, all running on an SSD. I ran into database size issues, and had to upgrade to a 64 bit version which can handle bigger file sizes. Since no 64 bit version was available for the WAMP server, I opted for a separate install of the Mysql Community server 8.0. It now works fine with my bigger databases.

The default install location on Windows is in Program Files (the MSI installer did not offer to install anywhere else), which on my machine is a regular hard drive. To speed things up, I modified the Mysql configuration to store the data files on the SSD, which also works fine. However, importing the same 2GB SQL dump file takes a lot longer than the 32 bit version did before (30 minutes vs 8-10 previously).

The only major differences between the two SQL servers are:

The server version (5.7 vs 8.0)
The architecture (64bit vs 32bit)
The install location (SSD vs regular hard drive)

I am tempted to dismiss 1) and 2), since I could not find any specific related speed issues online.

Thus my question: could it be that although the data files are stored on the SSD, that the server itself being installed on a regular hard drive slows things down? My searches regarding Mysql cache files showed that InnoDB temporary files are stored in the data folder, so that should be okay.

I have a nagging feeling that I'm missing something obvious.


texts: Privilege to select only user's own tuple only in mysql

texts:
I create a table named person which columns such as name, sex, occupation and so on.
I want to give each user select grant over that user’s own tuple only. I assume that each users use their own name as their user ID.
So I created view as

  create view mine as select * from person where name=current_user;

And I give privilege all user

  grant select on mine to public;

But it does not work.
How to grant privilege to all user in mysql?

And, in the person table, name column  store only just name. But current_user replies name@localhost. So it does not match. How to solve that?


texts:
We have 4 test environments running SQL Server 2014 Developer edition. Every night, we refresh one of those environments by restoring a SQL Backup file stored on a separate server, so that it's in a known state.

Every Monday, we refresh all 4 environments to the same known state. All restore jobs start at 1am.

The backup file we restore from is a regular SQL backup taken from a SQL 2014 Server using a standard BACKUP DATABASE command. The restore command is issued by SQL Server Agent using a standard RESTORE DATABASE command.

When the daily restore runs, it takes between 2-2.5 hours to complete. On a Monday morning, that server completes in around 3 hours. This makes some sense as there are 4 servers trying to read the same backup file instead of one.

When the other 3 servers restore on Monday morning, the restore command is issued as scheduled at 1am, but they restore command then appears to do nothing until about 11am (< 10% CPU, disk and network usage).

When selecting from sys.dm_exec_requests on all 3 servers during this time, I can see that the percentage complete is 0% until about 11am, when it starts increasing fairly evenly on all 3 servers. At the same time, network and disk usage both increase to around 100Mbps - which is an expected number for our dev environment.

I'm trying to work out what is happening for the approx. 10 hours between the restore command being issued and the data being written to disk.

The file doesn't appear to be locked by any one server, as at 11am all 3 servers are able to restore at roughly the same speed.

We will look to stagger the restore, or even move it to the weekend, but I'd really like to try and work out why the server is not restoring for 10 hours.

Is there something in the restore command that would cause it to wait?


texts: MySQL 5.7: REGEXP version of PostgreSQL REGEXP_REPLACE

texts:
I have a CASE STATEMENT in PostgreSQL which I need to convert into a MySQL statement.

       CASE
        WHEN mobilenumber ~'^09[0-9]'
        THEN regexp_replace(mobilenumber, '0', '+63')
        WHEN mobilenumber ~'^9[0-9]' AND LENGTH(mobilenumber) = 10
        THEN '+63' || mobilenumber
        ELSE mobilenumber
      END

I am using MySQL 5.7. When I run the statement below, I am getting syntax error.

CASE
   WHEN mobilenumber regexp ~'^09[0-9]'
   THEN REGEXP(mobilenumber, '0', '+63')
END

Any advice please?

-------Update---------

CASE
   WHEN mobilenumber regexp ~'^09[0-9]'
   THEN REPLACE(mobilenumber, '0', '+63')
END

The problem is I am REGEXP in my THEN instead of REPLACE. I also need to remove the ~ in the WHEN statement. When I do that, all 0s are replaced by +63. Is there a way to control this?


texts: What should I use as a primary key/index for a table with no unique columns that are mostly written to?

texts:
I have a table on a SQL server database that stores logging information about various user activity. Date/time, user id, and a few columns that specify where and what was done. None of this data is guaranteed to be unique (date/time will likely be unique, but it's far from guaranteed). This table will be written to often and searched rarely. I'd like to have it optimized for inserting, but at the same time be able to search the large quantities of data in as short a period of time as possible.

I'm still very new to sql server, if I was to have every column indexed, would that be a big performance hit with constantly adding new entries?

Edit: The problem is, it can be important to search for a lot of stuff. Some of the things this logs is who looks at important client personal information. So, ideally, you should be able to search for both which users accessed this specific information (select user where thing_done=looked at that data) as well as what a suspect user has been up to (select alldata where user=suspect). The speed of this is heavily dependent on those indexes though, right?


texts: Performance tuning a Great Circle distance calculation

texts:
I have a query that I am using to find locations that are within 1km of a known point.

To do this, I am using the Spherical Law of Cosines formula with my latitude and longitudes.

Currently, the query runs over roughly 5000 records in about 5 minutes. This is acceptable, but I would like to make it as fast as possible.

The main query I am using:

select loc.*, base.key
from locations loc left outer join baseline base
on base.key in
(select key
from baseline
where [dbo].CIRCLEDISTANCE(loc.LAT, base.LATITUDE, loc.LONG, base.LONGITUDE) <= 1)

The Circle Distance scalar value function:

ALTER FUNCTION CIRCLEDISTANCE
(
    -- Add the parameters for the function here
    @LAT1 varchar(250),
    @LAT2 varchar(250),
    @LNG1 varchar(250),
    @LNG2 varchar(250)
)
RETURNS float
AS
BEGIN

    DECLARE @Distance float
    DECLARE @LAT1_FLOAT float
    DECLARE @LNG1_FLOAT float
    DECLARE @LAT2_FLOAT float
    DECLARE @LNG2_FLOAT float

    select @LAT1_FLOAT = cast(@LAT1 as float)
    select @LNG1_FLOAT = cast(@LNG1 as float)
    select @LAT2_FLOAT = cast(@LAT2  as float)
    select @LNG2_FLOAT = cast(@LNG2 as float)

    SELECT @Distance = acos(sin(radians(@LAT1_FLOAT))*
            sin(radians(@LAT2_FLOAT))+
            cos(radians(@LAT1_FLOAT))*
            cos(radians(@LAT2_FLOAT))*
            cos(radians(@LNG2_FLOAT)-radians(@LNG1_FLOAT ))
            )*6371;

    RETURN @Distance

END
GO

The execution plan (with some anonymizing)

I have indexed all columns named in both query and function.

Some caveats:

I am converting to float in the function because the data I am getting arrives as a string and I am unable to change it prior to this point
The left outer join is required because I need to know locations that do not have a known point
I am using the Law of Cosines rather than Haversine because of it's simplicity and I don't require a large amount of accuracy, since I am only interested in things within 1 km.!

I am not a DBA myself, merely a .net developer, so I am not really knowledgeable about improving query performance beyond indexing, so any help is appreciated.

EDIT:

Rolling in Rob and Mark's answers allowed my to get my query execution down to 30 seconds. In case there's any further gains to be had, here is the query now:

select <columns>
from locations loc
left outer join baseline base
on base.key in
(select key
from baseline
where base.location_geo.STDistance(loc.location_geo) <= 0.00015678559)

The 0.00015678559 is 1000/6378137 (Earth radius) so I don't need to do any math when the query is run.

The location_geo is a computed column using the formula:

([geography]::STGeomFromText(((('POINT('+[Longitude])+' ')+[Latitude])+')',(104001)))

104001 is the Spatial ID for a spherical Earth model, since I'm not looking for a large distance this seems to make the query quicker to return.

I also have a Spatial index on the location_geo column as suggested here: http://social.technet.microsoft.com/wiki/contents/articles/9694.tuning-spatial-point-data-queries-in-sql-server-2012.aspx


texts: Store massive values data from remote controllers

texts:
I implemented telemetry system in order to observe state of remote objects. Every object has a controller collecting data from some sensors (about 10-30 controlled parameters, lets take average value = 20). Controller send stored values to server approximately 100 times per day. There ara about 5000 objects.
I use simple schema to store values:

Params table(id, controller_id, parameter, index) and

Values table(id, parameter_id, value, add_time).

So I've got a rapidly growing Values table. I need to store values for one year minimum and the object count is increasing dramatically.

20 (values row) * 100 (times per day) * 5000 (objects) = 10M rows per day;
3.65B rows per year

I want minimize the used space and amount of rows. Also I've applied partitioning.

The values are used for reporting, usually daily. The report is built by one or two selected parameters.

Question: is it a good idea to store daily values in a single text field? Every new controller params will be appended to existing values field. New Values table(id, date, controller_id, values);

For example:

00:00:01=0:1.234;1:4.44;2:124.4;|00:01:06=0:1.256;1:2.55;2:3.44;|...

So daily values query is very simple SELECT * FROM values WHERE controller_id=123;

Is there a better solution? Or should I use existing one?

EDIT #1:
Controller has multiple controlled params (temperature, pressure etc)
Param changes stored in values table.

So query for report usually will be like:
We show to user available parameter list for controller:

SELECT index FROM parameters WHERE controller_id = 123;

Then user selects needed indexes and submits them to server:

SELECT * FROM values WHERE parameter_id IN (index0, index7,...) AND add_time >= today_date;


texts: How can I trigger this stored procedure on insert?

texts:
I have a table of error messages to which errors will be inserted. I also have a table defining interactions between errors. For example, when a "Power Restored" error occurs, it clears any previous "Power Lost" errors. ("Error" being used loosely to describe any enumerable diagnostic message.)

To do this, I've defined the following stored procedure:

BEGIN
   -- Declare local variables
   DECLARE done BIT DEFAULT 0;
   DECLARE target SMALLINT(5) UNSIGNED DEFAULT '0';
   DECLARE `action` ENUM('Clear','Raise','SetSeverity');
   DECLARE severity TINYINT(3) UNSIGNED DEFAULT NULL;
   DECLARE timespan INT(11) DEFAULT NULL;
   DECLARE cutoff TIMESTAMP;
   -- Declare the cursor
   DECLARE interactions CURSOR FOR
   SELECT TargetErrNo, errorinteraction.`Action`, errorinteraction.Severity, errorinteraction.TimeSpan
      FROM errorinteraction WHERE ActorErrNo=NEW.Number ORDER BY Priority DESC;
   -- Declare continue handler
   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
   CREATE TABLE IF NOT EXISTS interactionrecord
      (`datediff` INT);
   TRUNCATE TABLE interactionrecord;
   -- Open the cursor
   OPEN interactions;
   -- Loop through all rows
   REPEAT
      -- Get
      FETCH interactions INTO target, `action`, severity, timespan;
      IF NOT done THEN
         -- Process
         IF timespan IS NULL THEN
            IF `action`='Clear' THEN
               UPDATE error SET Cleared=NOW(), UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NULL;
            ELSEIF `action`='Raise' THEN
               UPDATE error SET Cleared=NULL, UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NOT NULL;
            ELSEIF `action`='SetSeverity' AND severity IS NOT NULL THEN
               UPDATE error SET error.Severity=severity, UpdatedBy=NEW.Id WHERE Number=target AND error.Severity!=severity;
            END IF;
         ELSE
            SET cutoff=DATE_SUB(NEW.Reported, INTERVAL timespan SECOND);
            IF `action`='Clear' THEN
               UPDATE error SET Cleared=NOW(), UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NULL AND error.Reported>=cutoff;
            ELSEIF `action`='Raise' THEN
               UPDATE error SET Cleared=NULL, UpdatedBy=NEW.Id WHERE Number=target AND Cleared IS NOT NULL AND error.Reported>=cutoff;
            ELSEIF `action`='SetSeverity' AND severity IS NOT NULL THEN
               UPDATE error SET error.Severity=severity, UpdatedBy=NEW.Id WHERE Number=target AND error.Severity!=severity AND error.Reported>=cutoff;
            END IF;
         END IF;
      END IF;
   -- End of loop
   UNTIL done END REPEAT;
   -- Close the cursor
   CLOSE interactions;
END

This works perfectly when I insert a new error by hand, then call this function passing the new error's Id.

But when I try and insert a trigger to call it automatically, MySQL begins throwing Error 1422 at me. I've tried both FOR EACH ROW CALL updateerror(NEW.Id); and copying the stored procedure code into the trigger directly, replacing a few of the variables with their respective NEW columns. Should I simply leave this as a stored procedure and assume users are responsible to call it? Is there a better way to do this?

My exact MySQL version is 5.5.25.


texts: Why does SQL*PLUS need a slash after CREATE TYPE?

texts:
I just had the problem that I defined a Type and tested it in TOAD and all was OK. But running under SQL*PLUS it threw an error.

Example:

CREATE OR REPLACE TYPE MyType AS OBJECT (
    Item1 NUMBER,
    Item2 NUMBER
);

For some reason I have to add a slash here

CREATE OR REPLACE TYPE MyType AS OBJECT (
    Item1 NUMBER,
    Item2 NUMBER
);
/

To me it looks similar to a Create Table statement, which doesn't require a slash.
I find it rather confusing. I know how it works, but can anyone explain why this design decision was made?


texts: Identifying if spid on SQL Server is in rollback after low disk space

texts:
On a test box I wanted to get an idea of how long adding an identity field would take to a table with roughly a half billion rows.  The process ran for about 10 hours and we noticed it was getting dangerously low on disk space on the lun for the log.

We addressed the log disk space issue by removing some other items but I'm afraid we might have been too late as it got down to like 10MB.

Is there a way to know immediately if the query began rolling back the alter table statement due to insufficient space?  I know a transaction that has had a rollback will show up in sp_who with a status of rollback.  I also know a transaction that was purposely killed will have a status of killed/rollback.

But, is there a way to know immediately if a process is being rolled back due to insufficient space before the rollback is complete and the message is returned to SSMS?

Looking at sp_WhoIsActive, sp_Who, sp_Who2 it still shows the original alter table as the command with a status of suspended - I'm concerned that it's actually rolling back however.  Is there a way to tell?  Am I mistaken and it would show up as rolling back like a killed spid would?

If it fails it fails, but I'd like to know if I'm moving forward or moving backwards.


texts: T-SQL LIKE Predicate failed to match with whitespace in XML converted varchar

texts:
Recently I attempt to search for a particular pattern by converting XML data into varchar(max) although I'm aware it's not the best practice and found out it's not working as expected:-

Setup

declare @container table(
    [Response] xml not null
);

declare @xml xml =
'<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://abc.com/xsd" xmlns:ns="http://abc.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <soapenv:Header>
     <ns:MessageHeader>
       <xsd:ID>ABC</xsd:ID>
       <xsd:Date>2018-12-31T23:59:59</xsd:Date>
     </ns:MessageHeader>
   </soapenv:Header>
   <soapenv:Body>
     <ns:MessageResponse>
       <ns:return>
         <xsd:ResponseList xsi:nil="true" />
       </ns:return>
     </ns:MessageResponse>
   </soapenv:Body>
 </soapenv:Envelope>';

insert into @container values (@xml);

This query works

select *
  from @container
 where cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true"%';

notice the wildcard character ends 3 characters (i.e.' />') before the XML node

but this is not

select *
  from @container
 where cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true" %' -- with space
    or cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true" />%' -- whole XML node;

I suspect this is probably due to escape characters and tried a few other alternatives but to no avail, appreciate if someone can shed some light on this.

EDIT (ANSWERED)

Following query would work based on Mr. Browstone's insight:-

select *
  from @container
 where cast(Response as varchar(max))
  like '%<xsd:ResponseList xsi:nil="true"/>%';

Here's my follow question @ CodeReview with XQuery expression:-

T-SQL Verify whether XML node from SOAP request contains any child nodes


texts: can not connect to oracle database with ip address

texts:
I am new to oracle database, I have installed Oracle 11.g on redhat 5.6, I have a database that I can connect to it using the following command:

export ORACLE_SID=test
sqlplus user/passwd

but not connect to database neither remotely nor from localhost:
    sqlplus user/passwd@myhost:1521/test
    error:
    ORA-12541: TNS: no listener

this is my listener.ora file:

# listener.ora Network Configuration File:

/Oracle/oracle_base/product/11.2.0.4/database/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /Oracle/oracle_base

this inside tnsnames.ora file:

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

and this is output of lsnrctl status is:

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017

15:57:02

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                16-JAN-2017 15:45:32
Uptime                    0 days 0 hr. 11 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /Oracle/oracle_base/product/11.2.0.4/database/network/admin/listener.ora
Listener Log File         /Oracle/oracle_base/diag/tnslsnr/myhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=1522)))
The listener supports no services
The command completed successfully

and this is output of tnsping test:

oracle> tnsping test

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 16:00:45

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/Oracle/oracle_base/product/11.2.0.4/database/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test)))
TNS-12541: TNS:no listener


texts: More tables or more columns (in my simple example)?

texts:
I'm modelling DB and wondering if I should create more very simple tables or just have more columns in tables.

I mean, let's assume I have s table named User, each user can have status(ACTIVE, INACTIVE, BLOCKED, ...). Should I store status directly in User(as an ENUM?) table or maybe it's better to create a table named UserStatus with status ID, status name and FK pointing to the user?

Similar with employees and their position(manager, developer, etc.).

Can you give me some advices?


texts:
On SQL Server 2017 Std, I have an SSIS package that runs once a day on a 24×7 mission critical operational database to archive the largest transactional tables. The data is moved to tables on a separate archive database and the process runs smoothly with minimum locking contention on the live db (deletes are spread through a long period of time). Having a separate archiving database gives us many advantages: backup databases separately; refresh testing environments with current data only; improve query performance on current data, among others. The only drawback was code change (to add logic to union on archived data when requested by the user on the UI) and a slower cross-database query when needing to fetch archived data.
We are upgrading to Enterprise edition and considering table partitioning for data archiving, but the more I read about it the more worried I get in relation to the implementation and maintenance overhead. The only real advantage I can see so far is the transparency to the application, not requiring code changes.
Did any of you use table partitioning for data archiving? Is it really worth it on OLTP systems? Thanks very much in advance.


texts:
When i issue a "ps -ef|grep pmon", I get the below output.

oracle   230330      1  0 Jan08 ?        00:01:14 asm_pmon_+ASM1
oracle   244018      1  0 Jan08 ?        00:00:59 ora_pmon_vbsenopd
oracle   244020      1  0 Jan08 ?        00:01:01 ora_pmon_vbdrazpd
oracle   244021      1  0 Jan08 ?        00:01:00 ora_pmon_vbrmant
oracle   244042      1  0 Jan08 ?        00:01:25 ora_pmon_dx07123
oracle   244080      1  0 Jan08 ?        00:01:28 ora_pmon_rpasnap1

Now, Is there anyway that i can get only the active database names (for eg. rpasnap1 instead of ora_pmon_rpasnap1) using "ps-ef|grep pmon" or any other method?

Your help is my salvation.


texts:
I have a function where there is:

alter table a disable trigger trig1;
some code
alter table a enable trigger trig1;

It basicaly disables the trigger for the purpose of specific operations.

My question is:

What will happen if at the same time the trigger is disabled, another user from another session makes operations on a which relies on that trigger to be triggered?


texts: How can MySQL return an empty row matching a given table's schema, given the table name as an input string?

texts:
How can a query or procedure return an empty copy of a new record?
Ideally suited for objects which are in a transactional allocation process:
It is highly preferable to have authoritative defaults and NULLS for arbitrary tables when constructing new objects() that may-or-may not ultimately be saved to the database.
For example, a CMS user relies on AJAX which calls for a new post() or new product() dynamically. Rather than trusting stored profiles to set values in the scripting language, a blank "instance" of a row would be rather useful.


texts: Searching a big table for a few results without scanning the whole table

texts:
I need to search a very big PostgreSQL table (500+M rows), I want to limit the returned search results, but using the "limit" keyword cant prevent the search over the whole set of data(is it correct?)

Imagine my search result contains 1M rows, but I need only the first 100 records in the search result! does PostgreSQL database have to create this 1M search result rows temporarily in memory and then give me my needed 100 results?

or is there any way to tell PostgreSQL to stop searching as soon as it find 100 records?

Here is my table, of course not populated with 500M records yet!

CREATE TABLE con
(
  id bigserial NOT NULL,
  tag1 integer NOT NULL DEFAULT 0,
  tag2 integer NOT NULL DEFAULT 0,
  ref1 integer NOT NULL DEFAULT 0,
  ref2 integer NOT NULL DEFAULT 0,
  CONSTRAINT con_pkey PRIMARY KEY (id)
)

and the explain analyze for a test query:

explain analyze SELECT * FROM con where tag1 = '64813' and tag2 = '80'

Seq Scan on con  (cost=0.00..3215204.72 rows=2470 width=112) (actual time=0.016..36970.528 rows=7505 loops=1)
   Filter: ((tag1 = 64813) AND (tag2 = 80))
  Rows Removed by Filter: 152519685
Total runtime: 36972.921 ms


texts: MS SQL Server: DBCC ShrinkDatabase - Does it really not shrink the database below the minimum size?

texts:
Per Microsoft's documentation on DBCC ShrinkDatabase command, it shouldn't shrink the database below it's "minimum size".

But when I ran the command, my database data files' initial file sizes shrunk to a smaller size. Though I thought the initial file sizes were the "minimum size".
Or am I misunderstanding Microsoft's wording / intentions, and DBCC ShrinkDatabase is considered a "size changing operation"?

Shrink a Database:

  The database cannot be made smaller than the minimum size of the
  database. The minimum size is the size specified when the database was
  originally created, or the last explicit size set by using a
  file-size-changing operation, such as DBCC SHRINKFILE. For example, if
  a database was originally created with a size of 10 MB and grew to 100
  MB, the smallest size the database could be reduced to is 10 MB, even
  if all the data in the database has been deleted.

I.e. When Microsoft says "The database cannot be made smaller than the minimum size of the database" is that a tautology and I'm just overthinking things?


texts:
I have a full-text index set up on a table; the table is under fairly constant load, receiving ~50k inserts and ~35k deletes daily, with rather short (up to 5 minutes) gaps between inserts.
The index is configured for automatic change tracking, and is usually able to process all documents within seconds from insertion. However, yesterday monitoring alerted that the index was not updated for more than 4 hours.
After receiving that alert, I checked the following:

No new entries were appearing in full text log. The last entry was informational - full-text auto population completed.
Index population status (as reported by sys.dm_fts_index_population) was stuck on "Has stopped processing".
A single session running command "FT BATCH CMPLETE" was using up one entire CPU core. That session's last_request_start_time was within seconds of the last entry in full text log. Other than that the CPUs were idle.
Two (out of 30) fragments had status 6 / Being used for merge input and ready for query (as reported by sys.fulltext_index_fragments); the size of these fragments was close to 60GB each.

After roughly 10 hours, indexing resumed just as mysteriously as it stopped.

Am I right in thinking that indexing was paused because of the merge? If not, what else can I check in order to get a better diagnosis?

The issue occurs roughly twice a month on different servers. As far as I can tell, the index is not reorganized or rebuilt on a schedule. I'm looking for a solution that would allow me to either avoid indexing pauses completely, or schedule them during maintenance downtime. The server is SQL Server 2012 Enterprise.


texts: SQL Server - Tempdb data/log file placement

texts:
Currently we got TEMPDB Data (number of data files:2) and LOG file on same drive.

On monitoring we have found I/O issues on TempDB.

We got an extra drive available - should we move the TempDB Log file to new drive or should we move the second TEMPDb data file on the new drive to help us reduce I/O issue?


texts: Migrated servers... Innodb tables stayed innodb, but database is showns as MyISAM. Problem?

texts:
I moved servers.  I exported DB on old, imported to new.  Kept getting syntax error on import until I tried no compression.  Weird, but it worked.  I was skeptical so I compared the two carefully and noticed total file size was 51.7 megs (new) vs 51.4 (old).  Looks more carefully and noticed at the bottom under "type" it shows myisam (new) where it was innodb (old).  In both cases, each and every table is listed as innodb.

If I'm looking at a problem here, I'd rather know now than later.  Thanks.

ps. I noticed during SQL errors that it was referencing mariadb (never heard of that before) where old would always say "mysql".  Checked versions of both.  Old was NEWER 5.6 while new server is 5.5.  Is that a problem?


texts: NT SERVICE accounts should be sysadmins?

texts:
I have the following query to get me a list of sysadmins in the current server:

select 'ServerRole' = SUSER_NAME(rm.role_principal_id),
       'MemberName' = lgn.name ,
       'Status'= Case when lgn.is_disabled=0 then 'Enabled'
                                             else 'Disabled'
                 end
from sys.server_role_members rm
INNER JOIN  sys.server_principals lgn
        ON rm.member_principal_id = lgn.principal_id
where rm.role_principal_id = SUSER_ID('sysadmin')

Should it be a concern that the accounts starting with NT SERVICE are sysadmin?

the sql server agent service is running under the account shown above in the item number 10, so maybe the number 7 is even redundant, how can I find it out?

There is a similar question:

Is it viable to attribute permissions to SQL Server virtual accounts (NT Service\SQLSERVERAgent, etc)?


texts: SQL Server 2016 Express, need to add account with Administrator access to a database

texts:
Working with a new SQL Server 2016 Express installation. I loaded SSMS and have Administrator access to the instance. The vendor supplied a database for a new app that has only a couple internal accounts for their app defined in the database's Security. I only have public access to the database at this point- I can look at properties, but cannot add accounts to the database.

Is there a way to add my administrator account to this database's Security to gain admin access? SQL downtime is not an issue. I did some searching and found a handful of hits for regaining access to the instance, but haven't found any that address adding a new account at the database level.


texts:
I am struggling with the concept of 2NF form. Lets say I have a set of functional dependencies for R1(A,B,C,D,E,H,M,K)
Where :

B  -> M,C
AB -> D
DH -> E
H  -> K
A  -> H

Whenever I try to decompose it, I directly get 3NF tables. What are the rules to get something in 2NF, with minimal set of tables ?

Now how do I take this further to 3NF or BCNF?


texts: When should I start using connection pooling?

texts:
I have a MySQL database that peaks at about 300 connections/sec.

What is the recommended connections per server and at what point should I start using connection pooling?


texts: Running a query over the network multiple times seems to choke performance, how can I pinpoint the problem?

texts:
Background:

Oracle 11g running on Windows 2003 x64 SP2 on a 3.06GHz Xeon X5675 with 16GB RAM

Over the past 3 weeks, a query I used to run approximately every 5 minutes has started to respond very very poorly - moving from an execution time of around 50 seconds to sometimes over 11 minutes. The data has hardly changed and returns approximately 69000 records  If I restart the service, I find the query runs very quickly the first time but subsequent runs take an age to run and lock the CPU on the server at nearly 100%. This server is used for test/development purposes so is rarely under any serious load.

This ONLY happens when I run the query from a remote machine - running the query in SQL Plus on the server and dumping the result to a file gives a 25MB text file in about 1 second which made me think I have a network problem....

When I stop the service and use iperf.exe to transfer 25MB across the network on the same port of 1521, it takes 50 seconds no matter how many times I run it. It's not bandwidth related - Oracle just seems to choke.

I'll include the SQL statement but it is important to know that other similar queries which join the same tables are struggling to run in a timely fashion and yet run very quickly when ran locally. Again, those queries suffer from serious slow-down after their first run.

If anyone can give me any pointers on where to look for potential issues, I'd be eternally grateful!

SELECT
    (CASE
        WHEN t10.COURSE_ID IS NULL THEN t3.COURSENAME
        ELSE t10.COURSENAME
     END) AS C1, t3.LANGUAGE_CODE AS "LanguageCode"
FROM MYSERVERNAME.T_COURSETYPE t1
INNER JOIN MYSERVERNAME.T_CURRENCY t2 ON t1.CURRENCY_ID = t2.CURRENCY_ID
INNER JOIN MYSERVERNAME.T_COURSENAME_LANG t3 ON t1.COURSECODE_ID = t3.COURSECODE_ID
INNER JOIN MYSERVERNAME.T_COURSE t4 ON t1.COURSETYPE_ID = t4.COURSETYPE_ID
INNER JOIN MYSERVERNAME.T_PROFITCENTER t5 ON t4.PROFITCENTER_ID = t5.PROFITCENTER_ID
INNER JOIN MYSERVERNAME.T_COUNTRY t6 ON t5.COUNTRY_ID = t6.COUNTRY_ID
INNER JOIN MYSERVERNAME.T_COMPANYUNIT t7 ON t5.COMPANYUNIT_ID = t7.COMPANYUNIT_ID
INNER JOIN MYSERVERNAME.T_VENDOR t8 ON t7.VENDOR_ID = t8.VENDOR_ID
INNER JOIN MYSERVERNAME.T_COURSELOCATION t9 ON t4.COURSELOCATION_ID = t9.COURSELOCATION_ID
LEFT OUTER JOIN MYSERVERNAME.T_COURSENAME t10 ON (t4.COURSE_ID = t10.COURSE_ID) AND ((t3.LANGUAGE_CODE = t10.LANGUAGE_CODE) OR
((t3.LANGUAGE_CODE IS NULL) AND (t10.LANGUAGE_CODE IS NULL)))
LEFT OUTER JOIN MYSERVERNAME.T_ESHOP_COUNTRY t11 ON t6.COUNTRY_ID = t11.COUNTRY_ID
WHERE (t4.FROMDATE > DATE '2014-06-17') AND (t4.HIDE_ONLINEBOOKING = 'N') AND (t1.ACTIVE = 'A') AND ((t1.TMS IS NULL) OR (t1.TMS = 'N'))
AND (t8.EXTERNAL_VENDOR = 'N') AND (t1.VENDOR_ID <> 66) AND (t4.TYPEOFFEE = 'D') AND (t9.HIDE_ONLINEBOOKING IS NOT NULL) AND
(t9.HIDE_ONLINEBOOKING = 'N') AND (((t11.ACTIVE IS NULL) AND (t6.ONLINEBOOKING = 'Y')) OR (t11.ACTIVE = 'Y'));


texts:
PgAdmin 4 1.0 has been just released, but on the download page for Linux there is only a link to a yum installer.

Anybody knows if there are plans to create a deb installer? Or if there is already one?


texts: Is it possible to perform clustering when short of free space on disk?

texts:
According to v12 docs

Cluster with index scan requires

  free space on disk at least equal to the sum of the table size and the
  index sizes

and cluster with sequential scan and sort requires

  as much as double the table size, plus the index sizes

Is there any way out if I do not have that much free space? i.e. can it be run in portions somehow?

If I run CLUSTER command and it fails because of insufficient free disk space will the performed work be entirely rolled back (when run with or without any parameter if it matters)?


texts: Other databases deleted after restoring incremental MySQL Enterprise Backup

texts:
I am trying to restore some databases, which were backed up with MySQL Enterprise Backup (MEB).

The backup uses both full compressed backups and incremental backups.

There is no problem if I restore the full compressed backup, but when I restore the incremental backups, it deletes any other table in the server.

This is the commands to generate the backups:

mysqlbackup --login-path=usrbackup --socket=/var/lib/mysql/mysql.sock --compress
    --backup-dir=/backups/mydb-full --backup-image=/backups/mydb-full/full-mydb.mbi
    --include-tables=^mydb\. --exclude-tables=\.temp.+
    backup-to-image

mysqlbackup --login-path=usrbackup --socket=/var/lib/mysql/mysql.sock --incremental
    --incremental-base=dir:/backups/mydb-full
    --backup-dir=/backups/mydb-inc01 --backup-image=/backups/mydb-inc01/inc-mydb.mbi
    --include-tables=^mydb\. --exclude-tables=\.temp.+
    backup-to-image

mysqlbackup --login-path=usrbackup --socket=/var/lib/mysql/mysql.sock --incremental
    --incremental-base=dir:/backups/mydb-inc01
    --backup-dir=/backups/mydb-inc02 --backup-image=/backups/mydb-inc02/inc-mydb.mbi
    --include-tables=^mydb\. --exclude-tables=\.temp.+
    backup-to-image

And these are the commands to restore the backups

mysqlbackup --login-path=usrbackup --socket=/var/lib/mysql/mysql.sock --uncompress
    --backup-dir=/backups/mydb-full --backup-image=full-mydb.mbi
    copy-back-and-apply-log

mysqlbackup --login-path=usrbackup --socket=/var/lib/mysql/mysql.sock --incremental
    --backup-dir=/backups/mydb-inc01 --backup-image=inc-mydb.mbi
    copy-back-and-apply-log

mysqlbackup --login-path=usrbackup --socket=/var/lib/mysql/mysql.sock --incremental
    --backup-dir=/backups/mydb-inc02 --backup-image=inc-mydb.mbi
    copy-back-and-apply-log

When it deletes any other databases, this includes mysql, and information_schema, rendering the server completely unusable.

Is there something I am doing wrong?


texts: Is parallel execution enabled or disabled by default in Oracle Standard Edition database version 12.1?

texts:
Oracle Standard Edition 12.1 on Red Hat Linux 7

According to the documentation, parallel execution is enabled by default in Oracle SE 12.1.  However, when looking at the database parameters, the following exists:

PARAMETER              VALUE
---------              -----
Parallel execution     FALSE

Is there an error in the documentation?  Shouldn't this be set to TRUE.
What does this parameter refer to if not for enabling parallel execution?
If it needs to be set to TRUE, how to do so?


texts:
I tried to import job but my nohup command seems doesn't work.

devinim@koha-db57:~/kohaaktarim$ nohup mysql -u root -p kohadata < kohaaktarim.sql &
[1] 9619
devinim@koha-db57:~/kohaaktarim$ nohup: appending output to ‘nohup.out’

[1]+  Stopped                 nohup mysql -u root -p kohadata < kohaaktarim.sql
devinim@koha-db57:~/kohaaktarim$ jobs
[1]+  Stopped                 nohup mysql -u root -p kohadata < kohaaktarim.sql
devinim@koha-db57:~/kohaaktarim$ gf
gf: command not found
devinim@koha-db57:~/kohaaktarim$ fg
nohup mysql -u root -p kohadata < kohaaktarim.sql
Enter password:

I entered password but it's not responding. I open another terminal and connect there. I can't see any nohup when I search ps -ef|grep nohup . what should i do? thanks.


texts: How Uniqueidentifier in SQL Server is always a unique value globally?

texts:
As per microsoft documentation on UniqueIdentifier, This value is always a unique globally beacuse it's based on network clock and CPU clock time and on the other hand the same documentation says

  uniqueidentifier columns may contain multiple occurrences of an
  individual uniqueidentifier value, unless the UNIQUE or PRIMARY KEY
  constraints are also specified for the column.

I'm not able to come to a conclusion how UniqueIdentifier (GUIDs) can be unique globally, as Network address (Mac address) can be same on two different networks, How GUIDs can be unique globally with which combinations and also why Microsoft says there should be primary or unique constraint in order to ensure we've unique UniqueIdeitifer value always.

https://technet.microsoft.com/en-us/library/ms190215(v=sql.105).aspx


texts: Database design for one key with multiple values

texts:
In my application, users are able to create lists. Each list contains infinite records. Each record contains one key and this key, may contain zero or infinite values.

To clarify consider this:

List title: "Greeting in some languages"

List records: (Below table is a simple UI design (HTML form) that user has filled)

| Hello         | Hallo        | Ciao        |
| Bye           | Tschüss      | arrivederci | ... | ... | ... |
| Good morning  | Guten Morgen | Buongiorno  |
| Good night    | Gute Nacht   | Buonanotte  | ... |
|[Another key]  | [Value 1]    | [Value 2]   |

First column always contains keys. Next columns are values for the first column.

The approach that came to my mind is:

Use a NoSQL database (e.g., MongoDB).
Create a table called lists which contains two columns: id title (in this example "Greeting in some languages").
Create a table called lists_contents which contains three columns: list_id, key, values

In the lists_table I would like to store all values which belong to the key, in the values column as sub-record.

So this is a record (in above list) which is going to insert:

{
    list_id: 123,
    key: "Hello",
    values: ["Hallo","Ciao","Salam","..."]
}

Another approach is to create an independent table for values column.

What is your idea?
Is  using a NoSQL database a good tool or a relational database? Are my approaches suitable?


texts:
We have sensitive information (names of people) and we're looking to put into place a script to "scrub" of the names.  Has anybody come up with a good algorithm to do this, something other than just making all of the last names "TEST" or something?

Thanks.


texts: Add leading zeros to integers less than 7 digits and ignore anything 7+

texts:
I have a query and it almost doing what I want. It is adding leading zeros but when it comes to things 7+ long it turns everything into a 7 number digit.

Select RIGHT(rtrim('0000000' + cast(column as nvarchar)), 7) from table

I just think my brain is not working and I cannot figure this out. Can anyone help me with this?

Examples:

324 -> 0000324
2232 -> 0002232
3324124 -> 3324124

All I'm trying do is add 0s to anything that is less than 7.

The column is nvarchar.


texts: Trigger After Insert is preventing insertion

texts:
I have two tables that must have similar information, and one of them can be considered the base table for to other, so in order to have the info in the two tables, I create this trigger:

ALTER TRIGGER [dbo].[TR_INSERTED_READER_LOG]
ON [dbo].[TBL_READER_LOG]
AFTER INSERT
AS
IF EXISTS(SELECT * FROM INSERTED WHERE REL_ENROLLNUMBER < 9999999)
BEGIN
    SET LANGUAGE us_english
    DECLARE @DEV_NID INT
    DECLARE @DATE DATETIME
    DECLARE @USE_NID INT
    DECLARE @REL_MODE INT
    SELECT @DEV_NID = DEV_NID,
           @DATE = LOG_DATE,
           @USE_NID = REL_ENROLLNUMBER,
           @REL_MODE = REL_MODE
    FROM TBL_DEVICE
    INNER JOIN TBL_READER_LOG ON DEV_ORIGINAL_NID = RDR_NID WHERE DEV_TYPE = 1
    INSERT INTO TBL_USER_LOGS (DEV_NID, LOG_DATE, USE_NID, LOG_MODE) VALUES (@DEV_NID, @DATE, @USE_NID, @REL_MODE)
END

It works fine, however when the insert statement in the trigger has an error (like a FK violation) is preventing that the data in the first table can be inserted.

I have searched in the documentation of Microsoft and says that After Insert execute the Trigger when all the instructions has already done so, Why is preventing the insertion?


texts: Single query to return counts over different IDs in a single record

texts:
select count(title_id)as algodata from titles where pub_id =1389
select count(title_id)as binnet   from titles where pub_id =0877
select count(title_id)as newmoon  from titles where pub_id =0736

The database used is pubs in SQL Server.

Can I use a single query to  show the count of records for each publisher (here there are 3 publishers) in a single record?


texts: Facing error while executing function inside the package in PL/SQL

texts:
Hi everyone hope you're doing well.
I'm new to oracle and I'm reading about the concept of 'Package' and 'Package body'.
I have a function as you can see below
CREATE OR REPLACE Function Show_description(i_Course_No
   Course.Course_No%Type)
 Return Varchar2 AS
V_Description Varchar2(50);

 Begin

     Select description
     into v_Description
     From Course
     Where Course_No = I_Course_No;
        Return V_Description;

  Exception
     When No_Data_Found Then
        Return('The Course Is not in the DataBase');
     When Others Then
        Return('Error in running Show_Description');

  End;

and I have a package containing only this function :
CREATE OR REPLACE PACKAGE MY_First_Package is

   function Show_description(i_Course_No Course.Course_No%Type) Return
   Varchar2;

   end ;

and I have a package body as you can see here :
create or replace package body my_first_package AS

  Function Show_description(i_Course_No Course.Course_No%Type)
  Return Varchar2 AS
  V_Description Varchar2(50);

  Begin
    Select description
    into v_Description
    From Course
    Where Course_No = I_Course_No;
        Return V_Description;

    Exception
      When No_Data_Found Then
          Return('The Course Is not in the DataBase');
      When Others Then
          Return('Error in running Show_Description');

    End;
       End  my_first_package;

and everything seems fine without any error . When I want to execute this package as :
exec my_first_package.Show_description(12);

I face an error which is : ORA-00900 :Invalid sql statement
what is the problem??
Thanks in advance


texts:
I realize a similar question has been asked multiple times but all the ones I can find are from 2014/2015 and many versions of postgres ago.

I'm on postgres 11. I'm introducing a new primary key for a table of type uuid. I see that since postgres 10, hash index types are in pretty good shape ( e.g. http://rhaas.blogspot.com/2017/09/postgresqls-hash-indexes-are-now-cool.html ), and I'm wondering if it's the better choice for a uuid key in postgres these days?

Update I realize I'm missing part of the use case which impacts this question.

My primary use case is that I previously had a composite primary key and am replacing it with a UUID for easy joins and to not have to propagate the primary key's data to other tables.

In this sense, I'm likely to want create the primary key using INCLUDE (composite, fields, here) which is only supported for btree index types. So in that sense, I have my answer.

I am still curious about a 'generic' use case of simply wanting rapid access to looking up a value by its primary key, however.


texts: Why do I only see SYS and INFORMATION_SCHEMA tables? Access 2007 ODB to SQL Server 2008

texts:
I was finally able to create my ODBC connection but now when I go to External Data > ODBC > LINK TABLES using my ODBC connection in Access 2007 I get a huge list of tables such as

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA.DOMAINS

etc.....

and a whole bunch of

sys.all_columns

sys.all_objects

sys.all_parameters

etc...

the only tables that i really need are tables such as

dbo.users

dbo.stats

dbo.customers

etc...

but none of those tables are on the list of table that I am able to choose from to link.

Does anyone know why I can't see or link to the DBO tables I need?

(by the way i created a new user login in order to create my ODBC connection)


texts: What is the best architecture to run Oracle sucessfully run on VMWare?

texts: Due to a data center move we are mandated to migrate all servers to virtual machines. We currently have a 3 Node Oracle RAC running on physical servers. There seems to be a great debate about the suitability of running Oracle on virtual machines especially as RAC would not be part of the new system. What is the best architecture to run Oracle successfully run on VMWare?