texts: SSAS -> New cube contains two fact tables. How do I relate these so I can use measures from both at the same time?

texts:
I am working on a new cube.  I know that you cannot directly create a relationship between two fact tables (and expect good results).

I am just wondering how to go about doing this I have read around and looked at examples, but I am just having difficulties wrapping my head around how this works.

My example would be these two fact tables.

Fact Table A:

FactDeliveryDriver (example measure: years of service)

Fact Table B:

FactDeliveries (example measure: miles)

Each of these fact tables contain measures specific to them.  I am required to have these two both in the same cube.  So I have a primary surrogate key in factdeliverydriver called driverSid and a primary surrogate key in factdeliveries called deliverySid.  I also have the driverSid in factdeliveries even though I know I cannot directly link them.

I am guessing that I need some sort of bridge table.  Again, I have read up on these but haven't really had much luck finding anything that shows how this would work with two fact tables.  Maybe I have been looking at this for too long.

Any direction/advice is greatly appreciated.

Thanks.


texts: What factors do we need to consider in order to enable auto-growth settings for my PROD database

texts:
I want to know the factors that we need to consider in case we need to enable auto-growth settings for my PROD database.

I have already checked the database growth history and the database is growing 1 to 1.5 GB daily on a daily basis(on an average).

Current Database size is 600 GB. Also, I have checked the available free space and we have sufficient space for the files to growth. However, if I set auto growth now to 1 GB, then this space will get filled up after some time.

Please suggest me some more factors that should be taken into account so that I can utilize this available free space effectively and thus eliminate the need for new disk for at-least 10-12 months.


texts: How to get a single row per group where MAX(column) or first if none?

texts:
I have the following schema:

users:

id email
1  'user.one@test.com'
2  'user.two@test.com'

video_group:

id title
1  'Group 1'
2  'Group 2'

videos:

id group_id rank title
1  1        1    'Group 1 - Video 1'
2  1        2    'Group 1 - Video 2'
3  2        1    'Group 2 - Video 1'

user_video_play_times:

video_id user_id time last_update
2        1       12   01-02-2018
1        1       120  01-01-2018

I need to get the time, user_id, video_id, and group_id of the last video played by a user in specific groups, but if there's no records on user_video_play_times for a group, the video with the lowest rank should be returned. For example:

user_id group_id video_id time
1       1        2        12    -- user.one + group 1
1       2        3        0     -- user one + group 2

This is the query I have so far:

SELECT
   pt.user_id user_id,
   v.id       video_id,
   g.id       group_id,
   pt.time    time
FROM
   videos v
   INNER JOIN video_groups g ON g.id = v.group_id
   LEFT JOIN user_video_play_times pt ON
      pt.video_id = v.id AND
      pt.user_id = 1
   LEFT JOIN (
      SELECT
         g.id AS g_id,
         MAX(pt.last_update) AS pt_last_update
      FROM
         user_video_play_times pt
         INNER JOIN videos v ON v.id = pt.video_id
         INNER JOIN video_groups g ON g.id = v.group_id
      WHERE
         pt.user_id = 1 AND
         g.id IN (1, 2)
      GROUP BY
         g.id
   ) lpt ON lpt.g_id = g.id AND lpt.pt_last_update = pt.last_update
WHERE
   g.id IN (1, 2)
GROUP BY
   g.id

It is sort of working, but...

Adding v.title to the column selection messes the results for some reason, making everything return only videos with rank 1. Any idea why?
Could this query be optimized, or is there another slicker way to achieve the same results?

Any help with this is really appreciated!

db<>fiddle here


texts: Replace Character Without Using Looped REPLACE Function

texts:
My SQL Server database input values follow a pattern of where the first position is a character and then followed by number. I want replace the first character with another character or characters . For example:

input

Q234567888
R264747848
B264712481

output

I234567888
I264747848
U264712481

Potential values for the first position are [A-Z].

I am looking for an option without using multiple REPLACE statement and not using CASE.

If I use CASE, I will have to check 26 cases or if I use REPLACE in a nested way, I may end up using  26 REPLACE statements.  I am trying to avoid these.

Please suggest any better way to do achieve the results.


texts: Toad connection to new database oracle : ORA-12505. TNS: listener does not currently know of SID given in connect descriptor

texts:
I have created a new Oracle Database. I checked the tnsnames.ora file , I found the new instance is created in this file:
SIRH =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1421))
( CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SIRH.DB.COM)
)
)
LISTENER_ SIRH =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1421))

I tested the access from TOAD by this informations in the picture , but it generated the following error:
ORA-12505. TNS: listener does not currently know of SID given in connect descriptor
update : for the result of  lsnrctl status


texts:
I am evaluating PostgreSQL as an alternative to Oracle. I have a database with 533 Tables which contain up to 250,000 entries.

For a performance comparison, I built the datbase both on Oracle and PostgreSQL.

However, PostgreSQL is much slower, and it doesn't store much in RAM and instead has huge Disk I/O.

My performance tests:

Insert 50,000 entries into a table with roughly 250 columns
Select all of them including a join on a different table
Update a string field appending an 'A'
Dropping an entry in a referenced table which causes all entries to be deleted (on delete cascade)

My System configuration:

Windows 7, 2 Xeons à 8 cores, 32GB Ram, 256GB SSD
PostgresSQL 9.6
Oracle XE 11
MariaDB 10.2

Below is the performance I measured (average of 5 runs):

The NoTriggers version ran with an ALTER TABLE x DISABLE ALL TRIGGERS.

As seen in the Chart, PostgreSQL does not really use the available ram.
Looking into the resource monitor, it is indeed using high disk io:

What I have tried so far:

Increasing effective_cache_size to 16 GB
Setting autovacuum to off
Setting max_connections to 3
Setting shared_buffers to 3GB
Setting work_mem to 512MB
Setting maintenance_work_mem to 64MB
Setting synchronous_commit to off
Even setting fsync to off

None of these had any impact on either Disk IO or Memory Usage, nor the Performance.

What am I missing? Is there a flag I missed? Is windows just so bad as a host for PostgreSQL?


texts: Cannot run pgAdmin from the terminal: No such file libwx_gtk2u

texts:
(Probably irrelevant) I'm using Puppet to setup/manage a class of computers that are equipped with PostgreSQL and PostGIS, and this implies that each computer goes through an unattended install:

PostgreSQL

command     => '/bin/mount -o remount exec /tmp                ;
                /bin/mkdir -m 0755 -p /opt/PostgreSQL/9.1      ;
                /tmp/setup/postgres-9.1.4.run                  \
                  --mode unattended                            \
                  --superaccount postgres                      \
                  --superpassword sUP34_se<uR3^P4sSw0rd        \
                  --datadir /opt/PostgreSQL/9.1/data           \
                  --locale 1                                   ;
                /bin/mount -o remount /tmp                     ;
                /bin/touch $installflag',

I should mention that, even though the install succeeds (exit code 0, at least), PostgreSQL does not appear under Applications.

PostGIS

command     => '/bin/mount -o remount exec /tmp      ;
                /tmp/setup/postgis-1.5.bin           \
                  --mode unattended                  \
                  --pgport 5432                      \
                  --pguser postgres                  \
                  --pgpassword sUP34_se<uR3^P4sSw0rd ;
                /bin/mount -o remount /tmp',

When I now attempt to run /opt/PostgreSQL/9.1/bin/pgAdmin3 --version, I receive the following error (formatting added):

./pgAdmin3: error while loading shared libraries:
            libwx_gtk2u_stc-2.8.so.0:
            cannot open shared object file:
            No such file or directory

running locate libwx_gtk2u gives me the path /opt/PostgreSQL/9.1/pgAdmin3/lib as its location, so following the advice of a forum post, I alter LD_LIBRARY_PATH, previously empty, to be

export LD_LIBRARY_PATH=/opt/PostgreSQL/9.1/pgAdmin3/lib

which only spits out another error:

pgAdmin3: error while loading shared libraries:
          libssl.so.1.0.0:
          cannot open shared object file:
          No such file or directory

I'm out of both ideas and pages of Google search results.

I am running Red Hat Enterprise Linux 5 x86_64.


texts:
My application is working in Real cluster application(RAC)in production. I am new to RAC and  gone through this video .

My understanding after reading this

RAC uses more than one node only for data processing the data in memory but still uses one node to store data
processing can be like applying index, order by clause, any function etc
So its the load distribution at processing level not at data storage level

Is my understanding correct ?


texts: Deleting duplicate subscribers using max uuid postgresql

texts:
I am trying to eliminate duplicate subscribers from the subscribers table for a api_customer_id, this is the query that I use to get those duplicate subscribers :

select subscribers.email, count(*) from subscribers
inner join subscriptions on subscriptions.subscriber_id = subscribers.id
inner join subscription_lists on subscription_lists.id = subscriptions.subscription_list_id
where 793 in (subscribers.api_customer_id, subscription_lists.api_customer_id)
group by subscribers.email
having count(*) > 1

I tried to delete everything that is not max(subscribers.id), but I could not because the id column type is uuid. Here is that error :

ERROR:  function max(uuid) does not exist
LINE 1: select max(subscribers.id), subscribers.email, count(*) from...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

What can I do in the meantime to get rid of the duplicates?
There is created column on the subscribers, but the times are very close together that I'm fearful to use that to max/min on. Any other suggestions?

Update 1:

Per question in the comments I do have another column updated at too, they look like this :

But they're all in the same second, and it's still risky. I don't have the flexibility to change this design now, maybe I can going forward.


texts: Comparing previous/next record to calculate an average speed

texts:
I have a simple table ina MySQL db recording the speed in m/s of a production machine every n seconds.

ID    SPEED    TIME
----------------------------------
1     2.5      2017-01-27 08:23:03
2     1.9      2017-01-27 08:22:02
3     2.0      2017-01-27 08:21:02
4     2.1      2017-01-27 08:20:01
5     2.3      2017-01-27 08:19:03

ID is an auto increment field.

Due to some lacks in signal transmission, the time interval between two rows is not always the same.
What I would do is:

Calculate the difference of time between two rows (assuming that in
that timeframe speed is constant);
Multiply the calculated timeframe for the speed (so I get a distance);
Sum the results (I get the total distance);
Calculate the difference in seconds between the first and last records selected (I get the total time);
Divide total distance for total time getting my average speed.

