texts:
Please bear with me as I am relatively new to SQL. I am using MySQL and trying to run a query against a DB with two tables ('Network', and 'Server'). When I trying a portion of the query it executes without fail, however, when I try and add to it, I receive nothing but errors ranging from 1046 to 1140 to 1241. I am sure it is a matter of syntax and formatting, but I cannot figure it out. Please help. Below is my query:

SELECT (SELECT CONCAT(ROUND(SUM(server.Tech=7)/COUNT(server.Tech) * 100,2), '%') AS Server,
       CONCAT(ROUND(SUM(network.Tech=7)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server) AS V,
    (SELECT CONCAT(ROUND(SUM(server.Tech=2)/COUNT(server.Tech) * 100,2), '%') AS Server,
       CONCAT(ROUND(SUM(network.Tech=2)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server) AS J
    (SELECT CONCAT(ROUND(SUM(server.Tech=4)/COUNT(server.Tech) * 100,2), '%') AS Server,
       CONCAT(ROUND(SUM(network.Tech=4)/COUNT(network.Tech) * 100,2), '%') AS Network
           FROM network join server) AS K


texts:
I am having trouble decomposing a relation to 3NF/BCNF.

I have determined that for the relation:

R (A,B,C,D,E,F,G,H,I)

where:

{A -> B,C}
{E -> F}
{D -> I}
{A,D -> G}
{G -> H}

...the primary (only) key is A,D,E.

I have determined that the relation is not in 2NF, and therefore also not in 3NF and BCNF, so I must start by decomposing it into 2NF.

I have worked through the steps and come up with the following decomposition:

+----------+-----+------------+
| Relation | Key | Attributes |
+----------+-----+------------+
| R1A      | A   | G,B,C      |
| R1B      | G   | H          |
| R2       | D   | I          |
| R3       | E   | F          |
+----------+-----+------------+

Is this the correct decomposition for 3NF/BCNF?


texts: Postgres: Insert result of join as an array?

texts:
Here's what I have:

=# SELECT * FROM users;
 id |     name
----+---------------
  1 | Paul Gilbert
  2 | Bob Dylan
  3 | Cliff Downing

=# SELECT * FROM regs;
 reg_id | user_id
--------+---------
      1 |       1
     11 |       1
    111 |       1
      2 |       2
     22 |       2
      3 |       3

Here's what I want to obtain:

=# SELECT * FROM joined;
 id |     name      |       regs
----+---------------+-------------------
  1 | Paul Gilbert  | {1, 11, 111}
  2 | Bob Dylan     | {2, 22}
  3 | Cliff Downing | {3}

MWE:

-- create users table:
DROP TABLE users;
CREATE TABLE users(
   id    INT      PRIMARY KEY  NOT NULL,
   name  VARCHAR               NOT NULL
);
-- populate users table:
INSERT INTO users (id, name)
VALUES (1, 'Paul Gilbert'),
       (2, 'Bob Dylan'),
       (3, 'Cliff Downing');

-- reg table:
DROP TABLE regs;
CREATE TABLE regs(
   reg_id   INT  PRIMARY KEY  NOT NULL,
   user_id  INT               NOT NULL
);
-- populate reg table:
INSERT INTO regs (reg_id, user_id)
VALUES (1,   1),
       (11,  1),
       (111, 1),
       (2,   2),
       (22,  2),
       (3,   3);

-- joined table:
DROP TABLE joined;
CREATE TABLE joined(
    id    INT,
    name  VARCHAR,
    regs  INT[]
);

Here's my effort so far:

BEGIN;
  INSERT INTO joined (id, name)
       SELECT id, name
         FROM users;

  INSERT INTO joined (regs)
       SELECT ARRAY(
                   SELECT reg_id
                     FROM regs
               INNER JOIN joined
                       ON joined.id = regs.user_id
                   );
COMMIT;

It doesn't work:

 id |     name      |               regs
----+---------------+-----------------------------------
  1 | Paul Gilbert  |
  2 | Bob Dylan     |
  3 | Cliff Downing |
    |               | {1,11,111,2,22,3}

Edit

Solution by a_horse_with_no_name works great.

Do you think there's a way to add yet another join in a single expression?

CREATE TABLE ldap.univer_users(
  first_name  VARCHAR,
  middle_name VARCHAR,
  last_name   VARCHAR,
  code        VARCHAR,
  doc_number  VARCHAR,
  reg_numbers VARCHAR[]
);

BEGIN;
    INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, reg_numbers)
    SELECT
        d."firstName", d."middleName", d."lastName", d.uid, array_agg(r."regNumber")
    FROM
        student_data d
        JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
    GROUP BY d.uid;

    UPDATE ldap.univer_users
    SET doc_number = p.doc_number
    FROM (
            SELECT student_datum_uid, substring(number FROM '....$') AS doc_number
            FROM student_passports
            WHERE "passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
         ) p
    WHERE code = p.student_datum_uid;
COMMIT;

This works with my data, but I'm curious is there a way to put it into a single insert statement.

Edit 2

Trying to unite all in a single expression:

INSERT INTO ldap.univer_users (first_name, middle_name, last_name, code, doc_number, reg_numbers)
SELECT
    d."firstName", d."middleName", d."lastName", d.uid, substring(p.number FROM '....$'), array_agg(r."regNumber")
FROM
    student_data d
    JOIN student_reg_numbers r ON d.uid = r.student_datum_uid
    JOIN student_passports   p ON d.uid = p.student_datum_uid
      AND p."passportType" IN ('Паспорт РФ', 'Паспорт иностранного гражданина')
GROUP BY d.uid;

Postgres says

column "p.number" must appear in the GROUP BY clause or be used in an aggregate function


texts:
Is it possible to achieve that a client sends an event to the MySQL server and the server sends this event to all clients? How can clients get this event?

Sometimes a user sends some data to another user that use the same application by setting the second user_id in the working table.

I want to tell the second user immediately what happens.


texts: Stored Procedure Is Slower Than Query Due to Collation

texts:
I'm running two servers with MySql installed.  I'm having an issue with a slow stored procedure (approximately 3 secs vs 0.1 seconds), solely on my production server, that is very quick on development and as a stand-alone query.  On my production server, I have a stored procedure which simply does:

SELECT * FROM table WHERE uid = p_uid;

p_uid is specified as: p_uid VARCHAR(36) and takes an input like:

'e42018af-e0c5-11e5-a505-f8b156b860fc'

The column is VarChar(36) as well.  When I create a second stored procedure that returns the EXPLAIN, it shows plainly that the index is not used.  The index is used appropriately if I execute the query outside of the stored procedure.

I've seen a number of mentions of this possibly being a collation issue.  The collation of the database, table, and column are latin1 on both servers.  I've tried changing the query to specify a character/collation:

SELECT * FROM table WHERE uid =
CONVERT(p_uid USING latin1) COLLATE latin1_swedish_ci;

and this seemed to do the trick.  Even though this worked, I'm not happy with this solution as handling different and erratic behavior from development to production is not a scalable or efficient plan.

How can I prevent this from happening again?


texts: Why am I getting LOG_BACKUP as log reuse wait reason?

texts:
I have a few databases all in full recovery mode and with mirroring activated and I do transaction backups every 60 minutes.

When I query sys.databases a few of my databases has LOG_REUSE_WAIT = Nothing but some has LOG_BACKUP and it never seems to change.


texts: MySQL queries waiting for other queries to finish

texts:
We have 30-40 different projects in Python and PHP that update, insert and select more than 1 million rows of data in MySQL DB every day.

Currently we use InnoDB Engine for our tables.

The problem:

We have peaks in MySQL when almost all projects are working and lots of queries are processing in DB. There are main queries that are very important to finish ASAP (high priority) and queries that can wait for finish of main queries (less priority). But as they go to MySQL concurrent it causes main queries to wait finishing of less priority queries.

Questions:

Is there any possibility to release all lock in tables before executing main queries (so they can finish ASAP)? or create locks if it help?
Can we pause the less priority queries execution when start execution main queries automatically?
Can use HIGH_PRIORITY and LOW_PRIORITY in queries help?
Are there some configurations in MySQL that can help?
Can changing tables to MyISAM or other DB engine help?

Let me know your thoughts and ideas. If you need more info, I can provide.

Thanks in advance


texts:
This is normal MySQL query

mysql> select database();
+------------+
| database() |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql>

I notice that when I accidentally put ' in it ... the console seems to stuck there.

mysql> '
    '>
    '> select database();
    '>
    '> '

SQL query such as select database(); doesn't work anymore.

The question is why is this happen with '? Is there any explanation for this behavior?


texts: How can I re-structure my activities table?

texts:
I've created a feed but I'm not sure whether it's okay or not, hopefully will get some answers to correct it if there's anything wrong.

I'm using MySQL.

When a user does something, his activity is saved in activities table as you can see, but this user isn't alone. He has friends. I use feeds table to create a feed for a friend of this user. I'm using fan-out model (I hope it's clear. If there are 10 friends of a user, 10 new records are added to the feeds table when that user does something)

The question is, sometimes users upload photos. When a user uploads a photo, activity_type_id becomes 5 (photo) and I hold images in activity_image to know which post has which image(s). So, if a user is uploaded a photo, I deal with 4 tables to serve these images when a friend of his view his feed : feeds > activites > activity_image > images

activity_image

id
activity_id
image_id

activities

id
user_id
activity (text)
activity_type_id (text, photo or something else) (foreign key from activity_type table)
created_at
(I removed other columns since they are pretty irrelevant)

feeds

id
subscriber_id (foreign key from users table)
activity_id (foreign key from activities table)
created_at

I was wondering, would it be a good idea to get rid of activity_image table altogether and keep image_id's (even image names, but then I would have violated normalization) in the activities table's image column in an array?

That way I will deal with three tables (feeds > activities > images) tops rather than 4, (2 if I store image paths in the activities table) so that way performance will be better.

What's your opinion on this? What should I do?


texts: Is there a way to have SQL Server format a number into ordinal form?

texts:
Data 1, 2, 3, 4, 11, 21, 99

I want to get 1st, 2nd, 3rd, 11th, 21st, and 99th

I could roll my own, but is there any function built into SQL server that could so this for me?
Corollary:  I will also be looking for a function to turn numbers into words, as in:

72,451 ->  seventy-two thousand, four hundred fifty-one

And would expect it (if it exists) to be in the same family of formatting methods.


texts: How to get value from subquery in mysql?

texts:
I have this two tables:
Article:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price:(decimal) not null,
- cart_id(int) not null, //foreign key
- book_id(int) not null, //foreign key
- image_id(int) not null, //foreign key

Image:
- id:(int) primary key, autoincrement, not null,
- image:(Blob) not null

and
 Book:
- id:(int) primary key, autoincrement, not null,
- title:(varchar) not null,
- price:(decimal) not null,
- image_id(int) not null, //foreign key

I want when inserting into Article to insert also right image_id, here is query where i try to that:
insert into article(title,price,cart_id,book_id)
values('Book1',20.00,387,
(select b.id from books b join article a where b.title =  a.title limit 1))

When i run query like this(without image) it works fine, but i can't figure out query with image_id. So i thought maybe if i can get value from subquery select b.id from books b join article a where b.title =  a.title i can just to thisselect id from image where id= result from that query(i have foreign key book_image in book table btw). Am i totally missing point if not, how to solve this?


texts: Automate SQL Server restore from Prod to Dev

texts:
I would like to setup an automated restore job which takes the latest timestamp .bak file and restores it into the DEV instance.  I currently have a job on the DEV box that uses the UNC path to the prod backup location to restore from.  However, anytime I want to run this restore job I need to go in and manually change the file so that it's pointing to the latest one.  Is there a method, preferably using T-SQL, which will grab the latest .bak file to use in the restore job?  If there is a better method for doing this then I'm open to suggestions.  I definitely don't want to reinvent the wheel.

Below is the current code I'm using to perform the restore.  I have it narrowed down to where I only need to change the database name and backup file location.

DECLARE @DB nvarchar(max)   /** Database to be restored **/
, @SQL nvarchar(max)        /** Dynamic SQL **/
, @disk nvarchar(max)       /** Backup location **/
, @l_mdf nvarchar(max)      /** Logical primary data file **/
, @p_mdf nvarchar(max)      /** Physical primary data file **/
, @l_ldf nvarchar(max)      /** Logical Log data file **/
, @p_ldf nvarchar(max)      /** Physical Log data file **/
, @dp nvarchar(200)         /** Datafile path **/
, @lp nvarchar(200)         /** Logfile path **/

/*** These are the only variables to manually set ***/
SET @db = 'PRODBackup'
SET @disk = '\\PROD_UNC_PATH\MSSQL10.SQL2008\MSSQL\Backup\PRODbackup_2015_04_05_220001_1759944.bak'

/*** This builds a file list table to be populated by executing filelistonly for the backup file ***/
declare @fileListTable table
(
    LogicalName          nvarchar(128),
    PhysicalName         nvarchar(260),
    [Type]               char(1),
    FileGroupName        nvarchar(128),
    Size                 numeric(20,0),
    MaxSize              numeric(20,0),
    FileID               bigint,
    CreateLSN            numeric(25,0),
    DropLSN              numeric(25,0),
    UniqueID             uniqueidentifier,
    ReadOnlyLSN          numeric(25,0),
    ReadWriteLSN         numeric(25,0),
    BackupSizeInBytes    bigint,
    SourceBlockSize      int,
    FileGroupID          int,
    LogGroupGUID         uniqueidentifier,
    DifferentialBaseLSN  numeric(25,0),
    DifferentialBaseGUID uniqueidentifier,
    IsReadOnl            bit,
    IsPresent            bit,
    TDEThumbprint        varbinary(32) -- remove this column if using SQL 2005
)
/*** Insert into aforementioned table ***/
insert into @fileListTable exec('restore filelistonly from disk =''' +@disk+'''')

/*** Set variables based on this table using filters and string manipulation ***/
    /** datafile **/
select @l_mdf = logicalName from @fileListTable where Type='D'
select @p_mdf = PhysicalName from @fileListTable where Type='D'
select @p_mdf = RIGHT(@p_mdf, CHARINDEX('\',REVERSE(@p_mdf))-1)

    /** logfile **/
select @l_ldf = logicalName from @fileListTable where Type='L'
select @p_ldf = PhysicalName from @fileListTable where Type='L'
select @p_ldf = RIGHT(@p_ldf, CHARINDEX('\',REVERSE(@p_ldf))-1)
/*** Set default data and log paths ***/
--select @dp = CAST(serverproperty('instancedefaultdatapath') AS nvarchar(200))
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dp output, 'no_output'
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @lp output, 'no_output'

/*** Execute dynamic SQL ***/
SET @SQL = ('USE [master]
            RESTORE DATABASE ['+@DB+']
                FROM  DISK = N'''+@disk+'''
                    WITH  FILE = 1
                ,  MOVE N'''+@l_mdf+''' TO N'''+@dp + @p_mdf+'''
                ,  MOVE N'''+@l_ldf+''' TO N'''+@lp + @p_ldf+'''
                ,  NOUNLOAD,  REPLACE,  STATS = 5;')
EXEC (@SQL);


texts: SQLAZURE Resource limitations-Max concurrent sessions

texts:
I was trying to select a tier for our internal consumption and going through this limitations in detail..Azure SQL Database resource limits

One particular instance of trouble was below chart and particularly highlighted values

as you see ,from the highlighted ,there is a max concurrent limitation of 60 concurrent logins and 600 concurrent sessions

ASK:
a single query can go in parallel and can have multiple sessions,will those sessions count under this limitation  ?

i tried to test this using Paul White's  article Forcing a Parallel Query Execution Plan like below

DBCC FREEPROCCACHE
DBCC SETCPUWEIGHT(1000)

GO
-- Query to test
some expensive query
GO

DBCC SETCPUWEIGHT(1)
DBCC FREEPROCCACHE

or using

OPTION (RECOMPILE, QUERYTRACEON 8649)

but i  can only go upto 9 sessions,when i checked using below DMV

select * from sys.dm_os_tasks
where session_id=61

and these trace flags  and DBCC options wont work in azure .let me know if i am unclear


texts: Select Into removes IDENTITY property from target table

texts:
I am doing an exercise which removes the IDENTITY property from the target table when using a select into statement.

  source table name: Sales.Orders
  column name: orderid
  column property: int type, not null, defined as a primary key, identity property is defined on the column)
  Column name: custid
  column property: int type, foreign key, null
  target table name: Sales.MyOrders

In the 70-461 exam book - Querying Microsoft SQL Server 2012, chapter 10 p336:

it gives code

ISNULL (orderid + 0, -1) as orderid,
--this will remove IDENTITY property and make column not null

Make custid column not null in target able

ISNULL (custid, -1) as custid, --make column not null

I don't understand these two code items, though it works.

Could someone tell me the concepts behind the code?

The code provided in the book:

select isnull (orderid + 0, -1) as orderid,
isnull (custid, -1) as custid,
empid,
isnull (cast(orderdate as date), '19000101') as orderdate,
shipcountry,
freight
into Sales.MyOrders
from Sales.Orders
where shipcountry = N'Norway';


texts: Can I truncate log in simple recovery mode?

texts: I have a few SQL Server 2005 databases in simple recovery mode. The disk volume in which my transaction logs are stored had only 1GB free, when I started a stored procedure (massive delete in a single table) the log grows a lot and the disk is running out of space, and the stored procedure seems to work indefinetly. How can I manage this transaction log?


texts: Cannot start MySQL service on linux RHEL when pointing datadir to mounted ecryptfs folder

texts:
I moved the data directory for MySQL to an ecryptfs encrypted folder.
When I run service mysqld start as root, the service will not start.

[Warning] Can't create test file /mysql/ip-172-31-29-184.lower-test
^G/usr/sbin/mysqld: Can't change dir to '/mysql/' (Errcode: 13 - Permission denied)

When it used to start before my encrypting the folder

ls -lZ /mysql outputs

drwx------. mysql mysql unconfined_u:object_r:mysqld_db_t:s0 mysql

now

ls -lZ /mysql outputs
drwx------. mysql mysql system_u:object_r:ecryptfs_t:s0  mysql

I think the issue is with how I mounted the encrypted directory.
mount -t ecryptfs /mysql /mysql

mount outputs

/mysql on /mysql type ecryptfs
 (rw,ecryptfs_sig=6860c372da7bc418,ecryptfs_cipher=aes,ecryptfs_key_bytes=16,ecryptfs_unlink_sigs)

I am running Red Hat Enterprise Linux Server release 6.6 (Santiago), MySQL 5.6.22

Any advice is appreciated. Thank you for your help in advance.


texts: Redirecting MySQL errors and slow logs into another database

texts:
I am running a number of MySQL servers as slaves and masters. It is quite annoying to look for slow logs and errors in every server, so I would like to log all the errors into an external database.

    DB1    DB2    DBn ...
      \      |      /
       \     |     /          <-- error logs and slow logs
        \    |    /
[MySQL error & slow log database]

Is there some simple way of doing it?
Or I do need to write some scripts and stuff?

P.S. I hope you appreciate my superb ASCII diagraming skills


texts: How to succesfully set up sp_AllNightLog to create a Reporting Server?

texts:
I would like to set up a secondary, mirrored SQL Server for reporting purposes.

The reporting database doesn't have to be up to date with the primary and can generally lag behind the production database by as much as one day, maybe more.

I've been trying to achieve something like this for a few days now with little to no success, using Brent Ozar's SQL-Server-First-Responder-Kit and sp_AllNightLog.

Generally, the first FULL backup gets restored, and sometimes even a few LOG backups, but then it gets stuck, it stops seeing newer backups and just goes in a loop, doing nothing.

Troubleshooting seems to indicate to me that sp_AllNightLog @Restore = 1 runs into an error when restoring a database, or maybe a LOG file, records that in the restore_worker table, which stops all further restores from being issued. If I clear the error_number (-1) and last_error_date from the table, it starts restoring again but then hits an error again because it starts restoring the FULL backup instead of continuing with LOG restores. Then it logs that error date and error number and stops again.

Another issue is that sp_DatabaseRestore (from the Kit) leaves the restored databases, either from a FULL restore or LOG restore, in an unrecovered state (always uses NORECOVERY and never runs RESTORE WITH RECOVERY). That makes the databases unreadable on the secondary unless I change sp_AllNightLog from using @RunRecovery = 0 to @RunRecovery = 1:

-- Line 1302
IF @restore_full = 0
    BEGIN

        IF @Debug = 1 RAISERROR ('Starting Log only restores', 0, 1) WITH NOWAIT;

        EXEC master.dbo.sp_DatabaseRestore @Database = @database,
             @BackupPathFull = @restore_path_full,
             @BackupPathLog = @restore_path_log,
             @ContinueLogs = 1,
             @RunRecovery = 1,
             @OnlyLogsAfter = @only_logs_after,
             @Debug = @Debug

    END

IF @restore_full = 1
    BEGIN

        IF @Debug = 1 RAISERROR ('Starting first Full restore from: ', 0, 1) WITH NOWAIT;
        IF @Debug = 1 RAISERROR (@restore_path_full, 0, 1) WITH NOWAIT;

        EXEC master.dbo.sp_DatabaseRestore @Database = @database,
             @BackupPathFull = @restore_path_full,
             @BackupPathLog = @restore_path_log,
             @ContinueLogs = 0,
             @RunRecovery = 1,
             @Debug = @Debug

    END

Do I have to delete backup history before I set this up?
Has anyone written a complete guide to setting something like this up?
I genuinely can't seem to find one even while googling.

Any help is greatly appreciated!


texts: Set up common database server for vb.net application

texts:
I have developed a school management software in vb.net. I used in-built database management program of Visual Studio. At first my client requirement was single handed use (i.e. the SW works on only one computer), now suddenly when the software if complete and submitted to client they said to me that work load is very much and they want that multiple user should use the software.

Now I have problem that how do I use my database file (i.e. .mdf) in such a way that multiple users can connect to it with different computer "LOCALLY VIA LAN".

Actually I have done some research and I understood very little which is that I can use SQL Server Express as a server on one machine, import my existing DB file into SQL Server Express, and make some changes in my connection string in VB.net so that it connect to the specified DB.

Can someone please help me with my problem or give me some link to which I can refer.

Thanks.


texts:
I'm a .NET guy that is trying to start using table encryption on some of my Users tables but I could do with some questions being answered first. I don't want just anyone to be able to see these tables.

When should you start encrypting your tables in SQL server, should I encrypt all tables?

How to avoid going to far?

The steps required to encrypt a table?


texts:
For one of my assignments I have been attempting to demonstrate password file functionality. However, I have run into some unexpected behaviors. I have tried searching around for answers but can't find any relevant ones; additionally, behavior differs from what the documentation specifies in some scenarios.

I am using 12c and have made sure that the remote_login_passwordfile parameter is set to EXCLUSIVE.

I'm sure I'm probably just missing some base knowledge as the behaviors are consistent; the problems I am having are as follows:

Deleting the password file itself (without replacing it) and then
doing a subsequent lookup of v$pwfile_users removes all of the
sys-privileged users from the CBD, but users from my PDB still
remain in the lookup. Are PDB passwords stored somewhere other than
that file? How are they showing up if the file no longer exists?
The IGNORECASE option for the new file seems to only have an effect on common users rather than PDB users; newly-assigned CDBs cannot login without the correct case, but PDB users can, again making me wonder about some separate password storage.
In both the CDB and the PDB, the ENTRIES option seems to have no effect - despite setting it to 2, I can continually add way more than 2 users in both containers.
I wanted to demonstrate that the password files actually worked by doing some sort of sysoper operation with one of my newly-privileged accounts, such as shutting the database down. I can do this in the PDB but it will not let me do it in the CDB despite the documentation explicitly stating that sysoper users have that privilege. Am I missing some other piece of information that would allow this to happen? The error I get is insufficient privileges (and yes, I have made sure to connect using as sysoper).

Any insights are greatly appreciated - thanks!


texts:
How To change the column order in sql server 2008 R2?

Eg: My table order is Eid------Ename-----Esalary

Know i want this format like   Eid---Salary----Ename

Thanks.


texts: How often should I run integrity and consistency checks on SQL Server 2008?

texts:
Before updates of our application, I run some checks on the database like CheckAllocations and CheckCatalog, so I know the database is in good shape before the update scripts get executed.

What is recommended for checks like that? How often should they be executed?

Do we need more checks besides these two?

We used to run the CheckIdentityValues as well, but I removed that, because that method will be removed (according to MSDN).


texts: Storage strategy for combination of all table items

texts:
Let's say I've one table with items with the following fields:

id_item
name
description

& one table with the user information, following like:

id_user
email
name

Now I'd like to let vote my user for any combination of all the items (like a head-to-head competition)

The thing is now that I'm struggling to find a suitable database model. Following I can't decide between the following two options:

Create a relation table which creates for each combination of item an own hash. Then I would have a second table storing the results:

CREATE TABLE combination
(
    hash VARCHAR NOT NULL,
    id_1 INT NOT NULL,
    id_2 INT NOT NULL,
    primary key (hash),
    FOREIGN KEY (id_1)
        REFERENCES items(id_item)
    FOREIGN KEY (id_2)
        REFERENCES items(id_item)
);

CREATE TABLE result
(
    id INT NOT NULL,
    hash INT NOT NULL,
    user_id INT NOT NULL,
    vote INT NOT NULL,
    primary key (hash),
    FOREIGN KEY (user_id)
        REFERENCES user(id_user)
);

Create a result table which, creates for each combination and user vote.

CREATE TABLE result
(
    id INT NOT NULL,
    item_id_1 INT NOT NULL,
    item_id_2 INT NOT NULL,
    user_id INT NOT NULL,
    vote INT NOT NULL,
    FOREIGN KEY (item_id_1)
        REFERENCES itme(id_item)
    FOREIGN KEY (item_id_2)
        REFERENCES item(id_item)
    FOREIGN KEY (userID)
        REFERENCES user(id_user)
);

My thoughts are especially about:

checking for which combination the user already answered/voted -> finding the missing
triggers for creating new combination pairs
storage efficiency (saving hash + user or saving 2x ID + user)


texts:
Is there a view or table or relation in information_schema with the error codes or exception names?

My question is in STANDARD sql or in PostgreSQL.


texts: How to list all pending friend requests for a given user ID?

texts:
I have a friendship table like this

A and B are friends if there are (A,B) and (B,A) rows. For example, uid 132 & 67 are friends indicated by (132,67) on 2nd row and (67,132) on ninth row. Rows 4 - 8 are pending requests to uid 132.

My question is how to list all pending requests to uid X? I tried this (X=132):

SELECT a.uid1, a.uid2 FROM tbl_friendship a LEFT JOIN tbl_friendship b ON a.uid1=b.uid2 AND a.uid2=b.uid2 WHERE a.uid1 IS NULL AND b.uid2 =132

Still not right because the result is empty. The expected result is something like:

83, 90, 93, 94, 97

How to fix the query?


texts: First time running a replication on SQL Server 2008. "The process could not connect to subscriber 'SERVER2'"

texts:
I've set up Replication between two remote servers, Using Peer to Peer & Transactional updates. The subscription is all set, but the actual replication doesn't take place.

When viewing the Synchronization Status of the subscription, i get an error, saying "The process could not connect to Subscriber 'SERVER2\DBINSTANCE'."

Clicking on start, this is the error message I get:

TITLE: View Synchronization Status

The agent could not be started.

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1&EvtSrc=Microsoft.SqlServer.Management.UI.ReplUtilitiesErrorSR&EvtID=CantStartAgent&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

SQLServerAgent Error: Request to run job SERVER\DBINSTANCE-DBNAME-DBPUBLICATION-SERVER2\DBNAME-3 (from User sa) refused because the job is already running from a request by Start Sequence 0.
Changed database context to 'DBNAME'. (Microsoft SQL Server, Error: 22022)

For help, click: go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=22022&LinkId=20476

Viewing the Agent History, trying to get more specific into the error, i get the following Error:

Error messages:
The process could not execute 'sp_replcmds' on 'SERVER\DBNAME'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: help/MSSQL_REPL20011
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Source: MSSQLServer, Error number: 15517)
Get help: help/15517
The process could not execute 'sp_replcmds' on 'SERVER\DBNAME'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: help/MSSQL_REPL22037

I have looked each and every one of the errors online, went through all this kind of explanations and changes, and nothing worked.
Many thanks to anyone who can pour some light on the issue.


texts: Postgres: Difference between Tuples inserted and Live tuples

texts:
Many apologies in advance, but I was not able to find this out myself:

What is the difference between live tuples and tuples inserted in postgres statistics?

There's this post, but it doesn't answer the question: pg_stat_get_live_tuples are Representing the number of live [...] rows (tuples) in the table. But what means "live"?

The Postgres documentation doesn't provide answers either:

pg_stat_get_live_tuples(oid) Number of live rows in table
pg_stat_get_tuples_inserted(oid) Number of rows inserted into table

Why do my stats show 213,324,422 inserted tuples, but only 124,510,280 live tuples? (3,087,919 dead tuples)

The PG Admin documentation is also unclear about this. It further says that Tuples Inserted   displays the number of tuples inserted into the database within the last week.

I would like to know the total number of existing entries in my database. Is this live rows or inserted rows?


texts: Learning to Optimize SQL Queries and Understand Execution Plans - Resources?

texts:
I find myself writing more and more SQL queries at work (mostly Oracle 11g, but some SQL Server 2005-2008) and have started creating some pretty complex views for the rest of the analyst team.

They mostly all run quite well, but some of them not so well. So...

How do I learn to tune my queries?
Do I need to learn to read/act on Execution Plans?

And...

What books/websites can you recommend to learn about SQL query tuning 1) in general 2) specifically for Oracle 11g?

We have some good DBAs here, but they're just too swamped to help us tune every query we write.

Most of the books that I've found on Amazon for Oracle all seem to be geared toward overall database optimization and/or were written 8-10 years ago.

Thanks kindly for your advice :)


texts: I have an Oracle database in a remote server. I want to import data from it to SQL Server on a local machine using a stored procedure or job shedule but I don't know how can I do it.


texts: Postgres settings reported differently in pgsql

texts:
We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = 'effective_cache_size'; query. As far as I can understand these commands should be identical. For example

show effective_cache_size;

effective_cache_size
----------------------
 2816MB
(1 row)

vs

select name,setting from pg_settings where name = 'effective_cache_size';

name    | effective_cache_size

setting | 360448

I am trying to figure out the value used by PostgreSQL. I get the same results if I run this as a superuser. Which one do I trust?


texts: Simplest way to check if current user has permission to a MySQL table (from PHP / mysqli) for some desired operation?

texts:
While using an already established mysqli connection to a MySQL database from PHP, I'm looking for a simple way to check if this connection (i.e. the authenticated user for this connection, if any) has some desired access to a certain table (or at least any access to the table whatsoever).

If it matters, the table in question is in another database than the currently USED one, and I'm trying to access it by the following kind of query:

SELECT * FROM some_other_database_on_the_same_server_as_the_currently_used_database.some_table WHERE id = 1

My research so far has only found the ridiculously complex solution of manually parsing and resolving the depressingly messy raw-string output of the SHOW GRANTS command, which I just cannot accept being the only way of doing this!?

Things I have tried (and failed) so far are the following:

1.
To simply perform the desired operation in question against the table in question, and evaluate the error codes if it fails. To my big surprise, a SELECT query (issued e.g. with the mysqli_query() API) simply returns False if I don't have the appropriate permissions for the table in question, without any error code or error message being reported at all (inspected both manually in the debugger on the connection object and by means of the mysqli_error() API). The problem with this is that False will also be returned if e.g. the queried table doesn't exist at all, so I cannot know from this if it failed because of permission problems or because of something completely different.

2.
To search for some kind of equivalent of the HAS_PERMS_BY_NAME command of Microsoft SQL Server, which I always come up empty-handed with though, consistently just being politely herded back by Google to the extremely clumsy SHOW GRANTS command of MySQL.

So my question is, again:

How can I, with no holds (or hacks) barred, check if my mysqli connection has some desired access (or at least read access if nothing else) to a certain MySQL table?

There really must be some easy way of doing this, right?


texts: Query to extract all SQL database roles with INSERT and/or UPDATE privileges

texts:
I've taken over the support for a SQL 2008 database and there are a multitude of custom roles (25+) associated within. I'm trying to figure out which of the custom roles have the ability to perform privileged INSERT and/or UPDATE actions within the database.

Can anyone help me write a query to run against the database which would return a listing of all roles with INSERT and UPDATE privileges?

Any help would be much appreciated, Thank you!


texts: I have multiple sources INSERTing into a MySQL innodb table. My periodic aggregation script never makes accurate aggregates. Why?

texts:
I apologize in advance if this is a repeat. I'm not really sure how to properly ask for what I'm running into.

I have a large InnoDB table set up. I have 3 sources that all INSERT concurrently at a moderately high volume (300-500 INSERT/s).

I have a PERL script running on a cron job every 5 minutes that aggregates data from the last time it ran. It keeps track of the last record it processed by storing the auto_increment value of the last row in a metadata table.

The aggregates are always off. But not by much. This has been so frustrating because it's just plain simple math (SELECT account_id,sum(sold) GROUP BY account_id). I have a suspicion that it has something to do with the transaction isolation (repeatable-read).

I recently found FlexViews which looks very cool and might address my problem. But I was wondering if anyone could:

Tell me what is going on here. Why is this happening?
What can be done to produce 100% accurate aggregates

I'm like 95% sure the auto_increment thing along with transaction commit ordering is hosing me up, but I don't understand enough about DBs to really ask the right question.

Oh, one thing to note, I've already checked over the field types. This issues isn't the result of rounding.


texts: How to write a query to get concatenated names of riders with the same weight?

texts:
I want to retrieve a table with all Riders of the same Weight from a table named Rider, which is defined in this way:

CREATE TABLE Rider(
    name VARCHAR(30),
    weight REAL,
    PRIMARY KEY(name)
) ENGINE=innodb;

The data in it is:

Alan  55
Ana   55
Anne  50
Bill  65
David 60
Julia 55
Peter 65
Stan  60

I want a result like this:

50 Anne
55 Alan Ana Julia
60 David Stan
65 Bill Peter

I can't figure out how to do it. I can only manage to sort them all by weight with:

  SELECT weight, name
    FROM Rider
GROUP BY weight;

All help is greatly appreciated!


texts: Why do we need to compare keys from different table?

texts:
can you please help me make sense of this code?

The question: is to find the pair of students who have registered for the same course in the same semester.

why do I need to check the s1.sid = r1.sid ?
Why can't I just the SID in registration table? I know this gives me duplicates, but I don't understand the reason behind.

My understanding behind querying tables to get results is relatively weak, please pardon me if I am asking a fundamental questions :) thanks for helping!

    select s1.sname, s2.sname, cname, r1.semester
    from student s1, student s2, registration r1, registration r2, course c
    where s1.sid = r1.sid and s2.sid = r2.sid
    and r1.cid = r2.cid and r1.semester = r2.semester
    and r1.cid = c.cid and s1.sid < s2.sid;


texts: order of inserting record is mismatching

texts:
Please check the below table structure and records to be inserted in that table.

CREATE TABLE [tabGeneraltable](
   id int identity,
   [codGenLedger] [uniqueidentifier] NOT NULL,
   [codInvoice] [numeric](18, 0) NULL,
   [accountValue] [numeric](18, 2) NULL,
   [articleValue] [nvarchar](50) NULL,
   [codFinAccount] [int] NULL,
   [documentNbr] [nvarchar](50) NULL,
   [valueDate] [datetime] NULL,
   [insertDate] [datetime] NULL,
   CONSTRAINT [PK_tabGeneralLedger] PRIMARY KEY CLUSTERED([codGenLedger] ASC)
)

insert into [tabGeneraltable]([codGenLedger],[codInvoice], [codFinAccount],
                              [accountValue],[insertDate])
select NEWID(),1,11,232,getdate()
union all
select NEWID(),10,45,214,getdate()
union all
select NEWID(),9,425,410,getdate()
union all
select NEWID(),14,475,356,getdate()

After inserting all records, when executing a simple select statement on this table like

select * from tabGeneraltable

identity column ID is not coming in proper order or records are inserted randomly. (Check the insertDate column value if you insert all records separately.)

Why this is happening?


texts: What happens with statistics when reverting to a database snapshot

texts: Lets say I have a database FOO for which I created a snapshot (named FOO_snap). Now I'll run thousands of queries on this DB. After that I will revert DB to FOO_snap. Will I lose all of the statistics which were created for these thousand of queries?


texts: PostgreSQL will stop logging when logrotate rotate postgresql log file

texts:
I have installed

PostgreSQL 10.6 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.2.1 20180905 (Red Hat 8.2.1-3), 64-bit

And configured:

log_filename = 'postgresql.log'

But I would like to manage log rotations by logrotate instead of internal logrotation by PostgreSQL:

#vim /etc/logrotate.d/postgresql

/var/lib/pgsql/data/log/postgresql.log {
    daily
    rotate 7
    compress
    create 0664 postgres postgres
    missingok
    notifempty
    sharedscripts
}

Where logrotate works as expected but PostgreSQL will stop logging into the rotated /var/lib/pgsql/data/log/postgresql.log file. The only resolution is to systemctl restart postgresql which could be placed inside logrotate postrotate/endscript block but I can't do it this way if this is done on critical production system.

Do you have any advice how to properly logrotate PostgreSQL log file without need of restarting PostgreSQL?

Thanks!

UPDATE:
During reloading of PostgreSQL it looks for changes. I found a workaround how to achieve that PostgreSQL will start to log into rotated postgresql.log file - I need to modify config file, for example comment out/modify log_filename then reload PostgreSQL and revert changes back and reload PostgreSQL again. Because PostgreSQL looks for changes in configuration file. But I would be glad if there is another better solution than touching configuration file.


texts:
Continuing with my school-project, now I have to estimate the size of the database from here to 5 years. They give me a formula, so, in that formula, one of the values is the size of the empty database (when it is created, and doesn't have any tables, index, etc.).

For example, for MySQL, I found here that it is 10 MB.

But, for Oracle, what is the size?

I don't have Oracle or any RDBMS to make queries, it is just a work of research. I've googled a little, but I haven't found for Oracle.

Any version of Oracle (9 or later) is okay.


texts:
My SQL server version is as below

but When i check in summary.txt at this path below

The product version is different which 13.2.5698 as below

Can anyone advise me why it's different in summary.txt files


texts:
I have a website for an event. I set up a cron job to back up the database every hour. The backup command is

mysqldump -u"$DB_USER" -p"$DB_PASS" --database "$DB_NAME" >"${OUTPUT_DIR}/${DB_NAME}.$(date +%Y%m%d-%H%M%S).sql"

# crontab: 30 * * * * /path/to/backup_db.sh

I have 200+ of files like this in the backup directory (pulled from server):

$ ls -1 "$OUTPUT_DIR"
ibug.20190301-133002.sql
ibug.20190301-143001.sql
ibug.20190301-153001.sql
ibug.20190301-163001.sql
ibug.20190301-173001.sql
...

The files aren't very big by themselves, averaging less than 2 MB each but summing up to more than 400 MB.

The top few lines of a dump is

-- MySQL dump 10.14  Distrib 5.5.60-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: ibug
-- ------------------------------------------------------
-- Server version   5.5.60-MariaDB

I want to perform a series of SELECT queries on each dump (data analysis). The queries range from simple SELECT COUNT(*) ones to complex ones with multiple JOINs and multiple subqueries (all are SELECT).

While it's possible, I don't want to write a script to import them one by one and run the queries. I wonder if there is another better approach.

My local machine is running on 10.1.38-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04 (not exactly MySQL, it's Maria).

I have (I believe) enough skills and knowledge in Python and Bash (and regular expressions) for a moderately complex script to batch or automate the job, not sure if this is relevant, though.


texts:
Using: SQL Server 2008 R2

Under what circumstances would you set the Affinity Mask and Affinity I/O Mask options in sp_configure in SQL Server?

We are currently running on a machine with 16 cores dedicated to SQL Server.  We are also seeing slowness in our I/O operations.  Some writes (insert 1 row) can take up to 500 ms.  We would like to support many writes per second.  The process that inserts data can spawn up to 4 threads for executing the insert operations.

I'm considering dedicating 8 cores to the affinity mask and the other 8 to affinity I/O mask.

Is this recommended?


texts: How Should TCP/IP be configured to allow an external application access to SQL via the same port #?

texts:
My SQL Server instance is hosted on an internal network server (10.x.x.x address). It's a named instance currently using dynamic ports. An external application at a third-party hosting location connects to the instance through use of the IP address and port #. After each maintenance window I have to confirm the dynamic port # in use is the same as before as the firewall rule is set to allow connections between application host and database host on that specific port #.

Is this the correct way to ensure the externally-hosted app can connect to the host without having to change port numbers in the connection string or should another port forwarding method be used (i.e., port 9999 on application host is mapped to port 1434 on SQL host)? What configuration manager changes in the Network Configuration section should be applied to allow the external application to use the same port # but internal applications to rely on the Browser server to make the correct mapping?


texts: mysql - can a replica have a different primary key than the source?

texts:
I have a table with PRIMARY KEY (`id`) and I want to change it to PRIMARY KEY (`username`, `id`).  These columns are defined as:
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT '',

This table is within a primary/secondary MySQL topology with binary row replication.  Can I get away with taking the replica offline, changing the primary key, and reconnect it to the source without changing the source?  For clarity, only the primary key index would be different between the source/replica.  All other columns/order of columns would be the same.


texts: Select row with MAX(), or choose one if no MAX() exists

texts:
I have a MySQL InnoDB table with the following columns:

id: UNSIGNED bigint(20) AUTO_INCREMENT (PRIMARY KEY)
name_id: UNSIGNED bigint(20)
time_stamp: decimal(26,9)
event: enum('add', 'init', 'update', 'disconnect', 'remove', 'stopped')
value: longtext
alarm_status: enum('NO_ALARM','READ','WRITE','HIHI','HIGH','LOLO','LOW','STATE','COS','COMM','TIMEOUT','HWLIMIT','CALC','SCAN','LINK','SOFT','BAD_SUB','UDF','DISABLE','SIMM','READ_ACCESS','WRITE_ACCESS')
alarm_severity: enum('NO_ALARM', 'MINOR', 'MAJOR', 'INVALID')

Each row can be uniquely identified by either its id or the combination of its name_id, time_stamp and event.

There can be two rows with the same name_id and time_stamp if one of them has an event of 'add', 'init', 'update', 'disconnect' or 'remove' and the other one has an event of 'stopped'.

I am trying to put together the following query:

For a given time_stamp t0:

For each name_id, of the rows with an event not equal to 'stopped', find the one that has the largest time_stamp less than or equal to t0. (1)

For each name_id, of the rows with an event equal to 'stopped', find the one that has the largest time_stamp less than t0. (2)

For each name_id, return the row from (1) or (2) that has the largest time_stamp. If the time_stamps are the same, return the row from (2).

I believe the following queries work for (1) and (2), but I'm not sure how to do the last step to conditionally select from them:

(1)

SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp <= t0
AND data.event <> 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event <> 'stopped';

(2)

SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp < t0
AND data.event = 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event = 'stopped';

I would also be interested in any suggestions for improving the table structure.


texts: What's the most effective way to back-up / save data on Oracle 11g large data sets?

texts:
We have a database(actually more databases split by primary access path, modulo on a key.) with millions of records (about 400 millions ). Right now I need to save a specific table userData(which has  specific data such username, address, city, state, zipcode, country) remotely(on a different server). I'm wondering which would be the best solution given the following requirements :

The data transfer between the host server (database server) and the
remote server must be as small as possible.

The operation should not be CPU / resource intensive. It should be silent (should not move needle too much on the monitoring dashboard) due the fact we are using a shared  On Demand(as a service) service provider which seems to place a "hold" on our instance when we make too much noise.

The operation must be  fast.
I was thinking if it would be possible to do some "compression" on the fly (e.g. using 7z) and I'm wondering what kind of query should I use to make it "silent " to do not disturb the network / CPU too much .
11g database is used on Solaris . On the remote server I'm planning to use redhat but that can be different of course .


texts: SQL Server DIFFERENTIAL BACKUP question?

texts:
Simple question

At what point is a differential backup considered transactionally consistent?

Is it the creation date of the DIFF, or the time at which it completes?

That is if I restore it what transactions can I expect to have been committed? Those that completed before the time the DIFF started, or those that completed during the DIFF, that is the finished time?


texts: Can I recover a single table from a SQL Server 2012 database backup

texts:
I have accidentally dropped a specific table and I don’t want to restore whole database (it is huge) but instead I want to recover only that table from a backup?

What would be most simplest solution?


texts: Restrict access for Windows administrator

texts: As the SQL Server best practices says, "Windows Authentication mode is more secure than SQL Authentication". And now I want to know: is there a way to protect SQL Server from user with Windows administrator rights?


texts: How to change name for administrator login on azure sql server

texts:
I have myserver.database.windows.net with administrator login John.
I'd like to change it to Peter.
It is possible, if so, how ?
Alter which works with local sql server, doesn't work here

ALTER LOGIN John WITH NAME=Peter


texts: Questions about handling NULLs and empty strings in nvarchar and numeric fields

texts:
I understand that questions similar to these pop up often around here. I have searched before posting these but I didn't find any QA threads that completely answer my questions. In a table, I basically have to treat NULLs, empty strings and (pure) whitespace as 'blanks' and count the number of non-blank cells. The table contains a mix of numeric, bit and nvarchar columns.

Q1 In the table TABLE1, I have a column COLUMN1 nvarchar(32) with the following data distribution:

Value    RowCount
N/A      80             -- string 'N/A'
NULL     20             -- actual nulls

Why does the last of the following queries return unexpected results?

SELECT SUM(CASE WHEN COLUMN1 IS NOT NULL THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, as expected

SELECT SUM(CASE WHEN COLUMN1 NOT IN (NULL, '') THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, as expected

SELECT SUM(CASE WHEN COLUMN1 NOT IN ('') THEN 1 ELSE 0 END)
FROM TABLE1             -- returns 80, but I expected 100.

Q2 I have another column COLUMN2 numeric(18, 0) filled with values with no NULLs or empty strings (but it could contain either/both). But the second of the queries below fails due to a reason I don't understand.

SELECT SUM(CASE WHEN COLUMN2 NOT IN ('', NULL) THEN 1 ELSE 0 END)
FROM TABLE1             -- returns full rowcount (100), as expected.

SELECT SUM(CASE WHEN COLUMN2 NOT IN (NULL, '') THEN 1 ELSE 0 END)
FROM TABLE1             --query FAILS! (Msg 8114, Level 16, State 5, Line 1. Error converting data type varchar to numeric.)

Q3 What's an all-encompassing expression for my requirement of checking a column for NULLs, emptystrings and pure whitespace regardless of a column's datatype? If my columnname comes from a (cursorized) variable @column, what should I enclose it in and compare it to? I tried working with cast to nvarchar and using LTRIM/RTRIM, but frankly I am a bit lost at this point.

I am using SQL Server 2008. Thank you for reading this and for your assistance.


texts: What is an industry standard method for verifying openquery connection is open prior to running queries?

texts:
I'm a programmer, but this question is more specifically related to databases so I'm posting it among this community.  It could very well cross over into stackoverflow as well.

I'm seeking an approved industry standard method of verifying an openquery connection is properly connected and responding appropriately before proceeding with running queries.  The environment that is applicable to me would be nice to know if there is a specific answer, but I'm also looking for a generic approach as well if there is one for others and myself to benefit from.  My environment here uses Microsoft SQL 2005 and Oracle in the openquery.

I found this post in a blog which shows how this can be done, but I'm not sure if there is a more efficient method for achieving the same results and without false indication or failure.


texts: Pointing the database secondary data file towards a network drive

texts: I have a 2 TB database and the Data Drive has filled up. My network team cannot expand the drive nor provide another drive so that the secondary data file can be pointed towards that. My question is can the secondary drive be pointed towards  a network share .


texts: PostgreSQL insert not occurring serially

texts:
I have come across a problem where PostgreSQL does not insert records at the end of the table (according to id, which is the primary key)

So if, for example I have ids like 1521, 1522, 1527, the id of the next insert will be 1523 and not 1528 as I expected and would like to occur.

The missing ids may be due to the fact that the database that was migrated from MySQL.
The id has the following definition:
Data type: bigint
Default value: nextval('fleetmanager.workorderjob_id_seq'::regclass)
Not null
Storage: Plain

I use PostgreSQL version 10 on Windows.

How can I get it to always do insert after the last id?


texts: missing files, installing Oracle DB 12c on Oracle Linux 7.2

texts:
when I try to Install the Database I get this error message:

Source location is incomplete. Make sure you have downloaded and extracted all the relevant archives.

I did try this twice with the same error message with the main installation packages on Oracle website and from the support page (latest Patchset)

The files I tried are:

unzip linuxamd64_12102_database_1of2.zip
unzip linuxamd64_12102_database_2of2.zip

and for the patchset:

unzip p21419221_121020_Linux-x86-64_1of10.zip
unzip p21419221_121020_Linux-x86-64_2of10.zip

and when I create the ISO on my Mac

hdiutil makehybrid -o OracleDatabase12cEE.iso database/ -iso -joliet

all seems to be OK

I mount the ISO as a virtual CD/DVD in VMware then mount it in linux

tried to run the installation from the drive and after copying  everything locally!
and it's the same error message!

BTW I did run:

# yum install oracle-rdbms-server-12cR1-preinstall -y

# yum update -y

before trying to install!

also I login with ssh from my Mac

also I checked that everyone is the same as this article!

am I missing something?


texts:
SQL Server 2008.

I have several stored procedures.  I would like to execute them all in parallel, not serially. How can I do this?


texts: Does PgBouncer create a separate pool for every database config entry?

texts:
If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one?  I would think so, but have not been able to confirm this.

For example, with something like this:

[databases]
db1 = ...
db2 = ...

[pgbouncer]

.
.
.
max_client_conn = 480

default_pool_size = 250

Would I have a default pool of 250 for each DB and a max of 480, or a total of 250 default and max of 480 connections that are shared between both?

Thanks very much!


texts: Database Design - Employee Clocking In and Out System

texts:
I want to develop Employee Clocking In and Out System (website).

I am concerned two things:

If staff forgot to 'Clock Out' from yesterday and they have 'Clock In' today, it should flag to the manager.
Staff may work over time, eg: Clock In Monday 11:00 AM To Tuesday 01:30 AM (after Midnight). I dont want system think thay staff have forgot to clock Out..

Staff may clock In and Clock Out multiple times a day.

How to solve this issue and the database design?

staff table:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(50)  | NO   |     | NULL    |                |
| password    | varchar(50)  | NO   |     | NULL    |                |
| hourly_rate | decimal(6,2) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

clocking table:

+----------------+----------+------+-----+---------+----------------+
| Field          | Type     | Null | Key | Default | Extra          |
+----------------+----------+------+-----+---------+----------------+
| id             | int(11)  | NO   | PRI | NULL    | auto_increment |
| staff_id       | int(11)  | NO   |     | NULL    |                |
| clock_in_date  | datetime | NO   |     | NULL    |                |
| clock_out_date | datetime | NO   |     | NULL    |                |
+----------------+----------+------+-----+---------+----------------+

SQL:

CREATE TABLE IF NOT EXISTS `clocking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `staff_id` int(11) NOT NULL,
  `clock_in_date` datetime NOT NULL,
  `clock_out_date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `hourly_rate` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


texts: Checking unique entry based on related table

texts:
I have a certain problem that can be defined by an array of floats. These floats can be interpreted as sales at consecutive instants, e.g., 35 tickets at instant 1, 43 tickets at instant 2, 69 tickets at instant 3, and so on.
For a defined problem, I can use an algorithm to solve it and get a solution with optimized parameters.
A solution have a variable number of parameters, and can be something like this:

Parameter number 1: v1=1, v2=12, v3=6
Parameter number 2: v1=10, v2=14, v3=6
Parameter number 3: v1=24, v2=10, v3=9

I thought of the following schema to store the problems and its solutions:

Now let's say I'm about to solve a new problem. First of all, I would like to check the database to see if it's already there with a solution. Secondly, if I can't find it (or if there isn't a solution), I need to save the solution in the database.

My question is: how can I check for a unique entry in the sales table (unique problem)?
My first thought was to create a VARCHAR unique key on the sales table with all the values concatenated, ordered by instant_number. For the example in the beginning, it would be something like this: 35-43-69
Is there a better way? I'm using MySQL.
Thank you for your time, my friends.


texts: How to delete an endpoint only if exists

texts:
I'm creating a script to automatically configure 2 databases for mirroring, using this how to but I want to make sure there is no end point or master key already created so I would like to first do some clean up as:
DROP ENDPOINT End_Mirroring;
DROP CERTIFICATE PRIM_cert;
DROP MASTER KEY

But how can I do these drops only if they already exist?(i need this to avoid my script to exit with error)


texts: Amazon RDS Postgresql adding new extensions

texts: I want to download jsonbx to RDS instance. RDS PostgreSQL Features Supported shows only built-in features. Does this mean there is no way we can install an extension to RDS which is not in the Feature Matrix? Is there a work around for this?


texts: Timezone considerations when choosing an appropriate datatype

texts:
We are currently developing a task management system for our bespoke application. After a lot of deliberation we came to the conclusion that we only need users to specify the due date for a task.

I am wondering how it would work when a user in the UK adds a task and a user in Saint Petersburg sees it, considering that Saint Petersburg is at least 2 hours ahead of the UK.

Say that a user in the UK creates a task due on the 20/Apr/2018. At 23:30 (British time) on 19/Apr, a user in St Petersburg opens the task.  However, since it is already 20/Apr in St Petersburg it would look like the task has already completed, but in actual fact is not scheduled to run yet.

If the date field is in fact of type date, I am pretty sure that the user in St Petersburg would see the task marked as overdue, since it is already 20/Apr at his end.

I have seen several other task tools, and pretty much all of them also tend to allow only due dates (not time) for tasks. I wonder however how other systems handle this sort of issue, or if they just don't bother at all.

So being objective around my questions:

We acknowledge this discrepancy and accept it for what it is. If a task is due on the 20/Apr and it is already 20/Apr in Australia but not in California, we just accept that users in Australia will see the task as late and those in California would see it as 'In progress'.
I think that the only way to overcome this issue would be to capture the date in a datetime field (even if we don't allow users to enter the time), and then also allow for a timezone to be defined.

Am I missing something?

EDIT: To answer the question on whether we want the due date of a task to follow the timezone of the user seeing the task, or a fixed timezone when the task has been created, we have opted for a fixed timezone. So a task will be set to be due on 20/Apr UK time will only get to be due when it reaches YYYY-04-20 00:00:00.00 UK time.

To support this, we will allow for users to click a small icon next to the due date field when creating a task, allowing for them to select the timezone. The timezone will be set to a default based on the following workflow:

We will create two settings for the user's account page that allow the user to:

a) Toggle an option for the application to attempt to get their timezone automatically from the client side.
b) An option for them to select their default timezone.

Based on this requirement we can pretty much conclude that a date datatype is out of the question. Now what is not 100% clear to me is whether we should go for a datetime2 (I always use datetime2 instead of datetime) or a datetimeoffset.


texts: Are PostgreSQL clusters and servers the same thing?

texts:
From the official documentation (emphasis mine):

Chapter 22. Managing Databases

  Every instance of a running PostgreSQL server manages one or more databases. Databases are therefore the topmost hierarchical level for organizing SQL objects ("database objects").

Chapter 22. Managing Databases -> 22.1 Overview

  More accurately, a database is a collection of schemas and the schemas contain the tables, functions, etc. So the full hierarchy is: server, database, schema, table (or some other kind of object, such as a function).

Chapter 18. Server Setup and Operation -> 18.2. Creating a Database Cluster

  A database cluster is a collection of databases that is managed by a single instance of a running database server.

I don't quite understand the difference between the PostgreSQL concepts of a cluster and a server, but it seems to me that they are the same thing?


texts:
I added some triggers to third party DB and now the third party application failed to run.

Is there a way to see the failed queries?

for example:

I run:

SELECT * FROM Table_1  --query succeeded

And then:

SELECT * FROM XYZ  --query failed since table doesn't exist.

I can see succeeded queries by running the following:

SELECT st.last_execution_time, t.text FROM sys.dm_exec_query_stat AS st
    CROSS APPLY sys.dm_exec_sql_text(st.sql_handle) AS t
WHERE t.text LIKE 'SELECT * FROM %'

I'm looking for a similar query in manner to shows the failures.

Thanks.


texts: MAX worker thread in SQL server 2012/14/16

texts:
I was just browsing the system health extended event to come across an event with diagnostic result for query processing showing status for worker threads:

Why max worker threads showing 2994 when I have 80 logical processors which as per msdn calculation here 512 + ((logical CPU's - 4) * 16) does not looks correct. However per the latest link here makes sense for logical processors greater than 64 where calculation for 16 changes to 32. But that link says starting SQL 2017, however the number 2994 I am getting is for SQL2012, 14 16 where no of logical processors is 80. Am I missing something here?

max worker setting within sp_configure is set to '0'

Coming back to that system health event status shows WARNING for below no:

  maxWorkers="2944" workersCreated="456" workersIdle="314"
  tasksCompletedWithinInterval="1021881" pendingTasks="1"

Should I be checking something else or can this be ignored?


texts: DB2 - what happens when SQL archive log file reches its maximum value (S9999999.LOG)

texts:
we have DB2 in archive mode.
archive log files are created and deleted from the disk.
What will happen when archive log file reaches its maximum value for the name (S9999999.LOG) will it start to count from the beginning (S0000001.LOG)?


texts: How do I monitor PostgreSQL WAL shipping if I'm not using streaming replication?

texts:
We have a fairly simple setup to replicate from our on-premise master PostgreSQL database to our presentation tier in AWS. We're using WAL shipping using the archive_command setting. Basically the setup looks like this:

    +-------------+
    |   Master    |
    +-------------+
     WAL  |
segments  |
         \|/                     +--------------+
    +-------------+   WAL      +-+------------+ |
    |             |----------->| Hot Standby  | |
    |      S3     |  segments  |   Slaves     | |
    |             |            |              |-+
    +-------------+            +--------------+

This setup seems to generally be fairly robust, but I haven't come up with a good way to detect failures, either the master failing to push archives up or a slave or slaves failing to retrieve the log files. What's a good way to determine if a slave is up-to-date with respect to master? What's a good way to determine if the master has failed to ship a WAL file?

Just to clarify, we are using the slaves strictly as read replicas, we will never failover to them.


texts: Why MySQL Database is Bigger After Dump/Import?

texts:
I have 2 VPS and plan to migrate the DB from first to second. The original DB is taking around 15GB of storage and I manage to dump out a 7GB SQL. However, when I imported the SQL to second VPS, it takes up 2x of storage becoming 32GB.

My questions are:

What might cause my DB from 15GB to become 32GB by just dump+import?
How can I avoid this?

Below are some further detail:

Both VPS are not exactly same and so do the MySQL version. The source DB is version 5.5.47. The receiving DB is version 5.7.13.
The receiving DB is newly install and empty.
The command I used to dump out SQL is: mysqldump -u <username> -p --all-databases --add-drop-database --max_allowed_packet=16777216 --single-transaction > full_dump.sql. The reason I am setting max_allowed_packet is to have the same value compare to receiving DB
Not sure if this is relevant. If I do not specify single-transaction in my mysqldump command, it will error mysqldump: Got error: 2006: MySQL server has gone away when using LOCK TABLES
The source DB storage distribution is different from the receiving DB:

  14858M    /var/lib/mysql

  14483M    /var/lib/mysql/ibdata1

  365M  /var/lib/mysql/wordpress

This is the receiving DB storage distribution:

  31558M    /var/lib/mysql

  31306M    /var/lib/mysql/wordpress

  337M  /var/lib/mysql/wordpress/wp_wfLeechers.ibd

  101M  /var/lib/mysql/wordpress/wp_1059_posts.ibd

I've tried to run table optimisation, but no impact


texts: Getting ORA-32042 even though i can create the view in SQL Developer

texts:
I have a strange problem creating a materialized view using the code generated by the export wizard.

I have created a materialized view and pasted following code into the sql query tab from the popup. Everything works fine and the view is there. But when i try to run this code in a simple sql query editor and press run script i get the following error message.

Database: 12c Release 1
Error code: ORA-32042
Description: recursive WITH clause must reference itself directly in one of the UNION ALL branches

What have i done wrong?

CREATE MATERIALIZED VIEW axchannel_customer_category_hierarchy_discount_mview (
    "CHANNEL_ID",
    "CATEGORY_ID",
    "CATEGORY_PARENT_ID",
    "CATEGORY_REC_ID",
    "CATEGORY_NAME",
    "CUSTOMER_ID",
    "DISCOUNT_PERCENT",
    "DISCOUNT_ORIG",
    "LVL"
) AS
    WITH prejoined_discounts AS (
        SELECT
            axchannel_category_hierarchy.*,
            axchannel_customer.id   AS customer_id,
            axchannel_customer.customer_nr,
            axchannel_customer_category_discount.discount_percent
        FROM
            axchannel_category_hierarchy
            CROSS JOIN axchannel_customer left
            JOIN axchannel_customer_category_discount ON axchannel_category_hierarchy.channel_id = axchannel_customer_category_discount
            .channel_id
                                                         AND axchannel_category_hierarchy.id = axchannel_customer_category_discount
                                                         .category_id
                                                         AND axchannel_customer_category_discount.customer_id = axchannel_customer
                                                         .id
        WHERE
            axchannel_category_hierarchy.channel_id = axchannel_customer.channel_id
    ),foo (
        channel_id,
        id,
        parent_id,
        category_rec_id,
        category_name,
        customer_id,
        discount_percent,
        discount_orig,
        lvl
    ) AS (
        SELECT
            c.channel_id,
            c.id,
            c.parent_id,
            c.category_rec_id,
            c.category_name,
            c.customer_id,
            c.discount_percent,
            c.discount_percent   AS discount_orig,
            1 AS lvl
        FROM
            prejoined_discounts c
        WHERE
            c.parent_id IS NULL
        UNION ALL
        SELECT
            c.channel_id,
            c.id,
            c.parent_id,
            c.category_rec_id,
            c.category_name,
            c.customer_id,
            CASE
                WHEN c.discount_percent IS NULL THEN foo.discount_percent
                ELSE c.discount_percent
            END discount,
            c.discount_percent   AS discount_orig,
            lvl + 1
        FROM
            foo
            JOIN prejoined_discounts c ON c.channel_id = foo.channel_id
                                          AND c.parent_id = foo.id
                                          AND c.customer_id = foo.customer_id
    )
    SELECT
          channel_id,
    id as category_id,
    parent_id as category_parent_id,
    category_rec_id,
    category_name,
    customer_id,
    discount_percent,
    discount_orig,
    lvl
    FROM
        foo;


texts: Why is PDO::ATTR_EMULATE_PREPARES = false causing "MySQL server has gone away" errors?

texts:
I've been using PHP PDO with the default setting of ATTR_EMULATE_PREPARES = true and it was working fine.
However, I wanted to be able to select database values as integers instead of having them converted to strings.  When I set ATTR_EMULATE_PREPARES = false I was able to get integers, but I started getting these errors in the web error log:
Uncaught PDOException: SQLSTATE[HY000]: General error: 2006 MySQL server has gone away

The errors are intermittent and I cannot duplicate them at will. I cannot duplicate the problem on my dev system (which has PHP/Apache/MariaDB on the same system). The problem only occurs on my production servers.  The production webserver and database server are two separate systems, but are not heavily loaded.  I only see errors on the web server, I have not noticed any problems in the database server logs.
I've tried adjusting various timeouts and buffer sizes and nothing seems to make any difference.  Most timeouts are set to 60 seconds or more, but the problem occurs on pages with queries that take 15 seconds or less.  Sometimes they fail with the above error, but then it will work fine when I refresh the page.
It took me a while to figure out what was going on, but I finally remembered I had changed the ATTR_EMULATE_PREPARES setting a few days ago.  As soon as I changed it back to true (which is the default), all the problems disappeared.
I tried setting ATTR_STRINGIFY_FETCHES = false and hoped it would let me select integer values, but that setting does not appear to have any effect.
I'm running the following stack: Apache 2.4.18, PHP 7.3.20-1, MariaDB 10.2.27, Ubuntu 16.04.6
I don't really care about ATTR_EMULATES_PREPARES, I only changed the setting so I could select integer values.  Is there any way to do this without causing errors?


texts:
We have a SQL Agent job running some PowerShell, which creates a pretty long output (it has robocopy output, among other things).

In the SQL Agent Job History, we only see the first 1,000 characters or so of this output.

Is there another place we can look that will have the entire output?
Is there another approach that should be used to see the full output?


texts: We are moving our back-end storage so will need to change the paths for our log shipping. Can we do this without interrupting our log shipping? i.e. Just change the paths in the 'Network path to backup folder' field. Thanks.


texts:
I need to create a pivot table using only two columns, home team number and away team number.

This is the full table:

But i need to combine these two into a single query:

The resulting table should be 32 records. Any ideas?


texts: how to maximize usage of number of databases on a postgres server while limited by connections

texts:
We're building a system where ideally we'd give a database per client in a multi-tenant setup.
However, although a single Postgres install supports 4,294,950,911 databases on a server, the suggested max_connections is around 100 or so.
That means if we used every active connection up to some recommended amount (using 100 as an example), we can at one time only access 0.000002 % of the available databases.
In no way would I need to utilize 4B databases, but would be great to utilize more than 100, certainly.
In looking up connection info in pg_stat_activity, seems that each connection includes a database name, so I'm guessing you cannot create a connection pool on the same server across different database names. I was hoping there would be a solution that allowed a connection pool across databases on the same server, finding that's not the case - please enlighten me if there is something in this realm of solutions.
So my options I believe at each end of the spectrum are:

shard tenants across schemas, and use connection pooling on same database

multiplex and/or context switch connections from the API server, using an LRU cache of pg pools that manage connections, closing the ones that the LRU cache disposes.

question
Assuming I wanted to build 10,000 databases per database server, I'd look to get a solution like #2 working, to access different databases. How expensive it is really to open/close a connection if it happens quite often?
And if it's not feasible, is the number of databases allowed in postgres just a computer science theoretical number, that we'll only ever use a fraction of a fraction of a percent of? And in reality, we'd at best use a number of databases that correlates closely with the number of connections?
I'm also guessing there may be a hybrid solution that allows us to use many databases but minimize the connections per server with a fan-out using logical replication. But that sounds like it could get more expensive.


texts: Does the 'reads' column in SQL Profiler mean the same as IOPS?

texts:
I just executed a query that went pretty fast and the SQL Profiler said it used 30.000 reads.
Now I'm looking at this page: https://cloud.google.com/compute/docs/disks/performance and see that a standard persistent disk has 3000 reads IOPS.

Does that mean that my query would take 10 seconds when run there? Or does SQL reads and read IOPS mean a totally different thing?


texts: How to stop decrypting data after opening master key in SQL Server?

texts:
I have implemented data encryption using SQL Server symmetric key encryption technique using a certificate, master key with password.

When we backup database from PROD server and restore to a different server, we open the master key using OPEN MASTER KEY DECRYPTION BY PASSWORD = ''... statement.

Password for master key is being managed by a SQL admin.

After opening the master key, we are able to decrypt the column data using DecryptByKey function. However, once we have done some analysis, we want to stop decrypting the data. No developer should be able to decrypt the data after a while.

How to stop decrypting the data or close the master key so that no developer can see data in plain-text?


texts: How to get syntax and logical ORA in a trace file?


texts: MYSQL Query - Unexpected result in text messaging database

texts:
On a text messaging database I have built, I am trying to return the total amount of messages a user has sent and the total number of messages a user has received. I have the following code:

SELECT People.Name,
SUM(Case When Recipients.RecipientID = People.ID THEN 1 ELSE NULL END) AS 'Messages Received',
Count(Messages.SenderID = People.ID) AS 'Messages Sent'
FROM People JOIN Recipients, Messages
GROUP BY People.ID
ORDER BY People.ID ASC;

which returns unexpected results. I am just using a test database of 3 messages sent. 2 by 1 person and 1 by another person. All 3 messages were group messages sent to the other 9 people listed in the database. So I would expect one user to have 2 messages sent and 1 received, one of the users to have 1 message sent and 2 received, and the other 8 people to have 0 sent and 3 received.

What I am actually getting is the two users who have sent message(s) have 3 messages received and 54 messages sent. The other users all have 6 messages received and 54 messages sent.

The tables are set up as follows:

Messages (MessageID int, SenderID int, Content varchar, Type varchar, DateTime datetime)
People (ID int, Name varchar)
Recipients (RecipientID int, MessageID int)

Because each message was sent to 9 people, there are 9 entries for each messageID in the Recipients table, with each one responding to a recipientID (which corresponds to People.ID).

Where does my query go wrong?

Also, in a somewhat related question, what would be the best way to modify the database if I want to add Groups and then just add people ID's to groups and allow a group to be a recipient and each person that is a member of that specific group gets counted as a recipient?


texts: What is the best SQL Server field type to cast to when storing a UNC path stored in a sql_variant field?

texts:
What is the best choice of field type to cast to when storing a UNC path in a sql_variant in Microsoft SQL Server 2008?

It appears that I could store up to 4000 characters according to Microsoft Technet. Windows Dev Centre at MSDN seems to suggest that 260 characters will be sufficient.


texts: How to set a filesystem size limit for DB2 database? Is it possible?

texts:
I'm looking for a way to limit a DB2 database size (in disk)  at creation time. But I couldn't find it.

At SQL server we can do by this way:

Create Database MyDatabase
on (Name='MyDatabase_Data',
    Filename='c:\db\BdUnisanta_Data.mdf',
    Size= 20MB,
    FileGrowth = 10%,
    Maxsize=100MB)
log on
    (Name = 'MyDatabase_log',
     Filename = 'c:\db\MyDatabase_Log.ldf',
     Size = 5MB,
     FileGrowth = 5%,
     MAXSIZE = UNLIMITED
     )

What would be the equivalent form in DB2? Is it possible?


texts:
I'm provisioning a SQL Server 2019 build and have been allocated a virtual server running Windows Server 2016. It has 4 virtual processors and I have 410Gb of drive space to work with.
My build has to run two versions of the same 35Gb database.
My intention is to split the 410Gb drive on the new box up as follows:
L:\ to hold log files. Based on a rule of 25% of the size of the database = 9Gb per DB = 18Gb).
T:\ to hold tempdb files. 4 data files (one per logical processor) at 1.5Gb each with a log file of 3Gb and a further 2Gb per DB for auto growth = 11Gb.
D:\ drive of 190Gb for data and an E:\ drive of 190Gb for all backups.
Does that sound like a sensible split?


texts:
After finishing the installation of Oracle 12c on Centos 7, now I try to allow access on my PC (via SQL Developer) to the server. The problem is that when I do:

lsnrctl status

I get this error:

TNS-12541: TNS:no listener
 TNS-12560: TNS:protocol adapter error
  TNS-00511: No listener
   Linux Error: 2: No such file or directory

So, I try to start the listener with:

lsnrctl start

And I get this error:

TNS-01198: Listener failed to initialize valid node list
 NL-07007: internal error
  NL-00408: cannot find or read value
   NL-00408: cannot find or read value
    NL-00408: cannot find or read value

To finish I go to this website:
TNS-01198: Listener failed to initialize valid node list post upgrade from 11g to 12c

Then I added, as he tell the following line on the listener.ora file:

VALID_NODE_CHECKING_REGISTRATION_<LISTENER_SERVICENAME> = OFF

but nothing change.

To finish, this is my listener.ora file:

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

VALID_NODE_CHECKING_REGISTRATION_<LISTENER_SID> = OFF

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =  ns330926.ip-37-187-120.eu)(PORT = 1521))

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

I added my hostname in the /etc/hosts file.

Does anyone have a solution?


texts: Removing foreign key when it is not named

texts:
I've created a table named 'student' as :

create table student(
  stud_id number primary key,
  stud_name varchar2(30) not null,
  branch_id varchar2(4) references branch(branch_id),
  course_id number references course(course_id),
  hostel varchar2(4)
);

Later on, I found that I don't need course_id column, now I want to remove that column, when I tried to do that, a warning message appeared saying : "cannot drop column from the table owned by SYS" then I thought, I must remove foreign key constraint first and then drop table, but that didn't work. I used :

alter table student drop column course_id;

and since I didn't name my constraint, therefore can't use the following statement:

alter table student drop constraint;

I am using oracle database 11g


texts: Amazon RDS MySQL Troubleshooting High Sessions

texts:
I just migrated my forums application to AWS. I'm in the middle of tweaking things for performance and I noticed that the number of sessions for my RDS instance (t3.large) is consistently exceeding 2.0. At first there was a bad query that was writing way too much, but now that I've eliminated that, I can't figure out what's going on here. Here's some screenshots of the performance insights:

It looks like there's something going on with the binlog and innodb_log_file that is causing some of the sessions to block, but I'm unclear how to fix this. I've tried increasing the log file size from the default, but I'm not sure what else to do.
One thing to note is that my CPU usage on this RDS instance is very, very low, despite constant usage.


texts: PostgreSQL partial index not used depending on boolean comparison


texts:
I have a table in 2 different database.

In Database_A, there is a table created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime not null
    )

In Database_B, the same table is created as:

CREATE TABLE tbl_Employee(
    E_Id int null
    ,E_Name varchar(24) not null
    ,Joining_Date datetime null
    )

The difference is, the column Joining_Date is NOT NULL in Database_A but it allows NULL in the Database_B.

Now when I export data from the table Database_A.dbo.tbl_Employee
using command

bcp "select * from Database_A.dbo.tbl_Employee" queryout "D:\test.txt" -S Server_Name -n -T

and import into Database_B.dbo.tbl_Employee using command

bcp "Database_B.dbo.tbl_Employee2" in "D:\test.txt"-S Server_Name -n -T

It raises an Error:

  SQLState = S1000, NativeError = 0
  Error = [Microsoft][SQL Server Native Client 11.0]Unexpected EOF encountered in
  BCP data-file

I can not alter the column to NOT NULL on Database_B


texts:
Situation:

I currently monitor an instance of MySQL for slow queries. We have a mysql user dedicated to backups which performs a mysqldump, but the mysqldump gets logged as a slow query.

Question:

How can I set the system up to prevent the mysqldump from ever being being seen as slow query?

Restrictions given by security team:

The backup user can only have select
The backup can run anywhere from once a day to hourly and the process will be applied to varying sizes of databases (so I can't just speed up the process)
Only the backup user can be used

Environment:

OS: Ubuntu 14.04
MySQL: Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3
MySQLdump: Ver 10.13 Distrib 5.5.38, for debian-linux-gnu (x86_64)


texts: Unable to find oracle/dms/console/DMSConsole

texts:
I am using centos-8, java-8, tomcat-8, oracle 18.4.0 XE

I not able to connect to the database. Tried to check everything that I was able to do but not able to understand where is the mistake.

I added the latest ojdbc driver into tomcat/lib also set its class path but not connecting to the DB. Here is the error log:

Exception in thread "main" java.lang.NoClassDefFoundError: oracle/dms/console/DMSConsole
        at oracle.jdbc.driver.DMSFactory.<clinit>(DMSFactory.java:46)
        at oracle.jdbc.driver.PhysicalConnection.createDMSSensors(PhysicalConnection.java:1713)
        at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:849)
        at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:443)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:34)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:712)
        at java.sql.DriverManager.getConnection(DriverManager.java:664)
        at java.sql.DriverManager.getConnection(DriverManager.java:247)
        at Test.main(Test.java:9)
Caused by: java.lang.ClassNotFoundException: oracle.dms.console.DMSConsole
        at java.net.URLClassLoader.findClass(URLClassLoader.java:382)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:418)
        at java.lang.ClassLoader.loadClass(ClassLoader.java:351)
        ... 9 more

Furthermore, I googled and found that most forums recommended that dms.jar required. I explored our server + oracle + latest JDBC driver jars but not found anywhere this jar file.

Any help will be highly appreciated

Best regards


texts: Drop 15 days records in monthly partition

texts:
I have created a monthly partition on my table like below:

PARTITION BY RANGE (PARTITION_KEY)
     INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
   (
     PARTITION MayPartition VALUES LESS THAN (TO_DATE('01/06/2016', 'DD/MM/YYYY'))
   );

Since my table has data in TB, dropping this monthly partition is taking more than 10 hours for 1 month.

Is it possible to drop partition for only 15 days? Or is there a way to reduce the time taken to drop partition?

I made sure there was no other load on database while dropping the partition.

I am using this command to drop the partition:

alter table table_name drop partition partition_name update indexes

I have global indexes on the table. I don't have foreign keys referencing the PK of that table.


texts: Trying to count the number of character in a string

texts:
I've created this MySQL function to count the number of characters (str) in a string(search):

drop function if exists find_str;

delimiter //

CREATE FUNCTION `FIND_STR`(search VARCHAR(255), str CHAR(1))
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
BEGIN
SELECT ROUND (
        (
            CHAR_LENGTH(search)
            - CHAR_LENGTH( REPLACE ( search, str, "") )
        ) / CHAR_LENGTH(str)
    ) AS count INTO @count;
return @count;
END//

delimiter ;

If str is space, the function always return null, but I'm not sure why.

find_str('Hello', 'l') => 2 // correct
find_str('Hi there', ' ') => null // incorrect, it's supposed to be 1
find_str('Hi there', space(1)) => null // also incorrect, it's supposed to be 1


texts:
I have a SSIS package which performs a few tasks using SQL Server Agent.
First it executes a script task, then accesses Excel files, executes a SQL query and moves files to another directory.

The package worked fine when I executed it manually via debugger.

When I deployed the package and created an Agent job it fails on the excel connection task.

My implementation:
- Excel files are located on a share stored on another account in the domain
- the account which executes the package and runs agent jobs has permissions in the share and there is a proxy created for the credentials in the database

What are your suggestions to try? I spent a lot of time trying to fix it.


texts:
Given the following table:

 Date       Trd (INT(10))   Code (VARCHAR(10))  T-Date
 1-Jan-14   1               B                   1-Nov-13
 1-Jan-14   2               B                   3-Oct-13
 1-Jan-14   2               B                   3-Oct-13
 2-Jan-14   1               A                   1-Nov-13
 2-Jan-14   2               A                   3-Oct-13
 2-Jan-14   2               A                   3-Oct-13
 2-Jan-14   3               A                   2-Jan-14
 3-Jan-14   2               A                   3-Oct-13
 3-Jan-14   2               A                   3-Oct-13
 3-Jan-14   3               A                   2-Jan-14
 3-Jan-14   4               C                   3-Jan-14

How do I SELECT, to get the following result:

 Date       Trd (INT(10))   First showed up as Code   In Date       T-Date
 3-Jan-14   2               B                         1-Jan-14      3-Oct-13
 3-Jan-14   3               A                         2-Jan-14      2-Jan-14
 3-Jan-14   4               C                         3-Jan-14      3-Jan-14

So for a given Date (in this case Jan-3rd), GROUP BY Trd and find out with which Code it first showed up, and provide the Date that happened.

I have no idea where to start.


texts: Postgres 8.4 -- connection refused (remote)

texts:
I'm new to postgres and looking to set up a small environment here with it.

I've installed PostgreSQL 8.4 onto an ubuntu server, able to login to the postgres, submit commands, do queries, etc... however, I'm looking at using pgAdminIII from my windows machine.

I've followed along with the instructions from the ubuntu help page and made the following two changes:

In pg_hba.conf I have added the line (using other IP information, which should be fine as it's tested elsewhere):

host    all         all         10.0.0.1 255.255.255.0    trust

in postgresql.conf I had originaly set listen_address = 'localhost,10.0.0.1', but changed to listen_address = '*' out of frustration after reading this guide.

How should I configure my listen files here so that postgres isn't listening to * ?


texts: Why mongodb requires arbiter in a 4 members replica set?

texts:
sorry for my trivial question, I read the mongodb documentation but still I do not understand well.
The documentation says that the majority of the members should be considered at the start up configuration of the cluster.
So, if I have 4 members as my replica set, the majority is 3 members.
  After the fail over of one member I still have 3 members up and running and they can proceed correctly to perfom the election, why should I need an arbiter for this scenario??
  I understand perfectly the need of an arbiter in case of only 2 members, in that case the majority is 2 so if one node goes down the other can not elect himself.


texts: How to set STATISTICS_INCREMENTAL option ON for an index in SQL Server using c#?

texts:
I need help with programmatically setting the STATISTICS_INCREMENTAL option ON for an index in a table in SQL Server (2016 SP2). I am using Microsoft.SqlServer.Smo, Version=13.0.0.0. I do not see any property in the Index class to enable STATISTICS_INCREMENTAL. I checked Version 140.17283.0 as well.

I am trying to periodically update statistics for only a few partitions using the command: UPDATE STATISTICS [Schema].[Table] IX_Table_Index
WITH RESAMPLE ON PARTITIONS(4). I enabled the Auto Create Incremental Statistics option at the database level. I just need help with enabled STATISTICS_INCREMENTAL at the index level.


texts: Service Master Key inaccessible, SSRS key only one backed up

texts:
I’m working with a SQL Server 2012 instance that had a service master key, other secondary keys, and a SSRS key. The instance was uninstalled and reinstalled with new service accounts, and the master database was restored. No encryption keys were backed up.   The original master key, now inaccessible, was left as is and the SSRS key was regenerated and backed up.

Now we need to open or regenerate the master key for SSIS use.  If the SQL engine service is reset to the original login account, can the master key be opened?  If so, via backup with a newly created password?  If not, and the master key is regenerated, can the SSRS key be restored using its backup?

My google-fu is failing me and I want to accomplish this with the least amount of loss possible.  Any pointers or assistance is greatly appreciated.


texts: What is the proper way to assign the privileges when a MySQL instance has multi dba?

texts:
I have a MySQL instance offers services to multi-application, different application has different DBA who responsible to their own application schema.

For example:

Application A has schema DB_A,there are two type of MySQL account for A,one to satisfy the business requirement, and the other one is for administration purpose(performance tuning,DDL,and some other privilege need for administration), apparently ,the former is the subset of the latter.
In addition, there is a super DBA (root), who is responsible to create such accounts I mentions above.

As new to MySQL, I would like to ask that how to execute the proper grant statement to satisfy my requirement?


texts: SQL Server Standard License over written because of Report Builder Evaluation version

texts:
A colleague of mine rang me today to told me that on one of our servers the SQL Server has stopped working. If he tries to run the management studio it throws and error saying The evaluation period has expired...

But the person claims that initially he installed SQL Server 2008 R2 Standard Version (licensed copy) on the server, but later on someone by mistake has installed Report Builder 3.0 enterprise evaluation version on that machine and this has caused the SQL server license to expire.

I have never heard of such a thing. I thought Report Builder was a client application and as long as the server you are connecting to is licensed you should be able to use it, but it would never affect the licensing on the server it is connecting to.

Anyway I connected to the server itself and started going through the windows log to check when the SQL Server was installed.

In window's log almost a year ago when the SQL Server was installed , all the MsiInstaller events during SQL Server installation points to a directory

C:\appstore\2008 R2 Standard\Standard Edition\x86\setup\.....

which I think indicates that it was standard edition that was installed originally, yet after the installation was finished and the sql server service started for the first time the windows log show the following:

Question 1

am I correct in concluding that even if the person thinks he installed a standard version of sql server, it was actually an enterprise evaluation version of sql server ??

Question 2

Is it possible that an Enterprise Evaluation version of Report Builder can affect License of already installed SQL Server standard and cause it to expire?


texts: Does a SQL Server job run on the server or the local machine?

texts:
Does a SQL Server job always run on the server, or will it run in the context of the local machine, similar to a DTS package run from Enterprise Manager on the user's machine?

The job in question calls a DTS package. The package succeeds when run locally; the job fails whether run manually or scheduled.