texts: adding and removing a node from a mongo replica set

texts:
I have a new backup server I want to add to our mongo replica sets. As far as I can tell its as easy as logging on to the replica sets primary and adding the following command:

rs.add( { host: "mongobackup:10003", priority: 0, votes: 0, hidden: true } )

My understanding is that after doing so the primary should sync all its configuration and data with the new node.

Additionally I have an old backup server I want to remove from the replica set which I looks like all i have to do is type this command into the replica sets primary:

rs.remove( "oldmongobackup:10003" )

Is there anything else I am missing?

rs2:PRIMARY> rs.conf()
{
    "_id" : "rs2",
    "version" : 26,
    "members" : [
        {
            "_id" : 7,
            "host" : "mongo03:10001",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 20,
            "tags" : {
                "dc" : "maid"
            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 8,
            "host" : "mongo01:10002",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 10,
            "tags" : {
                "dc" : "maid"
            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 9,
            "host" : "mongo02:10003",
            "arbiterOnly" : true,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 10,
            "host" : "oldmongobackup:10003",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : true,
            "priority" : 0,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        }
    ],
    "settings" : {
        "chainingAllowed" : true,
        "heartbeatTimeoutSecs" : 10,
        "getLastErrorModes" : {

        },
        "getLastErrorDefaults" : {
            "w" : 1,
            "wtimeout" : 0
        }
    }
}


texts:
Slightly unimportant but it's piqued my curiousity.

I've just logged into an Oracle 10g database for the first time using the Oracle SQL Developer Tools.

I used a generic user login which isn't my name (nottstest2), from a machine that is called something that isn't my name (courgette). My name is nowhere in the database and isn't associated with the login.

Yet, the server logs show a connection from "Jon Hopkins".

How does it know who I am? Is it being pulled from my Windows login in some way (though I'm not using single sign in)?


texts:
Here's what I did. On the master, change /etc/my.cnf:

[mysqld]
server-id=1
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Save and restart mysql, then log in.

>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '123';
Mysql> flush table with read lock;//  Start copy data master (myisam) chuyển sang slave .
Mysql> show master status;

On the slave, change /etc/my.cnf.

[mysqld]
server-id=2
log-bin=mysql-bin
innodb_flush_log_at_trx_commit=1
sync_binlog=1

Save and restart mysql. Create a database on the slave (Snapshop master). Move data from master to slave.

Mysql> stop slave;
Mysql>CHANGE MASTER TO
MASTER_HOST='server2',
MASTER_USER='repl',
MASTER_PASSWORD='password',
MASTER_LOG_FILE='xxxx',
MASTER_LOG_POS=xxx;

On the master:

Mysql> unlock tables;

On the slave:

Mysql> start slave;
Mysql> show slave status /G

My problem is: I forget answer command belows.

Mysql> show master status;

when I start the slave. The error is

236 | Got fatal error 1236 from mast er when reading data from binary log: 'Could not find first log file name in binary log index file' |

What does the error mean, and how can I solve it?


texts: Which fillfactor is used by ALTER INDEX REBUILD?

texts:
From technet I got the impression, that I either have to explicitly set the wanted fillfactor or when I do not specify it, the default fillfactor 0 is choosen.

For tables I know to get rows added fillfactor 0 isn't a good choice. Is there a way to rebuild an index, so that the last explicitly index is used ?

BTW: I think that that should be the default setting.


texts:
I have a CSV file with IDs of rows. I want to use that to make a SELECT statement selecting the IDs within that CSV file.

There is 1 million rows and I want to select about 95,000 of those.

I want to SELECT and import these rows to another SQL Server database using the SQL Server Import and Export Wizard.

I'm using MSSQL 2005 with SQL Server Management Console.


texts: SAP BO Freehand SQL date formatting (Oracle db)

texts:
My original query worked perfectly via SQL Developer:

select * from [table] where DT_CURRENT_DT = '15-OCT-18'

However when ran via freehand SQL in SAP BO I got no results.
After running the query with no conditions I noticed that BO re-formats the date to 10/15/18


texts:
Background : (Multi-tenant design;separate schema per customer, all table structures identical;Data-warehouse / non-OLTP system here; Postgres 9.2.x).

Reviewing patterns for the following problem:  I have a matview that needs to be updated if an underlying condition is altered (in this case, an inclusion BOOLEAN).

Was considering implementing a TRIGGER, on update, which would then either execute the matview creation UDF or possibly call 'NOTIFY' to be handled by some listening event, perhaps at a later point in time as the immediate matview update isn't necessarily required.

Workflow :

UPDATE made to some underlying table.
TRIGGER is executed.
(Obtain schema information here).
Either,

EXECUTE matview creation
NOTIFY command for queue / processing at a later time.

First question, am I on the right track here or are there other, more efficient / effective patterns I should review?

Given the nature of my multi-tenant design, is it possible to obtain the current schema from the underlying table from which the trigger event was called as I'll need that information to pass into my matview creation UDF.


texts: Can sp_executesql be configured / used by default?

texts:
I am looking at an application that uses highly dynamic sql queries against SQL Server. Looking at the queries that are constructed in very weird and complicated ways, but that's a different story, I tell it to give a good reason for me being not able (too stupid) to find things out myself... I can't see any code where the queries are wrapped with sp_executesql.

But when I trace, I can see a lot of queries coming in wrapped with sp_executesql. The whole application solution does not even contain the command sp_executesql at all.

So I wondered if there is kind of a configuration I do not know yet that forces the software to wrap queries with sp_executesql by default?

What could cause this behaviour?


texts: Get profiles values where message.sender_user_id = profile.user_id

texts:
I have this tables:

default_messages

CREATE TABLE IF NOT EXISTS `default_messages` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `subject` varchar(150) COLLATE utf8_unicode_ci NOT NULL,
 `message` text COLLATE utf8_unicode_ci NOT NULL,
 `sender_user_id` int(11) NOT NULL,
 `reply_to_message_id` int(11) NOT NULL,
 `thread_root_message_id` int(11) NOT NULL,
 `date` datetime NOT NULL,
 `deleted` int(11) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

default_profiles:

CREATE TABLE IF NOT EXISTS `default_profiles` (
 `id` int(9) NOT NULL AUTO_INCREMENT,
 `created` datetime DEFAULT NULL,
 `updated` datetime DEFAULT NULL,
 `created_by` int(11) DEFAULT NULL,
 `ordering_count` int(11) DEFAULT NULL,
 `user_id` int(11) unsigned NOT NULL,
 `display_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `company` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
 `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
 `bio` text COLLATE utf8_unicode_ci,
 `dob` int(11) DEFAULT NULL,
 `gender` set('m','f','') COLLATE utf8_unicode_ci DEFAULT NULL,
 `phone` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `mobile` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line1` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `address_line3` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `postcode` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `website` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `updated_on` int(11) unsigned DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;

and

default_recipient:

CREATE TABLE IF NOT EXISTS `default_recipient` (
 `message_id` int(11) NOT NULL,
 `user_id` int(11) NOT NULL,
 `read` int(11) NOT NULL DEFAULT '0',
 `recipient_read_date` datetime DEFAULT NULL,
 `deleted` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Then I have this query for get some messages:

SELECT DISTINCT p.display_name, p.first_name, p.last_name, rcp.*, msg.* FROM default_messages msg LEFT JOIN default_recipient rcp ON (msg.id = rcp.message_id) LEFT JOIN default_profiles p ON (p.user_id = msg.sender_user_id) WHERE rcp.user_id = 1 AND rcp.deleted = 0 ORDER BY msg.date DESC

I need to get the values from default_profile where default_profile.user_id = default_messages.sender_user_id but for some reason I get the wrong values. It's something wrong in my query?

Here are some values for testing purpose and also the complete script for table creation: http://pastebin.com/T3mXfi0k


texts: Putting a constraints on a already made table in PostgreSQL

texts: I was wondering is it possible to add Not Null to a variable created already in the PostgreSQL database. I created a table already, but I forgot to add not null constraints to the variable ?


texts:
In MySQL I'm getting the following error message when trying to call a stored procedure:

The table mysql.proc is missing, corrupt, or contains bad data

I've tried performing CHECK and REPAIR on the table, but it's not made any difference.

How can I fix the issue and get stored procedures back again?


texts: sub and sub-sub item organization system

texts:
So I am looking to find the best database design for the following situation. I will refer to my entities as 'articles' as that would best/most-easily describe them.

So the articles have already been created and have

id  | name   | content
1   | aricle1| the frog jumped...
2   | aricle2| Joe met stan...
3   | aricle3| Mikey has never...

Now, I would like to be able to retrospectively make certain articles sub-articles of other articles, and make other articles sub-articles of sub-articles (sub-sub articles). Most articles will NOT have a sub article, but many will.

So essentially it is building lots of little trees, with a presumed max-depth of 5.

I would like to be able to shift a set (articles and all sub-articles) under the domain of another article with (relative) ease.

After having done plenty of reading, I am almost 100% certain anything relating to the nested set model would be overkill and not appropriate.

Other than that however, I don't have much idea as to what is the proper approach here.

Sincere thanks for any help. It is greatly appreciated.


texts: Conceptual model vs Logical model vs Physical model

texts:
I'm doing a work about database, and now I need to show three different images, one image with the conceptual model, other with logical model and other with physical model of a database.

I'm looking for reliable information about this, but I find different answers and I'm a bit confused.

So I came here to see if you can help me.

I have below my three images, do you think I have the correct title for each image?

Conceptual model:

In conceptual model, I think that I neeed to put my tables with atributes but without relationships.

Logical Model:

In logical model, I think I need to put my tables with  atributes, but now with my relationships.

Physical Model:

In physical model, I think I need to put my tables with  atributes, but now with my relationships and also with foreign keys.


texts: How do I create an Oracle 11g user with read access to all tables/schemas?

texts: I need to create a user that has read access to all the schemas.  Do they just need Select priviliges or is there more that needs to be done?


texts: Why does SET ROWCOUNT not reset after GO?

texts:
According to the documentation for SCOPE_IDENTITY:

  A scope is a module: a stored procedure, trigger,
  function, or batch. Therefore, if two statements are in the same
  stored procedure, function, or batch, they are in the same scope.

According to SQL Server Utilities Statements - GO:

  GO is not a Transact-SQL statement; it is a command recognized by the
  sqlcmd and osql utilities and SQL Server Management Studio Code
  editor. SQL Server utilities interpret GO as a signal that they should
  send the current batch of Transact-SQL statements to an instance of
  SQL Server.

  The current batch of statements is composed of all
  statements entered since the last GO, or since the start of the ad hoc
  session or script if this is the first GO.

Armed with that information I wrote (in SMSS) this T-SQL:

SET ROWCOUNT 1    -- Limit to 1 row returned

-- Returns 1 row as expected
SELECT *
FROM MyTable

GO -- Batch ends

-- Still returns one row
SELECT *
FROM MyTable

From my understanding SET ROWCOUNT (I know it is deprecated in DML but not SELECTs) is scoped to its module (from what I have seen examining other posts at any rate).

My questions:

Is SET ROWCOUNT an exception to the 'module' rule mentioned above?
Is it NOT scoped to the module at all?
Is there a more complete definition of T-SQL scope?

I ran a query, then a stored procedure, that only sets rowcount to 4 (I picked 4 out of the blue) and then ran query again: so

SELECT * FROM MyTable
EXE MySproc
SELECT * FROM MyTable

...returns all rows then 4 rows then all rows again so it is definitely scoped to at least sprocs.


texts: How to handle failed Index Optimization (Ola Hallengren) step in SQL Azure Elastic Job Agent?

texts:
I have created a SQL maintenance job using Azure Elastic Job Agent with the following step:

EXEC jobs.sp_add_jobstep @job_name = @jobName,
@step_name = 'Optimize indexes and statistics',
@command=N'
    EXECUTE dbo.IndexOptimize
    @Databases = ''USER_DATABASES'',
    @FragmentationLow = NULL,
    @FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'',
    @FragmentationHigh = ''INDEX_REBUILD_ONLINE'',
    @FragmentationLevel1 = 10,
    @FragmentationLevel2 = 30,
    @MinNumberOfPages = 10,
    @TimeLimit = 3600,
    @UpdateStatistics = ''ALL'',
    @OnlyModifiedStatistics = ''Y'',
    @SortInTempdb = ''Y'',
    @MaxDOP = 1,
    @LogToTable = ''Y''
',
@credential_name = @jobStepCredName,
@target_group_name= @targetGroupName,
@retry_attempts  = 0,
@step_timeout_seconds = 3600,
@max_parallelism = 1 -- IMPORTANT! We don't want to run index optimization on multiple databases at the same time

The code uses stored procedure dbo.IndexOptimize provided by Ola Hallengren.

The job is scheduled to run daily at 5 am and fails once or twice per week. The reason for failure is the internal issue to the Azure Elastic Job Agent: "Jobs service restarted while this task was in progress.". The service from Azure is still in preview so internal service errors are expected.

My current solution is to set @retry_attempts to some number greater than 0 so that Job Agent can retry the step, however I'm not sure if that is a good idea to retry a failed step for index optimization.

In particular, I'm not sure what would happen to the INDEX REBUILD, INDEX REORGANIZE, OR UPDATE STATISTICS processes if they would be cancelled or killed.

So to summarize I have the following questions:

Is this a good idea to retry index maintenance if step fails?
What happens when INDEX REBUILD, INDEX REORGANIZE, OR UPDATE STATISTICS processes fail or terminate.

I appreciate your feedback on this matter.


texts: Postgres systemd service unit is in activating (start)

texts:
I'm experiencing Postgres service in activating state after incorrect server shutdown. The Postgres seems fine: connections are available and DB is running.

[root@postgres-02 ~]# systemctl status postgresql -l
● postgresql.service - PostgreSQL database server
   Loaded: loaded (/etc/systemd/system/postgresql.service; enabled; vendor preset: disabled)
   Active: activating (start) since Mon 2019-11-04 11:41:26 +08; 4min 38s ago
     Docs: man:postgres(1)
 Main PID: 4882 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─4882 /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
           ├─4884 postgres: checkpointer process
           ├─4885 postgres: writer process
           ├─4886 postgres: wal writer process
           ├─4887 postgres: autovacuum launcher process
           ├─4888 postgres: stats collector process
           ├─4889 postgres: bgworker: logical replication launcher
           ├─4918 postgres: sa30 sa30 192.168.1.3(43566) idle
           ├─4919 postgres: sa30 sa30 192.168.1.3(43568) idle
           ├─4920 postgres: sa30 sa30 192.168.1.3(43570) idle
           ├─4921 postgres: sa30 sa30 192.168.1.3(43572) idle
           ├─4922 postgres: sa30 sa30 192.168.1.3(43574) idle
           └─4934 postgres: sa02 sa02 192.168.1.4(34640) idle
Nov 04 11:41:26 postgres-02.domain systemd[1]: Starting PostgreSQL database server...
Nov 04 11:41:26 postgres-02.domain postgres[4882]: 2019-11-04 11:41:26.652 +08 [4882] LOG:  listening on IPv4 address "192.168.1.10", port 5432
Nov 04 11:41:26 postgres-02.domain postgres[4882]: 2019-11-04 11:41:26.653 +08 [4882] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
Nov 04 11:41:26 postgres-02.domain postgres[4882]: 2019-11-04 11:41:26.892 +08 [4883] LOG:  database system was shut down at 2019-11-04 11:41:09 +08
Nov 04 11:41:26 postgres-02.domain postgres[4882]: 2019-11-04 11:41:26.894 +08 [4882] LOG:  database system is ready to accept connections

It's confusing because of I have other server with similar unit and it runs OK like:

  Active: active (running) since

As I understand it's not necessary for ExecStart to spit me  out to a shell prompt after executing '/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data', but how systemd understand service is activated.

The unit:

[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)

[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0

[Install]
WantedBy=multi-user.target

all this stuff is powered by Red Hat Enterprise Linux Server release 7.7

What may we change to achieve the clear 'active' state there?

ps. previous week I did Postgres from source code and faced similar question, but then just rewrote unit to use 'pg_ctl'. in this case configuration seems unchanged, so it confuses me.


texts: Adding field constraints on child tables based on parent table

texts:
I need to add constraints to a particular field in all the entries of a child table with a foreign key to a single row in parent table. What is the best way to do it?

For example, my parent table has

    Id Money Name
    -- ----- ----
     1  30    xyz
     2  40    abc

And the table referencing this

    Id Cost Person
    -- ---- ------
     1  10    1
     2  10    1
     3  20    2

Here Person is the foreign key and I need to put constraint in Cost based on Money, for instance, the sum of all cost should be always lesser or exactly equal to the total Money.


texts: Can you optimize a view with many UNION ALLs to external databases?

texts:
Given these views in a Sql Server 2012 database:

create view vManyUnions as
    select 1 as p, a from vSubQuery_1
    union all
    select 2 as p, a from vSubQuery_2
    union all
    select 3 as p, a from vSubQuery_3
    ...
    union all
    select n as p, a from vSubQuery_n

create view vSubQuery_1 as
    select a from [externalDb].[dbo].[someTable] where b = something

create view vSubQuery_2 as
    select a from [externalDb].[dbo].[someTable] where b = somethingElse

create view vSubQuery_3 as
    select a from [externalDb].[dbo].[someTable] where b = somethingElseStill

...

create view vSubQuery_n as
    select a from [externalDb].[dbo].[someTable] where b = youGetTheIdea

And given this query:

select * from vManyUnions where p = 1

What optimizations, if any, can be made such that Sql Server 2012 only evaluates vSubQuery_1 when running the query?

*Note again, the sub query views call external databases.

FOLLOW UP: This was an example I contrived from a proprietary, much more complicated case (~1K subviews) where the execution planner includes more subqueries than the one subquery I expect. Thanks to those that replied and comfirmed that Sql should optimize queries against this type of view. It made me realize I was being lazy and hadn't actually tested my basic assumptions on how the optimizer works.

So I did that. I built a simple proof of concept and confirmed very fast that Sql optimizes the query. Following that, I worked more with the real-world view that still doesn't optimize. I found that, strangely, the planner is optimizing out many of the subviews. There are particular subviews that the planner always includes though. At this point I don't know why the planner always includes these particular subviews, any ideas? ...otherwise I'll post something if we figure it out.


texts: Should I use a Column naming convention or rely on column descriptions?

texts:
Frankly I am stunned that after some basic (very basic) internet searches I have not been able to find a comprehensive naming convention or guideline or SQL object and column naming.

The parallel I'm used to is in the .NET development world where there are many offered conventions and Microsoft has gone so far as to document a comprehensive set of recommendations which is widely adopted:

General Naming Conventions
Design Guidelines for Class Library Development
Guidelines for Names

1. Is there a well-adopted standard for database object, column, etc naming?

2. Are there any pitfalls with relying on the Description property for describing columns of a table? Obviously one wants the name to reveal intent but that's sometimes difficult in a database and descriptions can be invaluable for someone learning about the DB (although they can also lie).

Help much appreciated!


texts: Can't list users - "select * from user" returns only one row with one column

texts:
I have a heroku postgres add-on database with a user table. However, when I attempt to select * from user, all I get is:

  current_user
----------------
 rtsjlhdfptlaqd
(1 row)

The table name is definitely user. There is no users table. There are definitely a bunch of users in my app, but I can't seem to list them.

Thoughts?


texts: Why --audit commands does not work in Mongo Enterprise

texts:
I am trying to make destination for my user activity log but every time I type --auditDestination I get errormessage "unrecognized option --auditDestination", can anyone tell me what should I do?

I really need a help with this, if anyone can help with some solution I will be grateful :/


texts: How to export data from sql server 2008 to excel and email as attachement

texts: I'm new. Can anyone advise how I can export data from database sql 2008 to excel and email as attachment?


texts: Ubuntu Multiple MySQL instances and Apparmor

texts:
I am trying to set up multiple instances of MySQL on an Ubuntu dev machine and running into AppArmor problems.

I get this error in the syslogs:

kernel: [227631.776360] type=1400 audit(1329786835.208:146): apparmor="DENIED"
    operation="mknod" parent=7957 profile="/usr/sbin/mysqld"
    name="/run/mysqld/mysqld-5-5-20.sock" pid=8066 comm="mysqld"
    requested_mask="c" denied_mask="c" fsuid=114 ouid=114

mysqld_safe: mysqld from pid
    file /usr/local/mysql-5.5.20/data/naveen-desktop-ubuntu.pid ended

I checked threads here that suggest to use mysql_multi - but I do not know if mysql_multi allows instances of servers with different versions to be run.

I already have mysql-5.1 running and want to install mysql-5.5.

I know there has to be something wrong the way I have configured AppArmor - but no help from Google!

Here are details and what I have done so far:

OS : Ubuntu 11.10 desktop 32 bit
MySQL installed: default Ubuntu package 5.1.58-1ubuntu1
Trying to install: mysql-server 5.5.20
running all commands as root

To keep most of the new installation files separately into a single folder (/usr/local/mysql-5.5.50) I created 4 new directories under the main mysql-5.5.50

source: downloaded and unzipped MySQL files
data : to hold data (owned by MySQL)
log : for all log files
tmp : for tmp files

user=mysql has write access to the entire top folder mysql-5.5.50
created new my.cnf file in the same dir with these details:

[client]
port        = 3307
socket      = /var/run/mysqld/mysqld-5-5-20.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld-5-5-20.sock
nice        = 0

[mysqld]
user        = mysql
socket      = /var/run/mysqld/mysqld-5-5-20.sock
port        = 3307
basedir     = /usr
datadir     = /usr/local/mysql-5.5.20/data
tmpdir      = /usr/local/mysql-5.5.20/tmp

log_error   = /usr/local/mysql-5.5.20/log/error.log

AppArmor details:

/etc/apparmor.d/usr.sbin.mysqld has the line #include  at the end.

I added below lines to local/usr.sbin.mysqld:

/usr/local/mysql-5.5.20/* r,
/usr/local/mysql-5.5.20/** rwk,
/{,var/}run/mysqld/mysql-5.5.20.pid w,
/{,var/}run/mysqld/mysql-5.5.20.sock w,

I've restarted AppArmor with /etc/init.d/apparmor restart.

MySQL install:

mysql_install_db --user=mysql --datadir=/usr/local/mysql-5.5.20/data/

This command completed with PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! and I can see that /data now has files.

Start MySQL

/usr/bin/mysqld_safe --defaults-file=/usr/local/mysql-5.5.20/my.cnf

Now I get the error in the syslog.

What am i doing wrong?


texts: GRANT SELECT ON SEQUENCE succeeds, but to no effect?

texts:
I am trying to grant select access to sequences from one user/role to another. There are no errors when I run the command but once run, the second role is unable to view the sequences. I have run the exact same command on several other services/database instances which succeeded, this is only misbehaving one.

I have run both:

GRANT SELECT ON ALL SEQUENCES IN SCHEMA schema_name TO new_role;

As per the recommendation here:

https://stackoverflow.com/questions/7386321/grant-select-privilege-to-all-sequences-using-one-statement

And as I mention above, this has been successful on other database schema on different machines. I have also tried individually:

GRANT SELECT ON SEQUENCE some_id_sequence TO new_role;

and

GRANT SELECT ON SEQUENCE public.some_id_sequence TO new_role;

This also has no effect. When logged in from new_role I see:

select * from information_schema.role_usage_grants ;
...
(0 rows)

Similar results (or lack thereof) when running \ds.

I can see from the previous role that the sequence should be "grantable" (whatever that means, I can't find any documentation on it)

live@live ~ => select * from information_schema.role_usage_grants limit 1;
┌──────────┬──────────┬────────────────┬───────────────┬──────────────┬─────────────┬────────────────┬──────────────┐
│ grantor  │ grantee  │ object_catalog │ object_schema │  object_name │ object_type │ privilege_type │ is_grantable │
├──────────┼──────────┼────────────────┼───────────────┼──────────────┼─────────────┼────────────────┼──────────────┤
│ old_role │ old_role │ old_role       │ public        │ some_id_seq  │ SEQUENCE    │ USAGE          │ YES          │
└──────────┴──────────┴────────────────┴───────────────┴──────────────┴─────────────┴────────────────┴──────────────┘
(1 row)

So I don't really know where to look at this point. The old role appears to have the ability to grant select to other roles, and doesn't error when attempting to run the command, however the new role still has no access.

The results of \dn+

\dn+
                  List of schemas
┌───────────┬──────────┬────────────────────────┬─────────────┐
│  Name     │ Owner    │ Access privileges      │ Description │
├───────────┼──────────┼────────────────────────┼─────────────┤
│ new_role  │ old_role │ old_role=UC/old_role  ↵│             │
│           │          │ new_role=U/old_role    │             │
│ public    │ old_role │                        │             │
└───────────┴──────────┴────────────────────────┴─────────────┘
(2 rows)

\du+ new_role
                   List of roles
┌───────────┬────────────┬───────────┬─────────────┐
│ Role name │ Attributes │ Member of │ Description │
├───────────┼────────────┼───────────┼─────────────┤
│ new_role  │            │ {}        │             │
└───────────┴────────────┴───────────┴─────────────┘

The results from \dp

\dp some_id_sequence
                                     Access privileges
┌────────┬──────────────────┬──────────┬───────────────────────┬───────────────────┬──────────┐
│ Schema │       Name       │   Type   │ Access privileges     │ Column privileges │ Policies │
├────────┼──────────────────┼──────────┼───────────────────────┼───────────────────┼──────────┤
│ public │ some_id_sequence │ sequence │ old_role=rwU/old_role │                   │          │
│        │                  │          │ new_role=r/old_role   │                   │          │
└────────┴──────────────────┴──────────┴───────────────────────┴───────────────────┴──────────┘

Question: How do I establish what is preventing the sequence grants from being applied?


texts: Can mutli-threading java client cause high CPU utilization of SQL Server 2008?

texts:
I am a Java Developer, not a DBA, and would like to consult about an issue encountered in high CPU utilization in the SQL Server 2008 (not in the Java app server).

The Java client uses multi-threading, to simplify:

40 threads each select / insert / update (simple SQL statements) from Table X - autocommit is ON
10 threads each select / insert / update (simple SQL statements) from Table Y and selects (again simple)  from Table X (to check for existence) - autocommit is ON

Both set of threadpools run simultaneously. Each thread is assigned to "message from a queue" (a file), loads / reads its assigned "message", and inserts / updates to its corresponding table. These 2 thread pools get connections from a single connection pool via datasouce, for example:

    <Resource auth="Container" driverClassName="com.microsoft.sqlserver.jdbc.SQLServerDriver" maxActive="100" maxIdle="10" maxWait="10000" name="jdbc/abc_DataSource" password="p" type="javax.sql.DataSource" url="jdbc:sqlserver://NTxxx\\instyyy:12345;databaseName=dbzzz" username="u"/>

The DB environment is SQL Server 2008 R2 and is shared by other apps, but the Java client above was flagged to be the causing the CPU utilization. When the Java client was shutdown, the utilization stabilized. From the Java client's application server Jboss, the statistics where the Java was running does not have any performance issues, CPU and memory are fine; it was only the DB Server that got its CPU spiked.

My question is, does the high amount of threads, which will consume the datasource's pool of maxActive 100, potentially be the cause? If so, how can I prove this from the DB perspective, ie. SQL server statistics pointing to the high volumne threads from Java client?


texts: Rebuild or Reorganize indexes in Sql Server 2016

texts: My database is around 8 Tb and it has 34.ndf files & tables are portioned i'm using ola hallengren script for index maintenance but job is running for more than 2 days to finish the job. is there any other index maintenance script which can bring down maintenance time?


texts: USER_TAB_PARTITIONS Query Runs Incredibly Slow

texts:
I seem to be seeing incredibly slow (5-10 seconds) to run a query against USER_TAB_PARTITIONS (Oracle) intermittently.  When I do an explain plan on :

SELECT * FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='MY_TABLE'

One of the steps shows a Nested Loops/Merge Join (Option = CARTESIAN) operation against TABPART$.  The TABPART$ system table shows it has been analyzed in the last day.  Is there a known problem with 11.2 and USER_TAB_PARTITIONS?

By the way, I am running Oracle v11.2.0.1


texts: Alternatives to DBCC PAGE for working with page numbers in SQL Server

texts: Are there any management views or functions or procedures that can resolve page numbers to object id's?  Everything I'm finding says to use DBCC PAGE, but I don't have the requisite permissions.


texts: Keep total transaction in invoice application

texts: I want to make an application called as Invoice Application with MySQL database. Now I have designed this database. Now I want to know is this database good for Invoice Application. I will use MySQL with Yii framework(php). I am really confused in calculation table. In calculation table I want to store every individual invoice's total tax amount, sub total and total. So is this daabase good for all that purpose or should I keep total in InvoiceItems table?


texts: Why do nonclustered Indexes use clustered indexes instead of the table data pages directly?

texts:
From a Microsoft Course (presented by a third party) I learned that when a table has a clustered index even non-clustered indexes hit the clustered index.

When there is not a clustered index (Heap) as I understand it Nonclustered indexes reference directly into the pages of the table.

Assuming correct understanding, why do the non-clustered indexes reference the clustered index instead of the underlying data pages of the table directly? It seems like this would be less overhead referencing the table data pages directly.


texts: Availability Group Notification when new DB is added

texts:
When new DB is added to SQL Server's AG setup, a notification email like below is sent to us.

  The availability group database "XXXX" is changing roles from "SECONDARY" to "SECONDARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only.

What is confusing is the phrase

  "changing roles from "SECONDARY" to "SECONDARY" ..."

Can someone please decode the meaning of it?
Is it normal that a notification of such is sent when new DBs are added or am I doing it wrong or any other issues ?

--In 'thoughts'...


texts: Cannot Create Backup Device - SQL Server 2008 R2, Error 1326 (permissions look ok)

texts:
Trying to create a backup device for a new db. I'm using management studio and just using the "New Backup Device..." wizard and giving UNC path to the backup directory.

There is a pre-existing db on another server, it has backups working to the same Samba share.

I'm setting up backups for a new db on a new node, same vlan/subnet using the same share, same domain account for the SQL service user.

I can access and read/write just browsing the UNC with Windows Explorer
The share is presented as Samba from Gluster storage on RHEL.

Node1: Working node:

Service user: sqluser@domain.local, Path:\\shr01\main-storage\files\dbbackup\db1\full

Node2: Not working node:

Service user: sqluser@domain.local  Path: \\shr01\main-storage\files\dbbackup\db2\full

Both directories have the same permissions and ownership, I've tried pointing the path from Node2 to the either share but I get the same result with either paths from Node2:

This means that the path can be found, but the permissions aren't working.
The directory is owned by a sambaadmin@domain.local user from the file server and can be accessed by BOTH nodes in Windows with full read/write with the service user sqluser@domain.local, but it doesn't work via SQL Server backup device creation? Help. Please.


texts: Granting permissions to SSIS for developer

texts:
I installed SSIS a couple of months after installing SQL Server 2014.
I granted permissions to users per MSDN and rebooted SSMS and SSIS.
I can login remotely from my client without issue, but have sysadmin rights.

The other user cannot even see instance when trying to log in via client
Only way for her is to log in remotely to server, right click as administrator.

Any thoughts or suggestions on why this is?

Edit

It's a developer creating stored procedures and wants to store packages. There is going to be approximately 50 packages when complete with this project. She uses SSIS. I'm not that familiar with SSIS at this time.


texts: How to get users how do not have a group (through join table)?

texts:
I want to get users how are not present in a group. The group name is "everybody".

I have a join table called groups_users with user_id and group_id.

I did this

SELECT users.email
FROM users
  WHERE NOT EXISTS (
    SELECT groups_users.user_id
    FROM groups_users
    INNER JOIN groups ON groups_users.group_id = groups.id
    WHERE groups.name = 'everybody'
);

But it doesn't return any result. I have a user without any group and it doesn't appear. Did I miss something?


texts: How to combine date and time to datetime2 in SQL Server?

texts:
Given the following components

DECLARE @D DATE = '2013-10-13'
DECLARE @T TIME(7) = '23:59:59.9999999'

What is the best way of combining them to produce a DATETIME2(7) result with value '2013-10-13 23:59:59.9999999'?

Some things which don't work are listed below.

SELECT @D + @T

  Operand data type date is invalid for add operator.

SELECT CAST(@D AS DATETIME2(7)) + @T

  Operand data type datetime2 is invalid for add operator.

SELECT DATEADD(NANOSECOND,DATEDIFF(NANOSECOND,CAST('00:00:00.0000000' AS TIME),@T),@D)

  The datediff function resulted in an overflow. The number of dateparts
  separating two date/time instances is too large. Try to use datediff
  with a less precise datepart.

* The overflow can be avoided in Azure SQL Database and SQL Server 2016, using DATEDIFF_BIG.

SELECT CAST(@D AS DATETIME) + @T

  The data types datetime and time are incompatible in the add operator.

SELECT CAST(@D AS DATETIME) + CAST(@T AS DATETIME)

  Returns a result but loses precision 2013-10-13 23:59:59.997


texts: Using PIVOT to convert multiple rows and columns

texts:
So my current query output is as follows:

I would like to consolidate all of these rows to just one long row like so:

MyID    Customer  relc_rate  relc_consumption   relc_daysofservice   relc_unitmeasure   relc_charge   relc_chargedate
-----   --------  ---------  ----------------   ------------------   ----------------   -----------   ---------------
10016    112992    110W            753                  31                 KWH

and Pretty much repeat that for RGAS, RWTR, etc all across one row. Yes I understand that will be about over 50 columns if I consolidate all the service types into its own column.

Now I was able to get started with getting the service number into the "Type" using this Pivot query. However, I got stuck after that trying to figure out how to get each service type, along with the rate, etc into it's own column.

select *
from
(
 SELECT [Premise]
    , [Premise Address] premise_address
    , [Customer] as customer_no
    , [Service Type]
    , [Service Number]

 FROM UTIL_MAY2019
) premise_table
pivot
(
  sum([Service number])
  for [Service Type] in ([RELC], [RWTR], [RSWR], [RGAR], [STRM], [DELC], [SLCM], [CGAS], [CWTR], [CSWR], [CGAR])
) piv_premise

Here's the DDL for testing:

CREATE TABLE supercharge (
MyID int
 , customer int
 , Servicetype varchar(10)
 , servicerate varchar(10)
 , serviceNumber int
 , consumption int
 , daysofservice int
 , unitofmeasure varchar(10)
 , chargeAmount double
 , chargeDate datetime
)

INSERT INTO supercharge (MyID, customer, Servicetype, servicerate, serviceNumber,consumption,daysofservice,unitofmeasure,chargeAmount, chargeDate)

VALUES
(10016, 112992, RELC, '110W', 100, 753, 31, 'KWH',99.92, '2019-05-08'),
(10016, 112992, RGAS, '120', 200, 0, 31, 'CCF',5.31, '2019-05-08'),
(10016, 112992, RWTR, '130', 300, 3, 31, 'TGAL',11.85, '2019-05-08'),
(10016, 112992, RSWR, '388', 400, 3, 31, 'NONE',10.12, '2019-05-08'),
(10016, 112992, RGAR, '', 500, null, null, '',32.12, '2019-05-08'),
(10016, 112992, STRM, '', 700, null, null, '',2.38, '2019-05-08'),
(10016, 112992, '', 'LATE', null, null, null, '',15, '2019-05-13'),
(10016, 112992, '', 'CUTO', null, null, null, '',30, '2019-05-08');


texts:
I'm new in MySQL programing and I have one problem.
I created this query:

SELECT *,
       topics.createdate AS TopicCreateDate,
       (SELECT (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost
FROM   topics
       INNER JOIN categories
               ON topics.categoryid = categories.id
       INNER JOIN users
               ON topics.userid = users.id
ORDER  BY topics.lastupdate DESC

This query lists all Topics, joins categories and users by UserID and CategoryID in Topics and Username from TopicAnswers as LastPost. It orders them descending by LastUpdate in Topics that controlled by TRIGER.

It work pretty well, but there is a problem with this subquery:

            (SELECT (SELECT username
                    FROM   users
                    WHERE  id = topicanswers.userid)
            FROM   topicanswers
            WHERE  topicid = topics.id
            ORDER  BY id DESC
            LIMIT  1)        AS LastPost

It selects Username, of last post in topic, from Users by UserID from TopicAnswers as LastPost, now I want to select another one column from table Users, AvatarURL, but when I add :

                        (SELECT username, avatarurl
                        FROM   users
                        WHERE  id = topicanswers.userid)

It returns error:

  1241 Operand should contain 1 column(s)

How I can slove this problem?


texts: Is it faster to load several small files, or one big file?

texts: We have a 26GB file that we have to load into an empty table. I was curious, is it faster to load nine 3GB files or one 26GB file? Or is there no difference?


texts:
I'm new to execution plans in SQL Server and I'm trying to understand why SQL Server is doing some operations. The query is:

select P.PERSONID
from KSS_V_EMPLEADOS_PER_3_CERRADOS p, COMBHOMEACCT c, LABORACCT L, kss_carga_empl_centros k, CALENDAR ca
where p.PERSONID = c.EMPLOYEEID
and c.LABORACCTID = l.LABORACCTID
and k.empleado = p.PERSONNUM
and convert(date,ca.CALENDARDTM,103) between DBO.KSS_PrimerDiaMes(convert(DATE,@DFECHA, 103)) and DBO.KSS_UltimoDiaMes(convert (DATE,@DFECHA, 103))
and convert(date,ca.CALENDARDTM,103) between p.Inicio and p.Fin
and convert(date,ca.CALENDARDTM,103) between convert(date,c.EFFECTIVEDTM,103) and convert(date,c.EXPIRATIONDTM-1,103)
and convert(date,ca.CALENDARDTM,103) between k.FECHA_INI and k.FECHA_FIN
and l.LABORLEV4NM <> k.CENTRO

Table Information

KSS_V_EMPLEADOS_PER_3_CERRADOS is a view that has no indexes and involves some tables and functions. It has 12827 rows
COMBHOMEACCT c is a table with 16771 rows
LABORACCT is a table with 2460 rows
kss_carga_empl_centros is a table with 6594 rows
Calendar is a table with 146462 rows (it's just a table with a column that has one row for every day)

I'm able to understand most of the plan, but just before the last Inner join it does a Index spool that returns 830563 rows and I don't know why it is doing this index spool and cannot find where this number come from. I'm able to calculate all the other row counts without problem, but this branch of the tree calculates the days of the current month and passes it to the index spool operator. But what is this operator doing and why it returns 830563 rows?

I've uploaded the plan, just in case someone wants to see it:

https://www.brentozar.com/pastetheplan/?id=BkbEnkDNX

If you need more details please ask me

Thanks in advance


texts: Mysql - How number of columns effect the database performance

texts:
I have a table to store social media information for users; I have 42 columns to store and will have 100000 entries per day to store. I am planning to have MyIsam engine for it and will use horizontal partioning for the table but my questions are

should I also do vertical partioning and divide the table into 2 tables ?
If we select only 4 columns from a table in query then does the presense of other columns affect the query even if those are not in column list ?


texts: Creating a job scheduled based on a specific timezone

texts:
I have an Oracle database hosted in Europe. I need to create a scheduled job in the database to send email. I need to send email daily at 17:30:00 in India standard time (IST).

I know how to send email and create jobs. But don't know how to create a job to execute on my local time (IST).

How can I do this?


texts: PostgreSQL - can I use logical replication from a replica's WAL?

texts:
I would like to use logical replication. However, as PostgreSQL works now, it can eat up the whole disk and then block the database. Especially in Amazon Web Services RDS, this is a problem because RDS won't give you SUPERUSER role so you could connect and turn off the slots.

So I set up an RDS replica and set rds.logical_replication to 1. But it seems that RDS replica doesn't react to that - SHOW ALL says wal_level = hot_standby while it should be logical.

This is what I want to achieve:

RDS master
    WAL --> RDS replica
                logical replication ON
                WAL + wal2json  ---->    pg_recvlogical on remote client

Is this possible?

Reference: wal_level

When I try to create a slot, I get:

  FATAL:  no pg_hba.conf entry for replication connection from host "...", user "postgres", SSL off

This looks like AWS is not putting the right entries to pg_hba.conf - i.e. does not allow the postgres user to create the replication slots.

Is my assumption right? Is there any way around that?


texts:
The file which contains the day to day activities of each employee. And it shows the detailed description of employee what they done at work time. So these files are prepared by employee.

Now, my requirement is how to design a database for this model. I know only the basics of databases and yet I didn't design any databases. So any one help me to solve how to create a database design for the application.

The design should also work for the query to get details about individual employee like

For Ex:

Total time spent by the shiva 'shiva' on CCTNS project
To find the team leader for the specified project
To find the members in a particular project


texts: Row-based Permission in SQL Server Newbie Question

texts: I am relatively new to this( Database, general security issues) , so I may be over-complicating this. I have a database containing student grades . I  want to design a view in which I restrict access to each student . I can think of 3 ways of doing this: by granting permissions in master database, using access control lists maybe creating groups in AD and by assigning roles, though this would seem to require one role for each student which seems too much work. Can someone please guide me along? Thanks.


texts:
This evening a developer asked to restore only schemas from a database into another database.
A colleague of mine did this, but after that the developer came to me saying that the sequences are missing from the restored schema.
I think this was the command my colleague used to get a dump of the schema only :
/usr/local/pgsql915/bin/pg_dump -U backupuser --schema-only myegrtx -p 5432 -t tb_roadtax_kioskinfo -t tb_rtx_expdelivery_postcode -t tb_roadtax_inquiryinfo -t roadtax_tx_history -t tb_roadtax_payment_data_info -t tb_roadtax_renewalinfo -t tb_roadtax_kioskusers -t tb_roadtax_kioskuser_trans -t tb_roadtax_delivaryinfo -t tb_roadtax_asinfo  -t tb_roadtax_disk_print_info > myegrtxbike-schema.sql

And this was the command to restore it to the new db :
/usr/local/pgsql934/bin/psql -U postgres -d myegrtxbike -p 6434 -f srikanth/myegrtxbike-schema.sql

How do I now get the backup of and restore the sequences that have been reported missing?
I need some guide on this, as googling the commands is not a success (The commands do not work or I do not get the proper commands, especially for the sequence part).


texts:
I am configuring a 10g Express database on Windows 2003 that will be the back end for a public web server.  There are many other things to do to harden the database but a good place to start is user privileges.
I know the user needs Connect and Create Session privileges.  All the tables, procedures and packages are owned by them.

Are there any other privileges required for basic CRUD operations on their own schema?


texts:
I have the following data,

Year        DRC             DISP        Id
0           140.21          0.00        5808
1           112.37          1.00        5808
0           140.21          0.00        5824
1           112.37          0.00        5824

I want to pivot it like this,

Id          DRC Year 0      DISP Year 0     DRC Year 1      DISP Year 1
5808        140.21          0.00            112.37          1.00
5824        140.21          0.00            112.37          0.00

I have tried this,

SELECT *
FROM
(
    SELECT Year, DRC, DISP, ID From OriginalDataTable
) AS SourceTable

PIVOT
(
    max(DRC)
    for Year IN ([0], [1])
) AS PivotTable;

It pivots on the DRC, but not on DISP as well. The result is like this (0, and 1 is the DRC value). I understand why that is, but how do I pivot on the DISP as well?

DISP        Id          0           1
0.0         5808        140.21      112.37
0.0         5824        140.21      112.37


texts: TSQL for "Ignore databases were the state is not online"

texts:
I have a few custom jobs on a mirrored SQL Server 2005 and they fail from time to time because of automatic failover.

Is there some T-SQL code that I can write into my automated jobs or do I just have to remember to set failover so the job doesn't fail?


texts: "Cannot insert the value NULL", but there is no NULL values!

texts:
This question follows the answer of my other question there:
Insert with OUTPUT correlated to sub query table

The Person.LastName column has a NOT NULL constraint. When I execute this code:

CREATE TABLE tempIDs
( PersonId INT,
  FinancialInstitutionId INT
);

MERGE INTO Person
USING FinancialInstitution AS fi
  ON 1 = 0
WHEN NOT MATCHED THEN
  INSERT (CreationDate, AdministrativeStatus, LastName, Street1, Number1, City1, State1, PostCode1, CountryId1, WorkDirectPhone1, Fax1, Email1)
  VALUES (GetDate(), 'Legal', fi.Name, fi.Street, fi.Number, fi.City, fi.[State], fi.PostCode, fi.CountryId, fi.PhoneNumber, fi.Fax, fi.Email)
OUTPUT inserted.Id, fi.Id INTO tempIDs;

UPDATE fi
SET fi.PersonId = t.PersonId
FROM FinancialInstitution AS fi
  JOIN tempIDs AS t
    ON fi.Id = t.FinancialInstitutionId ;

I get the following error:

Cannot insert the value NULL into column 'LastName', table 'Person'; column does not allow nulls. UPDATE fails.

The thing is that there is no FinancialInstitution.Name that is NULL.

SELECT Name FROM FinancialInstitution WHERE Name = NULL

This returns no row. Beside, if I replace fi.Name with a value ('A Last Name') the request works.


texts: Would the error log for MySQL for the IBM i be in mysql.general_log?

texts:
I have an IBM i running Zend DBI (Zend Supported MySQL 5.1) that I'm trying to find log information for. I do SELECT * FROM mysql.general_log and get 0 rows.

my.cnf looks like this:

[mysqld]
sql-mode=NO_AUTO_VALUE_ON_ZERO
innodb_flush_log_at_trx_commit=0
innodb_buffer_pool_size=100M
innodb_log_file_size=20M
innodb_log_buffer_size=8M
key_buffer_size=64M
table_cache=256
sort_buffer_size=4M
read_buffer_size=1M
bulk_insert_buffer_size=8M

Process startup looks like this:

  /usr/local/mysql/bin/mysqld --defaults-file=/usr/local/mysql/bin/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysqldata --port=3306 --user=mysql --socket=/tmp/mysql.sock --pid-file=/usr/local/mysqldata/zmysql.pid --tmpdir=/tmp

Am I looking in the wrong place for error messages


texts: oracle DB connection - hostdef extension doesn't exist

texts:
I am trying to connect to an Oracle DB through ERwin, yet I keep getting this error message:

I know that the DB exists, as my coworkers are accessing it. The Username and Password are also correct, and I've had a few other coworkers try their credentials too and it didn't work.

The possible error might be with the connection string,but I've tried several options, and none seem to work. These are the options I've used:

jdbc:oracle:thin:@DomainName.DN.DN.com:1521:DatabaseName

Of course, I didn't actually type DomainName, I changed those from the company name. This was a direct copy of the connection string that was used in Squirrel SQL for this DB.

I also tried, based on a friend's suggestion:

jdbc:tibcosoftwareinc:oracle://DomainName.DN.DN.com:1521;SID=DatabaseName

This is the connection page that I'm using:


texts: MongoDB Restore / Snapshot Schedule - Point In Time vs. Oplog Timestamp

texts:
in docs Restore a Replica Set from a Backup those restore points are available:

Snapshot
Point In Time
Oplog Timestamp

What's the differences between those restore points? I guess Snapshot is fast restore, because only copy already existing files to destination. But it takes compute and storage resources the generate every 6 hours a snapshot and it will be stored only for 3 days.

I don't understand the difference between Point In Time and Oplog Timestamp. When to choose what (advantages and disadvantages)? Point in Time is only available 24 hours back, but Oplog Timestamp since first backup (Oplog Store)?

this is our snapshotSchedule. Any improvments for generic DBaaS provider, which doesn't know individual customers? Any issues?

configureDefaultBackupOptions = true
snapshotIntervalHours = 6 //Supported values are 6, 8, 12, and 24
snapshotRetentionDays = 3 // Supported values are 1 - 5
dailySnapshotRetentionDays = 10 //Supported values are 1 - 365
weeklySnapshotRetentionWeeks = 6 //Supported values are 1 - 52.
monthlySnapshotRetentionMonths = 6 // Supported values are 1 - 36
pointInTimeWindowHours = 24
retryIntervalInSeconds = 15
maxRetryDurationInMinutes = 30


texts: Log Shipping Backup grey in Transaction Log Shipping Status

texts:
I have slow network that makes configure log shipping via GUI always timeout, so I generate script and run it in both primary server and secondary server.

The scripts work fine but the primary database not show in secondary server log shipping report if I select [msdb].[dbo].log_shipping_monitor_primary in secondary server my primary database not insert at that table.

Is there possibly something wrong when I generate or run logshipping script that makes it not configure properly?
How to fix this, can I manually insert at [msdb].[dbo].log_shipping_monitor_primary or it not enough?

Currently the backup,copy and restore works fine in my server just backup status not show at report transaction log shipping.


texts: What's the difference between --read-only and just permit SELECT queries?

texts:
I'm using Amazon Aurora and I have two endpoints, my endpoint which can read/write are having some issues to execute queries, these are the issues:

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `integration` SET `setup_type` = ? WHERE (integration_id='4')

SQLSTATE[HY000]: General error: 1290 The MySQL server is running with the --read-only option so it cannot execute this statement, query was: UPDATE `indexer_state` SET `indexer_id` = ?, `status` = ?, `updated` = '2018-03-12 14:38:45', `hash_config` = ? WHERE (state_id='1')

Amazon uses the global --read-only configuration in one of the servers.
What's the difference between to use --read-only and a user which can just execute SELECT queries?


texts: Using SQL*Plus to connect to remote DB from local server, how do I export a backup to my local server?

texts:
I am trying to have my entire remote DB backup made to my local server.
Running the following command from my local server: expdp \"user/user@ip as SYSDBA\" full=Y directory=/backup/backup/ dumpfile=backup.dmp logfile=backup.log
I am met with the following error:

ORA-39002 invalid operation
ORA-39070 Unable to open log file
ORA-39087 directory name /backup/backup/ is invalid

I know the directory path on my local server does exist.


texts: SQL Server restore database file access error

texts:
I am trying to restore a database from a file on a remote machine. The script I am using is as follows:

RESTORE FILELISTONLY
   FROM disk = '\\server\path\to\file.dmp'
RESTORE DATABASE MyDB
   FROM disk = '\\server\path\to\file.dmp'
   WITH REPLACE,
   MOVE 'MyDB_Data' TO 'D:\path\to\file.mdf',
   MOVE 'MyDB_Log' TO 'D:\path\to\log.ldf',
   stats = 20
GO

I am using this script because the SQL Server Management wizard appear to dislike the use of a .dmp file instead of a .bak file, though my team has used .dmp files in the past without issue. However, whilst the RESTORE FILELISTONLY command appears to complete successfully, the RESTORE DATABASE command fails with the following error.

Msg 3203, Level 16, State 1, Line 3
Read on "\\server\path\to\file.dmp" failed: 38(failed to retrieve text for this error. Reason: 15105)
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

This seems unusual, as it appears to be an error with the .dmp file, but the previous RESTORE command did not complain at all. On top of this, the database in question is now stuck in a neverending "Restoring..." state which I cannot seem to get it out of. I tried RESTORE DATABASE MyDB WITH RECOVERY but that gave the same error, tried adding RECOVERY to the original script but just got the same error.

Any idea what's going on? I am new to DBA, myself, so I have limited experience to draw upon.


texts: Moving site to amazon EC2 - better to have 1 large instance or 2 medium instances?

texts:
I currently have a website which is running on windows 2008 r2 standard along with sql 2012 express. All on the same box. The site gets around 700-900 visits a day,although that is slowly going up (yea us!).

The database itself is around 2gb in size and makes use of full-text search. The bulk of the full text search is done against a product table which currently has about 36,000 records in it and that's slowly growing.

We're leaning towards moving the site over to amazon EC2.

I have 2 basic questions

Would it be better performance wise moving it to EC2 or keeping it on a dedicated box? The reason we're considering the move is the ability to expand capacity if/when the website becomes busier without any downtime. We've been putting a lot of effort into getting more traffic and it's slowly starting to pay off.
If we do move it, would we be better off getting 1 large EC2 instance and putting everything on that one instance or would we be better off with 1 medium EC2 and 1 medium RDS instance? I don't think there's anything really heavy going on with the website itself, most of the resource i think are being used by the database. Highest mem usage for the w3 process is around 200megs.

I don't want the site to run any slower then it's running right now. Faster would always be nice. Budget is around $250/mo but we're willing to go higher if we see performance gains. Using either 1 large or 2 medium instances looks like it'll cost around $170/mo and that includes a WEB license for sql, which would be an upgrade for us as we're running express right now.

Thanks


texts: Always On: replication over cross server SQL instances

texts:
I do agree, the question is not very clear. Let's explain that.

Context: we are wondering if it's a good idea (regarding our business and technical architecture) to use AlwaysOn features (SQLServer 2014).

A consultant told us to have (case of two servers to replicate): two servers (let's call them S1 and S2) in which we have two SQLServer instances (S1\DB1 plus S1\DB2 and S2\DB1 plus S2\DB2).
S1\DB1 and S2\DB2 are masters and S1\DB2 and S2\DB1 are replicas.

With a schema:

Instead of basic architecture (4 servers, one SQL instance per server):

Consultant's point of view: less servers so simpler management and less costs.

Question:is that a good way to implement SQLServer AlwaysOn?


texts: Does DROPing a table create a bunch of transactions in the transaction log for all the records being purged?

texts: If I have a table with billions of records, is it faster and does it create less data in the transaction log to TRUNCATE it first and then DROP it or can I just DROP it all the same?


texts: How to restore replication after server crash

texts:
We had two databases on one SQL Server 2005 instance with transactional replication between them (3 tables as articles). This server acted both as publisher and distributor.

Then the RAID on the server failed. However we managed to detach and copy almost all .mdf files before the crash.

After the system restored, we reinstalled SQL Server, restored system databases (master, model, msdb) and placed the .mdf files to the same paths. So it started.

The problem appeared with the replication. The Local Publications folder was empty, though the Local Subscriptions folder contained the subscription in question. When I tried to add a new publication I got:

  New Publication Wizard encountered one or more errors while retrieving
  publication names.

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

  Invalid object name 'dbo.syspublications'. (Microsoft SQL Server,
  Error: 208)

I decided to try to remove the left subscription (eventually I managed to) and disable publishing and distribution on the server, which gave me:

  SQL Server could not disable publishing and distribution on
  'someserver'.

  Invalid object name 'dbo.syssubscriptions'. Transaction count after
  EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is
  missing. Previous count = 0, current count = 1. Transaction count
  after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION
  statement is missing. Previous count = 0, current count = 1.
  Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
  TRANSACTION statement is missing. Previous count = 0, current count =
  1. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count =
  1. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count =
  1. Changed database context to 'master'. (Microsoft SQL Server, Error: 208)

It seems like some info was not recovered from the system database backups (probably distribution database may be missing something).

Also I tried to follow the answer given in unable to disable publishing and distribution. But to no success. I tried to create syspublications both in master and distribution, but nothing changed.

So, how can I make replication work on this server?

Tried to publish some other database on the server and it worked. So the problem seems to be connected with the published database. How can I clear it up to manage create a new publication over it?

System Tables subfolder of the problem database misses the replication tables, which are created, when a database is published.

In replication monitor I can see those old subscriptions, while they are not available in  Replication folder.


texts: Mariadb/docker error : Can't init tc log

texts:
I tried to dockerize MariaDB. It was running fine until it crashes and this is the log:

2016-11-21 15:25:21 140091445393344 [Note] mysqld (mysqld 10.1.14-

MariaDB-1~jessie) starting as process 1 ...
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Using mutexes to ref count buffer pool pages
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: The InnoDB memory heap is disabled
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Memory barrier is not used
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Compressed tables use zlib 1.2.8
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Using Linux native AIO
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Using SSE crc32 instructions
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Initializing buffer pool, size = 256.0M
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Completed initialization of buffer pool
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Highest supported file format is Barracuda.
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: 128 rollback segment(s) are active.
2016-11-21 15:25:21 140091445393344 [Note] InnoDB: Waiting for purge to start
2016-11-21 15:25:21 140091445393344 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.29-76.2 started; log sequence number 49590423178
2016-11-21 15:25:21 140090688898816 [Note] InnoDB: Dumping buffer pool(s) not yet started
2016-11-21 15:25:21 140091445393344 [Note] Plugin 'FEEDBACK' is disabled.
2016-11-21 15:25:21 140091445393344 [Note] Recovering after a crash using tc.log
2016-11-21 15:25:21 140091445393344 [ERROR] Can't init tc log
2016-11-21 15:25:21 140091445393344 [ERROR] Aborting

I was thinking that it was a disk problem but no, I have 14G available.


texts: Advice on designing databases for things like Posts and categories; and general rule of thumbs

texts:
I'm creating my own website with a capability to CRUD posts and other related stuff like category, tags and other things (I believe it's similar to the first two mentioned).

Posts: (current create post query)

id                BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
uid               BIGINT(20) UNSIGNED NOT NULL,
title             VARCHAR(50) NOT NULL UNIQUE,
content           LONGTEXT NOT NULL DEFAULT '',
postname          VARCHAR(255) NOT NULL UNIQUE,
posttype          VARCHAR(10) NOT NULL,
status            VARCHAR(20) NOT NULL DEFAULT '',
created_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
deleted_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

FOREIGN KEY(uid) REFERENCES ct_users(id)

Now my additional requirement is to have a category, tags and other future possibilities/properties.

My problem is the design, should I create new tables for each, like category table?

If so, Category would have something like:

id                BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name              VARCHAR(50) NOT NULL UNIQUE,
parent_id         BIGINT(20) UNSIGNED NULL

For Tags:

// This will have a relationship table between this and post table.
id                BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name              VARCHAR(50) NOT NULL UNIQUE,
description       VARCHAR(255) NOT NULL DEFAULT ''

Or should I make a complex relationship table that can support my requirements? Example is wordpress' wp_term, wp_term_relationship& wp_term_taxonomy.

Now for general database question. Rule of thumb.

Is it okay, or is it good to have many tables, possibly few records on some, used in 'your' application? Example is, refer to Categorytable at top.

Or is it much better to do it with things like wp_terms etc? To reduce the number of database tables.

Which one is more optimized? Or good in performance? Good and bad of...

If you could also provide links to good read articles and tutorials, would be much appreciated. As I've been googling, most of the stuff I find are basic queries and more info of features/functions with examples like JOIN, UNION and others.


texts:
I want to check which tables have been updated during a given period, for instance in descending order of access time per table.

How can I get that for PostgreSQL?


texts: Is there a way to know the parent - child relations (in terms of foreign keys) of all the tables in postgresql database? We are planning a full database migration using AWS DMS, we need specify in what order we can migrate our tables.


texts: Non Buffer Latch - Forefront Identity Manager

texts:
We are using Forefront Identity Manger and we are doing an initial load to which we are experiencing slow performance.

I always start with wait types for such things and can see %30 CXPACKET, %25 LATCH_EX and %15 ASYNC_NETWORK_IO.

I understand that LATCH_EX is a non buffer wait type so I looked in sys.dm_os_latch_stats and can see that %87 of the latch waits is down to ACCESS_METHODS_DATASET_PARENT.

Is this a problem?  Even on Paul Randals blog I am unclear what my issue is if at all.

Thanks


texts: Should a table have a foreign key to another table if it already has a foreign key to another table that has that foreign key?

texts:
That was a hard title to digest even for me so I'll break it down.

I'm very new to SQL, and I just designed my first database. I also am not very good at JOINS which might be the answer to my problem. But to get back on track I currently have three tables of interest:

comments

   id    |      author       |     category    | date_submitted
---------+-------------------+-----------------+----------------

replies

   id    | date_submitted | comment_id
---------+----------------+------------

images

           image_url            | reply_id |                    original_id
--------------------------------+----------+---------------------------------------------------

So given this, the question came to mind when I was trying get a table with the columns comments.id and images.original_id, an operation that I would like to do often.

Currently to do this I can't directly JOIN comments and images because they have nothing in common, meaning that the middleman has to be the replies table. So I have to JOIN replies and images and then JOIN that with comments.

So my question is this. Would it be okay to add a foreign key column referencing comments to the images table so that I can directly JOIN the two tables instead of going images -> replies -> comments, or would it be bad practice to add a foreign key that I already have to access to through the replies table?


texts: Aggregate values by interval range given by parameter

texts:
I have a table containing an amount column and an index column.
I wish to build a single query, which sums up the amount for the given interval ranges for every resulting interval from given start index to end index - i.e. the 'interval range' slices the data according index column. For these groups, sum over amount is built.
Is this possible and how?

SELECT sum(amount) from mytable
WHERE index between <startindex> and <endindex>
GROUP BY ...

Example:

amount | index
55        1
88        5
45        6
86        7
87        10
88        11
57        16
58        17
59        20
60        21

A query with interval range 5, start index 5 and endindex 14 should result in two return values:

  88+45+86 => 219
  87+88    => 175

A query with interval range 4, start index 0 and endindex 15 should result in four return values:

  55       => 55
  88+45+86 => 219
  87+88    => 175
  --       => 0

Thanks for any help!


texts: Is ELSE 0 implied in my COUNT CASE WHEN statement?

texts:
What's the difference between COUNT(CASE WHEN [Column A]  = ____ THEN 1 END
and COUNT(CASE WHEN [Column A]  = ____ THEN 1 ELSE 0 END?

I've been using the former and I haven't seen the difference thus far; what is the reason for adding the ELSE 0 - are there situations where SQL Server will incorrectly count?


texts: Setting max server memory in SQL Server 2014

texts:
I have a SQL Server 2014 Standard Edition server running Windows 2012 R2, with 128GB memory.

I'm attempting to configure SQL Server to use max memory of 96GB, but it appears to not be working.

It is defaulting to 128 GB.  On the first attempt I did not catch it defaulting to 128 which resulted in  errors 233, 33086 and 17300 and "Wait on the database engine recovery handle failed".

I've since uninstalled and reinstalled SQL Server, however I still cannot set max memory to less than 128...

I am using a service account created for running the database engine. No other features are installed.

The exact statement I'm using is:

EXEC sys.sp_configure N'max server memory (MB)', N'96' GO RECONFIGURE WITH OVERRIDE GO

Right after I added the statement to my question I saw my mistake.


texts: How to get service broker database queue status?

texts:
I need to get service broker queue status (Enabled/Disabled) somehow programmatically. Either, T-SQL, PowerShell, etc.
I tried $MyQueue = Get-ChildItem SQLSERVER:\SQL\mydbserver\default\databases\MyQueue\ServiceBroker\Queues | where {$_.Name -like "MyQueue"} but couldn’t find how to get the status.
Thanks in advance.


texts: Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant?

texts:
I have a big table (30 millions rows) defined like this:

CREATE TABLE `drawing` (
`id` int unsigned NOT NULL,
`artist` int unsigned NOT NULL,
`imageUrl` text NOT NULL,
`uploadDate` timestamp NOT NULL,
PRIMARY KEY (`id`,`uploadDate`),
KEY `uploadDate` (`uploadDate`),
KEY `artist` (`artist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED /*!50100
PARTITION BY RANGE ( UNIX_TIMESTAMP(uploadDate)) (
    PARTITION up2009 VALUES LESS THAN (1262304000) ENGINE = InnoDB,
    PARTITION up2010 VALUES LESS THAN (1293840000) ENGINE = InnoDB,
    PARTITION up2011 VALUES LESS THAN (1325376000) ENGINE = InnoDB,
    PARTITION up2012 VALUES LESS THAN (1356998400) ENGINE = InnoDB,
    PARTITION up2013 VALUES LESS THAN (1388534400) ENGINE = InnoDB,
    PARTITION up2014 VALUES LESS THAN (1420070400) ENGINE = InnoDB,
    PARTITION up2015 VALUES LESS THAN (1451606400) ENGINE = InnoDB,
    PARTITION up2016 VALUES LESS THAN (1483228800) ENGINE = InnoDB,
    PARTITION up2017 VALUES LESS THAN (1514764800) ENGINE = InnoDB,
    PARTITION up2018 VALUES LESS THAN (1546300800) ENGINE = InnoDB,
    PARTITION up2019 VALUES LESS THAN (1577836800) ENGINE = InnoDB,
    PARTITION up2020 VALUES LESS THAN (1609459200) ENGINE = InnoDB,
    PARTITION up2021 VALUES LESS THAN (1640995200) ENGINE = InnoDB,
    PARTITION upNew VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

As you see:

Table is partitioned based on the value of uploadDate, per year. Since there are lots of rows, I've found it useful when filtering by date (that is a typical operation in our DB). Speed of filtering increased significantly.
Since the partition is based on uploadDate, and we have also the identifier id, the primary key must be composed by both id + uploadDate

Sometimes we also perform filtering by artist, then sorting on uploadDate. No performances issues here.

Instead, we've found INSERT operations very slow.

Question: is it still useful to have an index on just (uploadDate)?


texts: How to structure Postgres for `/user/title` lookups?

texts:
I've got a website that serves lots of URLs in the style of /user/title, and I'd like for those queries to be efficient.

My database is structured like this:

CREATE TABLE account (
    id              serial PRIMARY KEY,
    username        varchar(60) NOT NULL UNIQUE
);
CREATE TABLE article (
    id              serial PRIMARY KEY,
    user_id         int NOT NULL REFERENCES account (id),
    title           varchar(255) NOT NULL,
    content         text NOT NULL
);
CREATE UNIQUE INDEX article_title_user ON article (title, user_id);

If I want to fetch a single record from ARTICLE based on a title, username pair, what's the best way to write that query?

I can do SELECT account.id FROM account WHERE account.username = 'someuser', and then SELECT * FROM article WHERE article.title = 'sometitle' AND article.user_id = 'someuserid_from_prev_query', but there must be a faster way to do it in one query.


texts: Generated DDL doesn't match with CREATE TABLE statement

texts:
I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg:

CREATE TABLE rooms (
    id INTEGER NOT NULL AUTO_INCREMENT,
    my_id VARCHAR(15),
    house_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE
)

When I use a DB tool such as DBeaver to see the details of my database and this table, I see that my foreign key house_id is missing the ON DELETE CASCADE setting.

DBeaver also shows the DDL for this table as follows:

CREATE TABLE `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `my_id` varchar(15) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `house_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `house_id` (`house_id`),
  CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Why does the DDL have the extra line KEY 'house_id' ('house_id').? This extra line also causes my foreign key to show 2 house_id columns in my DBeaver table viewer. If I get rid of this, my ON DELETE CASCADE gets set correctly.

I'm using an ORM so I don't have control of the CREATE TABLE statement, so I'm just trying to at least understand what's going on here.

Thanks,


texts:
Good day,
I have an SSIS package that runs on scheduled job for a while now. all was okay, until very recently, i started receiving some failed job executions. When I investigated it, I found the following errors:

Error: 0xC0202009 at PACKAGE_NAME, Source - Query [127]: SSIS Error Code DTS_E_OLEDBERROR.
An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Protocol error in TDS stream".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "Communication link failure".

An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"
Hresult: 0x80004005  Description: "TCP Provider: A connection attempt failed because
the connected party did not properly respond after a period of time, or established
connection failed because connected host has failed to respond.".

Error: 0xC0047038 at PACKAGE_NAME, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on Source - Query returned error code 0xC0202009.
The component returned a failure code when the pipeline engine called PrimeOutput().
The meaning of the failure code is defined by the component, but the error is fatal and
the pipeline stopped executing.
There may be error messages posted before this with more information about the failure.

Can anyone help?


texts: Relational vs Non-Relational Database for Events Database

texts:
I'm trying to find out whether an SQL or no-SQL solution would be better for creating an events database. I'm creating a ticketing system, similar to ticket master. I know that for either database-type storage is the simple part. The deciding factor is the performance of the following queries:

Select events by location within a specific date range.
Select all events in a given location (city, town, etc.), sort by date.
Search for events by keyword, within a specific date range, within a specific location.

Events basically have ID, NAME, LOCATION, VENUE, START DATE, END DATE

In a relational schema I would have an EVENTS table, a DATES table for storing dates separately because events can occur on more than one date and they are repeatable, and a VENUES table from which the event location (country, city, etc) can be cross-referenced.

I have no experience with no-SQL databases, so if you vote for no-SQL please suggest how you see the "schema" being organized and which particular DB.

I hope this question is specific enough. Query performance is the deciding factor.

After further consideration, I realize the question more properly distills to the availability of date/time functions that can facilitate fast date range queries.  I know MySQL and PostgreSQL have such functions.  PostgreSQL is even looking a little better at this point in terms of syntax.  I don't know what NoSQL solutions have to offer regarding this.

I know the system can certainly be modelled comfortably in a relational database. I also am aware that each no-sql solution is different. I was wondering if anyone with any specific knowledge of a particular no-sql DB could cite why that particular DB is good for the solution.


texts:
I'm trying to create a query that will return a list of results based on a series of conditions. For example - a list of movies based on interests/preferences.

Example Source Data:

+--------+---------------+-------+-------------+--------+
|MovieID | ShortName     | Genre |  Time(Mins) | Action |
+--------+---------------+-------+-------------+--------+
|   1    | LOTR 1        |Fantasy|    208      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   2    | LOTR 2        |Fantasy|    223      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   18   | Harry Potter 1|Fantasy|    174      |   TRUE |
+--------+---------------+-------+-------------+--------+
|   45   | Love Actually |Romance|    145      |  FALSE |
+--------+---------------+-------+-------------+--------+

A separate table contains a limited series of filters directly related to columns of data. For example:

Filter 1: ShortName (Contains) "LOTR" && Time (IsGreaterThan) 220 Minutes
Filter 2: ShortName (Equals) "LOTR 2"
Filter 3: Action (Is) "FALSE" && Time (IsGreaterThan) 60 Minutes
Filter 4: Genre (IsNot) "Fantasy" && Time (IsLessThan) 180 Minutes

  Filter 1 returns: MovieID (1,2)
  Filter 2 returns: MovieID (2)
  Filter 3 returns: MovieID (45)
  Filter 4 returns: MovieID (45)

Ignore the way results are shown above, it's just to exemplify the returned values.
I need the table of results to be returned in 1 execution of the query.
I'm skilled enough in SQL, however due to legacy platform constraints, I have to achieve this without using looping (While/For) functions.
Is there a name for this kind of query (to return a list of values from a table based on a complex filter statement), and can someone provide an example query to help me to understand the concept.
Please let me know in the comments if you require more information to help.


texts:
First question for me on the DBA SA site!

This is for an Java email notification app using MySQL for backend.

I have a small number of groups in our organization, [Marketing, Commerce, Infrastructure, Design]. They all are looking to utilize a small number of email templates for this app. Currently all of the groups utilize all of the templates [AAR Summary, Awareness, Daily Status, Hourly Deploy Update].

My question is what is a very simple, scalable way to design these two?
My first thought was to have two tables:

Division
    - DivisionID
    - DivisionName

Template
    - TemplateID
    - TemplateName
    - AssociatedDivision

However, I don't want to have duplicate templates for different divisions. In the same regard, a comma delimited list would help but I have no clue how I could query for all divisions and all templates each division has available.

There is currently a solution in place that is less than ideal. This solution is driving what people can and can not subscribe to.

Essentially, user x can subscribe or not to each template for each division.

EXAMPLE:

              AAR Summary    |    Awareness    |    Daily Status    |    Hourly Deploy
            -------------------------------------------------------------------------------
Marketing |       X                   X
Commerce  |       X                   X                  X                     X
Infra.    |                           X
Design    |                           X                  X

Help, if you need more info just let me know?


texts: difference of left join and left outer join

texts: In SQL Server join syntax we can use LEFT JOIN or LEFT OUTER JOIN and query result with each of above join not difference. this is a question for me that which are more useful.


texts: Getting a list of records that don't have any tags associated with them

texts:
Two tables:
CREATE TABLE `records` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` longtext COLLATE utf8mb4_unicode_ci,
  `tagged` tinyint(1) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `tags` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `recordId` INT(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

I'm trying to get a list of records that have 0 tags associated with them, ordered by (DESC) the number of occurrences of the record's name property.
Here's what I've got so far; I don't really know where to go:
SELECT
    `name`,
    COUNT(1) AS count
FROM
    records
GROUP BY
    `name`
ORDER BY
    count DESC
LIMIT 10;


texts: Storing numbers that will be also used as digits

texts:
I'm new to databases, please help me to figure out the best design to store 8-digit numbers that are going to be used for two purposes:

Statistical analyses of separate digits. For example all 2nd digits across all entries or frequency of appearance of a particular digit etc.
Matching whole number to find if the number is in the database in order to get data from relevant columns.

The usage ratio between these two purposes is expected to be close to 50/50. The initial amount of entries is around 6000, with yearly growing close to 2000 entries. Chosen DMS is MariaDB (MySQL).

I can see the 3 possible ways of implementing this:

Store whole numbers and split them each time via programming languages later (for usage #1).
Make 8 columns and store numbers as separate digits. Concatenate them each time via programming language later (for usage #2).
Store both, the whole numbers and separate digits in 8 columns.

But personally I'm not excited about any of these ideas of mine, so I'm seeking an explanatory advice from you.


texts: Grant permission to execute stored procedure without granting access to underlying table - Oracle

texts:
I am new to Oracle (10g), my background is SQL Server. I have a service account that needs permission to execute a stored procedure, but nothing else.

I see from here how to add execute permissions for a single stored procedure:
How do you grant execute permission for a single stored procedure?

Say I have a stored procedure GET_A(), which returns the results of a SELECT from Table A. I don't want to grant this user permission to query Table A directly. Will granting permission to execute GET_A() be sufficient?

I know in SQL Server I can grant a role access to specific Views and Stored Procedures, without granting permission to the underlying Tables. However, my in-house DBAs tell me this is not possible with Oracle.


texts: Entity Relationship Diagram - Entity depending on attribute

texts:
I have an ER-Diagram containing the entity employees. When employees have high skills they may supervise lower skilled employees. How can I model that in an ER-Diagram?

My initial approach of was to have a disjunction of high skilled and low-skilled employees. But that appears to be rather inconvenient.


texts: Oracle SQL Developer Textcursor disappearing

texts:
In the Oracle SQL Developer on my manjaro Linux, the blinking cursor in the editor area won't show anymore. I deleted everything of local config files (~/.sqldeveloper/) and reloads the program. At first, the cursor is visible again but after i pressed the "excute script (F5)"-Button, it will get lost and there seems no way to fix this.

My setup:

SQL Developer

Version 4.1.5.21
Build MAIN-21.78

Java

java version "1.8.0_112"
Java(TM) SE Runtime Environment (build 1.8.0_112-b15)
Java HotSpot(TM) 64-Bit Server VM (build 25.112-b15, mixed mode)

OS

Linux 4.4.39-1-MANJARO


texts: How do I place an UpdateLock on a row with an index

texts:
Let's assume I have a table like this on a SQL Server 2014:

CREATE TABLE Accounts (
    Id int IDENTITY(1,1) NOT NULL,
    OwnerId int NOT NULL,
    Balance bigint NOT NULL
CONSTRAINT PK_Account PRIMARY KEY CLUSTERED (Id ASC),
CONSTRAINT UQ_OwnerId UNIQUE NONCLUSTERED (OwnerId)
)

Let's assume that there is a row with Id=1, OwnerId=1 and Balance=100.

I need to select the Id for a specific Account (only the Id) but at the same time would like to lock the row against updates.

In the first session I execute the following statement (in a transaction):

SELECT Id FROM Accounts WITH (UPDLOCK, ROWLOCK) WHERE OwnerId = 1;

In the second session I execute the following statement (in another transaction):

UPDATE Accounts SET Balance = 500 WHERE Id = 1;

Both statements complete without being blocked, because the first statement locks on the unique index. What can I do to force the second statement to wait?

Solutions I've already come up with, but don't like:

Select the whole row (SELECT *) instead of only the id on the first query → This results in too much overhead in my application.
Add an INDEX=PK_Account hint to the first query to force locking on the PK → I lose performance because this results in a clustered index scan.

Is there any other way to retrieve only the Id (while using the UQ_OwnerId) and prevent any update to the row?


texts: Drop tables where the last update is over 1 week ago

texts:
I can see in PMA that it stores information for every table.

I was wondering if there was an SQL query to drop any table in the database that have a last update time of more than 1 week ago?


texts: Data Warehouse how normalized for tracking metadata changes?

texts:
I need to build out a warehouse structure that is populated by periodic feeds consisting of a mix of scraped data, authority sources, and primary sources. In order to prioritize which data should be used for a data mart, I need to keep track of each data point's creation (when did we get this data?), update (when did the data change?), and existing match (when was the last time this data showed up?). From my research it looks like 5NF is the level of normalization I should use.

For example, I may get a feed which gives Student Names along a list of their favorite classes and rank order. Another feed may come in with the same data, but might have an incomplete list of classes, or lack rank order. Every feed would come in with a unique and reliable student identifier

I would was thinking there would be at least three tables for just that data:

Student (s_id, name)
Student_Class (s_id,class_id)
Student_Class_Order (s_id,class_id,order_num)

Each of those would also have columns for isDeleted, created_time, created_feed_id, modified_time, modified_feed_id, matched_time, matched_feed_id.

What kind of structure would make the most sense? It seems like any new combination of datapoint should be inserted, and I should not have updates (except for a soft-delete flag). Any ideas?


texts: Poor execution plan leads to query time increasing dramatically

texts:
I have a relatively complex query like the one that follows
select * from (
  select
      distinct on("car")
      entity.id as "car",
      metric as "last_visit"
  from now() ref_date
  cross join (values (142), (432), (583), (448), (381)) entity(id)
  left join (
    select
        gv.car_id as _entity_id,
        gv.start as _tc_past,
        gv.start as _keep_max_of,
        g.name as metric
    from garage_visits gv
    left join garage_tree_flat gtf on gtf.descendant_id = gv.garage_id
    left join garage g on g.id = gtf.parent_id
    where true
    and price != 0
    and  g.type = 2
  ) x
  on x._entity_id = entity.id
    and _tc_past < ref_date
    and _tc_past >= ref_date - interval '12 weeks'
    order by "car", _keep_max_of desc
) t
where t."last_visit" is not null;

My problem is that the query running time will vary tremendously based on the number of elements that are present in cross join (values (...)).
Namely, I have the following average running times depending on how many values there are:

# of car_id values
avg running time

1
25ms

2
30ms

3
35ms

4
40ms

5
4900ms

6
4900ms

7
4900ms

Checking the execution plan for both versions (up to 3 values or more than 3 values) shows that the culprit is the index scan on garage_visits gv. This table contains several million rows.
In the case of up to 3 car_ids, the index condition on garage_visits is as follows:
Node Type: Index Scan
Index Name: ix_gv_car_id
Index Cond: (gv.car_id = "*VALUES*".column1)
Actual Duration: 0.687ms

In the case of more than 3 car_ids, the index condition on garage_visits is as follows:
Node Type: Index Scan
Index Name: ix_gv_garage_id
Index Cond: (gv.garage_id = gtf.descendant_id)
Actual Duration: 3386.068ms

As we can observe, with up to three values, the query planner decides to filter garage_visits by car_id, and outputs only these rows, which is extremely fast. But with more than three car_id values, the planner decides against using this information and instead filters garage_visits by gtf.descendant_id instead, which dramatically changes the execution time because there are many more gtf.descendant_id values than there are car_ids values.
Is there a way to keep using the ix_gv_car_id index but with more than 3 car_id values? It looks like it's simply a wrong choice here from the query planner and it's frustrating to see that this query could run really fast (even for 30 or 50 values) but instead spends a lot of wasted time.


texts: 2 Cluster managers Control same 2 nodes and control OLTP traffic?

texts: can you have two cluster managers controlling the same 2 nodes so that you can achieve real time replication but still direct your oltp traffic to one node and your reports to the other node?


texts: Is it okay to use rsync on InnoDB database if the MySQL server is shutdown?

texts:
Is it okay to use rsync on InnoDB database if the MySQL server is shutdown ?

For example :

/etc/init.d/mysql stop
rsync -av /home/mysql/mydb/ root@newserver.com:/home/mysql


texts: sys.sp_audit_write Procedure not available

texts:
I am trying to execute the audit write procedure but it can't be found. I have looked in master for it in the system procedures list and confirmed it isn't there. Does anyone know why this might be? I have also looked on another instance of the same server type.

I am also connected to the right instance and have selected the master DB.

I am using SQL server 2012.

 EXEC sp_audit_write @user_defined_event_id= 12, @succeeded = 1,
@user_defined_information = @msg;

I am getting error:

Could not find stored procedure 'sys.sp_audit_write'.


texts: Why is column default value not portable through different RDBMS?

texts:
Some ORMs (doctrine) do not support default values in database schema definition, because they states it is not portable.
But SQL92 supports the DEFAULT value clause, and all the databases supported by the ORM (MySQL, PostgreSQL, SQL Server, Oracle) support it too.

What are the pitfalls of the DEFAULT clause in different vendors/versions of RDBMS?


texts: At what point does one database per client become unfeasible?

texts:
For one of our systems, we have sensitive client data and store each client's data in a separate database. We have about 10-15 clients for that system.

However, we're developing a new system that will have 50-100 clients, maybe more. I am thinking it might be unfeasible to have one database per client in this instance (to store sensitive records and audit history). However I don't know if this is perfectly normal or not, or if there's another way of maintaining security.

Any thoughts on this?


texts: Calculating the optimal size for the oracle undo tablepsace

texts:
To size the undo tablespace I query v$UNDOSTAT

SELECT BEGIN_TIME, END_TIME, UNDOBLKS, MAXQUERYLEN from v$UNDOSTAT;

BEGIN_TIME          END_TIME              UNDOBLKS MAXQUERYLEN
------------------- ------------------- ---------- -----------
14.10.2016 10:38:57 14.10.2016 10:48:13        911         244
14.10.2016 10:28:57 14.10.2016 10:38:57        977        1120
14.10.2016 10:18:57 14.10.2016 10:28:57       1031        2266
14.10.2016 10:08:57 14.10.2016 10:18:57       1018        2365 <--

To find out the blocksize of the undo tablespace, I query

SELECT TO_NUMBER(value) DB_BLOCK_SIZE FROM v$parameter WHERE name = 'db_block_size';

DB_BLOCK_SIZE
-------------
         8192

Now I can calculate the undo tablespace size. I have to divide the UNDOBLKS by 600 to get the rate of undo generation in blocks per second, and multiply by the blocksize to get the figure in bytes. Multiply by the largest figure for MAXQUERYLEN to find the space needed if the highest rate of undo generates coincided with the longest query and then divide by a billion to get the answer in gigabytes

1018/600 * 8192*2365=328713557,33
328713557,33/1000000000=0.3287GB

Is this a correct calculation? Should I consider other metrics to calcualte the size of the undo tablepsace?


texts: How to calculate exact month (in days) difference b/w two dates

texts:
I am trying to get all the rows where the visit date was exact before five months from to date but somehow DateDiff function is including 26th Sept 2013 record which is not right as five month has average 150 to 153 days. I think it should count up to 3rd Sept 2013.

I am using DATEDIFF(MONTH, VisitDate , GETDATE())  > =5

Should i use DATEDIFF( DAY  , VisitDate , GETDATE()) >= 153 instead