My problem is to understand how to get previous and next record and how to do that iteratively.

This is now in MySQL, but will be moved to Postgres soon.


texts:
I'm designing my table, but I have doubts: I will have two columns A and B that cannot have repetitions and, in the same row, can't both be null.

I don't know if the best thing to do is to use a primary key with a combination of the values of A and B or a completely different column (for example an autoincremented column).


texts: Trying to work out how to escape a hyphen in an XML Tag in Azure SQL Analytics

texts:
working with Kusto / KQL to try and parse the deadlock xml in Azure SQL Analytics and getting stuck with escaping the tag name process-list as follows
So the deadlock xml looks like
<deadlock>
  <process-list>
  ...

I want to get one of the attributes under process-list but KQL chokes on the hyphen and I don't know how to escape it to make it work - see the bold section in the last line
AzureDiagnostics
| where (Category == "Deadlocks")
| where ResourceId contains "/SERVERS/"
| where ResourceId contains "/DATABASES/"
| where Resource contains "Analytics"
| project Resource = strcat(extract(".+/SERVERS/([^/]+)/.+", 1, ResourceId), ".", extract(".+/DATABASES/(.+)", 1, ResourceId)).tolower(), TimeGenerated, deadlock_xml_s
| extend DeadlockXML = parse_xml(deadlock_xml_s)
| extend KeyDeets = DeadlockXML.deadlock.process-list.waitresource


texts: Adding Security to a legacy DB with Schema

texts:
I am working on a DB where almost every technical user has full read / write permissions on all tables. We are working towards removing permissions, gradually, from certain users, and I had hoped that implementation of some new code on a new schema would be able to be strictly implemented from day 1.

However, I have realised today that most of our users have been granted db_datareader and db_datawriter role membership, which means that they all have access to this new schema by default. Therefore, I want to find a way forward that gets this schema secure without creating a load of additional work.

My idea is:

Create a new 'generalReader' role which has SELECT privilege on our huge dbo schema, and a couple of other schema we have (but are not being changed right now);
Create a new 'generalWriter' role with UPDATE, INSERT, DELETE privileges on the dbo and existing schema;
Drop the db_* roles from users, and replace them with the new generalReader and generalWriter roles;
Add new roles to control access to the new schema - I think most people could be allowed to SELECT from it, where only a few should be granted INSERT privileges. (e.g. I would create an LoanAccountReader and LoanAccountInserter roles)

Does this sound like a broadly appropriate plan to move forward - or am I missing some tricks or some security issues?

Before coming up with the above, I did some experimentation with roles and could create roles DENYing write access (that overrides the database writer role), but could not then re-enable that permission with a 'writer' role.


texts: Large accounting table, best way to partitioning by two dates

texts:
I need to create a log table to stores the connections to out network (last 3 years, after that the log will go into backup). The hardware/software used is proprietary, and for accounting it just call our custom script with some arguments like this:

when a user connects (our_script START user mac ip);
when a user disconnects (our_script STOP user mac ip in_bytes out_bytes more)

Sometimes we do not receive the disconnect message. So we need to adapt to this.

So far I came up with this structure for the accounting table:

