texts: PL/SQL - Drop User inside BeginEnd block

texts:
I have some experience with SQL Server but now I'm starting to work with PL SQL and I'm facing some problems because the difference between the two technologies.

I have some scripts to drop my database and when I need to delete my user I do something like this:

DEFINE OWNER = MYUSER

ALTER SESSION SET DDL_LOCK_TIMEOUT = 3600;

BEGIN
  FOR r IN (select sid,serial# from v$session where username=&OWNER)
  LOOP
      EXECUTE IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial# || '''';
  END LOOP;

  DROP USER &OWNER CASCADE;
END;
/

However I'm getting this error:

  Details:
           ORA-06550: line 6, column 3:   PLS-00103: Encountered the symbol \"DROP\" when expecting one of the following:

  (    begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with        << continue close current delete fetch lock   insert open rollback savepoint set sql execute commit forall   merge pipe purge json_exists json_value json_query   json_object json_array (DBD ERROR:

  error possibly near <*> indicator at char 189 in 'BEGIN   FOR r IN (select
  sid,serial# from v$session where username=MYUSER)   LOOP   EXECUTE
  IMMEDIATE 'alter system kill session ''' || r.sid  || ',' || r.serial#
  || '''';  END LOOP;   <*>DROP USER MYUSER CASCADE;   END; ')

I already tried to add a slash in line 8:

  END LOOP;
  /
  DROP USER &OWNER CASCADE;
END;
/

but the error still appearing, and I already tried to move DROP USER &OWNER CASCADE; to the end of the script but I'm still getting errors.

I can't understand what is wrong and I already read many links this about this, including:

https://www.tutorialspoint.com/plsql/plsql_basic_syntax.htm
https://stackoverflow.com/questions/15664924/dropping-connected-users-in-oracle-database
https://stackoverflow.com/questions/20334067/pls-00103-encountered-the-symbol-create

What is the problem? Begin End don't allow empty lines? DROP USER &OWNER CASCADE; must be inside another Begin End?


texts: Connecting to MySQL 5.1 using IBM Data Studio

texts:
I am trying to connect to MySQL database version 5.1 hosted on a web server using IBM Data Studio 4.1. I am getting an error

"The JDBC driver class was not found in the specified JAR or .zip file. Click the ellipsis (...) button to update the path to the JDBC driver files.".

I downloaded the connector file from "http://dev.mysql.com/downloads/connector/j/" but IBM Data Studio doesn't recognize this .zip file as the right .jar file.

I was able to connect using SQL Developer but it isn't as friendly as I wished for. So, can someone please help me with the right .jar file and instructions for updating data studio with the jar so that I can connect successfully.


texts: Update existing SQL Server 2008 table via Flat File using SSIS

texts:
I am trying to update existing records or add any new ones to a SQL Server table using a flat file. Also I need it work as an SSIS package. I don't get an option to do a source query like I do for Excel, etc. when setting up the import task and even when I was trying to use Excel my options seemed limited to simple selects for the source query.

I really need to be able to run a full script that checks the first column (which is my key), updates existing records, then adds any new ones. Maybe I am taking the wrong approach here altogether, but I thought there was a way to do this with SSIS. Can someone point me in the right direction? If it were two tables and I didn't have to use SSIS I would just write the sql and be done with it. It's really the bridge with using flat files and SSIS (automating the import) that I'm looking for here.

Just to give a quick background of what I ultimately am trying to accomplish:
We do exports of our data into flat files and those will go to a ftp server. We then have the customer import from those files into table representations of them so they can report against them.


texts: Always On Instance Service Broker/Database Mirroring transport connection endpoint Error

texts:
I have a production instance that has 2 AOG. There is no mirroring configured for both groups. Both groups are also in 'Healthy' state.
Upon checking the error log on SQL Server, I noticed that starting from 12/19/20 there has been this error that started at 9:03PM. I went through an dug in further to see that there is a pattern. Every night around 9:03-9:04pm the same error is happening. This does not occur during normal business hours.
Error msg:
An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')`
My thought was to stop and restart the endpoints, but I don't think that would be necessary in this case.
Anyone have any thoughts as to why it might be happening on a daily basis around the same time, and how I may be able to solve this error?
Thanks in advance.


texts: Foreign keys pointing to "look-up" tables: Should we reference Id or Code values?

texts:
We are rewriting from a huge monolithic application to smaller application using rest.

While the old application had one huge tables called REFERENCE tables with applicable values had all these ids and codes and names and description. The other tables had references using ids.
E.g Current Design

Tbl_Status_Types

 +---------------+----------------+---------+
 | id            | code           |  name   |
 +---------------+----------------+---------+
 |             1 |              O | Open    |
 |             2 |              C | Close   |
 +---------------+----------------+---------+

Tbl_Order

     +---------------+----------------+---------+
     | order_id      | cust_id        |fk_status|
     +---------------+----------------+---------+
     |             1 |              O | O or 1  |
     |             2 |              C | C or 2  |
     +---------------+----------------+---------+

Question:

Should the Foreign Key FK_Status be referencing ID or CODE
what value adding a CODE would add to other than readability and is it maintainable?
if you are exposing your rest endpoint for an operation to update status - which value would you expose name or code


texts: cannot find -lpq after upgrading PostgreSQL 8 to 9 on CentOS 5

texts:
I'm trying to upgrade a (proprietary, closed-source, legacy) database-driven application from using PostgreSQL 8.1 to PostgreSQL 9.4. I have it installed on a virtual machine running CentOS 5.9. Here's a rough summary of what I've done so far:

Downloaded and installed the PostgreSQL 9.4 RPM package from yum.postgresql.org:

$ wget http://yum.postgresql.org/9.4/redhat/rhel-5-x86_64/pgdg-centos94-9.4-1.noarch.rpm
$ yum install pgdg-redhat94-9.4-1.noarch.rpm

Installed all the postgresql94 packages:

$ sudo yum install postgresql94*

Dumped my current database to a file:

$ sudo su postgres
$ pg_dumpall > dump.sql

Uninstalled the the old postgresql packages:

$ sudo yum remove postgresql postgresql-*

Sym-linked the new postgres94 bin directory to the location of the old postgres bin directory:

$ sudo ln -s /usr/pgsql-9.4/bin/ /usr/bin/pgsql

Initialized a postgres94 database and loaded the dump file:

$ /etc/init.d/postgresql-9.4 initdb
$ /etc/init.d/postgresql-9.4 start
$ /usr/pgsql-9.4/bin/psql < dump.sql

Then I tried to run the application. For the most part it seemed to work, but it had a few quirks. After a brief attempt at debugging I realized that the application was also using the PostgreSQL C API. So I tried to re-compile the program:

Reconfigure:

$ ./configure

Check that the include directories and the library directories are configured correctly:

$ cat Makefile | grep -P '\-I|\-L'

PG_CFLAGS = -I/usr/pgsql-9.4/include
PG_CFLAGS_SERVER = -I/usr/pgsql-9.4/include/server
PG_CXXFLAGS = -I/usr/pgsql-9.4/include
PG_CXXFLAGS_SERVER = -I/usr/pgsql-9.4/include/server
PG_LDFLAGS = -L/usr/pgsql-9.4/lib

Double-check the postgres include and library directories:

$ pg_config --includedir

/usr/pgsql-9.4/include

$ pg_config --libdir

/usr/pgsql-9.4/lib

Recompile:

$ make clean
$ make > make.out 2> make.err

Check error output:

$ cat make.err
/usr/bin/ld: cannot find -lpq
collect2: ld returned 1 exit status
[...]
make[2]: *** [all] Error 2
make[1]: *** [all-recursive] Error 1
make: *** [all-recursive] Error 1

So it looks like the linker can't find the libpq library, but I'm not sure why. Help?


texts: How does TOPN influence a query with group by?

texts:
I have a query that looks like the following:

Select t1.field1, t2.field2, sum(t1.field3)
From  t1 inner join t2 on t1.id = t2.id
Group by t1.field1, t2.field2

It's simplified, normally there are a few more joins and attributes on it.
My question is now, how much is the impact of adding a TOP N clause to the query instead of stopping the query after N rows are returned to my C# code.

I know, that adding the clause is better for the server, but I'm interested in a rough estimate of how much impact the clause has. My guess is, that because I have an aggregation, the server has to process all rows anyways, therefore the impact is not that high.

Edit:
C# Snippet how we stop the execution

var cmd = new SqlCommand();
cmd.CommandText = sqlStatement;
cmd.Connection = connectionString;
SqlDataReader dr;
dr = cmd.ExecuteReader();
while (dr.HasRows)
{
    while (dr.Read())
    {
        MyDataRow newRow = new MyDataRowDataRow();
        newRow.Parse(dr); //Parse the value from datarow to our format
        result.Add(newRow);
        if (topN.HasValue)
        {
            if (result.Count >= topN.Value)
            {
                break; //stop reading!
            }
        }
    }
    dr.NextResult();
}


texts:
I have inherited a few scripts that are pumped through a c#.NET custom app to update a SQL2005 SP3 database.  One of these scripts does a deletion on a user table called msw_timer_task, based on a filtered join to sys.conversation_endpoints.

Here is the SQL:

SET ANSI_NULLS , ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL,
    QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
/*
SELECT name, is_ansi_nulls_on, is_ansi_padding_on, is_ansi_warnings_on,
    is_arithabort_on, is_concat_null_yields_null_on, is_numeric_roundabort_on,
    is_quoted_identifier_on
FROM sys.databases where name = db_name()
*/
print 'clean up unattended time tasks'
delete from msw_timer_task where timer_task_id in (
    select m.timer_task_id
    from msw_timer_task m
        left join sys.conversation_endpoints c on
                c.conversation_handle = m.conversation_handle
    where c.conversation_handle is null and m.state = 'WAITING')

This script errors with the following error message:

ERROR:
Severity 16, state 1 error number 1934 line 8 of proc on server SomeSillySrv:
DELETE failed because the following SET options have incorrect settings:
'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or
indexes on computed columns and/or query notifications and/or xml data type methods.

I have done a lot of Googling on the issue, and have played around a lot with the set statement in an attempt to get it to work.  In testing, in SSMS, the script did not produce an error, at least not before shipping it.

I am not sure if I am missing something, but it seems that the settings are not properly set when run through the app.

Am I missing something in the script?


texts: select rows having condition met for group (without temporary table)

texts:
Having the table with 3 columns:

ID  category    flag
1       A       1
2       A       0
3       A       0
4       B       0
5       C       0

I want to select all rows that have flag = 1 at least once per category.

Expected results:

ID  category    flag
1       A       1
2       A       0
3       A       0

It can be solved using a temporary table like this:

select ID into #tempTable from someTable where flag = 1
select * from someTable join #tempTable on someTable.ID = #tempTable.ID

But I'd prefer a solution with grouping, which I struggle to came up with. Any help will be appreciated.


texts: What is the most efficient way to alter a column definition in a table with millions of rows

texts:
I need to change a column from NOT NULL to NULL in a table that contains millions of rows. I've tried a simple

alter table Table1 ALTER COLUMN Column1 XML NULL

but it takes forever. So here are my questions:

Why does it take so long to apply the alter?
Is there a better way to do it?


texts: SQL Server 2008 R2 Express for a single website?

texts: Someone recently told me I could use SQL Server 2008 R2 Express edition for a single website. But don't you need the Server Agent for connections (I think. I'm trying to remember things from over several years ago now!) and Express has it's Server Agent disabled?


texts: Normalise an expenditure table in a budget database

texts:
We have a budget database that stores expenditure items per budget.  There are over 100 expenditure items each having common attributes: Amount, Frequency, Comment. The table will have over 300 columns in total and look similar to below.  How would you guys look to normalise this table?

Expenditure
BudgetID
FoodAmount
FoodFrequency
FoodComment
FuelAmount
FuelFrequency
FuelComment
BlahblahAmount
BlahblahFrequency
BlahblahComment
etc...

Thanks.


texts: Getting PAM-compatible password hashes from pgcrypto

texts:
I want to keep user records in Postgres database and faced one issue which block me. It is password encryption. For example, this is correcetly encrypted password 12345 via default Ubuntu crypt function:

$6$P5jfk4Ufh33f$.Dc8H9jsWAl/igt4QPbYI/El28SyUEAoJPPHsLIGMKOymhC7AIaiizlW5W9hm7kU7PYMCySEhYtMqVPFsVTMK/

As I understand first 6 means that it is encrypted via sha384 (or sha256) algorithm. Ok, i created an extension pgcrypto in desired database, but following code: SELECT digest('12345', 'sha384') as pwhash; returns absolutely different result - without algorithm mark and salt:

 \x0fa76955abfa9dafd83facca8343a92aa09497f98101086611b0bfa95dbc0dcc661d62e9568a5a032ba81960f3e55d4a

I want to get exactly specified format because it is used by PAM to check user credetials and it does not understand password hashed another way.


texts: Oracle ASMM - Are specific values ignored?

texts:
I've inherited an 11.2.0 DB and I'm trying to do some performance tuning.

At present, it's started from a pfile and it looks like ASMM is enabled (SGA_TARGET, SGA_MAX_TARGET and PGA_AGGRIGATE_TARGET are all set). My question is, there are SGA structures also set in the pfile, are they ignored in favour of the ASMM?

E.g. I have the following:

  shared_pool_size         = 800M
  large_pool_size          = 32M
  java_pool_size           = 160M

Probably a fundamental question, but I'm struggling to get a definitive answer online.


texts:
I'm designing a database for contacts and I'm looking for some feedback on the current design.

What I want:

A contact can be a person or an organization.
A contact can have many groups.
A contact can have many emails and addresses
A contact can have one organization (employed)
An organization can have many contacts (employees)
A group can have many contacts.

What I have:

Contact(Id, PersonId (FK), OrganizationId (FK), EmployerId (FK, self reference))
Person(Id, FirstName, LastName, Birthday)
Organization(Id, Name, Founded)
Group(Id, Name)
ContactGroup(Id, ContactId (FK), GroupId (FK))
Email(Id, ContactId (FK), Name)
Address(Id, ContactId (FK), Street, City, ZipCode, Country)

What bothers me the most is that the contact entity has one organization or one person, and also a self reference (EmployerId) if an organization has employees (which are also contact entities) and vice versa.

Is this a problem or see you a better way?


texts: Is changing the SQL Server port really that much safer?

texts:
SQL Server's default port is 1433. I've been told by our admin that the port has to change "for security reasons".

Is it really that much safer to change the port? If the server is behind a firewall and it only allows connections to it from a certain range of IP's, is that not good enough?


texts:
For performance reasons we are thinking about moving our SSRS installation to a separate server.
Since you can have a standalone installation of SSRS.
Question being, if we install SSRS (standalone) on a new server with X cores.
Do we need a separate enterprise license for this server?
Is there a licensing cost for installing standalone SSRS?
Or is the license used from the instance where the ReportServerDB is located ?


texts:
I have a table in mysql with the following data:

| StartDateTime       | FinishDateTime |
| 2016-08-18 10:00:00 | 2016-08-18 11:00:00 |
| 2016-08-18 12:00:00 | 2016-08-18 14:00:00 |
| 2016-08-31 17:00:00 | 2016-09-01 09:00:00 |

What I would like to get as the output is

| Month               | Duration       |
| Aug                 | 10             |
| Sept                | 9              |

I can get the right output when each record is only within 1 month but I am not sure how to go about crossing month boundaries. Can anyone point me in the right direction?


texts: I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.


texts:
I am trying to setup table level Autovacuuming on postgres database. What all metrices I need to collect and analyze specific to that particular table?
I have to enable autovacuuming on all the tables wherever it is required.(With most inserts/deletes probably). How do I proceed on this?
Thanks


texts:
There is a plan to create a new ETL job to do a particular task. It will retrieve data from database1, and from that data, it would update equivalent records in database2. Both database1 and database2 are Oracle 12c. Now, the issue is there are  about a million and a half records in database2 and based on statistics, everytime the ETL will be run, about 45% of the records in database2 is likely to be updated.

Now we're thinking how long could it take to update about 675,000 records every time the ETL is run. We would want it to be execute at a certain time window and not go on running beyond that. Would it be more efficient to insert then delete instead? rather than update them all? We would also like the implementation to be future proof - although there is db purging for database2 (delete those more than 3 years), there is a possibility for data to grow at a high rate in the next few months.


texts: SQL Server mirrored backup using cluster shared volumes

texts:
I have SQL Server 2014 cluster with 2 nodes and a third server with iSCSI target server configured on it, all of them are virtual Servers with Windows Server 2012 R2.

Both nodes of cluster are iSCSI initiators and connected to the iSCSI target which was converted to Cluster Shared Volume(CSV).

I'm using Ola Hallengren scripts to make backups, the idea here is backup to local Z:\ drive and mirror to C:\ClusterStorage\BackupSQL (this folder is the cluster shared volume), backup run successfully on each location by separate, but if I configure the script to use Mirror Device, it gives me an

  Error 3212 The mirror device "%ls" and the mirror device "%ls" have different device specifications.

The block size in both drives C: and Z: is the same, tested with the command:

fsutil fsinfo ntfsinfo <drive:>

After the job runs unsuccessfully the folder structure was created, but no backups are inside, what am I missing?

This works:

BACKUP DATABASE [msdb]
TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak'
--MIRROR
--TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT

This also works:

BACKUP DATABASE [msdb]
--TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak'
--MIRROR
TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT

This NOT work:

BACKUP DATABASE [msdb]
TO DISK = N'Z:\MSSQL\Backup\some_path_and_name.bak'
MIRROR
TO DISK = N'C:\ClusterStorage\BackupSQL\some_path_and_name.bak'
WITH CHECKSUM, COMPRESSION, FORMAT

the last one gives me the Error 3212


texts:
I am stumped.  I need to return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different.

PRODUCTID  CREATEDATE    STATUS    LEVEL
ABC        1/3/2016      fail      1
ABC        1/4/2016      pass      2
ABC        1/5/2016      pass      3
DEF        1/1/2016      pass      1
DEF        1/2/2016      pass      1
DEF        1/10/2016     pass      1
DEF        1/11/2016     fail      1
GHI        12/29/2015    pass      1
GHI        12/30/2015    fail      1
JKL        1/1/2016      pass      1
JKL        1/2/2016      pass      1

Based on values above the following would be returned:

PRODUCTID   RUNSTATUS
ABC         TRUE
DEF         FALSE
GHI         FALSE
JKL         FALSE

Any help is greatly appreciated.

UPDATED POST with additional logic.

I added LEVEL column.  The query needs to return as described above.

Return a TRUE or FALSE by ProductId if the 2 most recent records created are 'pass' and the LEVEL values are different.

If a ProductID has only one record total, and it is PASS, it would be FALSE.  There needs to be two 'pass' in a row.


texts: Unknown source of high number of connections

texts:
In MySQL/Aurora I see from time to time a spike in connections number (from about 100 to 4000):

MySQL doc says:

Connections
The number of connection attempts (successful or not) to the MySQL server.

So I was thinking those coming from failed connection, however I don't see failures:
MySQL [(none)]> SHOW GLOBAL STATUS like 'Conn%';
+-----------------------------------+----------+
| Variable_name                     | Value    |
+-----------------------------------+----------+
| Connection_errors_accept          | 0        |
| Connection_errors_internal        | 0        |
| Connection_errors_max_connections | 0        |
| Connection_errors_peer_address    | 0        |
| Connection_errors_select          | 0        |
| Connection_errors_tcpwrap         | 0        |
| Connections                       | 34388970 |
+-----------------------------------+----------+
7 rows in set (0.00 sec)

MySQL [(none)]> show global variables like '%conn%';
+-----------------------------------------------+-------------------+
| Variable_name                                 | Value             |
+-----------------------------------------------+-------------------+
| aurora_max_connections_limit                  | 16000             |
| connect_timeout                               | 10                |
| disconnect_on_expired_password                | ON                |
| init_connect                                  |                   |
| max_connect_errors                            | 100               |
| max_connections                               | 4000              |
| max_user_connections                          | 0                 |
| performance_schema_session_connect_attrs_size | 512               |
+-----------------------------------------------+-------------------+

I would very much like to understand what those are and when they are coming from.


texts: What is the best way to query these tables? Inner Join?

texts:
database: showsdb
tables:

shows  (id, date, venue, city, state, length, source, source2, rating)
songs (id, name)
shows_songs (shows_id, songs_id)

I want to be able to query the shows table and display all the songs from that show.  I also want to query the songs table and display all the shows where that song appears.
For example:
shows table has 2 shows.  3 songs played at each show.
songs table has 5 songs.  Some of those songs were played at show1.  some at show2.  some at both
show1 setlist: song1, song2, song3
show2 setlist: song2,song4, song5
Do I need a FK in one of these tables?  How would that look?  Or can this be done with joins?  I am not too clear on that either.
Thanks for your help and suggestions


texts: Should I include another column here to keep track of cost + tax or not?

texts:
I am making a two database tables tho add a sales order line item functionality to my application.  On the second table, which lets  call salesorderlineitems, I have the following schema

CREATE TABLE salesorderlineitems (
    other columns here,
    units INT(11),
    costPerUnit DECIMAL(11,2),
    totalCost DECIMAL(11,2),
    tax DECIMAL(11,2)
);

Now I realized after some developing that besides totalCost being sort of a misnomer since it's only costPerUnit * units, and not including the totalCost.  That made me think, should I change the name of totalCost to say totalUnitsCost and have totalCost be costPerUnit*units + tax.

That made me think well I can always just calculate that manually by doing totalUnitcost + tax, so why bother saving it.  But then I started thinking of well that's also true of totalUnitsCost since it's definition is just multiplication of two other columns.

I know my use case here is pretty simple but I'm sure this concept has popped up in many peoples databases.  Is it worth it to have a column that is just a calculation between two other columns, and under what conditions is it so?  Also, how would I document this if say I choose to go the other route and and only keep track of units costPerUnit and tax and leave the rest up to doing some intra-query calculations, and under what conditions would this be preferable?


texts: Invalid syntax near RECURSIVE when creating a routine

texts:
I'm trying to restore an mysqldump file but it complains about an syntax error related to my procedures.

ERROR 1064 (42000) at line 2860: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'RECURSIVE emptree (id, username, owner_id) AS (SELECT id, username, owner_id FRO' at line 3

Here is the routines syntax on the generated file:
--
-- Dumping routines for database 'XXXX'
--
/*!50003 DROP FUNCTION IF EXISTS `str_rand` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` FUNCTION `str_rand`(u_count INT UNSIGNED,
    v_chars TEXT
) RETURNS text CHARSET latin1
    NO SQL
    SQL SECURITY INVOKER
BEGIN
    DECLARE v_retval TEXT DEFAULT '';
    DECLARE u_pos    INT UNSIGNED;
    DECLARE u        INT UNSIGNED;

    SET u = LENGTH(v_chars);
    WHILE u_count > 0
    DO
      SET u_pos = 1 + FLOOR(RAND() * u);
      SET v_retval = CONCAT(v_retval, MID(v_chars, u_pos, 1));
      SET u_count = u_count - 1;
    END WHILE;

    RETURN v_retval;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `getSubResellerIds` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8mb4 */ ;
/*!50003 SET character_set_results = utf8mb4 */ ;
/*!50003 SET collation_connection  = utf8mb4_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `getSubResellerIds`(ABC INT(11) )
BEGIN
    WITH RECURSIVE emptree (id, username, owner_id) AS (SELECT id, username, owner_id FROM reg_users WHERE id = ABC UNION ALL SELECT c.id, c.username, c.owner_id FROM reg_users c JOIN emptree p ON p.id = c.owner_id)
    SELECT id FROM emptree;
END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;

Any help?


texts: Syncing Database to Application (polling)

texts:
I have created a windows application that uses a MS SQL 2008 database server.

There are two types of the application:

1) Client Application
2) Host Application

The client application is able to send commands to the database that the host application polls and performs the command,
e.g. restart host server, restart host appliation, host check for application updates, host show debugs etc.

Once the host application picks up the command from the database the command is removed. The client application has a command task list that shows the outstanding tasks. Currently to keep this task list up-to-date (e.g. to remove tasks that have been completed) I have to query/poll the database every 3 seconds to get a refreshed dataset for the client application.

Is there a better way to poll the database, I am not sure querying the databse every 3 seconds is a good idea even if the query is very small.

Is there a way for the database to tell the application to refresh if a table is updated?

Thanks, Greg


texts: How long will it take to migrate a 5 GB Database to Amazon RDS

texts:
I just need an estimation.

How long will / can it take to move a 5 GB big database from an external source to amazon RDS. What is best practise to do it?

I just moved succesfully a few days ago a small test database (wordpress) from an external webhost to amazon. But without compression etc.

I`d say it would take 4-5 hours to move a 5 GB database, but I have not so much experience and I am unsure.

Thanks.


texts: Restore SQL Server DB encrypted by EKM - where's the asymmetric key?

texts:
MS is clear about restoring TDE backups: Certificate has to be restored on destination OR, in case of EKM, the asymmetric key

"
When moving a TDE protected database, you must also move the certificate or [[asymmetric key]] that is used to open the DEK. The certificate or asymmetric key must be installed in the master database of the destination server, so that SQL Server can access the database files
"

Now, there is no asymmetric backup built-in SQL Server. We might assume that the EK can export it or so?? we can restore Asym keys created as strong name files but the create statement of Asym key with EKM does not look to support a source file

CREATE ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE

FROM Provider [SafeNetLunaHSM]

WITH ALGORITHM = RSA_2048,

PROVIDER_KEY_NAME = 'EKM_RSA_2048_Key_TDE',

CREATION_DISPOSITION=CREATE_NEW

I am using Safenet Luna HSM. Has anyone been in a similar situation? simply restoring a TDE backup from EKM source to Native TDE destination.

Appreciate the help


texts: SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn

texts:
I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2.
There are various restrictions if one wants to setup Replication on top of AlwaysOn..For e.g below

Merge replication is not supported.

Transactional replication with immediate or queued updating subscriber is not supported.

Peer to peer replication is not supported.

Bidirectional transactional replication is not supported.

Does anybody have any simple select script which can confirm that my replication setup on SQL 2008R2 is anything out of the above type?

I tried checking "mspublication" table but that isn't providing any guidance to confirm whether my replication setup is out of the above 4 types.
I don't have access on client's SQL instances so want to provide them simple select statement.


texts: Oracle 11g connectivity over windows based client-server net

texts:
I have two PCs as follows: I want to create network for both PCs and share Oracle database from PC1

PC1 (windows 10 Pro 64-bit operation system) - Oracle 11g Installed
PC2 (windows 10 Pro 64-bit operation system)

I need SQL Developer connectivity and application connectivity. The application is developed with vb.net 2015. Is this possible?

If yes then please tell me how. Tried many things but not working.


texts: How to query all entities associated with all children (recursive) of a node in SQL?

texts:
This question is about SQL in general. Answering specifically for MySQL would be helpful but not necessary.

Ok, I’m having trouble putting this into words… so bear with me.

Say I have a tree of things (I’ll call them nodes), with a table that looks something like this (the structure can be changed; this is just a simple version):

+---------+-----------+
| node_id | parent_id |
+---------+-----------+
| 1       | NULL      |
| 2       | NULL      |
| 3       | 1         |
| 4       | 1         |
| 5       | 5         |
| 7       | 2         |
| 8       | 5         |
+---------+-----------+

Then I have a table of entities. Each entity is associated with a certain node. For example (again, the structure can be changed):

+-----------+---------+
| entity_id | node_id |
+-----------+---------+
| 1         | 1       |
| 2         | 1       |
| 3         | 4       |
| 4         | 7       |
| ...many more rows   |
+-----------+---------+

This structure could represent a lot of things, e.g. each entity is a movie, while each node is a genre (but there can be unlimited levels of sub-genres).

So retrieving all the entities for a given node is simple; just running a query on the entities table for a specified node_id.

Here’s my question: how would I query the entities table for all entities associated with a given node, and all it’s children nodes (every level, recursively). In the movies example, I want to find all movies for a particular genre and all its sub-genres, and its sub-genres, etc.

I mentioned the word recursive, but that doesn’t mean that the query should be recursive, but conceptually it is. The query should be as fast as possible. The structure of the tables may need to be changed as well.

Thanks for your help!


texts: Measuring latency of a long-distance SQL connection

texts:
We have written a simple "client" app for a customer that connects to their SQLServer database, runs about 360 queries against a "property", and returns that data as a .Net object we call a "cProp".

The queries vary in complexity, but the very longest take perhaps 400ms. When we recently ran it for 60 properties, the entire run took about 3 minutes. The customer has decided to move their database to a remote server with 65ms between us. As a result, the same query required 51 minutes.

Now I'm 99% sure the problem is row retrieval, but I need to prove that. The DBA ran a trace on the process, but having never used it before, I was dismayed to find out how little information it contained. What I did see is that it took just under 4 minutes of server time. This reinforces my belief that the problem is in the row retrieval.

But how do I measure that? The trace returns nothing about the number of rows or total data size - am I doing it wrong? I also know that batching happens outside the server, so how do we even find out what's going on there? Generally speaking, how does one debug issues that are taking place "outside the server" so to speak.


texts: MongoDB cross datacenter replication without elections or high data availaility

texts:
I want to replicate a MongoDB database to another node, located in another datacenter.
This is to help guard against data loss in the case of a hardware failure.
We don't want/need high availability or elections; just a 'close to live' read-only copy of the database, located in another DC.
Everything I read says that you need an odd number of nodes, due to elections, but this isn't something we need/want and I can't find anything related to just having one primary, and one secondary (I might be being blind).
Is this something we can achieve with MongoDB, and if so are there any 'gotchas' or serious downsides we should consider?


texts: Connection issue to Mysql from Apache server

texts:
I am operating on Centos7 and Mysql 5.6.46. I needed to move the default datadir location to an encrypted partition and I changed my default /etc/my.cnf file accordingly:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/NEW_FOLDER/mysql
socket=/NEW_FOLDER/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=""

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/NEW_FOLDER/mysql/mysql.sock

After restarting, I verify that I can connect to Mysql by writing: mysql -u <USERNAME> -p, no issue there.
What my issue is has to do with when my CGI-based server is trying to access the DB. I get the error: Error connecting to database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) which does not make much sense to me since the default socket directory has changed.

I looked into the Apache error log and I see this:

DBI connect('testuser:localhost','testdb',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /srv/www/TestSite/perl-lib/Database.pm line 31.

I go to line 31 and I see this:

my $dsn = "DBI:mysql:testdb:localhost";
    $dbh = DBI->connect($dsn, 'testuser', 'testpasswd')
      or throw Portal::DatabaseError("Error connecting to database: $DBI::errstr");

The way I see it, Apache is trying to access Mysql but it cannot use the mysql.socket from the new default mysql datadir location. Any ideas what to do?


texts:
I've got the following table:

CREATE TABLE gorfs.inode_segments
(
  st_ino "gorfs"."ino_t" NOT NULL, -- Inode number the segment belongs to. alongside segment_index, it forms the table's primary key to ensure uniqueness per relevant scope
  segment_index "gorfs"."pathname_component" NOT NULL, -- See st_no's column description for further details. The meaning of this column varies based on the host inode type:...
  st_ino_target "gorfs"."ino_t", -- Target inode number. Meaningful for directory inode segments (objects in the directory)
  full_path "gorfs"."absolute_pathname", -- Exploded absolute canonical path for quick lookups. Meaningful only for directory inode segments (objects in the directory)
  segment_data "bytea", -- Actual data segment. Meaningful only for S_IFLNK and S_IFREG....
  CONSTRAINT pk_inode_segments PRIMARY KEY ("st_ino", "segment_index"),
  CONSTRAINT fk_host_inode_must_exist FOREIGN KEY (st_ino)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_target_inode_must_exist FOREIGN KEY (st_ino_target)
      REFERENCES gorfs.inodes (st_ino) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT uc_no_duplicate_full_paths UNIQUE ("full_path"),
  CONSTRAINT cc_only_root_can_be_its_own_parent CHECK ("st_ino_target" IS NULL OR "st_ino"::bigint <> "st_ino_target"::bigint OR "st_ino"::bigint = 2)
)

The column "segment_data" has all the attachments from the customers, like photos, documents, audio, etc.

To improve the performance, we'll be migrating all the data from the segment_data column to a NFS server.

Full database size: 2 TB
Using PostgreSQL 9.2
Table gorfs.inode_segments size: 1698 GB (Command: SELECT
pg_size_pretty( pg_total_relation_size('gorfs.inode_segments'));)
Basically we'll be using: UPDATE gorfs.inode_segments SET segment_data = NULL WHERE batch_number = 0
We won't be migrating all the data on the segment_data column by once, it will be done gradually (over few weeks).

Question:

Just by setting "NULL" into that column, will I get free space on the database? Will I have to run any command such VACUUM/VACUUM FULL/ANALYZE/PG_DUMP?


texts:
PostgreSQL 9.6, on a table with ~100000 rows of text up to 3200 characters long.

I have the trigram indexes:

CREATE INDEX trgm_idx_gin ON test_trgm USING gin (t gin_trgm_ops);
CREATE INDEX trgm_idx_gist ON test_trgm USING gist (t gist_trgm_ops);

SELECT t, 'word' <<-> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

Index is used as predicted by the documentation, execution: 331 ms.

Limit  (cost=0.41..23.91 rows=10 width=886)
  ->  Index Scan using trgm_idx_gist on test_trgm  (cost=0.41..421142.11 rows=179185 width=886)
        Order By: (t <->> 'word'::text)

But the commutator <->> of the <<-> operator for word_similarity() does not use the index:

SELECT t, 'word' <->> t AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

Execution: 38 s, more than 100x more.

Limit  (cost=70833.94..70833.96 rows=10 width=886)
  ->  Sort  (cost=70833.94..71281.90 rows=179185 width=886)
        Sort Key: (('word'::text <->> t))
        ->  Seq Scan on test_trgm  (cost=0.00..66961.81 rows=179185 width=886)

How could I speed up the commutator?


texts: Datawarehouse - Dimension, Fact, or Degenerative dimension

texts:
I am building a warehouse for a billing system that bills a number of services/utilities on the same invoice, as such I am having real trouble conforming to a recognised design method for the warehouse. I think I still very much have an OLTP hat on.

As an example a single invoice could be produced containing charges for electricity, gas and mobile phones.

All three components of the bill, themselves have two components, fixed charges, and usage based charges. Even simplified to just a mobile phone it still means to store the level of detail required for the business I need 2 fact tables:

FactMobileFixedCharge
FactMobileCalls

Each of these go onto a single invoice, in addition to this there are transactions at an invoice level too, so I now need a further fact table:

FactInvoiceTransaction

The basic schema for this simple example is as follows:

All the reading I have done approaches the InvoiceLineItem example to resolve the issue of an invoice appearing like a fact and a dimension, where the facts of the invoice are essentially propogated to the child table, causing some redundancy but conforming to standard practises.

In the production billing system itself there are 15 tables that contain items that appear on an invoice, a summary table that contains invoice level charges and a summary of the charges in the other 15 tables by category, and the invoice table itself.

I don't feel that my scenario matches this though, because the invoice has a number of different things that also need to be reported on (Invoicing address, marketing material on invoice, payment method, invoice format, email, online or paper billing, balance brought forward, payments since the last invoice etc). While this is acceptable redundancy if I had one or two tables it seems like a lot for 15 fact tables.

In addition to being a lot of redundancy when there are multiple tables, I would also still need to link FactMobileCall and FactMobileCharge to get records for the same invoice, and I don't beleive a degenerate dimension is a good way to link the two.

Basically this all points towards Invoice being a dimension, especially as there are no measurements on an invoice level, however it still feels like it should be a fact as there will only ever be new records on each import.

Which leads to my questions:

Am I correct in thinking that an Invoice in this scenario should be considered a fact?
If so is it acceptable to have further facts referencing this fact, or is there a better way to approach this?
I can't believe I am the only person who has encountered a situation where the InvoiceLineItem examples don't really seem to fit. Is there a recognised way of dealing with this?

For further information, my warehouse schema currently looks likes this (where I have conceded to Invoice being a fact like dimension):


texts: SQL Server Replication - Publication and Subscriber schema differ, need to sync

texts:
I have a SQL Server 2005 database where the publication and subscriber schema are different. This was intentionally done by turning the "Replicate Schema" option to False.

However I now need to propagate the publication schema to the subscriber.

What is the best way of achieving this?


texts: Active Directory group as a schema in SQL Server

texts:
In SQL Server 2008 R2 we have a database that is being maintained by a team of developers.
Each of them should have permissions to perform DDL operations such as adding a column or creating a table.
However, we do want to log all the DDL actions done by a developer.

Using one user for everyone is not an option due to poor audit, using multiple users (Active Directory users) results their tables having separate schema names and that's untidy.

Is there a way to use Active Directory group as the schema to enjoy both worlds?


texts:
If I have, in my Java code, the following classes:

Patient :contains instance of anther classes (mentioned bellow)
PatientBasicInformation: contains PatientId, phoneNumber, Name, Gender, Age.
PatientImageFinidgns: contains Date, comments, lesionSize, site, procedure.
PatientLabFindings: contains Date, t1,t2,t3,t4.
PatientTreatment: contains visitNumber, Date, some other data.

Id is the primary key for Patient and Date is very important key in both PatientImageFindings and PatientLabFindings. visitNumber is my primary key in PatientTreatment.

How could I design these classes in a database. Should I create a table Patient, which holds only foreign keys of the other classes (like ID, Date, VisitNumber) or should I put PatientId in the table Patient as a primary key and as a foreign key in the PatientBasicInformation table. If so, what would be the primary key in the PatientBasicInformation table?

I want appreciate a simple and clear answer as I'm a new to databases and I have read a lot of articles, but I still don't get the whole picture.


texts: Windows 2000 can't connect to SQL Server 2014

texts:
We have an old workstation running Windows 2000 that must connect to MSSQL14 running on Windows server 2012 R2.
When i try to create an ODBC using:

sa
TCP/IP (ex. 123.456.789.012)
not dynamic port 1434 (the one we use on every other workstation successful)

it gives me this error:

  Impossible to connect

  SQLState: '01000'

  Sql server error: 1006 Impossible to connect

  SQLState: '08001'

  Sql Server error: 17

I think it's driver fault, but I can't find something useful...
On the workstation there is this driver: Sql Server driver 2000.80.194.00

We need to change this workstation, but I need to make it works ASAP until that moment.

Can someone help me?


texts: I have 30.7GB backup file generated from RMAN. The original ./datafile is 950.28GB. According to previous DBA, there is a lot of free space in table space. Now, I have 300GB free disk space in hard disk. How can i restore the backup file using limited disk space ? Thkb4


texts: PostgreSQL/RedShift - students that attended zero classes on a given day

texts:
I have a couple of tables:

students

id,name,email,age
01,john,john@something.com,14
02,Mike,mike@something.com,15
03,Ted,Ted@something.com,14

Classes

classid,classname,date,studentid
01,Mathematics,2017-01-01,01
02,Physics,2017-01-01,01
03,Physics,2017-01-01,02
04,Chemsitry,2017-01-01,01

students.id = classes.studentid. Note that Ted has not attended any class on 2017-01-01. How do I find students that have zero classes on a given day? I am looking for an output like this:

name,email,date,class
Ted,ted@something.com,2017-01-01,NULL


texts:
need to replace the blank column  into 0
for example the table is

i have tried this query to bring the output
but it is taking more number of time because i have put this table in to procedure

instead of using 'case' can we bring the output with any other concept

select
case when hail_n like '' then '0' else hail_n end as Hail_N,
case when hail_s like '' then '0' else hail_s end as Hail_S,
case when hail_e like '' then '0' else hail_e end as Hail_E,
case when hail_w like '' then '0' else hail_w end as Hail_W,
case when wind_n like '' then '0' else wind_n end as Wind_N,
case when wind_s like '' then '0' else wind_s end as Wind_S,
case when wind_e like '' then '0' else wind_e end as Wind_E,
case when wind_w like '' then '0' else wind_w end as Wind_W
from vw_hail_wind_directions

thanks


texts: When PostgreSQL will use Index Only Scan over Bitmap Index + Heap Scan

texts:
Today I've been doing some reading on the difference between the two and I think I've got a reasonable idea of each.

Index Only: Accesses heap pages as it moves through the index, possibly access the same page multiple time.

Bitmap Index Scan + Bitmap Heap Scan: Builds a bitmap of heap pages that need to be visited containing a matching tuple. The map(s) are then combined and sorted and heap pages accessed.

What I didn't get a definitive answer on, is the conditions that will determine when one will be used over the other. As far as I can gather, these are some of them:

The retrieved columns are a subset of the indexed columns.
A vacuumed and analyzed table (index only will only work on these)
Number of rows that will be fetched - though this didn't make sense to me.


texts: Is it possible to disable strict mode per user

texts:
I have a host that I use for running some data import scripts. These are pretty old / legacy scripts that aren't important to anything and aren't sensitive. The host recently upgraded mariaDB and now it's in strict mode, such that my old scripts that used work fine are failing when they try to insert empty strings against a column defined for int values.

I've asked the host to disable strict mode so that I can continue without making a bunch of changes to my scripts that always worked fine-- they say they need to think it over to "assess the impact on other customers".

Am I wrong to think that if they were running my user as its own user / schema, they wouldn't need to impact any other customer? While I take their point that using strict mode is a best practice, it seems like they should have more separation between customer environments so this shouldn't be an issue.


texts: Spiky COMMIT results in performance slowdowns with Postgres 8.4

texts:
We just installed performance monitoring on our web application, and we're seeing a spiky pattern in response times from our Postgres 8.4 server. The spikes correspond with significant slowdown in our web app. The time seems to taken up largely in "Postgres commit":

We're self-hosting Postgres 8.4.3 on Ubuntu 10.04, with essentially stock settings, on an Amazon EC2 c1.xlarge instance w/ EBS. Yes, I know that Postgres on EBS probably isn't the best setup. We plan to move to a newer Postgres DB on RDS later this year.

In the meantime, is there anything obvious that I should look at that might tame this spiky pattern?


texts: Connection error while installing mongodb on fedora 28

texts:
I have been trying to install Mongodb but it keeps failing midway. When the download is complete, installation fails with a connection error:

MongoDB shell version v3.6.3
connecting to: mongodb://127.0.0.1:27017
2018-08-17T12:26:33.340+0200 W NETWORK  [thread1] Failed to connect to 127.0.0.1:27017, in(checking socket for error after poll), reason: Connection refused
2018-08-17T12:26:33.340+0200 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:27017, connection attempt failed :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

What I've tried:
1. Changing the port:
mongo --port 4332
but it doesn't connect still:

MongoDB shell version v3.6.3
connecting to: mongodb://127.0.0.1:4332/
2018-08-17T12:32:55.743+0200 W NETWORK  [thread1] Failed to connect to 127.0.0.1:4332, in(checking socket for error after poll), reason: Connection refused
2018-08-17T12:32:55.743+0200 E QUERY    [thread1] Error: couldn't connect to server 127.0.0.1:4332, connection attempt failed :
connect@src/mongo/shell/mongo.js:251:13
@(connect):1:6
exception: connect failed

Reinstalling the DB shows up as complete but I still can't start it. Check out the screenshot

Checking for any config files in /etc/mongo* or /var/lib/: there is none.


texts: MySQL InnoDB Insertion Faster Than MyISAM

texts:
I wrote code that inserts about 15 millions rows into tables in MyISAM and InnoDB for comparsion.

MyISAM and InnoDB are set up with no optimization configuration in /etc/mysql/my.cnf.

And tables are created with no indexes.

Insertion are done using statements like

INSERT INTO Table (columnA, columnB, columnC) VALUES (?, ?, ?)..."
(10,000 (?, ?, ?)s)

db.Exec(stmt, valueArgs...)

with valueArgs being the actual VALUES
 It is widely believed that MyISAM is faster than InnoDB in insertion.

Baron Schwartz, author of the book High Performance MySQL, explained why in this post Mysql: Insert performance INNODB vs MYISAM.

However, in my experiment, I found out that InnoDB is actually faster than MyISAM. Could anyone give me some insights?

mysql  Ver 14.14 Distrib 5.7.30, for Linux (x86_64) using  EditLine wrapper


texts: Efficient configuration for a purely temporary, small Oracle installation

texts:
I have a personal Oracle installation on my development machine (Oracle 12.1 EE, Windows 7, 16 GB RAM (about 7 of which is unused at all times), SSD) which is used exclusively for running automated tests.

This means:

Default Oracle installation using their GUI - all defaults were accepted during installation.
One database user/schema.
The schema is wiped before and after every run of the whole test suite (i.e. database objects are regularly dropped and recreated; the user itself is not dropped). This step does not need to be optimized, its time is insignifikant in relation to the total runtime.
During the test run, between individual scenarios, the tables are either wiped by ROLLBACK or TRUNCATE depending on circumstances.
All objects, including all data rows, are recreated everytime the suite runs. There is no permanent data staying around between suite runs or individual tests.
It would be totally irrelevant if there were any data loss at any time, up to including having to wipe the whole schema or even the DB itself.

So.

Obviously, there is a lot of overhead right now, since Oracle doesn't know that I don't care about my data. So it will have its redo log etc. chugging along like usual.

What is a good configuration to make my use case as quick as possible? I.e., turning off as many security/consistency/history features as possible; avoiding writing buffers to the HDD as far as possible, etc.?

The time for the one-time initial setup and teardown (i.e. CREATE TABLE ... DROP TABLE) is insignificant in relation to the runtime of the test suite.


texts:
I'm need to run a query from mySQL 5.6 which involves looking up date from different tables. I'm new to mySQL and I'm not sure what this operation is called, or else I would have just googled the subject... I'm hoping to get someone to point me in the right direction... Please note, I can't change the structure of the tables, as this is pre-populated from the application I am using.

example:

3 tables in the same local database: network_segments, Buildings, Departments

network_segments description:

network_segment_ID, display_name, starting_ip, ending_ip, building, department
1, 10.0.0.1, 10.0.0.254, 1, 2
2, 10.1.0.1, 10.1.0.254, 2, 3
3, 10.4.0.1, 10.4.0.254, 3, 1

building table description:

building_ID, description
1, ACHQ
2, EHQ
3, AHQ

departments table description:

department_ID, description
1, marketing
2, sales
3, hr

I want to "select * from network_segments" but in stead of the building and department IDs coming back, I'd like the description field for those IDs:

1, 10.0.0.1, 10.0.0.254, ACHQ, sales
2, 10.1.0.1, 10.1.0.254, EHQ, hr
3, 10.4.0.1, 10.4.0.254, AHQ, marketing

any advise would be appreciated...

Thank you!


texts: Remove duplicate rows from select query using group by

texts:
I have a situation to grab rows with a minimum value of a certain column. Below is the image of my made up table to explain what I am trying to do.

I need to pull one record per instance_oid with the minimum shelf number. I wrote the query like this:

select instance_oid, site, bldg, room, min(shelf) group by instance_oid, site, bldg, room, shelf

This gives same record in multilpe rows (as many rows as I have for each instance_oid). How do I make sure I get only one record, i.e, the one with the min(shelf) as I highlighted in the attached image?


texts: Stop agent job after executing for a certain number of hours

texts:
I have a scheduled job which runs nightly. I want it to stop itself at 07:00AM in the morning whether or not it is completed.
I have seen some duplicates in the site but they don't exactly address the problem.
Thanks.


texts: What is happening under the hood in MySQL 8 when I apply an auto-increment primary key to a raw table of data?

texts:
I'm responsible for a production database using MySQL 8.0 (InnoDb engine). It has become necessary for me to archive data from a sizeable table (the data file takes up approximately 50% of the hard drive).
I've learnt that using DELETE FROM x WHERE y is a horrible idea and doesn't necessarily reduce disk space usage. My new strategy is to:

Export the data I need to archive to CSV (I was doing this before)
Select the data I want to keep in the DB and place it in a new table (using CREATE TABLE ... SELECT)
Apply the PK AI NN BIGINT primary key that I have in the existing table to the new table
Delete the non-FK indices from the current table (to reduce disk usage)
Apply FK indices to the new table
Apply non-FK indices to the new table
Rename the old table and new table
Verify new table is working
Drop old table

My problem / question really begins at step 3: my raw table consumes 44.6 GB before applying the primary key, and as I am typing this the replacement file is pushing 52.2 GB. That seems like a lot of space for a primary key that's technically based on data that's already there.
Could anyone explain why it's taking so much space? I'm running out of hard drive space now and don't know how to estimate how large the final data file might be.
Here's the CREATE statement I used to generate the temporary table:
CREATE TABLE ts_temp
SELECT * FROM timeseries
WHERE
        (type_id IN (1, 4, 5) AND date > CURDATE() - INTERVAL 24 MONTH)
    OR (type_id IN (8) AND date > CURDATE())
    OR (type_id IN (10, 11) AND date > CURDATE() - INTERVAL 9 MONTH)
    OR (type_id IN (12, 13, 14, 15, 22) AND date > CURDATE() - INTERVAL 6 MONTH)
    OR (type_id IN (16, 17, 19, 20, 21) AND date > CURDATE() - INTERVAL 2 MONTH)
    OR (type_id IN (2, 6) AND date > CURDATE() - INTERVAL 1 MONTH);

Getting a subsequent CREATE statement from MySQL Workbench for this table would look like this:
CREATE TABLE `ts_temp` (
  `id` bigint unsigned NOT NULL DEFAULT '0',
  `item_id` int DEFAULT NULL,
  `date` date DEFAULT NULL,
  `type_id` int DEFAULT NULL,
  `node_id` int DEFAULT NULL,
  `value` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Then, I applied the PK using this:
ALTER TABLE `ts_temp`
CHANGE COLUMN `id` `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY (`id`);

I should perhaps mention that this is a timeseries table and we ran out of AI numbers when it was a (default) signed INT field, which I subsequently converted to BIGINT unsigned.


texts: How to stop MySQL recreating SSL files on restart

texts:
We're using MySQL 5.7.22. Each time I restart MySQL it creates a set of SSL files ( ca-key.pem etc) in the datadir.
The problem is that I already have these files on the server, in /etc/mysql/ssl.

Running show global variables like '%ssl%' shows me that it is looking at the correct files in /etc/mysql/ssl, but it still creates the new ones in the datadir.

I can see that there is an option to auto_generate_certs=off
but when I add this to my.cnf file, it just comes up as unknown variable and MySQL won't start

Is there a way to prevent it from creating these certificates every time I restart the database?


texts: Consolidating multiple related queries, each using different window and/or criteria for counting records

texts:
I'm accumulating results for multiple contingency tables, one for each Test. As an example:

Contingency Table for TestA (one for each test, and for each word)
                EventsWithWord  EventsWithoutWord
TestA passed          n11                 n10
TestA failed          n01                 n00

For analysis, I need the number of events matching each of the following categories (this is how R expects the data results):

TestA passed for each word: n11
TestA results available for each word: n_1 (sum of n11 and n01)
TestA passed:   n1_ (sum of n11 and n10)
TestA results available: n__

Table "TestResults": columns: CompanyID nvarchar(20) NOT NULL, TestName nvarchar(255) NOT NULL, CompanyResults bit NULL

Table "Data" (one row for each event): Columns: EventID int NOT NULL, CompanyID nvarchar(20) NOT NULL

Table "WordEventMap": Columns: EventID int NOT NULL, Word nvarchar(255) NOT NULL

Is there a way of combining some of the queries (e.g., with windowing functions)? Some of these tables are very large--40 million rows currently in WordEventMap and 1.3 million rows in Data, and I expect the results table will have about 4 million rows (40 different tests, 100,000 different words). The current execution plan does not consolidate any of the Common Table Expressions.

Current query:

WITH CTE1
AS (SELECT
  COUNT(DISTINCT Data.EventID) AS n__,
  TestResults.TestName
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName),
CTE2
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n11,
  TestResults.TestName,
  WordEventMap.Word
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
INNER JOIN WordEventMap
  ON Data.[EventID] = WordEventMap.EventID
WHERE TestResults.CompanyResults = 1
GROUP BY TestResults.TestName,
         WordEventMap.Word),
CTE3
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n1_,
  TestResults.TestName
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
WHERE TestResults.CompanyResults = 1
GROUP BY TestResults.TestName),
CTE4
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n_1,
  TestResults.TestName,
  WordEventMap.Word
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
INNER JOIN WordEventMap
  ON Data.[EventID] = WordEventMap.EventID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName,
         WordEventMap.Word)
SELECT
  CTE2.TestName,
  CTE2.Word,
  n11,
  n1_,
  n_1,
  n__
FROM CTE2
INNER JOIN CTE4
  ON CTE2.Word = CTE4.Word
  AND CTE2.TestName = CTE4.TestName
INNER JOIN CTE1
  ON CTE2.TestName = CTE1.TestName
INNER JOIN CTE3
  ON CTE2.TestName = CTE3.TestName

Here's a SQL fiddle using the above structure and the below data. The above query is saved as the View [Results]. http://sqlfiddle.com/#!6/a8155e/1

Sample data:

Table TestResults
CompanyA TestA 1
CompanyA TestB 0
CompanyB TestA NULL
CompanyB TestB 1
CompanyC TestA 0
CompanyC TestB 1

Table Data
1    CompanyA
2    CompanyA
3    CompanyB
4    CompanyC
5    CompanyB

Table WordEventMap
1 airplane
1 tightrope
1 eggplant
2 eggplant
2 aardvark
2 eggbeater
3 airplane
3 aardvark
3 spaghetti
4 airplane
4 eggplant
4 wikipedia
5 eggplant
5 eggbeater
5 tightrope
5 licorice

Results Set
TestA   aardvark    1   2   1   3
TestB   aardvark    1   3   2   5
TestA   airplane    1   2   2   3
TestB   airplane    2   3   3   5
TestA   eggbeater   1   2   1   3
TestA   eggplant    1   2   2   3
TestB   eggplant    2   3   3   5
TestB   licorice    1   3   1   5
TestB   spaghetti   1   3   1   5
TestA   tightrope   1   2   1   3
TestB   tightrope   1   3   2   5
TestB   wikipedia   1   3   1   5


texts: Access Secondary with SQL Agent Account - Security Question

texts:
I have installed three SQL Server Instances on three different server and created an availability group using all three instances.
The sql server engine service runs on all three instances with the same domain account.
The sql server agent service runs with the same dedicated domain account on all three instances.

Now I want to create a agent job which can be run and accomplishes the following tasks:

Check if job runs on primary, if not abort
Remove Database from availability group
Restore the database
Drop old databases from secondaries
Add restored database to availability group.

My Problem occurs in step "Drop old database from secondaries". I thought I could accomplish this using SQLCMD with powershell. I thought, as I use the same domain account for the agent on all three instances it should be possible to connect from the job using integrated security. But it is not possible for the agent to connect to the secondaries. I think I found out that the reason therefore is, that the agent connects to the database using a local virtual account NT-SERVICE\SQLAGENT which has a different SID on all three instances. But now I wonder what would be the best way (from a security point of view) to accomplish the task. I see the following options:

Adding the agent service account domain user to all three instances as login and grant sysadmin rights. I cannot tell why but this feels a little bit strange although it would be very easy.
Create a proxy account for the step and add it as login on the instances using sysadmin rights. I cannot see any difference to option 1 except it gets more complicated.

I tend to use option one but would like to know if option two would be better and if yes, why. Maybe there are other options as well. I "stumbled" also over group managed service accounts although this is not an option at the moment.


texts:
I am connecting to same db from 2 different machines using SQL Developer and querying a table which stores timestamp.
But to my surpise the time value returned by query has a gap of one hour on different machine. How is this possible as we are conencting to same DB? and whats the solution.


texts: Checking for multiple identical values in a Posgresql array

texts:
I have a simple table in Postgresql:

CREATE TABLE data (id integer, values integer[]);
INSERT INTO data VALUES (1, '{1,2,3,4,5}');
INSERT INTO data VALUES (2, '{1,1,2,3,4,5}');
INSERT INTO data VALUES (3, '{1,1,2,1,3,4,5}');

My basic query for values is something like:

SELECT id FROM data WHERE values >@ ARRAY[1,2];

I am trying to select rows with multiple copies of the same value, e.g.

SELECT id FROM data WHERE values >@ ARRAY[1,1,3];

Since values are compared one by one, the query above will match all 3 rows, while I would like to match only IDs 2 and 3, so ones where there are at least two copies of 1 in the 'values' array. Similarly

SELECT id FROM data WHERE values >@ ARRAY[1,1,1,2];

would match only ID 3.

Any pointers on how to proceed, or which functions to look into?

Thanks.


texts: Adding a check constraint on a new column with default value on a large table

texts:
I have a large table (1–2m rows) and need to add a column. It is an integer column with a default of 0. I am also adding a check constraint that column >= 0.
Currently, I am making the change in these steps:

Add the column with the default
Add a check constraint with NOT VALID so it doesn't lock the table
VALIDATE CONSTRAINT

A coworker brought up that Postgres could be smart enough to know that adding a column with a default and then, in the same transaction, immediately adding the check constraint. Since the default passes  the check constraint, all rows are guaranteed (I think?) to pass and so it wouldn't need to do a full table scan.
Is that correct? Are there better ways of doing this?


texts: sp_send_dbmail fails when mailing queries, but no error returned, query just hangs

texts:
I have an issue SQL Server 2005.

sp_send_dbmail fails when mailing out with the result of a query (inline or attatchment).

No error message is given / reported, the query just keeps executing but nothing returned (have left it for 28 minutes so far) with no time out etc.

Mails go out fine when no queries are involved.

Does anyone know how to solve this?


texts: Oracle: deleting all services associated with a SID

texts:
Is the situation when an Oracle instance is accessible via its SID, but not a single Service Name, technically feasible?

Is it possible to delete all Service Names associated with a given SID?

I have already set service_names of my XE instance to an empty string:

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      XE
db_unique_name                       string      XE
global_names                         boolean     FALSE
instance_name                        string      xe
lock_name_space                      string
log_file_name_convert                string
service_names                        string

Still, XE and XEXDB show up in the services list:

SQL> select name from sys.v$active_services;

NAME
----------------------------------------------------------------
XEXDB
XE
SYS$BACKGROUND
SYS$USERS

SQL> SELECT name, enabled FROM sys.dba_services;

NAME                                                             ENA
---------------------------------------------------------------- ---
SYS$BACKGROUND                                                   NO
SYS$USERS                                                        NO
XEXDB                                                            NO
XE                                                               NO

and an attempt to delete any of them fails:

SQL> exec dbms_service.delete_service('XE');
BEGIN dbms_service.delete_service('XE'); END;

*
ERROR at line 1:
ORA-44305: service XE is running
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_SERVICE", line 454
ORA-06512: at "SYS.DBMS_SERVICE", line 343
ORA-06512: at line 1


texts:
Have no idea how indexing works in SQL Server.

Working with a SQL Server 2012 Intel Xeon 2.2 GHz dual core 8 GB RAM.

Will indexing a nvarchar(128) in addition to int primary key cause any performance issue for a 1000 row table which has a nvarchar(max) as one of its columns?

This will be part of website that gets average 3000 hits a day.


texts:
I am running MySQL 5.1 on a CentOS 6 server with 16G ram. I got also apache on it.
MySQL is constantly using too much cpu.
top - 17:40:36 up 112 days, 12:13,  1 user,  load average: 1.24, 1.72, 1.82
Tasks: 222 total,   1 running, 221 sleeping,   0 stopped,   0 zombie
Cpu(s): 12.3%us, 11.6%sy,  0.4%ni, 72.9%id,  2.8%wa,  0.0%hi,  0.1%si,  0.0%st
Mem:  16329040k total, 12946136k used,  3382904k free,   587252k buffers
Swap:  1051832k total,  1050988k used,      844k free,  4986272k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
25368 mysql     20   0 8854m 5.3g 5064 S 295.4 33.9  11912:37 mysqld
 1757 apache    20   0  982m  80m  28m S  2.0  0.5   0:03.04 httpd
 2494 apache    20   0  878m  68m  27m S  2.0  0.4   0:00.92 httpd

my.cnf
[client]
default-character-set=utf8

[mysqld]
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
bind-address=127.0.0.1

key_buffer        = 800M
max_allowed_packet    = 32M
thread_stack        = 256K
thread_cache        = 8
thread_cache_size    = 20
thread_concurrency = 4
max_connections        = 510
table_cache            = 9000
interactive_timeout = 80
tmp_table_size = 540M
max_heap_table_size = 540M
max_connect_errors = 150
wait_timeout = 20
read_rnd_buffer_size = 1024K
connect_timeout = 10
query_cache_type = 1
query_cache_limit   = 40M
query_cache_size        = 128M
innodb_buffer_pool_size = 256M

join_buffer_size = 14M
sort_buffer_size = 14M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

SHOW FULL PROCESSLIST; is returning me on average 2-5 queries.
Here is the output of SHOW VARIABLES; command.
We got MyISAM and InnoDB tables in this system.
Thank you for any suggestions.
EDIT 1
Here is the vmstat output.
EDIT 2
SHOW GLOBAL STATUS output is available below in the comments.


texts: Is timestamptz preferred when timezone logic is performed by the client?

texts:
I'm in the process of migrating a web application from SqlServer to PostgreSQL and I'm trying to figure out which type to replace datetime2 with.

The general advice seems to be always use timestamptz, and never use timestamp. The reasons given tend to be along the lines that timestamp and timestamptz are stored the same regardless (so no performance penalty) and timestamptz auto-converts to the timezone of the connection. Ignoring timezones altogether in Rails and PostgreSQL | Stack Overflow

Unfortunately my legacy .NET codebase is very inconsistent with datetimes and we usually render in UTC regardless of the users timezone. More recent code has been using NodaTime and it's Instant class, but we rarely have to deal with times and displaying just date has been "close enough". My understanding of using NodaTime properly, however, is to convert an Instant to LocalDateTime as late as possible - and not in the database.

In addition to this, I'm not entirely sure how Postgres knows the correct timezone of the "current user". I know you can set the timezone specifically as a session parameter SET TIME ZONE 'UTC';, are you expected to do this for every connection as appropriate for the "current user"? If so, is this reset whenever the connection is retrieved from the connection pool? I also see that Npgsql has the ability to set a timezone for a connection string, presumably this isn't appropriate if it's per user?

All this leads me to think the best option is to use timestamp for all datetimes, and use application logic to convert to local datetime. I guess another option is to use timestamptz for all datetimes, force the connection to use UTC in the connection string, and use application logic to convert to local datetime. However I worry that Postgres will perform extra work in doing a no-op conversion between UTC and UTC.

TLDR: Is timestamptz still preferred if the application always inserts/reads UTC and converts to local datetime itself?


texts: Permission denied in file trying to import

texts:
When I try to use \i on a file that is not in the psql.exe folder it says C:: permission denied. For example I have a file with SQL command at C:\Users\Work\Desktop\School Work\load_database.sql and when I type \i "C:\Users\Work\Desktop\School Work\load_database.sql" it says C:: permission denied. How can I fix this?

I found a work around here where you copy the .sql file to the same folder as psql.exe is in.

By the way, does \i stand for import?


texts:
First, I am not a DBA. Second, the person who set up mariadb left company recently.
On a corporate system, we have mariadb 5.5.2 running on two Dell servers, CentOS 7.3. Servers are about 16 months old. Never rebooted until July 2017. Noticed after that that data from server 1 (master) was not updating on server 2, which is slave. Missing some rows.
Following notes on mariadb replication here: https://mariadb.com/kb/en/the-mariadb-library/setting-up-replication/
Can connect slave server 2  to master server 1 with this:
CHANGE MASTER TO
MASTER_HOST='SOMEIP',
MASTER_USER='esg',       <<< -- replication user, i think
MASTER_PASSWORD='*********',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000012',
MASTER_LOG_POS=5067,
MASTER_CONNECT_RETRY=10;

On server 2, SHOW SLAVE STATUS shows a connection to the master SOMEIP.
When I run this command, from the link above:
GRANT REPLICATION SLAVE ON *.* TO esg;

I get:

ERROR 1045 (28000): Access denied for user 'esg'@'localhost' (using password: YES)

Running same command on master server 1 as --skip-grant-tables, user root, says operation not permitted under skip-grant-tables.
Wondering which way to go here. esg is only user I know about.

I set up replication with these directions:
http://www.techrepublic.com/article/how-to-set-up-database-replication-with-mariadb/
Currently see this on slave:

Slave_IO_State: Waiting for master to send event.

All other variables like Seconds_Behind_Master=0 look good. Slave_SQL_Running, Slave_IO_Running are YES. Never see replication tables/databases on slave.
Why do tutorials say to copy databases from master to slave? Replication should handle this.
Background
Added configurations for bind-address=0.0.0.0 to master/slave on both master and slave and create unique user ids for each of two affected servers. Get master_log_pos and master_log_file from master. Run these commands:
GRANT REPLICATION SLAVE ON *.* TO root;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'someIP' IDENTIFIED BY 'somePassword' WITH GRANT OPTION;

On slave, connect to master with command like this:
CHANGE MASTER TO MASTER_HOST='SOMEIP', MASTER_USER='esg',
MASTER_PASSWORD='*********', MASTER_LOG_FILE='master1-bin.000012',MASTER_LOG_POS=5067;
START SLAVE;


texts: SQL Server Configuration Manager not showing services

texts:
My SQL Server 2014 Configuration Manager is empty except for SQL Server Integration Services 12.0. It should show a bunch of other services, Like SQL Server, SQL Browser, SQL Agent, etc. I found a related question, and did the solution listed there. I successfully changed the value, but it didn't fix my issue. Here's the question/solution I tried:

https://dba.stackexchange.com/a/5208/125955

I tried installing another instance of SQL Server but those services do not show up either. The Database Engine is for sure installed, I can connect to the server and do stuff via SQL Server Management Studio.


texts: inner joins with where clause for no values?

texts:
I have 2 tables :

Table1

Parent(varchar) Child (varchar)

Table2

C1(varchar)    PC (varchar)

Sample data:

Requirement - I need Table2.C1 values for which column Table2.PC = Table1.Child , but Child values must be different from Table1.Parent's values..

I'm using below query in mysql:

select distinct(C1),Child,PC
  from Table2 inner join Table1
    on Table2.PC=Table1.Child
 where Table1.Child not in (select Parent from Table1);

It is giving empty set, but there are values in Child which is same as in PC, but not in Parent....

Where I'm getting wrong?


texts: Microsoft SQL linked server to Oracle data not updating

texts:
I need to pull data from an Oracle database running on a remote machine and use it on my sql server instance. I need to check these tables periodically for updated or new records.

I have created a linked server connection to the oracle db. I am able to select data from tables using the following syntax.

SELECT * FROM OPENQUERY(CUSTOMER_HOST, 'SELECT * FROM dc_15.MISSIONPLAN')

This works, I get the data I expect. However I noticed when the data on the remote server changes (adding, removing or modifying records) the changes aren't selected. The same data is pulled as before. If I alter the table on the oracle db, and run the select again, I get the most recent data. However now the data seems to be frozen again in this state, unless I alter the table again.

Is there some sort of refresh I'm supposed to invoke on the sql server side? Or is there something I need to have triggered on the oracle db to say the data has been updated?

To interface with the DBs I'm using Miscrosoft SQL Server Management Studio, and Oracle SQL Developer.


texts: Restroring MYSQL databse from .frm and .ibd files (database does not exist error)

texts:
please help ...

I want to restore MYSQL database using .frm and .ibd files (that's all I am having). here is additional information..

I reinstalled same version of WAMP without taking backup of mysql data. Now i am remain with "BIN" directory which contains some .frm and .ibd files. When i opend phpmyadmin i can find  My old database and table names in browser but when I click on it it says "'xyz'table doesn't exist". But exceptionally one of table is working and displaying it's data. all my tables were of  innoDB.


texts: SQL Server 2016 (SP2-GDR) - correct upgrading path

texts:
I've found in my organization VM with SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64), which I would like to patch to the latest available version.
Can I just install CU15 (KB4577775) on it or there is more complex path?
Best Regards


texts: How can I calculate time spent at work in a single column?

texts:
I would like to calculate the amount of time spent working from rows of data that contain a single date/time field.

The first occurrence on a given day can be considered the start time, with the next occurrence being the end time, alternating back and forth on any given day.  So if I start at 9:00am and go on lunch at 12:00pm, return from lunch at 12:30pm, then leave for the day at 5:00pm, my data would look like:

2013-03-07 09:00:00
2013-03-07 12:00:00
2013-03-07 12:30:00
2013-03-07 17:00:00

Here is a further sample (in my locale we use . as the date separator):

22. 2. 2013 9:09:00
22. 2. 2013 13:28:09
25. 2. 2013 9:43:16
25. 2. 2013 15:15:19
25. 2. 2013 19:30:00
25. 2. 2013 21:30:00
26. 2. 2013 8:27:54
26. 2. 2013 16:31:51
27. 2. 2013 8:52:59
27. 2. 2013 15:08:35
28. 2. 2013 8:37:54
28. 2. 2013 16:37:08
28. 2. 2013 18:30:00
28. 2. 2013 20:30:00

I would like to know the total time spent at work for any given day, and would also like to be able to tally the time spent over, for instance, any given week or month.


texts: How to quota a CDB only tablespace to a CDB user?

texts:
I can create a CDB tablespace like this: (all using: sqlplus / as sysdba)

-- create unique table space for admin
CREATE TABLESPACE admints DATAFILE '/path/to/admints.dbf' SIZE 20M AUTOEXTEND ON;

I can create a CDB user with a default tablespace "system" since all PDBs have a "system" tablespace:

-- create admin user on CDB (the defaut tablespace is "system")
CREATE USER C##admin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE system QUOTA UNLIMITED ON system ACCOUNT UNLOCK;

I can't create my user with default tablespace "admints":

CREATE USER C##admin IDENTIFIED BY P@ssw0rd DEFAULT TABLESPACE admints QUOTA UNLIMITED ON admints ACCOUNT UNLOCK
*
ERROR at line1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ORCLPDB
ORA-00959: tablespace 'ADMINTS' does not exist

And I can't give quota to my created user on my created tablespace since it only on the CDB and didn't been created for each PDB. is that correct?

ALTER USER  C##admin quota unlimited on admints unlimited
*
ERROR at line1:
ORA-65048: error encountered when processing the current DDL statement in
pluggable database ORCLPDB
ORA-00959: tablespace 'ADMINTS' does not exist

The "admits" tablespace has been created once only for the CDB, not for each PDB.
I want to create the C##admin user's tables on the "admits" tablespace on the CDB and have an unlimited quota on it.
I want the C#admin to have at least read access on all PDBs, so I can't unload them.
Is it possible?


texts: How Can I restore a database to a point of time without Old backup?

texts: I have a database in full recovery mode with five tables. I accidentally deleted one table. I don't have any old backups. Can I restore the database with five tables?


texts: What is meant by the RMAN Duplicate "noncurrent point in time"?

texts:
When doing a RMAN DUPLICATE from backup connected to the target, how current does the duplication make the database when no until time is set?

The closest thing I can find in the documentation is the following statement:

  Restores and copies the duplicate data files and recovers them with
  incremental backups and archived redo log files to a non-current point
  in time.

How current is this non-current point in time? Does it apply to all the Archive Logs it has available to it at the start of the duplicate, or are they perhaps only available when it reaches the recovery part of the duplicate?


texts:
Typically when an oracle object goes invalid due to a dependent object being modified, the next execution of the invalid object will compile it and everything will be execute as expected (assuming no errors caused by the modification). This works as expected for standard table triggers:

-- connect to DB
connect myschema@TESTDB

-- Create table
create table myschema.t as select * from all_objects;

-- Create before insert
create or replace trigger myschema.trg before insert on t
for each row
begin
:new.object_id :=12;
end;
/

-- Grant insert privilege to table
grant insert on myschema.t to otherschema;

-- Modify underlying table to make trigger go invalid
alter table myschema.t drop column owner;

-- Connect as otherschema
connect otherschema@TESTDB

-- View status of trigger (invalid)
select object_name, status from dba_objects where owner = 'MYSCHEMA';
OBJECT_NAME       STATUS
-----------       -------
TRG               INVALID

-- insert record into myschema.t
insert into myschema.t
select OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,
DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,
TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,
NAMESPACE,EDITION_NAME
from all_objects
where rownum=1;

-- View status of trigger (valid)
select object_name, status from dba_objects where owner = 'MYSCHEMA';
OBJECT_NAME       STATUS
-----------       -------
TRG               VALID

All of that is as expected... now lets look at a logon trigger:

-- Connect as privileged schema
connect mydba@TESTDB

-- Create table in privileged schema
create table mydba.test_table ( c1 varchar2(100), c2 date );

-- Create logon trigger
create or replace trigger mydba.test_logon_trg
after logon on database
declare
v_variable varchar2(100);
begin
select c1 into v_variable
from mydba.test_table;
exception when no_data_found
then
  null;
end;
/

-- Modify table to make trigger go invalid
alter table mydba.test_table modify ( c1 varchar2(200) );

-- Check status of trigger ( invalid )
select owner, object_name, status
from dba_objects
where owner = 'MYDBA' and object_name = 'TEST_LOGON_TRG';

-- Connect as non-privileged user (if you use a privileged, i.e. DBA,  account it bypasses the trigger)
connect myschema@TESTDB

ORA-04045: errors during recompilation/revalidation of
MYDBA.TEST_LOGON_TRG
ORA-01031: insufficient privileges

It looks like the database tries to compile the invalid trigger as expected, but it throws a privilege warning. The MYDBA schema has the DBA privilege and so it should have all the privileges necessary.


texts:
Using the Import/Export Wizard, I ended up with an annoying problem:

  Error 0xc02020f4: Data Flow Task 1: The column "pinNumber" cannot be processed because more than one code page (874 and 1252) are specified for it.

Looks like a different codepage (SQL_Latin1_General_CI_AS vs Latin1_General_CI_AS), so I changed those to match: Latin1_General_CI_AS for both.

However, the wizard still throws an error and detects two codepages - I even checked the tables and both source and destination show the same Latin1_General_CI_AS collation.


texts: Does a trigger in a transaction only fire when the transaction is committed?

texts:
I'm a developer, but I turn to the dba community for the following question.

I'm writing an integration test in C#. In this test, I start a transaction first, and always rollback so nothing is committed to the database, regardless of wether the test succeeds or fails.

After starting the transaction, I insert something in a table. There is a trigger for insert on this table, but I get the impression this trigger is only triggered when I commit my transaction.

Is this true?

Is there a way for me to make the trigger fire inside the transaction?

So what happens at runtime is:

Insert in table A

Trigger is fired and inserts in table B

What happens in my test is:

Open transaction
Insert in table A
Check table B --> insert hasn't been executed


texts:
I'm in the progress of creating a separate login for users who want to track specific product shippings.

So I got the tables:

Products (Id, Name, Color, Description, ...) Used for storing the products, nothing else
ProductTrackings (Id, Name)
ProductTrackingProducts (Id, ProductTrackingId (FK), ProductId (FK)) Used for storing sold products that are being shipped and can be tracked
ProductTrackingUsers (Id, ProductTrackingId (FK), Username, Password) used for storing the individual users who can track product shipments

But now I'm stuck in how to accomplish my task, the requirements are as following:

A single user needs to be able to track multiple (shipped) Products (via the ProductTrackingProducts table, I suppose?)
A single (shipped) Product (via ProductTrackingProduct) can be tracked by multiple users

So I have a list of shipped products that are available for tracking (ProductTrackingProducts?) and want n users to be able to track the status.

I suppose my database schema is not sufficient or effective in handling that, so I'm asking for help.


texts:
I partitioned my table  day by day. The child tables are look like points_20150830. Every day, I run a cron job to create table and I insert records without using any trigger function. I insert records by calculating day of timestamp.

However, when I run a select query, query plan shows that every child table visited without checking constraints. Also I enabled constraints via "constraint_exclusion = on" in postgresql.conf

I think that the problem may be related to timestamp with timezone but I could not find any solution.

Thanks for your help, here are details

Master table :

CREATE TABLE points (
    point_key bytea,
    users_id integer,
    point_date timestamp with time zone
);
CREATE INDEX points_point_date ON points USING btree (point_date);
CREATE INDEX points_users_id ON points USING btree (users_id);

Child table :

CREATE TABLE points_20150701 (
    CONSTRAINT points_20150701_point_date_check CHECK (((point_date >= '2015-07-01 00:00:00'::timestamp without time zone) AND (point_date < '2015-07-02 00:00:00'::timestamp without time zone)))
)
INHERITS (points);
CREATE INDEX points_20150701_point_date ON points_20150701 USING btree (point_date);
CREATE INDEX points_20150701_users_id ON points_20150701 USING btree (users_id);

Query that I perform :

EXPLAIN (ANALYZE, BUFFERS)
            SELECT
                COUNT(point_key) as points
              FROM points
              WHERE users_id = 100 AND point_date >= '2015-08-25 00:00:00'::timestamp AND point_date <= '2015-08-31 23:59:59'::timestamp

Result of the query :

http://explain.depesz.com/s/VDI0


texts:
I have a heavy load DWH server.
I wish to create a copy of that server for report purposes (Cognos reports to be exact).
In today's situation I have many reports and can cause locks on other processes, can be locked or get only 1 thread and take a long time to finish.
The reports run on several huge databases.
I have thought of several ideas, non are good enough:

First thing comes to mind is Always On but something tells me that changing the databases to FULL recovery is not a good idea and perhaps the sync will fail all the time because of the transactions size.
Maybe snapshots? Don't know if it is a good idea on production and what about the fact it will kill reports once snapshot is switched.
Any hardware solution I'm unaware - files copy or whatever.
3rd party tools?

Any idea will be much appreciated!
It doesn't have to be online like always on (it's preferred of course) but once a day if there is no other choice.


texts: Can I prevent SQL 2008 Server Management Studio's intellisense from autocompleting with the longest word?

texts: In SQL 2008 Server Management Studio: is there a way to keep intellisense from always picking the longest word to autocomplete?  For example, I could be trying to type the word client and it'll autocomplete with clientdetails.


texts:
To improve performance of a query I set up an in-memory table and fill it with data. Of course to increase speed I will use an index on the data stored in the in-memory table.

create table helper (
    id int, index using hash(id)
) engine = memory;

insert into helper (id)
select id from source;

[select that uses IDs stored in helper]

Now the problem I anticipate is that the actual query will be run prematurely - before the index is ready to use. How do I know how long I have to wait?

I think executing CREATE INDEX ... does the job because it will halt the sequence of SQL statements until it is done. But it seems odd to create the index again, when the propper place to set up an index is within the CREATE statement of the table.

(I am using MySQL)


texts: Transact-SQL script equivalent to the option: Apply Tracked Changes

texts: Could anybody tell me what is the transact equivalent script of using the: Apply Tracked Changes menu?


texts:
BEGIN
    declare @t1 table(
date1 date,
EmpID int,
bnumber varchar(24),
Name varchar(50),
Depname varchar(50),
mintime time,
maxtime time,
Status1 varchar(50))

insert into @t1 exec dbo.InOut2 @fromDate=@fdate,@toDate=@tdate

select distinct EmpID,
case when Status1='Present' then count (date1) end as 'Present',
case when Status1='Late-Absent' then count(date1) end as 'Late-Absent',
case when Status1='Absent'then count(date1)end as 'Absent' from  @t1
where date1 between @fdate and @tdate
group by EmpID, Status1

my output is like this

empid present late-absent  absent
124   null     null        3
124   null      5          null

but I want to see the output like these

empid  present  late-absent   absent
124     null          5           3

EDITS: - jcolebrand (brought in "answers" as edits with verbiage cleanup)

Instead of case what can I use to do this:

max(case when Status1='Present' then count (date1) end) as 'Present' ,

When I use this I get:

  Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

The result is still same I do not get my expected result, it's still

empid   present   late-absent    absent
124      0          0              3
124      0          5              0


texts: Show the matching record only for the 3 rows

texts:
I have 2 tables box_data and box_data_logs, both with same fields (exact same field names and a same number of records), I am trying to match only the following 3 records to see if in those rows in both the tables the values are 101, 201, 301. If the record matches show the matching record

I tried the following buy it throws an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM box_data UNION ALL SELECT box_data_log.id , box_data_log.diIndex, box_data_' at line 2

Here is what I tried

SELECT box_data.id , box_data.diIndex, box_data.diMode, box_data.diStatus,
FROM box_data
UNION ALL
SELECT box_data_log.id , box_data_log.diIndex, box_data_log.diMode, box_data_log.diStatus,
FROM box_data_log

The table structure with the values

CREATE TABLE IF NOT EXISTS `box_data_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `box_name` varchar(255) DEFAULT NULL,
  `diIndex` tinyint(1) NOT NULL,
  `diMode` tinyint(1) NOT NULL,
  `diStatus` tinyint(1) NOT NULL,
  `status` varchar(125) DEFAULT NULL,
  `last_updated` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

INSERT INTO `box_data_logs` (`id`, `box_name`, `diIndex`, `diMode`, `diStatus`, `status`, `last_updated`) VALUES
(7, 'boxa', 0, 0, 0, ' ', '2017-03-02 10:49:35'),
(8, 'boxa', 1, 0, 1, ' ', '2017-03-02 10:49:35'),
(9, 'boxa', 2, 0, 0, ' ', '2017-03-02 10:49:36'),
(10, 'boxa', 3, 0, 0, ' ', '2017-03-02 10:49:36'),
(11, 'boxa', 4, 0, 0, ' ', '2017-03-02 10:49:36'),
(12, 'boxa', 5, 0, 0, ' ', '2017-03-02 10:49:36'),
(13, 'boxa', 6, 0, 0, ' ', '2017-03-02 10:49:36'),
(14, 'boxa', 7, 0, 0, ' ', '2017-03-02 10:49:36');


texts:
I have 2 tables (Oracle database):

ANEXA2A_TABEL1:

id  fk_id_anexa pet1    pet2    alt1    alt2
1   123         10      1       20      2
2   321         11      2       10      3

ANEXA2A_TABEL2_ROWS:

id  fk_id_anexa tip cant
1   123         PET 100
2   123         PET 10
3   123         ALT 200
4   123         ALT 20
5   321         PET 11
6   321         ALT 22

And I need to create a select (used after that in a view) to have a column like in that example:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I tried:

SELECT a2a.ID, a2a.FK_ID_ANEXA, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'PET') AS PET3,
    (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS where TIP = 'ALT') AS ALT3
FROM ANEXA2A_TABEL1 a2a
    INNER JOIN ANEXA2A_TABEL2_ROWS a2a2
        ON a2a2.FK_ID_ANEXA = a2a.FK_ID_ANEXA

but I get something like:

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22
2   321     11      2       11      10      3       22

how can I get just that?

id  fk_id_a pet1    pet2    pet3    alt1    alt2    alt3
1   123     10      1       110     20      2       220
2   321     11      2       11      10      3       22

I guess I have to use a Group By - but I don't know how.
Thanks!