CREATE TABLE `accounting` (
  `user` varchar(50) NOT NULL DEFAULT '',
  `mac` varchar(20) NOT NULL DEFAULT '',
  `ip` varchar(15) NOT NULL DEFAULT '',
  `ipv6` varchar(39) DEFAULT NULL,
  `start_datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `stop_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `in_bytes` bigint(32) unsigned DEFAULT '0',
  `out_bytes` bigint(32) unsigned DEFAULT '0',
  `more_columns` varchar(255) default NULL,
  PRIMARY KEY (`user`,`mac`,`ip`,`start_datetime`,`stop_datetime`),
  KEY `prim_ipv6` (`user`,`mac`,`ipv6`,`start_datetime`,`stop_datetime`),
  KEY `user` (`user`) USING HASH,
  KEY `mac` (`mac`) USING HASH,
  KEY `ip` (`ip`) USING HASH,
  KEY `ipv6` (`ipv6`) USING HASH,
  KEY `start_datetime` (`start_datetime`),
  KEY `stop_datetime` (`stop_datetime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

The query used for writing are (these needs to be real fast):

when a user connects a simple insert
when a user disconnects update accounting set stop_datetime=now(), in_bytes=$in_bytes, out_bytes=$out_bytes, more_columns=$more where user="$user" and ip="$ip" and stop_datetime="0000-00-00 00:00:00" order by start_datetime DESC limit 1;, this is a workaround in the case we have more then one row for start, we just update the last start.

A better workaround, is to update the stop_datetime for rows without it, at every start, and use another column for storing that this is not a normal stop, I think I will go with the second workaround.

We have 2 ways to select from this table (this could be slower max 1 - 2 sec):

select the last 50 connection for a user: select ... where user="$user" order by start_datetime DESC limit 50;, for this the user HASH key is handy, beacause AFAIK HASH is better than BTREE for equality.
select witch user was connected with ip=$ip on date=$date select ... where ip="$ip" and $date between start_datetime and stop_datetime;

This table will hold between 0.5 to 1 bilion rows, that's why I thought to partition it.
The best option is to partition it by month, but I have 2 relevant dates, and at the begining/end of the month start will be in one partition, stop will be in another.

The questions:

Witch is the best way to do partitions, by start_datetime, by stop_datetime, by start_datetime with subpartitions for stop_datetime ?
Do I realy need to partitionate ?
Do you have any other suggestion on how to improve this ?

Thank you


texts:
Oracle 11.2.0.4 on OEL 6.5

Our app admin run some update script from app vendor,
and this script has been running for several hours and no one know when it will end. Also database now generates lots of archivelogs.
I started sql trace for 15 minutes and found that there is almost the only query.

INSERT INTO house_t
VALUES
 (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16,
 :17, :18, :19, :20, :21, :22, :23)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       70      0.00       0.00          0          0          0           0
Execute     71     23.86     633.03      83564      41375    4597750      355735
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      141     23.87     633.03      83564      41375    4597750      355735

Why one simple insert needs to use this huge amount of blocks in current mode 64757=4597750/71 ???

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 103
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     0          0          0  LOAD TABLE CONVENTIONAL  (cr=584 pr=1443 pw=0 time=11388214 us)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
SQL*Net more data from client                9523        0.00          0.08
db file sequential read                     82252        0.60        603.94
SQL*Net message to client                      70        0.00          0.00
SQL*Net message from client                    70        0.00          0.15
log file switch completion                      4        0.08          0.18
undo segment extension                          4        0.00          0.01
log file sync                                   1        0.00          0.00

Table has simple structure:

SQL> desc house_t
Name                 Null?    Type
-------------------- -------- --------------
POSTALCODE                  VARCHAR2(6)
IFNSFL                      VARCHAR2(4)
TERRIFNSFL                  VARCHAR2(4)
IFNSUL                      VARCHAR2(4)
TERRIFNSUL                  VARCHAR2(4)
OKATO                       VARCHAR2(11)
OKTMO                       VARCHAR2(11)
UPDATEDATE                  DATE
HOUSENUM                    VARCHAR2(20)
ESTSTATUS                   NUMBER(5)
BUILDNUM                    VARCHAR2(10)
STRUCNUM                    VARCHAR2(10)
STRSTATUS                   NUMBER(5)
HOUSEID                     VARCHAR2(36)
HOUSEGUID                   VARCHAR2(36)
AOGUID                      VARCHAR2(36)
STARTDATE                   DATE
ENDDATE                     DATE
STATSTATUS                  NUMBER(5)
NORMDOC                     VARCHAR2(36)
COUNTER                     NUMBER(10)
CADNUM                      VARCHAR2(100)
DIVTYPE                     NUMBER(5)

There are no triggers on this table, no mviews, no defined fga policies, no fgac policies.

Also table has 3 indexes

CREATE UNIQUE INDEX HOUSE_T_IDX0 ON HOUSE_T(HOUSEID);
CREATE INDEX  HOUSE_T_IDX1 ON HOUSE_T (T_HOUSEGUID);
CREATE INDEX HOUSE_T_IDX2 ON HOUSE_T (T_AOGUID);

Raw trace file is full of 'db file sequential read' waits on this 3 indexes and undo blocks.

Table now has about 50M records, size is about 12Gb.
Indexes are about 3-4GB and have blevel=3 in dba_indexes.

I expect that this insert statement can take 20-30 blocks to run, but why thousands ?


texts: #1170 - BLOB/TEXT column 'variable' used in key specification without a key length

texts:
I am trying to create a database table for a blog website, but I have an error from a text variable.
the database structure looks like this:
CREATE TABLE place(
    id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(30) NOT NULL UNIQUE,
    author VARCHAR(30) NOT NULL,
    city VARCHAR(30) NOT NULL,
    state VARCHAR(30) NOT NULL UNIQUE,
    latitude FLOAT NOT NULL UNIQUE,
    longitude FLOAT NOT NULL UNIQUE,
    description TEXT NOT NULL UNIQUE,
    post_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE = MyISAM;

but, every time I do try to generate the table it shows up this error:
#1170 - BLOB/TEXT column 'description' used in key specification without a key length


texts: SQL how to remove duplicate results from same table

texts: I created an sql table on visual studio for visual basic, where i can put dynamic data every time i click on a button. I get the table results from textboxes and one datagridview with values(quantity,price,discount). I use the "command insert values into" inside a for loop to be able to get all the datagridview results. The problem is that the table has rows with duplicate values. I want to remove the duplicate values and keep only the values from datagridview e.g I have a table with the following format:


texts: Trigger actions on database creation or attach

texts:
On our dev environment we need to run a bunch of actions when some databases are created or attached. Actions like cleaning up some tables, reseeding some others, changing emails, etc. I was trying to use DDL trigers and actually found here a question similar to mine. Starting from that code I'm trying a slightly different action but can't manage to even get started.

The following code, taken from the question I mentioned, I just added a condition to check db name and if corresponds to the one I need, then do some action. Problem is that is no running the SET statement inside the IF BEGIN..END. The last SELECT using fn_listextendedproperty doesn't return any data.

IF EXISTS (SELECT NULL FROM sys.server_triggers WHERE name = 'ddl_trig_database')
BEGIN
    DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
END
GO

CREATE TRIGGER ddl_trig_database
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        , @CreatedBy NVARCHAR(128)
        , @CreatedDate NVARCHAR(23)
        , @SQL NVARCHAR(4000);

    SELECT  @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)');

    IF @DatabaseName = N'asd'
    BEGIN
        SET @SQL = '
            USE ' + @DatabaseName + ';
            EXEC sys.sp_addextendedproperty @name = N''Owner'', @value = N''' + @CreatedBy + ''';
            EXEC sys.sp_addextendedproperty @name = N''StartDate'', @value = N''' + @CreatedDate + ''';';
        EXEC (@SQL);
    END;
GO

CREATE DATABASE asd;
GO

SELECT name, value
FROM asd.sys.fn_listextendedproperty(default, default, default, default, default, default, default)

DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

DROP DATABASE asd;
GO


texts: How to keep Mongodb log small via the conf file (or other persistent method)

texts:
This is related to my question in stackoverflow where vb.net simply hang on updating a mongodb query.

https://stackoverflow.com/questions/12780258/insert-mongodb-using-vb-net/12796593#12796593

After long soul searching we check the log. The log is 3GB in size.

A log with that size is absolutely useless. I cannot have any text editor to open it.

How to keep the log of mongodb small, say 50 mb max.

We are only interested in the last few commmand anyway not the whole log.

Is there a setting for that in MongoDB? If so what settings:

Note: The question is simmilar with:
https://stackoverflow.com/questions/8777449/mongodb-log-file-size

However, that question doesn't ask

How do I do so by changing some text files or something persistent so that the setting remain every time I start mongodb.


texts: MariaDB Galera Cluster (master-master) does sync Tables but no data

texts:
I'm currently experiencing some sync-issues on my MariaDB Galera Cluster which is a master-master scenario. I've got everything set up properly and the cluster is syncing the DDL changes flawless.

The output of the DB-Parameters seems right because every relevant variable is correct (both nodes are: ready, have the same sync-state and no errors are detected).

I made the check by creating a table and checking if it's syncing to the other node - after that i did it backwards. Everything works except the syncing of the data from the first node to the second.

Is there a way to initiate tis sync-process manually? Might there be a log-file which i oversaw?

Best Regards
k


texts: ORA-31626: job does not exist during schema export

texts:
I'm exporting schema with following command -
expdp system/system@xxx.xxx.xxx.xxx:1521/orcl schemas=schema-name directory=test_dir dumpfile=Schema.dmp logfile=SchemaLog.log

but it results into following error -
Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 1009
ORA-04063: package body "SYS.DBMS_LOGREP_UTIL" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_LOGREP_UTIL"

I googled a lot and tried solutions provided around ORA-31626: job does not exist and ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors but none helped to solve the problem.
Could you please help to resolve this?


texts: SQL server 2005 SP4 differential backup is failing

texts:
We have an SQL server 2005 with SP4, x86. When we attempt to take the differential backup, we are getting the following error message.

  Cannot perform a differential backup for database "ABCD", because a
  current database backup does not exist. Perform a full database backup
  by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
  [SQLSTATE 42000] (Error 3035) BACKUP DATABASE is terminating
  abnormally.

Any help in resolving this issue is appreciated


texts: What is Collation Compatibility_60_406_30001 in SQL Server

texts:
We have an SQL Server 2014 (recently upgraded from 2008 R2) instance where one of the databases has some strange settings for collation:

In the Properties window for the database we have the following:

Under the "General"-tab and Maintenance it says: Collation:  Compatibility_60_406_30001
Under the "Options"-tab the Collation is blank in the drop Down (all though many collations are available for choosing in the list)

I tried googling the collation Compatibility_60_406_30001, but the search came up empty.
Anyone have any suggestions on what to do here? So far I have not seen any errors becaus of this, but I would like to choose a valid collation for this database also.


texts:
One possible way to select random rows in PostgreSQL is this:

select * from table order by random() limit 1000;

(see also here.)

My question is, what does order by random() mean exactly? Is it that somehow a random number is generated and it is taken as some kind of "seed"? Or is this special built in syntax, and in this place random() has a different meaning than in other contexts?

From some experimentation, the last explanation seems more plausible. Consider the following:

# select random();
      random
═══════════════════
 0.336829286068678
(1 row)

# select * from article order by 0.336829286068678 limit 5;
ERROR:  non-integer constant in ORDER BY
LINE 1: select * from article order by 0.336829286068678 limit 5;


texts:
I have following table:

create table osoba(
id int primary key,
idNd int,
nazwim nvarchar(500),
)

Data looks like below:

id  idNd    nazwim
1   NULL    grandpa
2   1   child1
3   1   child2
4   2   grandchild1child1
5   2   grandchild2child1
6   3   grandchild1child2
7   3   grandchild2child2

I've got following cte:

;WITH ret AS(
       SELECT   *
    FROM    osoba
    --WHERE   ID = 1
    UNION ALL
    SELECT o.*
    FROM    osoba o INNER JOIN
            ret r ON o.idNd = r.ID)

Select * from ret returns all descendants for each node and starting node:

id  idNd    nazwim
1   NULL    grandpa
2   1   child1
3   1   child2
4   2   grandchild1child1
5   2   grandchild2child1
6   3   grandchild1child2
7   3   grandchild2child2
6   3   grandchild1child2
7   3   grandchild2child2
4   2   grandchild1child1
5   2   grandchild2child1
2   1   child1
3   1   child2
6   3   grandchild1child2
7   3   grandchild2child2
4   2   grandchild1child1
5   2   grandchild2child1

How to retrieve information about starting node for each record? I would like to get result like that:

idStartingNode  id  idNd    nazwim
1   1   NULL    grandpa
1   2   1   child1
1   3   1   child2
1   4   2   grandchild1child1
1   5   2   grandchild2child1
1   6   3   grandchild1child2
1   7   3   grandchild2child2
3   6   3   grandchild1child2
3   7   3   grandchild2child2
2   4   2   grandchild1child1
2   5   2   grandchild2child1
2   2   1   child1
3   3   1   child2
6   6   3   grandchild1child2
7   7   3   grandchild2child2
4   4   2   grandchild1child1
5   5   2   grandchild2child1


texts: what could be the fastest way to delete all data from a partition of a partitioned table?

texts:
I have a partitioned table that in real life has 80 million rows.

for testing purposes I have created and partitioned this table here.

when I run the following query:

select * from countries
where visit >= '20110101'
  and visit <= '20111231'

as you can see in the query plan here and on the picture below, it uses partition elimination so I know I am doing something right.

I am aware that partition generally is not meant to speed up my queries, it is a management feature, however, it can speed up queries on large tables.

I will start by stating what I don't want.
I don't want to remove any partition from my table.

what I want?

I want to delete all data from a partition in the quickest possible way:

can something be quicker than this?
without considering deleting in batches

BEGIN TRANSACTION T1

DELETE
FROM dbo.countries WITH (TABLOCKX)
WHERE visit >= '20110101'
  AND visit <= '20111231'

--COMMIT TRANSACTION T1

query plan is here


texts: Why disabling a clustered index makes the table inaccessible?

texts:
When an index is disabled, the definition remains in the system catalog but is no longer used.  SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to satisfy queries. If a Clustered Index is disabled, the entire table becomes inaccessible.

Why isn't it possible to access the data directly from the table discarding the B-tree? (most likely by scanning the table row by row) Wouldn't that be more appropriate than making the data completely inaccessible?

It's a purely theoretical question - I would never actually do that. It's not a scenario, nor a to-do thing, I just want to know why thing goes that way, consider it an internals question.


texts:
I am doing a small course in SQL during my academic degree, I am very new with this material.

I saw Inner joinning 3 tables on StackOverflow, I tried to do INNER JOIN between my three tables, the INNER JOIN between the two tables are working well, but when I am adding the thired it's not working well, maybe I missed something?

this one is working:

 SELECT Actors.FirstName,Actors.LastName
 FROM Actors
 INNER JOIN ActorsMovies  ON Actors.ActorID= ActorsMovies.ActorID;

this one doesn't work, I get an error message that something is missing:

 SELECT Actors.FirstName,Actors.LastName
 FROM Actors
 INNER JOIN ActorsMovies  ON Actors.ActorID= ActorsMovies.ActorID
 INNER JOIN GenresMovies ON ActorsMovies.MovieID=GenresMovies.MovieID;


texts: Minimum SQL Server rights that allow viewing column default values

texts:
I have an SQL Server 2008 database, some of the fields (columns) in some of the tables are configured with default values.

I have a requirement to limit user security as much as possible, ideally only allow read/write permissions. However, the users need to be able to see what the default values for the columns (well it's actually an application that needs to read the defaults).

Normally I can give the user access to the following roles if I want read/write only:

db_datareader
db_datawriter

The problem is that this prevents access to the default values. This can be confirmed by opening a table is design view (they get a message saying they have limited access and can only view the table design), and the default values are just shown blank.

If I add the db_owner role then they can view the default values fine, but I don't want to give the owner powers.

Is there something else I can do in order to restrict to only read/write but with the extra ability to to see what the default values are?


texts: Lots of Indexes - MySQL vs MongoDB - Migrating

texts:
So, I've never used MongoDB, I just read a lot about it and I think it's going to be good for my project. Also, I do not have lots of experience with MySQL and to be even more honest I have no clue of what I'm about to ask.

Scenario:

MySQL table profile:

id = [pk, auto_increment, smallint]
user_id = [pk, fk, varchar]
category_id = [pk, fk, smallint]
role_id = [pk, fk, tinyint]
country_id = [pk, fk, smallint]
state_id = [pk, fk, smallint]
legal_document = [pk, varchar, ?unique]
name = [pk, varchar, ?unique]
type = [pk, boolean]
last_activity = [pk, date]

All the fk you see there are MySQL tables of course. Then I'm thinking to use MongoDB to store the profile info, profile_info collection should contain documents like:

{
  '_id' : 1 (profile_id),
  'address': 'Some street in some state of some country :P',
  'phone': [5555555, 5555555],
  'email': 'example@example.com',
   etc...
}

I'm planning to use mongodb because my project needs to be public ASAP and we may add several new profile info and other things and I do not want to do alter table and migrate from tools with lots of rows.

With that said, we may have to add a few more pks to the MySQL table, so I was thinking of migrating all the project to MongoDB and I do not know if that is a good move.

Questions:

It's better to keep the pks in MySQL and trivial info in MongoDB, or would it be fine if I move all to MongoDB?
May MongoDB be faster if I just migrate the whole project but I keep the structure like that? I mean like having profile and profile_info collections instead of just profile.
I'm worried about how many resources MongoDB could use for a 'table/collection' with that many indexes, I want to keep disk space and ram at minimum use. Is there a critical difference between MySQL and MongoDB?

PS: SSDs are going to be used in the system.

PS II: All tables are just planned, nothing is written yet. I'm a bit of plan very ahead person so please be patient with me.


texts:
pgBouncer docs note:

pool_mode

Specifies when a server connection can be reused by other clients.

session:
  Server is released back to pool after client disconnects. Default.

transaction:
   Server is released back to pool after transaction finishes.

Please explain what “server connection can be reused” and “server is released back to the pool” mean.


texts: Can I use postgres_fdw without foreign tables defined?

texts:
I have a production database "PRODdb1", with a read-only user account. I have a need to query(select statement) this database and insert the data into a secondary database named "RPTdb1". I originally planned to just create a temp table in PRODdb1 from my select, but permissions are the issue.

I've read abut dblink & postgres_fdw, but are either of these a solution for my issue? I wouldn't be creating foreign tables because my SELECT is joining many tables from PRODdb1, so I'm unfamiliar if postgres_fdw would still be an option for my use case.

Another option would be any means of getting the results of the SELECT to a .CSV file or something. My main blocker here is that I only have a read-only user to work with, but no way around that issue.


texts: alter table (mysql) adding column with default value of space (' ')

texts:
On my Mac, using  5.5.11 MySQL Community Server (GPL)

alter table foobaz  add column (
extendedTitle varchar(3000) DEFAULT NULL,
clasindxIndent int,
spacedash1 enum (' ', '-', '1') default ' ',
plusSubs enum (' ', '+') default ' ');

works perfectly. On my Debian server using 5.1.49-3-log (Debian), it fails with

ERROR 1067 (42000): Invalid default value for 'spacedash1'

I don't understand the error, or why it works on my Mac and not on my server. Any hints or pointers greatly appreciated.

Pat


texts: MySQL import csv only gets half the lines


texts:
I'm experiencing a tough issue for me. When I was updating some columns into the database I have previously installed, the main memory corruption led the database jump into the suspect mode. I follow the following steps to recover the database:
1. EXEC sp_resetstatus 'dbNSFP';

2. ALTER DATABASE dbNSFP
   SET EMERGENCY;

3. DBCC CHECKDB('dbNSFP');

3. ALTER DATABASE dbNSFP
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

4. DBCC CHECKDB (dbNSFP, REPAIR_ALLOW_DATA_LOSS);

5. ALTER DATABASE dbNSFP SET MULTI_USER;

When I was in the 1st step, MS SQL Server Management Studio gives:

Warning: You must recover this database prior to access.
Completion time: 2020-06-21T11:33:19.6094013-04:00

When I was in the 2nd step, the Studio reports an error:

Msg 5011, Level 14, State 7, Line 3 User does not have permission to
alter database 'dbNSFP', the database does not exist, or the database
is not in a state that allows access checks. Msg 5069, Level 16, State
1, Line 3 ALTER DATABASE statement failed.
Completion time: 2020-06-21T11:34:47.5199727-04:00

I've been search for this solution for several days. Some cues are pointing to change the alter permission to the user. However, I'm a SA user, SA should hold all CONTROL permissions. I have been stuck here for days. I hope someone could help me out.


texts: How to perform FullText search on numeric column in MySQL to meet user requirements

texts:
Having below table:

CREATE TABLE `event` (
      `eventId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
      `eventTime` bigint(20) NOT NULL COMMENT 'ex: 1431201865000 (epoch in milliseconds)',
      `plateNumber` varchar(10) NOT NULL COMMENT 'ex: 5849',
      `plateCodeId` SMALLINT(5) UNSIGNED NOT NULL COMMENT 'ex: 1 = A, 2 = D, 3 = Q, 4 = NY',
      PRIMARY KEY (`eventId`),
      KEY `eventTime` (`eventTime`),
      FULLTEXT INDEX `platenumberft` (`plateNumber`)
    ) ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

The User want to search plateNumber in form '%334%', knowing that's only supported by fulltext index, I wonder if its possible to avoid the usage of fulltext index since it create 6 tables and even I didn't find benchmarks highlight the impact of fulltext index on large table I am worried about the performance since the table above will hold over 1 billion records in which insertion rate is 150 records per second 24/7.

MySQL Docs recommends never to use string "varchar" data type to store numeric data, and since the platenumber always numeric, it should be created as:

`plateNumber` INT(10) UNSIGNED NOT NULL

Is it possible to query numeric field in search form '%334%' with speed equal to the fulltext index? and How?

Use Case: the user received information that the plate number contain digits '334' in the middle, so the first and last digits are unknown.


texts: Percona Cluster Failed with Haproxy : Connection Limit Exceeded

texts:
We have 3 node PXC cluster setup on amazon ec2. Its been working fine for around 4-5 months. Since last week, every node starting to fail due to MySQL connection limit exceeded error. we increased the max_connections  and still no help. We had about 1-2hr downtime since last week.

When a one node failed(Client connection failed) i was logged in to that MySQL server and i was able to do a SHOW FULL PROCESSLIST but it has only 20-30 connections. At the same time, when i tried to connect to the same server via another terminal, I got the connection limit exceeded error message. So i did a netstat -na | grep 3306 | wc -l and there were exactly 500 connections to 3306 port from loadbalancer ip with TIME_WAIT status.

max_connect_errors 1000000
max_connections 500

Setup as follows.

App1 ---> Haproxy1 ---> PXC Node1
App2 ---> Haproxy2 ---> PXC Node2
App3 ---> Haproxy3 ---> PXC Node3

Each haproxy configured with 1 active mysql node and 2 backup servers

server SQL1 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup
server SQL2 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3
server SQL3 xx.xx.xx.xx:3306 check port 50000 inter 3000 maxconn 160 rise 3 fall 3 backup

We also use clustercheck script from percona to monitor mysql nodes on port 50000 with help of xinex.d

Any suggestions?


texts: xlog flush request is not satisfied in log files

texts:
Today I saw that Postgres produces error messages related to possible data corruption (?). Everything's working as expected so I didn't realize anything was wrong.

ERROR:  xlog flush request 6E/82FFED10 is not satisfied --- flushed only to 3D/CA02E920
CONTEXT:  writing block 1008 of relation base/118517/118823
CONTEXT:  writing block 1008 of relation base/118517/118823
WARNING:  could not write block 1008 of base/118517/118823
LOG:  request to flush past end of generated WAL; request 6E/82FFED10, currpos 3D/CA02E920
DETAIL:  Multiple failures --- write error might be permanent.

PostgreSQL 11.
Configuration:

max_connections = 100
shared_buffers = 16GB
effective_cache_size = 48GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 41943kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

pg_control version number:            1100
Catalog version number:               201809051
Database system identifier:           6775130872383000604
Database cluster state:               in production
pg_control last modified:             Thu Mar 12 04:46:20 2020
Latest checkpoint location:           85/802C9268
Latest checkpoint's REDO location:    85/802C9230
Latest checkpoint's REDO WAL file:    000000010000008500000080
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:69393025
Latest checkpoint's NextOID:          511846
Latest checkpoint's NextMultiXactId:  11365
Latest checkpoint's NextMultiOffset:  23069
Latest checkpoint's oldestXID:        562
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  69393025
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Thu Mar 12 04:45:50 2020
Fake LSN counter for unlogged rels:   0/1
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              100
max_worker_processes setting:         8
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float4 argument passing:              by value
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            edec50c3ef6ee1a42351c2e593de539feb25343ad239e12b813b4c212ae2a1d6

I did a backup and then I restored it to another database and removed the old one. It didn't work. I still getting those errors.

P.S. PostgreSQL did not crash lately. There is also a plenty of space on SSD.


texts: Oracle: Select values and join result from multiple rows in one with seperat cells

texts:
I have to create a select Statement, which bring me a multiple row result in one.
For better understanding here my table

Id |  Name  |  Typ  |  Date
---------------------------------
1  |  John  |  200  |  2019-01-05
2  |  John  |  201  |  2016-09-01
3  |  John  |  202  |  2020-11-03
4  |  John  |  601  |  1955-03-03

Now i‘m looking for a select which brings me the following result

Id  |  Name  |  200         |  201         |  202         |  601
-----------------------------------------------------------------------
1   |  John  |  2019-01-05  |  2016-09-01  |  2020-11-03  |  1955-03-03

The only Option i found was the LISTAGG Function, but with it, the result is a concatenatet String.
I want to use the result in a c# application, because I don‘t like to select an sort in c#.


texts: PostgresSQL: Get single attribute of UDT in SELECT statement

texts:
I created a user-defined type in a PostgreSQL 9.2 database and am trying in the SELECT statement to get only an attribute of the UDT. However, I don't seem to get my code to work.

Creation of type and table:

CREATE TYPE  ttp AS (f1 int, f2 int);
CREATE TABLE tbl (ctyp ttp);

The SELECT statement:

-- does not work
SELECT ctyp.f1 FROM tbl
SELECT ctyp(f1) FROM testtable

Both times I get an error. Is it possible to access a single attribute defined in a UDT in a SELECT?


texts: Does SQL Server support restoring to a point in time down to the millisecond?

texts:
Using the Management Studio GUI, I am only able to restore a database to a point in time down to the second.  Is this just a limitation of the GUI or if I use a script will it restore to the millisecond I specify?

I'm trying to do it with a script and it 'seems' like it's ignoring the millisecond but unfortunately the date value I'm comparing to is, unfortunately, not necessarily the exact time the record was written so I just want to rule out any possibility that the problem could be because of a limitation of SQL Server.


texts: Slower SQL Server performance after allocating more CPU and RAM

texts:
We have SQL Server 2008 R2 (10.50.1600) running on a virtual Windows 2008 R2 server. After upgrading the CPU from 1 core to 4 and the RAM from 4 gb to 10 gb, we've noticed the performance is worse.

Some observations I see:

A query that took <5 seconds to run is now taking >200 seconds.
The CPU is pegged at 100 with sqlservr.exe as the culprit.
A select count(*) on a table with 4.6 million rows took over 90 seconds.
The processes that are running on the server haven't changed. The only change was to increase the cpu and ram.
Other sql servers have a static paging file where this server is set to manage it on its own.

Has anyone run into this issue before?

Per sp_BlitzErik, I ran

EXEC dbo.sp_BlitzFirst @SinceStartup = 1;

Giving me these results.


texts:
I have found myself in a situation where the pg_xlog contains 60GB of data due to a wrong configuration option set.

I had setup the wal_level, archive_mode=on and archive_command for barman which then got uninstalled. Can I simply comment these options as per default postgresql.conf config file and delete the files in the pg_xlog directory?

What is the correct procedure to remove these files in order to avoid database corruption?


texts: In on of our Oracle 11.2.0 installations the directory database\diag\rdbms\$DB\$DB\alert is filled with hundreds of xml files (log.xml, log1.xml ...). Can Oracle be configured to do log rotation there? And where are these files used?


texts: MySQL: why is the time reported at the end of a query different from real time?

texts:
I have recently been running some fairly slow select statements, and what I notice is that the time reported at the end is typically much less than the time it actually ran. Is that to be expected - and what is the reason? I'm not looking to optimise my query, I just want to understand this better in general. My most recent statement ran for several hours with no locks and all alone on that server - however, at the end it showed:

...
+----------+----------------------+--------------+------------------------+----------------+-----------------+-----------------+-----------------+--------------+---------------+---------------+---------------+---------------+
7693 rows in set (17 min 36.08 sec)

When I EXPLAINed it, it showed that it would only do things like index lookups (no full table scans) with less than a few thousand rows examined at most. It just seems odd.


texts: How to create a table to a specific user

texts:
First good evening every body.
assume that you are logged in as a super user and you want to create a user.

  CREATE USER 'user1'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*483EC567DF968A6AD5E279257828E7CC4A12F0C2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;

and you want to create some tables for that user something like this

CREATE TABLE user1.user1Table -- table for user1
(
idUser INTEGER PRIMARY KEY AUTO_INCREMENT,
Name VARCHAR(30)
) ENGINE=INNODB ;

when I run the sql script I get an error like this

ERROR 1049 (42000): Unknown database 'user1'

I thought at the beginning that I would have something like that

mysql> desc user1Table ;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| idUser | int(11)     | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(30) | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql>

why that is not possible ??? i have wamp server

thank you in advance


texts: ERROR 17058 COULD NOT FIND THE ERROR LOG

texts:
We just installed SQL Server on 1st node of windows cluster and it stop/start fine from configuration manager but from command line it is giving error and I checked that service account have all the rights on the folder, any idea how to fix the issue since we need to some tasks and I need to start SQL Server in single user mode from command line (registry entry looks fine):

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn>sqlservr.exe
2013-02-22 17:27:39.68 Server      Error: 17058, Severity: 16, State: 1.
2013-02-22 17:27:39.68 Server      initerrlog: Could not open error log file ''.
 Operating system error = 3(The system cannot find the path specified.).
2013-02-22 17:27:39.99 Server      Error: 17058, Severity: 16, State: 1.
2013-02-22 17:27:39.99 Server      initerrlog: Could not open error log file ''.
 Operating system error = 3(The system cannot find the path specified.).
2013-02-22 17:27:40.30 Server      Error: 17058, Severity: 16, State: 1.
2013-02-22 17:27:40.30 Server      initerrlog: Could not open error log file ''.
 Operating system error = 3(The system cannot find the path specified.).
2013-02-22 17:27:40.62 Server      Error: 17058, Severity: 16, State: 1.
2013-02-22 17:27:40.62 Server      initerrlog: Could not open error log file ''.
 Operating system error = 3(The system cannot find the path specified.).
2013-02-22 17:27:40.93 Server      Error: 17058, Severity: 16, State: 1.
2013-02-22 17:27:40.93 Server      initerrlog: Could not open error log file ''.
 Operating system error = 3(The system cannot find the path specified.).
2013-02-22 17:27:41.24 Server      Error: 17058, Severity: 16, State: 1.
2013-02-22 17:27:41.24 Server      initerrlog: Could not open error log file ''.
 Operating system error = 3(The system cannot find the path specified.).


texts:
I have a function that needs a string as parameter. It's always a insert. How do I make it work?  The double quotes for the values are not good syntactically

select inversor_insert('INSERT INTO clien(column1,column2) values('value1','value2')')
from dual;


texts: TSQL Join on Nearest (tables have no relationships)

texts:
I have 4 tables
Listings
Cities
Regiions
Countries
Listing is not related to others at all, but cities, regions and countries have FK.
I would like to get a list of Listings but also include in the query the nearest city, region, country data for each item.
Listing table has a geography field called Location, and so does City so basically I want to be able to feed the l.Location to City sub-query to get a single city for that listing.
I have the following but I am not sure what else I need to make this work and make this to be fast.  I have 2 indexes Spatial indexes on Listings and City's Location field, I can share those if needed, using SQL Server 2017/2019
 --MISSING on what goes here
 select  top 1  c.UnicodeName, r.code as region, cn.code as country from cities c
 inner join regions r on r.regionid = c.regionid
 inner join Countries cn on cn.CountryId = r.countryid
 where c.location.STDistance(l.Location)


texts: Create user with the replication access only

texts:
Is it possible to have a user in mysql who can only have an access to the replication?

What I need is to monitor the replication to show if the replication is up and running or not. But, I want that the mysql user has only access to the replication infos, not everything.


texts: How can i give SECADM to instance owner in DB2?

texts:
i'm using db2 9.7 on linux and created a user "db2fee1i" to be a instance owner. I restored a database, but even being SYSADM i only can connect to database. I need to give DATAACCESS to another user, and even doing a query appears...

  The command was processed as an SQL statement because it was not a
  valid Command Line Processor command. During SQL processing it
  returned: SQL0551N "DB2FEE1I" does not have the required authorization
  or privilege to perform operation "EXECUTE" on object
  "NULLID.SQLC2H23". SQLSTATE=42501

I tryed everything but nothing solves my problem, now i found "SECADM" that can give me the "privileges to execute" into database, but i dont know how to do it. Someone can help me?!


texts:
I have the following MDX tuple calculation for my KPI in Dashboard Designer:

AVG([Person].[Person].children,[Measures].[Hours])

This works perfectly when, for instance, I view it by Team name.

However, when I view it by the [Person] it's returning no values. Does AVG not work when you're looking directly at the leaf nodes or something? Or is there something else I'm doing wrong?


texts: Sharding a MongoDB instance by a spatial key and a date

texts:
This may be more an advice than anything else, but I'm designing an app that stores in a table objects that have a 2dsphere index and they also have a date (+time), both of them being part of queries that I'm doing to retrieve some of these objects.

This database will have to be sharded, so sharding according to the spatial key sounds like the first step. It's basically a given that this index will be part of the sharded key. The other key should be time, and I'm pretty sure that hashing the time will not be adequate, as the queries are looking for date interval for the objects. So hashed will make force accessing all chunks (with the proper spatial constraints), which I don't want.

So my question is to know if this is feasible and also if mongodb will be clever enough not to split according to the spatial key unless I manually ask for it?


texts:
say we have a fixed table T. Is there a unique normalized version of T,
i.e., does the/a correct normalization, say up to 3NF produce a unique table up to
some equivalence? Basically, I am trying to find out how many different correct ways of normalizing a table there are.
Thanks.


texts: How to copy a database using scripts only, no bak, no mdf, no ldf

texts:
Too often I face a terrible situation:  I have a little time to take the backup of a customer db and download it for studying problems on it.

The problem is that the database contains many BLOBs (as sql server filestream).

Typically tabular data ("mdf") is 5% of the total data.

To study most issues I do not need BLOBs.

In a world with infinite bandwidth I could simply take a backup and download it, but this conflicts with "a little time" in the real world.

To get rid of BLOBs I also do:

create a copy of the  DB at the customer server
set all blobs to NULL
run CHECKPOINT to force filestream data clearing from disk
take backup after some time (proportional to BLOBs total size)
download a small backup

BUT point (4) takes time.

So what I would like to achieve is:

a) create an empty database with a filestream filegroup on the customer's server

b) with a script, create the schema (at least tables, views, sp, sf) on the copied DB

c) with a script copy all the data but not the BLOBs

d) take backup and download

With my knowledge I can somehow manage (b) using the Tasks/Generate Scripts feature of Management Studio.

Then manually I should write queries like this to fill the tables:

INSERT INTO CopiedDB.dbo.Table1
SELECT * from MainDB.dbo.Table1

It is a very long procedure to be done manually even because the insert order must be sorted out manually (because of FK relationships).

Is there a way to create this script in  a smart way avoiding the manual way?


texts: What is a database with ".res" in its name?

texts:
I'm not that experienced with MySQL and I'm trying to fix some databases.

In mysql, a "SHOW DATABASES" shows (among others) the main database "MainDB" and another database "MainDB.res". If I'm trying to DUMP or USE that second database (with `` around the name), I get the error
ERROR 1102 (42000): Incorrect database name 'MainDB.res'.
Dumping the MainDB works without problems. Now I'm wondering if MainDB.res is some special or maybe auto-generated database that the MySQL admin has no rights for? How can I look into that database?

Detailed information:
Originally I wanted to see all ibfk connections between all tables.
Now I'm searching for a bug in the databases because I can't look into information_schema, as a
mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE;
results in the error message
ERROR 1103 (42000): Incorrect table name 'accessgroup'
and I read some forum posts suggesting that the error happens because more than one table was found with that name, and renaming it may solve the problem. That is the reason I wanted to take a look at "MainDB.res" in the first place...

EDIT:

MainDB.res/ is indeed a direcory (in /var/lib/mysql/) containing some, but not all files in the directory MainDB/. Some missing files are of type .frm, some of .MYD and .MYI. 'accessgroup.frm' is in both directories. (I can't anonymize the output of ls -l enough to paste it here.) I'll try what happens if I move the directoy MainDB.res somewhere else.


texts: SQL Server database schema (and likely, some data changes) - how to auto-distribute over many database instances

texts:
Our development involves a SQL Server database (also might be Oracle or Postgres later) and we would sometimes make database schema changes or some other interventions in database.

What solutions exist to create a "patch" or "script" to distribute these changes on other installations of same database (we do not have direct access to these)? It needs to alter database schema and execute SQL and/or other complex, pre-programmed database data alterations as defined by person who initializes/designs change. On other instances, a system admin should be able just run some program/press button, so these changes would be applied automatically.

In addition, it is  plus if such solution can take database snapshot and derive "difference" on contents of particular table that would be then distributed.

The solution can be commercial.

Thanks in advance!


texts:
Why would an insert into a MongoDB 3.4 collection require global write locks? I'm creating a copy of a large collection in a new replica set (via mongodump/restore) and I'm finding that some inserts are taking > 100 seconds to run, however if I run an insert from the shell, it takes only a second or two. An example of an insert is below

 {
        "desc" : "conn433",
        "threadId" : "140528463828736",
        "connectionId" : 433,
        "client" : "127.0.0.1:39208",
        "active" : true,
        "opid" : 2609838,
        "secs_running" : 114,
        "microsecs_running" : NumberLong(114553087),
        "op" : "insert",
        "ns" : "dbName.collectionName",
        "query" : "{ insert: \"collectionName\", documents: [ ...",
        "numYields" : 0,
        "locks" : {
            "Global" : "w",
            "Database" : "w",
            "Collection" : "w"
        },
        "waitingForLock" : false,
        "lockStats" : {
            "Global" : {
                "acquireCount" : {
                    "r" : NumberLong(23),
                    "w" : NumberLong(23)
                }
            },
            "Database" : {
                "acquireCount" : {
                    "w" : NumberLong(23)
                }
            },
            "Collection" : {
                "acquireCount" : {
                    "w" : NumberLong(12)
                }
            },
            "Metadata" : {
                "acquireCount" : {
                    "w" : NumberLong(11)
                }
            },
            "oplog" : {
                "acquireCount" : {
                    "w" : NumberLong(11)
                }
            }
        }
      }


texts: Update Postgres on server that already has version included with system

texts:
Mac OS X (Lion) Server includes Postgres (9.0.5) that it uses for some system utilities (Wiki server, caldav, etc.). I'm about to start using this server for some Rails applications and am wondering how to get my "kit" set up correctly. I'd like the version of Postgres to be the same (9.1.3) from development into production...though I suppose this isn't absolutely mandatory.

Being as Apple probably won't update the system version of Postgres past 9.0.x until Mountain Lion this summer what's the best way to get 9.1.3 on the server? I've been warned against replacing the system version as it might screw up some of Apple's tools or, perhaps, just get overwritten when there's a Lion update.

Is it true that Postgres can happily run multiple versions of itself on the same server...under same OS instance? If so then I assume I could just install 9.1.3 into /usr/local/ and create new clusters there...leaving the system install alone? But I'm concerned about conflicts. Dunno. never tried this?!?

Maybe there's a another way to handle this?


texts: SSRS - Data missing when running report from server

texts:
I have an extremely strange issue with an SSRS report i'm working on. We use SQL Server 2012 availability groups and as number of groups and the nodes they run on has swelled, confusion on the part of users accessing them has increased, so I've created a report that, for a given DB, returns the correct primary and lists other related DBs as well.

The issue relates to displaying the AG. certain records were returning a null AG name, and having done some investigation, I've narrowed it down to the results from the following

Select * from sys.availability_groups AG

I have created a simple table in SSRS to take these results and when running the query in SSMS and BIDS, I get 13 records. When I deploy it and run the report, I get somewhere between 10 and 12 records, depending on the node I run it against. I am frankly at a loss how to proceed from here. I feel I've reduced the complexity of the query down to it's most simple form. I've checked there is no filtering taking place. Even performing a count(*) instead yields a result of 10 rather than 13. There is also no commonality to what is being omitted; naming conventions are standard across all AGs, they're not all on the same primary, but other AGs on the same primary do show up, and as I said, running against different primaries varies the number of records returned

Any suggestions?


texts: AlwaysON : Log_Send_Queue_Size is increasing but the log_send_rate keeps decreasing?

texts:
Question

Why, despite the increasingly log_send_queue_size for these two databases, did the log_send_rate keep decreasing?
There were no bandwidth issues on the network at this time, and no other databases experienced this issue. If this happens again, is there a recommended fix, apart from having to manually restore the primary database over the secondary to re-synchronize the pair?

Environment :

  SQL 2012, SP1 CU7 (Build 3393)

  Windows Server 2012 Standard (Build 9200)

  Availability Group of 10 databases (PRDDB1-AG1)

  2 AG replicas, one in London and one in New York (LDSERVER1 & NYSERVER1), primary in NY, secondary in London.

  2 databases within AG1, E-DB1 (50GB log file) and T-DB2 (250GB log file)

The T-DB2 database imports files from clients, processes them (lots of log activity) and then outputs to/updates data in the E-DB1 database.

This process generates lots of data churn and log activity against both databases.
We have occasional spikes of latency between London and New York database replicas maybe once or twice a week maximum but these always clear within a few hours.

Issue :

Last week we saw an increasing log_send_queue_size, and a decreasing log_send_rate. This started on the Monday, and carried on through to Friday night, when it was manually resolved (see Fix section below).
At its lowest, the log_send_rate of the E-DB1 database was just over 100KB/sec with a log_send_queue of over 40GB.
The T-DB2 database had a log_send_rate of 2000KB/sec shrinking down to 300KB/sec, with a log_send_queue of over 300GB.

This led to an increasing amount of latency between the primary and secondary replicas for these two databases within the availability group.
This was characterised by a build-up of log activity within the transaction log for each database affected, which is expected.
Because of this latency, the logs of each database affected expanded to the point where the log drive was in danger of running out of space.

This latency only occurred on these two databases, despite some quite large spikes in transactional activity across all databases within the availability group, as is normal.

Throughout this issue, there was no build-up in the redo queue on the secondary, and the redo_rate stayed high. This would imply that the issue was due to the low send rate for both the affected databases.

Steps attempted

Suspend data movement of the T-DB2 database. I hoped this would free up network bandwidth for the priority database, E-DB1. No effect.
Rebooted the secondary node (LDPRDENTDB1). No effect.

Fix

The following steps resolved the issue. As the log files had grown to over 300GB, I needed to purge and shrink them soon before we ran out of disk space.

a. Removed databases from the availability group.

b. Dropped databases on secondary.

c. Re-added databases back into availability group on primary (NYSERVER1, manual synchronisation option).

d. Backed up the databases on primary and restored to secondary (70GB copied from NY to LD, over just under 24 hours)

e. Re-added databases back into availability group on secondary.


texts: What is a good way to parameterize a materialized view on Postgres?

texts:
Given for example a materialized view like this (Postgres 10.3):

create materialized view my_view as
    select * from my_table where sell_date < '2018-03-01';

The sell_date comparison value ('2018-03-01') can change sometime, but I want to avoid to drop and recreate the materialized view each time. The only idea I came up with it is using an external table with some metadata values like the date needed:

create materialized view my_view as
    select * from my_table where sell_date <
        (select original_sell_date from some_metadata_table);

Is there any other way to work around this limitation of materialized views on Postgres?

One of the problems with my current solution is that you can have two or more materialized views that use the same value, but they may need at some point of different values. In this case a duplication of the metadata table is needed.


texts: Unable to change root user unix_socket to mysql_native_password

texts:
Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9

I have installed a LAMP stack on my Debian 9 machine and can not log in with the root user.  From my searching, it seems that it's because the install commonly creates a root user without a password, causing it to default to unix_socket, which foils logging in with that user via terminal.

MariaDB [(none)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------+-------------------------------------------+-------------+-----------+
| user       | authentication_string                     | plugin      | host      |
+------------+-------------------------------------------+-------------+-----------+
| root       | *3624F23A73825C0D79BF3591774631513FE76C24 | unix_socket | localhost | | phpmyadmin |                                           |             | localhost |
+------------+-------------------------------------------+-------------+-----------+
2 rows in set (0.00 sec)

I found an article explaining how to resolve this issue by shutting down mysql, starting it with sudo mysqld_safe --skip-grant-tables & so I can log in and then running:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass';

then stopping and starting the daemon.  My problem is that no matter how I format this line, I get:

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MariaDB server version for the right
  syntax to use near 'user 'root'@'localhost' IDENTIFIED WITH
  mysql_native_password BY 'mypass'' at line 1

I've tried tons of different formatting with backtics, case change and other iterations of the command found on the web but everything fails with the same result.

Could someone tell me how I go about changing this successfully?


texts:
If I run a statement such as SELECT * FROM stuff WHERE something=NULL I will get an empty result set.

I know that the solution is to use something IS NULL. I also understand why: SQL interprets NULL as unknown, and it makes logical sense that if something is unknown, it can’t match something else.

What puzzles me is the meaning of the result set. I have always understood that you can’t match something=NULL, so why is is acceptable? It would suggest that under some circumstances, one might get a different result?

Alternatively, why does SQL not accept something=NULL as an idiom equivalent to IS NULL? It wouldn’t be the only idiom which doesn’t have a literal interpretation (such as count(*) vs SELECT *).

The question is: since something=NULL is not an error, does it mean something useful?


texts: Oddities with AT TIME ZONE and UTC offsets

texts:
I don't understand the difference between these two columns. America/Chicago timezone is UTC-6, so I expect both to return the same result:

select timezone('America/Chicago', '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC'),
       timezone('UTC-6'          , '2017-01-01 12:00:00'::TIMESTAMP AT TIME ZONE 'UTC');

However, the result is:

2017-01-01 06:00:00 | 2017-01-01 18:00:00

Also, this behavior is highly awkward,

SELECT '1:00 -1'::time with time zone AT TIME ZONE '-1';
  timezone
-------------
 03:00:00+01

Can anyone explain this?


texts:
I don't understand why the default collation on my psqlserver 11 instance returns a different result than using with explicit collation.

Details:

show LC_CTYPE;        -- de-AT
show LC_COLLATE;      -- de-AT

When I use the default collation I get an unexpected output:

demo=# SELECT unnest(array['a','B', 'A']) order by 1;
 unnest
--------
 A
 B
 a
(3 rows)

When I add the default collation explicitly, I get the expected output:

demo=# SELECT unnest(array['a','B', 'A']) collate "de-AT-x-icu" ORDER BY 1;
 unnest
--------
 a
 A
 B
(3 rows)

The collation de-AT-x-icu uses exactly the default values for LC_COLLATE and LC_COLTYPE shown above:

SELECT collname, collcollate, collctype
FROM pg_collation
where collname like '%de-AT%';

  collname   | collcollate | collctype
-------------+-------------+-----------
 de-AT-x-icu | de-AT       | de-AT

So I think both queries should return the same result: What am I missing?

More info:

postgresql version 11.5
OS inside the Docker container:
Linux 8660fb4cef84 4.9.184-linuxkit #1 SMP Tue Jul 2 22:58:16 UTC 2019 x86_64 Linux


texts:
I've a database system which we want to define tags for many tables into it. For example I've videos, photos, ... different entities that we wish to make them taggable. The tags could be anything. By tagging I mean that we wish to allow the user from the front end to define tags for these entities (keywords), something like Facebook tagging. We would like to make it extensible for any future entity that may appear later in the system and we want it to be taggable too. Initially we thought that this could be a many to many relation ship between each entity that we want it to be taggable and each tagged entity in the database. For example, if we want to tag users in photos, then many users can be tagged in a single photo and many photos may have the same user tagged into them. But this design complicate things because we will need many to many relationship between all the taggable entities and the objects that we want to add tags to. So we will have a many to many relationship between user and photos, user and video, .... and this is only for the user entity, if we have other entity we will do the same, so it's complex and non extensible. So we tried to enhance that and we came with the following idea.

We will have a table with no relationships at all, it's not connected to any other table, this table will contain the following, the tag id, tag value, the entity object id (ex: user id), the entity object type (ex: user), the taggable object id (ex: photo id), the taggable object type (ex: photo). and we will add indexes for entity object id column and entity object type, also for taggable object id and taggable object type to fasten the search process.

But I was wondering if there any pattern for that, some design pattern that solves this issue, and which of the above 2 solutions is better.


texts: Deploying schema changes to AWS RDS hosted Databases

texts:
With a database hosted in AWS's RDS, the database is only accessible externally if publicly accessible is set to true and the relevant ingress rules are configured to allow connection from a known CIDR range.
Most of the time we don't need this access; we just want our app (hosted in AWS) to access the DB; but when deploying schema changes or data correction/upload exercises that aren't covered by our app's logic we need a way to access the DB manually (e.g. via SSMS).
Is there a best practice / recommended way of doing that?  e.g.

Enable public access and have the security group's ingress only allow known static IPs/CIDRs for our company to access the DB.
Don't enable public access, but instead have a bastion server hosted in the same AWS subnet / have DBAs SSH into this, then work with the DB from this bastion server.
Enable public access / known IPs through the security group only when changes are required; then lock things down outside of these "maintenance windows".
Or is there some better option that I've not considered; e.g. some DBA tool available through the AWS console / a management layer outside of what I've mentioned above?


texts: Compressing Base64 Encoded Image in Database Field. Table Size too Large

texts:
Database Info

Database Type - Percona MySQL.
Table engine - InnoDB.

I have a database table that is currently using 17GB at 400,000 rows. This size is mainly because I'm storing a 40kb base64 encoded image in each row. I'm wondering if anyone else has ran into the same type of problem and what they decided to do about it. The options I'm currently thinking about are:

Method 1 Storing each image named by the row's unique id in some sort of cloud hosting server. This would fix my database size problem, but it would be a pain to manage because the images would be on a different server. Also pushing the images up to the cloud server would take longer than the current method.
Method 2 Compressing just that column on a mysql database level? (if that's possible? I'm not sure but I believe it is, still researching. I would assume this would slow down the insert and select statements also though.)
Method 3 Compress the base64 before sending it over to the database with something like LZString Compression. (I'm sure this would entail on my database level such as what field type to put this data in or character set/encoding would be needed. Possibly could combine method 2 and 3 together for maximum savings? not sure if that would work or if it would be possible)

If anyone could give me some information on what would be the best way for me to approach this dilemma or if anyone has run into this problem before I would really appreciate the any help/info.

Thanks in advance.


texts: How to check/change MySQL named pipe settings?

texts: I'm having a hard time finding anything on this because I don't know what to call it.  But when you install MySQL it asks you if you want to allow connections to the database from other machines or not, and gives you an option to use named pipe.   Well, I want to check how a particular server is setup and change it.  How do you do that? I mean without reinstalling.


texts: deploy data from sql 2016 backup to azure database

texts:
I've got backup data from a SQL 2016 server that i want to move/restore onto an azure database.  I'm not seeing an in-house option to do this anywhere, so it looked like the best option was to restore locally, build schema/data scripts for the data, and then run the script on the azure database.

Only problem is that the script that i get back is around 4GB large.  it won't load in sql management studio or azure's own 'query editor' page, and if i deploy via sqlcmd it crashes halfway w/out an easy way to figure out where it crashed at and how to resume from that point.

Are there good methods out there to do this?  I plan on needing to do this several times, so i'd like to get my methods down.  Thank you!


texts:
Does Oracle have any builtin SQL syntax help.

I find I may have forgotten a few statements and sometimes have no Internet access.

I am using Oracle 11g.


texts:
I have the following where condition:

lr."dateEnd" >= to_date(?, 'dd.mm.yyyy') and lr."dateEnd" <= to_date(?, 'dd.mm.yyyy')

All works fine. But the second unnamed parameter is optional. How to check if it's empty or null then don't check the second condition?


texts:
I am in need of performing a calculation with a where clause based off two date fields in the joined two tables.  I have set-up the query like such, however the returned results I get are

SalesPerson SalesAmt    DiffAmt
G           22          -66

And my expected result set is

SalesPerson SalesAmt    DiffAmt
 G           11         -33

Below is sample DDL and the query I attempted to get my desired ouput.  What should be set-up differently in order to obtain my desired output?

Declare @Main Table (sn varchar(100), amt float, sd date)
Declare @Sub Table (sn varchar(100), samt float, ssd date)

Insert Into @Main (sn, amt, sd) Values
('G', 23.00, '20170601'), ('G', 21.00, '20170602'), ('G', 33.00, '20170608')

Insert Into @Sub (sn, samt, ssd) VALUES
('G', 11.00, '20170502'), ('G', 12.00, '20170601'), ('G', -1.00, '20170601')

Select
SalesPerson = m.sn
,SalesAmt = SUM(s.samt)
,DiffAmt = Sum(s.samt-m.amt)
FROM @Main m
Join @Sub s
On m.sn = s.sn
Where m.sd >= '20170601' And m.sd <= '20170605'
And s.ssd >= '20170601' And s.ssd <= '20170605'
GROUP BY m.sn


texts: How to join at least two tables with different column names but same data, and only show the matching data

texts:
I am trying to combine two (possibly more) tables that has different column names but the same data within the columns I am trying to line up.  For example, I have a table called dbo.member and within this table is a column called UID. On another table called dbo.tasklist_data, there is a column called TaskID which holds some of the same data as UID.  What I am looking for is to combine the two tables with the result giving me ONLY the UID's on the member table, that matches up with the data in the TaskID column on the tasklist_data table.

I do hope I explained that well enough.  Please bear with me as I am new to this but eager to learn.  Any help or advice will be greatly appreciated.  Thank you.


texts: mysql wont start after increasing innodb_buffer_pool_size and innodb_log_file_size

texts:
I am following this solution here https://stackoverflow.com/questions/3927690/howto-clean-a-mysql-innodb-storage-engine/4056261#comment14041132_4056261 and tried to increase my innodb_buffer_pool_size to 4G and later 1G (also 1024M) in addition to the log file size, but mysql wont start with those values.
If I put it back to 512M mysql starts fine.

How can I solve this? My server is a 16GB one, and according to Webmin sysinfo:

Real memory 15.62 GB total, 3.13 GB used

Meanwhile I found the error log as well:

  120529 10:29:32 mysqld_safe mysqld from pid file
  /var/run/mysqld/mysqld.pid ended

  120529 10:29:33 mysqld_safe Starting mysqld daemon with databases from
  /var/lib/mysql

  120529 10:29:33 [Note] Plugin 'FEDERATED' is disabled.

  120529 10:29:33 InnoDB: The InnoDB memory heap is disabled

  120529 10:29:33 InnoDB: Mutexes and rw_locks use GCC atomic builtins

  120529 10:29:33 InnoDB: Compressed tables use zlib 1.2.3

  120529 10:29:33 InnoDB: Using Linux native AIO

  120529 10:29:33 InnoDB: Initializing buffer pool, size = 1.0G

  120529 10:29:33 InnoDB: Completed initialization of buffer pool

  InnoDB: Error: log file ./ib_logfile0 is of different size 0 134217728
  bytes

  InnoDB: than specified in the .cnf file 0 268435456 bytes!


texts: T-SQL query - how to AVG() the SUM() of columns' rows to a new row column?

texts:
What is asked is to find the average marks for each student comprised by the total of the marks divided by their count.

How should the query look like to achieve this, since the AVG function is an aggregate one that is retrieving the vertical average column-wise but not the horizontal. Is there an elegant way to achieve this by a query?

Could you please elaborate the below query on the screenshot or modify it so that it works? Is there an elegant way to make this using AVG and COUNT() only without the trivial sum/count?

I have the following solution on the screenshot to extend more so that it does the job but for me it seems not to be correct:

Please find the below code (my trivial solution as a sum and division by the number of the summed fields):

USE [Students]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Students](
 [StudentID] [int] NOT NULL,
 [StudentName] [nchar](100) NOT NULL,
 [StudentSurname] [nchar](100) NOT NULL,
 [Biology] [decimal](3, 2) NOT NULL,
 [Maths] [decimal](3, 2) NOT NULL,
 [Geography] [decimal](3, 2) NOT NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
 [StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

GO
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (1, N'Mike                                                                                ', N'Manson                                                                                ', CAST(2.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(1.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (2, N'Timo                                                                                ', N'Torn                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(2.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
INSERT [dbo].[Students] ([StudentID], [StudentName], [StudentSurname], [Biology], [Maths], [Geography]) VALUES (3, N'Jeffrey                                                                                ', N'Jones                                                                                ', CAST(1.00 AS Decimal(3, 2)), CAST(3.00 AS Decimal(3, 2)), CAST(4.00 AS Decimal(3, 2)))
GO

SELECT * FROM Students;

SELECT
(SELECT Biology + Maths + Geography)/3 AS Mark_average
FROM Students;

Here is the output of the above query:

Mark_average
1.666666
2.333333
2.666666

Is there an alternative and more elegant/sophisticated way (similar to what is on the screenshot, by e.g. a subquery using AVG() of SUM() function) to retrieve the result?

Thanks much in advance!


texts: Unable to connect to Mysql via JDBC but works fine with Mysql

texts:
I have a mysql server running locally to which I am able to connect using

mysql -h localhost -u root

It does not require a password. But when I try to connect with the same attributes (jdbcurl = "jdbc:mysql://localhost:3306/mysql", user = root) via JDBC, I get this weird exception

he last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
    at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:151)
    at com.mysql.cj.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:167)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:91)
    at com.mysql.cj.NativeSession.connect(NativeSession.java:144)
    at com.mysql.cj.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:956)
    at com.mysql.cj.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:826)
    ... 33 more
Caused by: java.net.ConnectException: Connection refused (Connection refused)
    at java.base/java.net.PlainSocketImpl.socketConnect(Native Method)
    at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399)
    at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242)
    at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224)
    at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403)
    at java.base/java.net.Socket.connect(Socket.java:591)
    at com.mysql.cj.protocol.StandardSocketFactory.connect(StandardSocketFactory.java:155)
    at com.mysql.cj.protocol.a.NativeSocketConnection.connect(NativeSocketConnection.java:65)
    ... 36 more

What could be the issue here?

EDIT
Tried out the suggestion given in the comments to try to connect to mysql database via mysql as being done in java.

mysql -h localhost -u root mysql

This works and I get connected to the mysql db.


texts: Inaccurate 'Actual' row counts in parallel plan

texts:
This is a purely academic question, in so much that it isn't causing a problem and I'm just interested to hear any explanations for the behaviour.

Take a standard issue Itzik Ben-Gan cross-join CTE tally table:

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[TallyTable]
(
    @N INT
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
    WITH
    E1(N) AS
    (
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    )                                       -- 1*10^1 or 10 rows
    , E2(N) AS (SELECT 1 FROM E1 a, E1 b)   -- 1*10^2 or 100 rows
    , E4(N) AS (SELECT 1 FROM E2 a, E2 b)   -- 1*10^4 or 10,000 rows
    , E8(N) AS (SELECT 1 FROM E4 a, E4 b)   -- 1*10^8 or 100,000,000 rows

    SELECT TOP (@N) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM E8
)
GO

Issue a query that will create a 1 million row number table:

SELECT
    COUNT(N)
FROM
    dbo.TallyTable(1000000) tt

Take a look at the parallel execution plan for this query:

Note the 'actual' row count prior to the gather streams operator is 1,004,588. After the gather streams operator the row count is the expected 1,000,000. Stranger still, the value is not consistent and will vary from run to run. The result of the COUNT is always correct.

Issue the query again, forcing non-parallel plan:

SELECT
    COUNT(N)
FROM
    dbo.TallyTable(1000000) tt
OPTION (MAXDOP 1)

This time all operators show the correct 'actual' row counts.

I've tried this on 2005SP3 and 2008R2 so far, same results on both. Any thoughts as to what might cause this?


texts: MySQL 5.7 Master-Slave Replication Functional but Not Replicating New Records

texts:
I've got a Master-Slave topology set up with the following two servers:

Master Information:
OS: GNU/Linux Centos 7.6.1810 (Core)
Kernel: 3.10.0-042stab140.4
MySQL version: 5.7.25
Server_id: 1

Slave Information:
OS: GNU/Linux Centos 6.10 (Final)
Kernel: 2.6.32-042stab134.8
MySQL version: 5.7.29
Server_id: 2

I have re-setup this whole thing a few times now, with the same results each time. It seems to work, but doesn't. Everything indicates that it is working fine, but the slave does not show new records. Here are the things I've checked:

SHOW SLAVE STATUS; output looks fine:

Master_Log_File and Read_Master_Log_Pos match what I see on the master. It is up to date on the binary log files

Both threads are running

Last_IO_Error and Last_SQL_Error are both null, no errors here

Slave_SQL_Running_State = Slave has read all relay log; waiting for more updates

Looked for errors in mysqld.log on both master and slave and can't find anything indicating what is wrong
Checked disk space on both hosts, plenty of free disk
SELinux is disabled on both hosts
Ensured unique server_id values between hosts

My replication filters should be fine, I've read just about everywhere that binlog_do_db and such are not a good idea to use, so we are using only Replicate_Wild_Do_Table: ('database.%') on the slave. That is the only thing filtering being done in the whole topology.

When I dumped the database which I used to import into the slave (using the mysqldump program), I used the --skip-triggers flag but the triggers only affect tables which I don't care about, and those do not have keys which are relied upon to update other tables.

I'm using ROW based binary logging.

Here is the CHANGE MASTER TO statement I used to begin replication after importing the database with the dump file:
CHANGE MASTER TO MASTER_HOST='host-ip', MASTER_PORT=host-port, MASTER_USER='replication_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1353, MASTER_SSL=1 FOR CHANNEL 'channel-1';

I have no error messages in /var/log/mysqld.log on either the master or the slave that would indicate the problem. All I know is that everything looks healthy and fine, but nothing is being replicated! Seems kind of absurd that I can't even see that anything is wrong, not even one of mysql's amazingly vague error messages.

Does anyone have any ideas? What have I not checked? I tried searching for related posts but everyone else seems to have a problem they can work on but I don't seem to have a problem.. just a major symptom.


texts: what are the costs of enabling extended events?

texts:
I have been dealing with deadlocks, specifically getting their information, as you can see on this post here.

On this same post Shanky recommended "You must now rely on extended events trace to capture deadlock information"

My questions would be:

1) is there any increase of CPU by using extended events?

2) how much memory, disk space and I/O does it use?

any link to related documentation will be a bonus.


texts:
I'm an Oracle DBA getting started on DB2.

On Oracle we have a document called Optimal Flexible Architecture that sets a group of naming standards, like what should be the disk name for the database. This is a great because if everyone follows the document you can easily administer new databases without suffering.

I've installed a DB2 (next next finish way) and I noticed that the database is created on /home/db2inst1 is this ok?

I was wondering if IBM provides some guide for that.


texts:
I'm trying to reduce the time to install Oracle on a pipeline we have. At the moment, it's installed using dbca in silent mode with a database template file. The majority of the time seems to be creating the data dictionary etc. I have a theory that it would be quicker to clone a totally vanilla install and add the application specific data using data pump after. I just wanted to see if it's worth the effort trying this - has anyone tried it?

FYI - it's Oracle 12c EE using ASM for storage.


texts: Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results in another table oracle

texts:
I am very much new to the DB world, so wanted to review whether I am following right approach or not.
I have two tables,

table A  --> is a table with 40 columns

table B --> is a table with the 3 columns (each column of table A is represented as a row in this table.)

Example:

A:
column_1    |      column_2          |    column_3 ......... | column_40
-----------------------------------------------------------
value1_1    |      value1_2          | value1_3......        | value1_40

B:
column_name      |column_value  | column_errorKey
----------------------------------------------------
column_1         | value1_1     | value1_1_errorKey
column_2         | value1_2     | value1_2_errorKey

What am I doing?

Validate each value of a row from table A and insert into the table B with
 its value, error key and corresponding column name.

My PL SQL code is as below, (Note: SQL code has considered only two columns to minimize the code here)

INSERT WHEN  (LENGTH(column_1)  <=7) THEN
        into table_B values(
          'column_1',column_1,'NoError')
WHEN  (LENGTH(column_1)  >7) THEN
        into table_B values(
          'column_1',column_1,'invalidLength')
WHEN  (LENGTH(column_2)  <= 75) THEN
        into table_B values(
          'column_2',column_2,'NoError')
WHEN  (LENGTH(column_2)  > 75) THEN
        into table_B values(
          'column_2',column_2,'invalidLength')
( select column_1,column_2,...,column_40
          from table_A );

The validation that is happening within When the condition has only one validation but we have more validation like this for the value of each cell. I wanted to know whether I am on right approach or not.


texts: What's the maximum acceptable latency if using a remote MySQL DB server?

texts:
Aside from single server optimization, I am making evaluation in splitting up an existing web application to separate WEB and DB servers.

I understand network latency is an important factor when using a remote database server.

Ideally I would place a dedicated DB server in the same DC as the WEB server or even with a private network.

However, if I really need to put it on a different DC, what's the maximum acceptable network latency in order to get a responsive connectivity?

Update:

In the end, I want to ask, if I absolutely need to deploy remote database, what's the best practice and things to consider when it comes to choosing the datacenter and infrastructure?


texts: Best use of RAM for multiple SQL instances?

texts:
I have a new Windows Server 2016 box with 32GB of RAM, it will be used for dev testing by one or two users max. There will be occasional use of VM/Hyper-V instances and a couple of IIS test sites running from time to time.

I also need to install all versions of SQL Server from 2008 through to 2016. These will each have many databases attached but very few will actually be active at a time unless testing calls for it.

Assuming there is no practical issue with installing several different SQL Server installations on the same box, what options do I have to configure the best use of RAM? I know that on a standard installation, SQL will try to hog all available RAM but I want to try to avoid this leaving around 12GB for other services and allocate a maximum of 24GB for all the SQL instances to share equally without fighting with each other over resources.

Is there a good way to achieve this?

PS I'm not an experienced DBA just a humble developer