texts: How to have multiple condition on one column

texts:
I have table with students - name, id
and table with grades - id, grade value, discipline.
Each students has few grades. I want to select only the students with grade = 2 and grade = 6 in the same time.
SELECT NAME

FROM STUDENTS, GRADES

WHERE STUDENTDS.ID = GRADES.ID

AND GRADE = 2 AND GRADE = 6

The logical condition can't be valid there. Which is the function that I need?


texts:
I have a virtual box with 2 installed instances of MySQL.

First instance:

- /etc/mysql/my.cnf
- /var/lib/mysql
- port = 3306

Second instance:

- /etc/mysql2/my.cnf
- /var/lib/mysql2
- port = 3307

I can connect to both. The first instance is fine. The second is not.

MySQL doesn't ignoring some params from my.cnf file (socket, port, pid-file, data-dir...). But if I'm trying to change something else: cache, buffers, log files it completely ignores changes after restarting.

I need to make MySQL read all params from my.cnf.

I tried:
- to move my.cnf from /etc/mysql to /var/lib/mysql;
- commenting out !includeir /etc/mysql/conf.d.

Could someone guide me?


texts: BI and Self Service Reporting Tools that Support Web (HTML) Interface?

texts:
Hopefully a simple question.  What BI tools (commercial or OSS) support self-service reporting or self-service BI via an HTML web interface?

I specified HTML because I know of at least one that can do it via the web, but only in Silverlight.


texts: ODBC Calling Fill - Unexpected closed connection after 2 hours

texts:
Using PostgreSQL 12.3 (Ubuntu 12.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
I use driver PostgresSQL Unicode(x64)  version 13.00.00.00
I have a query that is executed through an ODBC connection in a Powershell 5.1 script. I use the Fill() method to retreive about 3500 records daily. When the script works, it takes 2-5 minutes to execute and retrieve data when it works.
Problem is that the script "fails" half of the time approx. When this occurs, the Powershell script does stop only after 2h and 30 seconds.
We double checked the postgres logs and when this occurs, we see that the query successfully completed within 6 minutes always.  I don't know what to look for. any Idea?
Below is the error got:
Executed as user: NT Service\SQLSERVERAGENT. A job step
received an error at line 94 in a PowerShell script. The
corresponding line is '(New-Object system.Data.odbc.
odbcDataAdapter($cmd)).fill($ds) | out-null  '. Correct
the script and reschedule the job. The error information
returned by PowerShell is: 'Exception calling "Fill" with
"1" argument(s): "The connection has been disabled."  '.

Process Exit Code -1.

Not too familiar with postgreSQL.
Thanks!


texts:
It has been determined that user "SCOTT" should use a new temporary
  tablespace called TEMP2 size equivalent to TEMP tablespace (refer to
  Notes below for determining the size of TEMP).   Create a script that
  performs the following

  Create one Bigfile tablespace for TEMP2.  size equal to the size of
  TEMP Change the default temporary tablespace for "SCOTT" to TEMP2

This is what I have come up with so far, I have used a combination of the Oracle 11g DBA Handbook, also the Oracle website knowlege base at http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011
for example.

I cannot find how to determine the size of "TEMP". I have been able to locate the temp tablespace though. I just havent figured out the query to find out the size.

what I have so far is:

create bigfile tablespace TEMP2
    datafile '/u06/oradata/TEMP2.dbf' size ?g;

alter user SCOTT temporary tablespace TEMP2;

any help or insight would be appreciated, the script is for 11g and SQL Plus. Thanks, I am a newb.


texts: Trigger function check if all NEW.* fields exist in another table

texts:
In PostgreSQL I need to code a trigger function that checks if all the fields in NEW.* exist in another table that has the exact same name as the table that fired the trigger but appended with "_hv".

For example, if the table my_example fires the trigger I need to check if all the fields in my_example exist in my_example_hv, and if it does not, alter my_example_hv to add the fields that are missing.

I am very new to plpgsql and I starting trying to code it with no success:

CREATE OR REPLACE FUNCTION trigger_hv()
  RETURNS trigger AS
$BODY$
DECLARE
    typeoffield character varying;
BEGIN

FOR field IN NEW LOOP
      IF NEW.field not exists ON TG_TABLE_NAME||'_hv' THEN
        typeoffield := typeof(NEW.columns); -- of course does not work
        EXECUTE 'ALTER '||TG_RELNAME||'_hv ADD COLUMN '||NEW.column ||' '||typeoffield;
      END IF;
END LOOP;

RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql;

I think I need some expert help ...


texts: SQL Server Mirror failover on failing storage

texts:
Recently we had an issue with a sql server 2008 r2 HA mirror. I have tried to reproduce this in a lab environment and i came to the conclusion that i am missing something OR something is not possible.

I am using 2 sql server 2008 r2 servers. Both the servers have a iscsi storage connection to a storage server. Mirroring is done with a witness server in HS mode.
i have followed a blog post by Glenn Berry about how to create a group failover, he told me on twitter it would also be usable in case of failing storage. However, somewhere i can't really get things to work.

What am i doing. I have a sql-01 as princiapl and a sql-02 as mirror. 2 databases running in HA mirror mode. I have a failover script running on WMI 7 and 8. When i go to my storage server to disable the storage of sql-01 i would suspect that eventually i get a failover to sql-02. but it doesn't...

Anyone can give me some hints on this?

Edit : Failover with a shutdown-ed storage for sql-01 looks working, because of a tempdb that is getting corrupt which needs recovery (according to logs). Therefor the instance has to shutdown (also in the logs), so probably that is my reason i can failover from sql-01 to sql-02, by accident and apparently not by design.


texts: How to check if a trigger fires on INSERT, UPDATE or DELETE statements?

texts:
How do I determine whether a trigger is targeting INSERT, UPDATE or DELETE statements?

I've checked in sys.triggers and I can't see anything related to what operation the trigger fires on.


texts: Function is accepting varchar when input is set to INT

texts:
I have a function that is suppose to have INT as input. However, when I pass any string, the input is accepted.

Why does the function not error out because of the input not matching the input type?

This is the function

DROP FUNCTION IF EXISTS erbaccess.canAdminIRB;

DELIMITER $$
CREATE FUNCTION erbaccess.canAdminIRB(_userID int) RETURNS int

BEGIN

set @canPerformAction = 0;

if (select count(*)
    from erbaccess.usersVsGroupsVsActions
    where (actionCode in ('ADMSYS', 'ADDIRB')
            or privilegeGroupCode  in ('SYS', 'REG')
    and userID = _userID) > 0 then

    set @canPerformAction = 1;
end if;

RETURN @canPerformAction;

END $$

The RETURN for @canPerformAction is also returned as 1, when it should be 0. IF I run just the if statement outside of this function with a fake username (not INT, but VARCHAR) then the return is 0.


texts: Change rowguidcol property to a different column

texts:
In my SQL Server 2008 database, I have a table with two uniqueidentifier columns.

CREATE TABLE MyTable (
    Col1 uniqueidentifier rowguidcol not null,
    Col2 uniqueidentifier not null,
    AttachmentData varbinary(max) filestream null
)

Now, I want to switch the column that has the rowguidcol identifer on it by moving it from Col1 to Col2, so it would end up being

CREATE TABLE MyTable (
    Col1 uniqueidentifier not null,
    Col2 uniqueidentifier rowguidcol not null,
    AttachmentData varbinary(max) filestream null
)

When I try to do this through the table designer (by setting Col1.RowGuid = No and Col2.RowGuid = Yes), I receive the following error:

- Error modifying column properties for 'Col1'.
A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column.

Is there any way to switch the column with the rowguidcol identifier, or should I rename this table, make a new one, and then copy the data over from the old table?


texts: SQL Server 2005 Log Truncate - how long for a 60Gb log; mins, hours or days?

texts:
I have taken over an old website. To cut a long story short the people that host the computer, backup all the database files, but there is no maintenance plan in place for the backup (e.g. they literally backup the files from the disk, so truncating does not happen).

The recovery mode is FULL (SQL Server 2005).

This is a bit of a "how long is a piece of string" question, but just need a ball park figure really of how long a truncate will take:

BACKUP LOG  databasename  WITH TRUNCATE_ONLY

Is it likely to be minutes, hours or days for a 60Gb log (the machine is reasonable intel 4 core 2.5Ghz 8Gb machine (not SSD drives))?


texts: How to optimize MariaDB database when it is serving queries

texts:
We would like to run an optimization on our MariaDB database while still allowing it to serve queries.

I see that for MySQL this is possible since 5.7.4 (from this link)

  For InnoDB tables prior to 5.7.4 and other table types, MySQL locks
  the table during the time OPTIMIZE TABLE is running. As of MySQL
  5.7.4, OPTIMIZE TABLE is performed online for regular and partitioned InnoDB tables.

Does anyone know how we can do it for MariaDB as well? I see no mention at the MariaDB OPTIMIZE page.


texts: I need to use xp_cmdshell in one of SQL job.So is it safe to reconfigure xp_cmdshell to 1 or can i set value 1 in the starting of SQL statements and set it back to 0 in the end of SQL Statement ?


texts:
I need assistance getting labels to print from SSRS 2014 to a Zebra GK420D direct thermal label printer. The labels show up when running the report but when I print them to the label printer, all that comes out is a blank page. If I export to a PDF and then print, it works fine, but this isn't a viable option for our end users.

I've set the printer to the correct label size as well as changing the page size in the report to match and I've changed the printer settings to "Always use driver settings" and to print directly to the printer (bypassing the spooler) which were the two recommendations I found thanks to googling. Any assistance is greatly appreciated.


texts: CREATE TEMPORARY TABLE, temp_buffers and Postgres performance

texts:
I have a bunch of queries that use temporary tables, and most of them are working really good, but from time to time they take an usual amount of time: 3-5 seconds.

My temp_buffers is set to the default 8Mb and I'm thinking that maybe the problem is caused by the buffer being swapped to the disk when it overflows or something like that.

Is there a way of checking how much of the buffer is taken? Is my way of thinking at all reasonable? Maybe the allocation of new space is really fast and I should look at other places?


texts:
Sorry for the long post!

I have a database containing ~30 tables (InnoDB engine). Only two of these tables, namely, "transaction" and "shift" are quite large (the first one have 1.5 million rows and shift has 23k rows). Now everything works fine and I don't have problem with the current database size.

However, we will have a similar database (same datatypes, design ,..) but much larger, e.g., the "transaction" table will have about 1 billion records (about 2,3 million transaction per day) and we are thinking about how we should deal with such volume of data in MySQL? (it is both read and write intensive). I read a lot of related posts to see if Mysql (and more specifically InnoDB engine) can perform well with billions of records, but still I have some questions. Some of those related posts that I've read are in the following:

Can MySQL reasonably perform queries on billions of rows?
Is InnoDB (MySQL 5.5.8) the right choice for multi-billion rows?
Best data store for billions of rows
How big can a MySQL database get before performance starts to degrade
Why MySQL could be slow with large tables?
Can Mysql handle tables which will hold about 300 million records?

What I've understood so far to improve the performance for very large tables:

(for innoDB tables which is my case) increasing the innodb_buffer_pool_size (e.g., up to 80% of RAM).
Also, I found some other MySQL performance tunning settings here in
percona blog
having proper indexes on the table (using EXPLAN on queries)
partitioning the table
MySQL Sharding or clustering

Here are my questions/confusions:

About partitioning, I have some doubts whether we should use it or not. On one hand many people suggested it to improve performance when table is very large. On the other hand, I've read many posts saying it does not improve query performance and it does not make queries run faster (e.g., here and here). Also, I read in MySQL Reference Manual that InnoDB foreign keys and MySQL partitioning are not compatible (we have foreign keys).
Regarding indexes, right now they perform well, but as far as I understood, for very large tables indexing is more restrictive (as Kevin Bedell mentioned in his answer here). Also, indexes speed up reads while slow down write (insert/update). So, for the new similar project that we will have this large DB, should we first insert/load all the data and then create indexes? (to speed up the insert)
If we cannot use partitioning for our big table ("transaction" table), what is an alternative option to improve the performance? (except MySQl variable settings such as innodb_buffer_pool_size). Should we use Mysql clusters? (we have also lots of joins)

Thanks for your time,


texts:
I have a table with 25 million records and 10 columns on which I am trying to create indexes. According to the following documentation no table copy should occur but when I monitor disk usage it is showing that MySQL has high disk usage and data directory is increasing in size. Can anyone explain why this is happening.

http://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid


texts: How to check parameter value existence in a procedure?

texts:
The following is a procedure that uses 2 parameters: customer_code and pay_amount. The procedure works as expected, however when I enter wrong cus_code I get this error instead of my custom checking:

ORA-01403: no data found
ORA-06512: at "XXXXX.CUST_PAY", line 19
ORA-06512: at line 2

The procedure:

CREATE OR REPLACE PROCEDURE cust_pay (temp_ccode IN NUMBER, pay_amount IN NUMBER)AS
BEGIN
    DECLARE
      Value_check NUMBER;
      cbalance NUMBER;
    BEGIN
        SELECT Count(cus_code)
        INTO value_check
        FROM customer
        WHERE cus_code = temp_ccode;

        IF value_check IS NULL THEN
            Dbms_Output.put_line('the value was not FOUND');
        ELSE
            UPDATE customer
            SET cus_balance = cus_balance - pay_amount
            WHERE cus_code = temp_ccode;

            SELECT cus_balance
            INTO cbalance
            FROM customer
            WHERE cus_code = temp_ccode;

            IF cbalance < 0 THEN
                Dbms_Output.put_line('The client owes us ' || cbalance);
            ELSE
                Dbms_Output.put_line('Customer new balance is ' || cbalance);
            END IF;
        END IF;
    END;
END;

What did I do wrong? I guess I need to check the value before I SELECT, right?


texts: SQL Errorlog Constant FlushCache Messages

texts:
A server that is part of a SQL 2017 Always on availability group (BAG) I have noticed an increase of these messages. I understand these are messages that appear in the log as standard since 2012 (trace flag prior to 2012) but over the last 2 days they are appearing every few minutes, there are no backups running or maintenance jobs at these times
The server is acting as the secondary non readable fail over partner and the primary server is not exhibiting the same behavior.

09/04/2020 10:52:24,spid82s,Unknown,FlushCache: cleaned up 2303 bufs with 1881 writes in 81090 ms (avoided 11 new dirty bufs) for db 7:0
09/04/2020 10:52:07,spid52s,Unknown,last target outstanding: 2 avgWriteLatency 86
09/04/2020 10:52:07,spid52s,Unknown,average writes per second:  17.70 writes/sec             average throughput:   0.19 MB/sec I/O saturation: 13073 context switches 15434

There is also a warning in the system error log about disk issue, seconds after this the availability group failed over ever since the FlushCache messages have increased.

Has anyone experienced anything similar or have any advice, my sysadmin is also looking at the SAN and VMware estate.
Had a sudden thought could these be caused by autogrowth activities?


texts: Why is it that "aggregate functions are not allowed in FROM clause of their own query level"?

texts:
The former gives me the error message "aggregate functions are not allowed in FROM clause of their own query level" whereas the latter is permitted.  Unless I am much mistaken the latter is equivalent to the former because it just makes a new "local" name for v.

I'm writing a code generator so rewriting the former, natural form, to the latter, artificial form, is somewhat fiddly.

But what is the rationale for forbidding the former?

SELECT * FROM
((SELECT 0 as "v") as "T1"
 INNER JOIN LATERAL
 (SELECT
  SUM("v") as "r"
  FROM (SELECT 0) as "T1") as "T2"
 ON TRUE) as "T1"

SELECT * FROM
((SELECT 0 as "v") as "T1"
 INNER JOIN LATERAL
 (SELECT
  SUM("v_again") as "r"
  FROM (SELECT "v" as "v_again") as "T1") as "T2"
 ON TRUE) as "T1"


texts:
I have a scheduled task in Oracle 11g scheduled to run at 12:00 and 17:00 each day. If the first run takes more than five hours, what would happen to the second scheduled run? Would it simply be dropped or is it postponed until after the first is complete? If it is possible to do either, how can I check what is used in my DB?

I've seen the task run unscheduled a while after the first completes, but I'm not sure if there are other people starting it manually or if something else is affecting it.


texts: SQL Server 2014 log growing just in Navision 2015 just when always on is active

texts:
My question is about if anybody can help me to find out why my LOG file growing to more than 2T just when I activate ALWAYS ON in my NAVISION database.
I have more databases in my SQL Server 2016 and I have configured for my CRM DB and others and there is no problem. But with NAVISION I have problem.

Any suggestions?


texts: Selecting row grouped by an "add-on" indicator

texts:
I have a (pre-existing, no I did not design it like this!) schema / dataset like this:

CREATE TABLE t(id,name,seq,addon,kind)
AS VALUES
    ( 1234::int, 'A' , 345::int, 'f'::bool, 'c'  ),
    ( 1235     , NULL, 346     , 't'      , NULL ),
    ( 1236     , NULL, 347     , 't'      , NULL ),
    ( 2345     , 'B' , 348     , 'f'      , 'x'  ),
    ( 2346     , NULL, 349     , 't'      , NULL ),
    ( 3456     , 'C' , 350     , 'f'      , 'c'  ),
    ( 3457     , NULL, 351     , 't'      , NULL );

Where:

The "id" field is just a SQL sequence (order not guaranteed)
The "seq" field is guaranteed to be sequential throughout the table, e.g. table is normally sorted on this column.
The "addon" field is indicating a group of related entries, e.g. TRUE entries are related to the nearest FALSE entry with a lower "seq".

I want to SELECT all the rows with "kind" = 'x' and all of their associated "addon" rows, without getting any other "addon" rows for other kinds.

So for instance:

SELECT * FROM t WHERE kind = 'x' AND ???

Would give me:

  id  | name | seq  |addon | kind |
------+------+------+------+------+...
 2345 | "B"  | 348  |  f   | "x"  |
 2346 |      | 349  |  t   |      |

I have PostgreSQL 9.6...


texts:
I am having difficulty representing additional charges in an invoice. I understand how to represent the association between an invoice and individual items in that invoice with the use of a invoice_item table, however, there are charges that I want to include to an invoice which are not orders or products. For example, I might want to include an ad-hoc licence fee or an additional administration fees or similar.
What I have so far looks like this:

invoice
============
PK uid
FK cust_id
date_created
status
...

invoice_line
============
FK invoice_id
FK order_id
...

order
============
PK uid
FK cust_id
date_created
status

order_line
============
FK order_id
FK product_id
actual_price
quantity

product
============
PK uid
name
desc

Now, the above mentioned additional charges cannot really be represented as products here, nor are they associated to any specific order. It should be on a level similar to order, in the sense that there might be a number of those additional charges connected to each invoice, there might in fact be such charges even in cases where there were no orders placed whatsoever within the invoice time period.
What is the recommended way to represent such additional charges in my invoice/invoice_item table?


texts: Native dynamic SQL - execute immediate with column and table names

texts:
I have to make dynamic query, which binds column names and table name dynamically using native dynamic sql in oracle pl/sql.

Here is my first try:

declare
  komenda varchar2(255);
  v1 varchar2(10):='id_osrodek';
  v2 varchar2(10):='nazwa_o';
  v3 varchar2(10):='osrodki';
begin
  komenda := 'select distinct e.id_student, e.przedmiot, e.:1, o.:2 from egzaminy e
    inner join :3 o on o.:4 = e.:5
    inner join przedmioty p on e.id_przedmiot = p.id_przedmiot';
  execute immediate komenda using v1, v2, v3, v1, v1;
end;

And second:

declare
  komenda varchar2(255);
  v1 varchar2(10):='id_osrodek';
  v2 varchar2(10):='nazwa_o';
  v3 varchar2(10):='osrodki';
begin
  komenda := 'select distinct e.id_student, p.nazwa_p, e.' || v1 || ', o.' || v2 || ' from egzaminy e
    inner join '|| v3 ||' o on o.' || v1 || ' = e.' || v1 || '
    inner join przedmioty p on e.id_przedmiot = p.id_przedmiot';
  execute immediate komenda;
end;

And I wonder why first doesn't work?


texts:
i want to grant user update permission in Table
and deny delete permission for this user.

Is there any way to accomplish this?


texts: Zabbix MariaDB Copy item data to another item

texts:
My current DB:
MariaDB [zabbix]> SELECT VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 10.0.28-MariaDB-2+b1 |
+----------------------+

There are some duplicated items in my Zabbix.
I have the old item which has "history" and "trend" data, but it was disabled.
The new item also has data and is enabled.
I thought of copying old item history and trends to the new item but I'm not really sure how to do it safely.
I created some test items to check the update process, but It doesn't seem to go as I expected.

Old item - 30886 New Item - 32041

So, I wish to copy all data from the old item to the new item.
Meaning:

Old item keeps its data intact.
New item keep its current data and receives old item's data.
Both items use the same tables.

"history" table excerpt:
MariaDB [zabbix]> SELECT * FROM history WHERE itemid = 30886 LIMIT 5;
+--------+------------+--------+-----------+
| itemid | clock      | value  | ns        |
+--------+------------+--------+-----------+
|  30886 | 1609965887 | 0.3270 |  59358611 |
|  30886 | 1609966187 | 0.2747 |  14863699 |
|  30886 | 1609966486 | 0.3037 | 796425573 |
|  30886 | 1609966787 | 0.2855 |   9516260 |
|  30886 | 1609967086 | 0.2866 | 903642778 |
+--------+------------+--------+-----------+

I thought of doing something like this:
INSERT INTO history (`clock`, `value`, `ns`)
SELECT `clock`, `value`, `ns`
FROM history
WHERE itemid = 30886;

But that doesn't work. This way it won't insert the new itemid for every line and table "history" is specified twice, as target and source.
Maybe a more experienced fellow would point out some thoughts.
Any ideas?


texts: Creating indexes on AutoId and time column

texts:
My table structure is as follows

Events(
  AutoId int identity(1,1) //Primay Key, clustered index
  EventTime   datetime          // non unique, non clustered index
  FileName    nvarchar(1000)
  SystemName  nvarchar(1000)
  other columns
)

values in time column is almost increasing

Queries:
  List all events for last 7 days, last 1 month
  List all events where order by EventTime and SystemName or FileName like something

EventTime is present in most of the queries either through order by or filter clause.

Data Variations:
  Events table can have a two or three million rows
  It can have data for past one year

Non clusterd index on EventTime column is not always used because it can't filter enough data.

What all options can I try to improve overall query performance?


texts: Unable to Run psql in Windows Command Prompt

texts:
I have just installed postgreSQL and have set up the user postgres and its password. In the psql Shell I am able to login as normal however I can't login in Windows Command Prompt. When I run the psql command it asks for Password for user username for which I don't know the password.

I have tried running dropuser username in the psql shell however this doesn't make a difference. When I use psql -u postgres to login and run postgres=# \du, the other role doesn't appear:

                                  List of roles
Role name |                         Attributes                         |
Member
of
----------+------------------------------------------------------------+-----------
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}


texts:
Collation                | Charset  | Id  | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| utf8_general_ci          | utf8     |  33 | Yes     | Yes      |       1 |
| utf8_bin                 | utf8     |  83 |         | Yes      |       1 |
| utf8_unicode_ci          | utf8     | 192 |         | Yes      |       8 |

What's sortlen of mysql collation?
What's the difference between sortlen of utf8_unicode_ci and utf8_general_ci?


texts: Deleting already duplicated Filestream Files

texts:
I have three FileStreams (FS1 on F drive, FS2 on H drive, FS3 on E drive) belonging to the same FileStream group of one particular database (DB) which is in Simple recovery mode in the SQL Server 2012.

FS1 contains huge number of files due to which F drive is completely full.

So, I am trying to move some of the extra files from one FileStream (FS1 on F drive) to another FileStreams (FS2 on H drive and FS3 on E drive) using command:

dbcc shrinkfile('FS1', emptyfile)

Then, I take the Full and Differential backup of the database and issue the CheckPoint and try to delete the already duplicated files from the Filestream FS1 to get some space in the F drive using command:

sp_filestream_force_garbage_collection @dbname = 'DB' , @filename = 'FS1

But still no files get deleted and I receive the output as such:

  file_name num_collected_items num_marked_for_collection_items num_unprocessed_items last_collected_lsn

  DB_FS1 0 0 0 25000001749500000

Please suggest how to delete these already duplicated files.


texts: Functional dependency not detected for some columns

texts:
Here's a simple example schema:

CREATE TABLE parents (
  parent_id INT,
  type text null,
  PRIMARY KEY (parent_id)
);

CREATE TABLE children (
  child_id INT,
  parent_id INT,
  name text,
  PRIMARY KEY (child_id)
);

INSERT INTO parents (parent_id, type) VALUES (1, null);
INSERT INTO children (child_id, parent_id, name) VALUES (1, 1, 'foo');

This query works:

SELECT child_id
FROM children
JOIN parents USING (parent_id)
GROUP BY child_id, parent_id

Fiddle

If I change that to SELECT child_id, name it still works, as it should because name is functionally dependent on child_id and child_id is in the GROUP BY clause.

Now if I change it to SELECT child_id, type I think it should still work, because type is functionally dependent on parent_id and parent_id is in the GROUP BY clause. But instead I get an error message:

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

What's the difference? Does it have anything to do with the JOIN?


texts: How RDBMS solve C10K problems, it's also load balance?

texts:
I know AP server meet C10K problem can use Load Balance to be solved.
But RDBMS server in my experience is very difficult to make load balance, especially for writing db, it should meet the client 10000 problem
Therefore there's a question 'How do RDBMS solve C10K problems?" It's also load balance?


texts: fetching Remote table data and storing into local dump file in pgSQL?

texts:
I want to copy one table's content from a remote machine to local. I have tried with psql Command \copy. It's working fine as "copy" is not working due to copying from remote.

But is there any way I can do the same without the command console, and execute a simple query through c# and do it programatically? I am using  Postgres 9.4


texts: MySQL Replication: Insert generating VERY huge temporary tables

texts:
our replication slave is generating ~300GB temporary files while running relatively simple inserts.

Our setup:
Master: 5.0.45-0.sles10.x86_64,

Slave: 5.0.85-win32, also tried with 5.1.57-win64, same DB

Slave configuration:

  max_connections=100
  query_cache_size=1M
  table_cache=2048
  tmp_table_size=32M
  thread_cache_size=8
  binlog_cache_size = 10M
  myisam_max_sort_file_size=100G
  myisam_sort_buffer_size=69M
  key_buffer_size=55M
  read_buffer_size=64K
  read_rnd_buffer_size=256K
  sort_buffer_size=8M
  innodb_additional_mem_pool_size=16M
  innodb_flush_log_at_trx_commit=0
  innodb_log_buffer_size=16M
  innodb_buffer_pool_size=512M
  innodb_thread_concurrency=10
  datadir=D:\MySQLrepl\
  innodb_data_home_dir=D:\MySQLrepl\
  innodb_data_file_path=ibdata1:33266M;ibdata2:31484M;ibdata3:52988M;ibdata4:123492M;ibdata5:100M:autoextend
  innodb_log_group_home_dir=D:\MySQLrepl\
  innodb_log_files_in_group=2
  innodb_log_file_size=128M
  log-bin=mysql-bin

Database information:
~360 GB total data size
~50 GB data size for affected database, rest in different database
Storage engine is innoDB for all tables
Database was transfered to slave with MySQL Enterprise Backup 3.5.2
sernumbers_results contains around 317 million rows, size is around 17,7 GB, rowid is the primary key.

One of the queries generating the problem:

  INSERT INTO sernumbers_results_2009 SELECT * FROM sernumbers_results WHERE rowid>(SELECT rowid FROM sernumbers_results_2009 order by rowid desc limit 1) ORDER BY rowid LIMIT 10000

What this should do, according to my colleagues logic, is to copy results from 2009 to a separate table bit by bit. He has a good excuse for this, he is french ;)
He also says that running the insert queries was fast on the server and indeed: the SELECT-part from that query runs in 0.18s without any problems, so it seems that there is a problem with the insert bit. It seems that all databases are converted to temporary MyISAM-tables before the query is applied. It also seems that there is little to none back-conversion because the server is starting with the next temporary table almost immediately when the one before has reached it's maximum size.

I'm really totally clueless here, so any help or suggestion is really appreciated.


texts: Building and analyzing trends in a database

texts:
I am quite new to working with databases but have spent a few hours reading about XML and trying to understand the use of CML for building databases and find myself completely at a loss.
Here is what I hope to do :
1. I have a few hundred xml files storing the service and operation history of a instrument with the following format :

<history title="History Report">
   <infos>
      <record date="01/01/2000" time="24:00">
         <item>
         <name>Name</name>
         <comment>SYS1</name>
         </item>
         <item>
         <name>Catalog No</name>
         <comment>ABCD</name>
         </item>
         .
         .
         .
         <item>
         <name>Country</name>
         <comment>XYZ</name>
         </item>
      </record>
   </infos>
   <operation>
      <record date="01/01/2000" time="24:00">
         <item>
         ....
         </item>
         .
         .
         .
         <item>
         ....
         </item>
      </record>
   </operation>
....
</history>

I want to build a database with the data from these xml files and query things like :
Q) How many of Cat No. 123 went to country ABCD

We have traditionally used excel for this kind of work, which I realize is a not a good idea in the long term. I am thus trying to figure out how to directly parse and analyze the xml files.

I am completely at a loss as to how to get started on this. Any help would be appreciated.


texts:
I need to get an unique static ID that is not changeable by the users, and if the user copies the database to another server this static ID is different.
Background
My company wants to sell our app and allow for the end user to control the administration of the database.  At the same time we do not want someone to pay for one license and then install the app on 100 different database (as an extreme example).
I thought server_id was is needed. Then, I found out that it can be configured by the end user. Therefore, this is no good.
thanks for the help!


texts:
To make updates faster, I am using:
ALTER TABLE imagingresourceplanning.opnav_fact_revenue_costs SET UNLOGGED ;

What are the drawbacks of this command?
What will happen if system crashes during the update? Is all the data present in the table deleted? Or only the updates which are being done will be lost?


texts:
I am currently facing a problem with a slow running Hibernate query that is being kicked off from a Java program. It is making a call to Oracle 11g at the back end.

This query is taking anything from 40-90 seconds the first time it is run. On subsequent executions however the query is returned in a fraction of the time (and I don't even see the database being hit, so assume that hibernate is caching it).

If I copy and paste the query from Enterprise Manager into a SQL client and run the very same query directly (even changing a few parameters) the query returns in a fraction of a second.

If I look at the performance tuning tab in EM I see that the time taken is primarily taken up with User I/O Waits (97.5%), and CPU (2.5%). Could this mean that the fetch size I am using in hibernate is configured at too small a value?

If there is any other information you might need to help me get to the bottom of this issue then please let me know.

=====

Additional information:

We do have an index on the table and I can see that it is being used as part of the execution of the query, unfortunately it isn't very readable but I am not sure how else to include it:

Id  Operation   Name    Rows
(Estim)     Cost    Time
Active(s)   Start
Active  Execs   Rows
(Actual)    Read
Reqs    Read
Bytes   Mem
(Max)   Activity
(%)     Activity Detail
(# samples)
0   SELECT STATEMENT                        1
1   . FILTER                        1
2   .. HASH JOIN RIGHT OUTER        2674    7223    1   +4  1   0           1M
3   ... TABLE ACCESS FULL   TOTEM_EQ_EXPIRYCODES    475     4   1   +4  1   481
4   ... HASH JOIN RIGHT OUTER       2674    7219    1   +4  1   0           399K
5   .... TABLE ACCESS BY INDEX ROWID    TOTEM_EQ_UNDERLYINGS    1   2   1   +4  1   1
6   ..... INDEX UNIQUE SCAN     TOTEM_EQ_UND_PK     1   1   1   +4  1   1
7   .... NESTED LOOPS                       1
8   ..... NESTED LOOPS      2674    7216    42  +4  1   0
9   ...... TABLE ACCESS BY GLOBAL INDEX ROWID   EQUITIES_MONTHLY_INSTRUMENTS    2671    1871    45  +1  1   8438    3517    27MB        24.44   db file sequential read (11)
10  ....... INDEX RANGE SCAN    EQ_MON_INS_UNDERLYING_INDX  2671    12  42  +4  1   8438    27  216KB
11  ...... PARTITION RANGE ITERATOR         1   2           8438
12  ....... INDEX RANGE SCAN    EQ_MON_RESULT_INSPT_UNQ     1   2   44  +2  8438    0   5403    42MB        75.56   Cpu (1)
db file sequential read (33)
13  ..... TABLE ACCESS BY LOCAL INDEX ROWID     EQUITIES_MONTHLY_RESULTS    1   3

Here are the Global stats:

Elapsed
Time(s)     Cpu
Time(s)     IO
Waits(s)    Fetch
Calls   Buffer
Gets    Read
Reqs    Read
Bytes
45  0.73    45  1   22980   9740    76MB


texts: SSDT Schema Comparison for Stored Procedure signed by a Certificate

texts:
I am using SSDT 14.0.50730.0 in Visual Studio Professional 2015. I am comparing databases between two instances and it is finding that there are stored procedures that do not match with regard to their signatures.

In both instances, the Certificate was created in each instance from the same script and the signatures were also added by CERTIFICATE with a PASSWORD using the same script in each instance.

However, Schema Comparison is showing the procedure in both the source and target as having a CERTIFICATE with a SIGNATURE with Signature values that do not match.The generated publish script drops the SIGNATURE BY CERTIFICATE, modifies the procedure, and then adds a SIGNATURE BY CERTIFICATE WITH SIGNATURE. The signature value matches what was shown in the source instance. However, the script fails with the message "The signature of the public key is invalid."

What do I have to do to get these procedures in sync?


texts: I have 1 DB called - DB_A. I would like to create read only database DB_B from DB_A which updates itself every 10 mins. Is their any easy way to do it in postgres? I am using Postgres 11. Also any data deleted from DB_A should get removed from DB_B on next update.


texts: Audit Queries in SQL Server 2008 Including Start Stop Times

texts:
I have SQL Server 2008 Enterprise. I know how to set up auditing on the SQL Server, but it looks like the audit logs don't include query start/stop times.

We currently use a product called Splunk to do a lot of data analysis at our organization. I would like to be able to feed Splunk our machine performance data along with a SQL query audit log to be able to correlate high CPU usage to long running queries and things like that.

However, I would need query start/stop times to be able to do that. It doesn't look like the built in auditing has that ability. Is there any other way to achieve this?

Edit:
I am aware of the event time column in the audit log. That gets me half way there as it is the start time. So, I either need the query length or the end time to get the data I need.


texts: Can't Dump Database Because MS SQL Server Management Studio Won't Show Any Databases If 1 Is Permission Denied

texts:
I am logged into SQL Server Management Studio with an account that has access to multiple databases, but not DB_123. As shown in the picture below, clicking to expand the "Databases" folder in the Object Explorer results in an error message.

I'm trying to follow instructions here and here to right-click on my database and export it that way. However, I cannot right-click on it because it won't show up in the Object Explorer. Yet I do have permission to query the database.
Is there any way to get the database(s) to appear in the Object Explorer or to otherwise dump the database(s) without using the Object Explorer?


texts: Log for database tempdb is not available

texts:
I have been struggling with this issue from so many months now, I am saying its not a database issue and assigning this case to Storage and Operating system team and they are assigning it to me afterwards. This issue is happening repeatedly and there is no any defined pattern of occurrence.

I checked the same question asked here and I can say this is not an issue with database corruption as I am using Ola Hallengren's script for maintenance job and database integrity check(checkdb) is done on weekly basis for User and system database and no issues reported in that.

Accessed second link also for the similar question and can confirm that tempdb is in simple recovery state.

I added one additional file for data and log both so that if one becomes unavailable, other one will still be accessible however later I came to know that the access of tempdb is sequential, so it will go to second file only when first gets full:

Every time this issue occurs, i could see that we have another error in the windows application log as below:

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

Storage team has excluded these files from anti-virus scanning.

One thing to note here - I checked other system databases and their file locations and could see that for master and model, data and log file is in the same drive(E drive) whereas for tempdb and msdb, data is in E drive and log file is in G drive, not sure if this is relevant.

Is there any sequence of checking system database when a job triggers or any event gets fired? If its an issue with that drive then, msdb is also on the same drive.

This is a clustered server with database drives shared in two servers and server is used for sharepoint application.

Server Version: Windows Server 2012 Standard
SQL Server: Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64)
    Jan  5 2018 22:11:56
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

Only option that we are left with is to restart the service or fail-over - it just doesn't sound good to do so.

Any help in this regard is appreciated.


texts: IndexOptimize is successful, but sys.dm_db_index_physical_stats still shows huge fragmentation

texts:
I'm working on a project that requires a database shrink. Following the shrink, I checked sys.dm_db_index_physical_stats and can see a lot of my indexes are over 99% fragmented (avg_fragmentation_in_percent).

In response to this, I executed IndexOptimize by Ola Hallengren, with the following code:

EXECUTE [dbo].[IndexOptimize]
    @Databases = 'Dynamic.Migrate',
    @FragmentationLow = 'INDEX_REORGANIZE',
    @FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
    @FillFactor = '80',
    @UpdateStatistics = 'ALL',
    @StatisticsSample = '100',
    @LogToTable = 'Y'

This completed successfully, but when I check sys.dm_db_index_physical_stats again, the avg_fragmentation_in_percent values have not changed, and state my indexes are still fragmented by over 99%.

Am I missing something here?


texts:
I'm by no means a DB admin. Just a hobbyist actually, and more of a programmer than a database person. I have MAMP 3.05, PHP 5.5.10, Mac OSX 10.9.3. I have a big suite of PHP applications and a gigantic database (several tables with over 1M records) using InnoDB. I hear that TokuDB is a big performance improvement over InnoDB, so I'm trying to install it, and having some trouble.

After some install steps, the doc tells me to start the new mysql instance using a command called service. This command is not present on my machine. Looking around, it seems that most linux installations have this command, but mine doesn't, and I can't figure out where to get it. Plus this idea of starting a new instance from the command-line is bothering me: I sort of thought that I could install this and continue to use MAMP as I always have, letting it start the mysql server, rather than me having to start it manually.

What is the correct way to install TokuDB to work with MAMP?

Edit: In my terminal window, uname -m reports x86_64.


texts:
Does using the in operator affect the use of indexes? For example, is it possible for WHERE id IN (10) to cause the optimizer to ignore an index?

Specifically, I'm trying to understand this answer from StackOverflow. The question asks why an index is not being utilized (in MySQL), and the answer suggests that it could be due to the use of in with only one value. Eg WHERE id IN (10) might get the index ignored, while WHERE id=10 would be fine.

I've briefly looked around on Google, and skimmed through some MySQL docs, but I can't find any reference to in affecting the optimizer's decision to use an index. Neither with a single value, nor multiple values.

Assuming that the IN values are the same datatype as the column they are comparing, can they affect the index usage?

The question that I linked to was for MySQL, but I work in other DBs, so I'd be interested in knowing if this is a universal thing to note about indexes, or if it's a quirk with MySQL.


texts: how do I get extra fields with in a column with calculations

texts:
I have a table AR--X with columns as Region, Product type, pofit, Target, 1, 2, 3,....... till 31 ( days ) as columns.

In Product type Column, there are fields like  Electronic, Plastic, wood, other, xx.

Now I want:

Select region, product type, profit, 1,2... 31 from AR--X.

In the results I would like to see:

for Product type with extra fields with calculations

a Field with a name as 'extra' = Target - (Electronic+ Plastic+ wood +other+ xx )
a Field with a name as 'Total'= Sum(Electronic+ Plastic+ wood +other + xx )
a Field with a name as '% of total' = total/Target*100.

Is this possible?


texts:
I'm trying to select the most recent authors of messages in a room, using this (simplified) table:

             Table "public.message"
 Column  |  Type   | Nullable |               Default
---------+---------+----------+-------------------------------------
 id      | bigint  | not null | nextval('message_id_seq'::regclass)
 room    | integer | not null |
 author  | integer | not null |
 created | integer | not null |
Indexes:
    "message_pkey" PRIMARY KEY, btree (id)
    "message_author_created_room" btree (author, created, room)
    "message_room_author_created" btree (room, author, created)
    "message_room_created" btree (room, created)
    "message_room_id" btree (room, id)

The problem is a query like this is slow:

select message.author as id, max(message.created) as mc from message
where room=12 group by message.author order by mc desc limit 50;

And here's the explain(analyze, verbose, buffers):

miaou=> explain (analyze, verbose, buffers) select message.author as id, max(message.created) as mc from message
where room=12 group by message.author order by mc desc limit 50;
                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10627.14..10627.26 rows=50 width=8) (actual time=54.887..54.901 rows=50 loops=1)
   Output: author, (max(created))
   Buffers: shared hit=490
   ->  Sort  (cost=10627.14..10629.19 rows=820 width=8) (actual time=54.885..54.891 rows=50 loops=1)
         Output: author, (max(created))
         Sort Key: (max(message.created)) DESC
         Sort Method: top-N heapsort  Memory: 29kB
         Buffers: shared hit=490
         ->  Finalize GroupAggregate  (cost=1000.46..10599.90 rows=820 width=8) (actual time=14.019..54.788 rows=160 loops=1)
               Output: author, max(created)
               Group Key: message.author
               Buffers: shared hit=490
               ->  Gather Merge  (cost=1000.46..10583.50 rows=1640 width=8) (actual time=14.007..54.636 rows=248 loops=1)
                     Output: author, (PARTIAL max(created))
                     Workers Planned: 2
                     Workers Launched: 2
                     Buffers: shared hit=490
                     ->  Partial GroupAggregate  (cost=0.43..9394.18 rows=820 width=8) (actual time=3.439..34.733 rows=83 loops=3)
                           Output: author, PARTIAL max(created)
                           Group Key: message.author
                           Buffers: shared hit=2989
                           Worker 0: actual time=0.297..49.593 rows=116 loops=1
                             Buffers: shared hit=1550
                           Worker 1: actual time=6.624..40.612 rows=60 loops=1
                             Buffers: shared hit=949
                           ->  Parallel Index Only Scan using message_room_author_created on public.message  (cost=0.43..8904.09 rows=96377 width=8) (actual time=0.030..20.067 rows=73907 loops=3)
                                 Output: author, created
                                 Index Cond: (message.room = 12)
                                 Heap Fetches: 139
                                 Buffers: shared hit=2989
                                 Worker 0: actual time=0.035..28.355 rows=109834 loops=1
                                   Buffers: shared hit=1550
                                 Worker 1: actual time=0.030..23.723 rows=79112 loops=1
                                   Buffers: shared hit=949
 Planning time: 0.211 ms
 Execution time: 57.071 ms

I'd like to know how to make that faster, the real important goal being to get the N most recent authors. Is there a faster way to query that information?


texts:
I'm trying to create a stored procedure. Once I created it, it was successfully created. But it is not displaying in the list of stored procedures. I refreshed the list, but it is not appearing. I also tried to recreate it, but there was an error:

  Msg 2714, Level 16, State 3, Procedure spProducts_DeleteProduct, Line 6
  There is already an object named 'spProducts_DeleteProduct' in the database.

What is the reason behind not seeing that stored procedure in the list?


texts:
I am experiencing occasional connection errors from my webserver to one of my databases.

The code attempts to connect. If it fails, it sleeps for 1 second, then retries, then (if it still fails) sleeps for 2 seconds more and retries, then gives up if it didn't work

The error messages that come back are:

SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (4)
SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (113)
SQLSTATE[HY000] [2003] Can't connect to MySQL server on '10.0.0.210' (113)

4 = interrupted system call

113 = no route to host

I am trying to work out what sequence of events might cause this to happen so I can try and resolve the issue. My hunch is that it's a network issue, but the two machines are both VPSs in the same cloud, so I am not sure how this could be the case!


texts: SQL2542N - No match for a database image file was found based on the source database alias

texts:
I'm trying to restore a DB from a Linux system to a Windows. I'm working with DB2 9.7.7. I want to create a new DB with the restored DB.
The DB file has this name: DBBACKUP.0.db2inst1.DBPART000.20140614125950.001 and I've the file under /home/db2inst1 folder. So, logged as DB2 administrator (db2inst1), I'm trying with this command:

  db2inst1@machine:/data/home/db2inst1> db2 restore db DBBACKUP

or

  db2inst1@machine:/data/home/db2inst1> db2 restore db DBBACKUP taken at 20140614125950

and it returns me:

  SQL2542N  No match for a database image file was found based on the source database alias "DBBACKUP" and timestamp "" provided.

It can not find the database, but I don't know if the error is the name of the DB I put in the command, or the path is wrong.

Thanks.


texts: What the best Configurationfor mysql using 4GB

texts:
I'm trying to  improve my mysql performance. So I add more memory. But still slow in some part. Im most LEFT JOINs and UPDATE.

My database have 45GB of data.

My current mysql configuration is:

key_buffer_size = 256M
max_allowed_packet = 1024M
table_open_cache = 5000
sort_buffer_size = 4M
net_buffer_length = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M

innodb_buffer_pool_size = 4G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 100

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer = 256M
write_buffer = 256M

The question is. This is the best configuration ?

MY Serve is running: Linux/Open Suse

4GB of ram

4 cores.


texts: Indexed range comparison of array key's value in row inside JSONB

texts:
This is for tests with results. The order and if the test was done can be vary per row.

column jdata
{"name": "Somedata",
 "array": [ {"name":"test0","value":4.6},
            {"name":"test2", "value": 6.7},
            {"name":"test3", "value": 7.5},
            {"name":"test1","value":4.6}
           ],
"otherstuff": "stuff"
}

I want to get all occurrences of test1 where value is gt 4.5. test1 can be in any position in the array.

The best I can come up with is doing an indexed scan for @> {"name": "test1"} to get the IDs then on that to somehow construct a path getting (jdata->'array'->(gettest1indexsubqueryoffsorts)->'value')::float > 4.5

An index could maybe be constructed that summarizes all the tests into {"test1": 4.6, "test2": xx.xx} but again range checking cannot be done on that or the data structure can be changed. Pointers would be great.


texts: How to test if the query is taking a longer period of time as our production database is growing?

texts:
I would like to have a test (read: whatever can send a mail) in which whenever our query is taking more than 2 seconds to execute, it will send to me an email. If it matters we are using cakePhp with CircleCi and PhPUnit. We have also three databases, first is production, second one is development and the third one is test_database.

What is the best approach to this problem ? Is there a app which can test this ?


texts: returning a null value instead of no value

texts:
Why are the last 2 selects (cases 5 and 6) returning a null value? This may seem like a trivial question but it actually caused a bug in one of my joins where I expected rows to be filtered out by cross applying to some value and instead it returned the row with a null value.

DROP TABLE IF EXISTS #temp;
CREATE TABLE #temp (t VARCHAR(1));

Cases 1-4 return expected "no value" result.

-- CASE 1
SELECT *
FROM #temp;

-- CASE 2
SELECT (SELECT t)
FROM #temp;

-- CASE 3
SELECT (T)
FROM #temp;

-- CASE 4
SELECT CAST(value AS VARCHAR(MAX))
FROM STRING_SPLIT('t', ',') s
WHERE EXISTS (
        SELECT 1
        FROM #temp
        WHERE t = 't'
        );

Problem cases - returning "NULL".

-- CASE 5
SELECT (
        SELECT value
        FROM STRING_SPLIT('t', ',') s
        WHERE EXISTS (
                SELECT 1
                FROM #temp
                WHERE t = 't'
                )
        );

-- CASE 6
SELECT (
        SELECT T
        FROM #TEMP
        );


texts: I have an error before I connect to the user Oracle 12c

texts:
After that, I log in normally.

I tried it sqlplus as sysdba error is the same

Why this error occurs

I'm a beginner

[oracle@localhost ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 21 13:08:51 2018

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name: system
Enter password:
Last Successful login time: Wed Nov 21 2018 13:07:42 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


texts: Is the innodb_lazy_drop_table problem solved?

texts:
I'm researching database configuration for MySQL/MariaDB. I've been reading the configuration chapter in "High Performance MySQL". In there, it suggests that innodb_lazy_drop_table can be used to avoid server stalls when dropping tables if innodb_file_per_table is set, to stop an immediate scan of the buffer pool to invalidate pages that refer to the dropped table.

However, I also found that innodb_lazy_drop_table has now been removed from XtraDB.

Does that mean the original issue is fixed, and I no longer have to worry about performance issues when dropping InnoDB/XtraDB tables? Or perhaps the lazy invalidation of pages has been enabled by default and the configuration option is all that's been removed?


texts: How does SQL Server handle the data for a query where there is not enough room in the buffer cache?

texts:
My question is how does SQL Server handle a query that needs to pull more volume of data into the buffer cache than there is space available? This query would contain multiple joins, so the result set does not exist in this format already on disk, and it would need to compile the results. But even after the compilation, it still requires more space than is available in the buffer cache.

I will give an example. Suppose you have a SQL Server instance that has 6GB total of Buffer Cache space available. I run a query with multiple joins that reads 7GB of data, how is SQL Server able to respond to this request? Does it temporarily store the data in tempdb? Does it fail? Does it do something that just reads data from disk and compiles segments at a time?

In addition, what happens if I am trying to return 7GB of total data, does that change how SQL Server handles it?

I am already aware of several ways to address this, I am just curious how SQL Server handles this request internally when it runs as stated.

Also, I am sure this information exists somewhere, but I have been unsuccessful in finding it.


texts: How to know which website is causing the sql server to stop responding?

texts:
I have multiple databases on the same sql server 2008. Some times the server stops working and I should restart it to make it work again. Is there an easy way to know which application or website caused this stop?
Or can I know which database takes the most of the server power?


texts: MySQL row length is almost the triple of what I expect

texts:
I shamelessly took title and content from this question and adapted it to my similar yet different problem:

This is the table I have:

CREATE TABLE `test` (
    `field1` MEDIUMINT(8) UNSIGNED NOT NULL,
    `field2` INT(10) UNSIGNED NOT NULL,
    `field3` SMALLINT(5) UNSIGNED NOT NULL,
    `field4` SMALLINT(5) UNSIGNED NOT NULL,
    `field5` FLOAT UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`field1`, `field2`),
    INDEX `field2` (`field2`, `field1`)
)
COLLATE='latin1_swedish_ci'
ENGINE=ndbcluster
ROW_FORMAT=DEFAULT

I expect the row length to be 15 bytes:

3 bytes for field1 mediumint(8)
4 bytes for field2 int(10)
2 bytes for field3 smallint(5)
2 bytes for field4 smallint(5)
4 bytes for field5 float

But show table status; returns

Name: test
Engine: ndbcluster
Row_format: Fixed
Rows: 143120123
Avg_row_length: 44 <--------------  (I assume this is in bytes?)
Data_length: 7659913216

As you can see, I have quite a few rows, so row length really does matter.

Why does it take so much space?

And why is Data_length so much bigger than expected?

Data_length - (Rows * Avg_row_length) = 7659913216 - 6297285412 = 1362627804 = 1.26 GB

And what are those digits in brackets after the type names for? (e.g. mediumint(8))
I guess it's the char length of the field's string representations and I have the strange feeling that MySQL's ndbcluster engine stores them exactly like that!
Which seems amazingly stupid to me...


texts: Using Solr/Lucene for searching non-text tables?

texts:
I am creating a web application to retrieve subsets of one large (4m rows) table. The 4m rows only change once a year. The table has 200+ columns of types boolean and numeric. It has no text columns.

Users will query subsets of this table for download.

I am rather familiar with the PostgreSQL 9.1 database, and my plan is:

The webapp will help the user to create queries like "WHERE a=3 AND b=true AND c>300"
The DB will have proper indexes for the most-used columns.

NOW.. I read here:
https://stackoverflow.com/questions/10053050/why-is-solr-so-much-faster-than-postgres:

  I recently switched from Postgres to Solr and saw a ~50x speed up in our queries. The queries we run involve multiple ranges, and our data is vehicle listings. For example: "Find all vehicles with mileage < 50,000, $5,000 < price < $10,000, make=Mazda..."

So now I wonder: Will/can Solr, Lucene, ElasticSearch, Amazon Cloud Search searches be faster than PostgreSQL even if no full-text search is involved?


texts: MSSQL - Uniquely identify an application connecting SQL Server

texts:
we are implementing a database firewall on Oracle based on a unique value associated to a given application. With this value, we can reliably identify the application, even if one changes the name of the application before connecting to the RDBMS.

We plan on doing the same thing with SQL Server.

I've already seen that there is a connection_id column in sys.dm_exec_connections, but it identifies a connection, not an application.

In SQL Server, iIs there something, that could reliably identify an application ?


texts: Backing up a single node Percona XtraDB Cluster

texts:
I have a single node percona XtraDB Cluster.

Version:
Server version: 5.6.15-56 Percona XtraDB Cluster (GPL), Release 25.5, Revision 759, wsrep_25.5.r4061

All tables in the DB are using InnoDB engine and the database is partitioned.

The server is a RHEL 6 server and I want to upgrade it to RHEL 7. What I am planning to do is, stop mysql on the old server, shut it down, disconnect the disk (old and new servers are VMs), connect it to the new RHEL 7 server, turn the new server on, mount the disk, change mysql configuration so that it will point to the new filesystem. Sounds good on paper but will this actually work?


texts: 3rd Party Platform Unique Identifier for Joins vs Internal Identifiers

texts: What is the general practice when it comes to choosing a unique identifier? In my case I have the option to use the eBay unique listing ID (Item ID) or my own internal ID for use in table joins and general identification. The argument could be made that some 3rd party identifiers are non-numeric and I would understand this proposition but in my specific case the identifier is numeric. Thanks for any advice or personal experiences in advance!


texts:
When dealing with very large collections i came across timeout issues when querying and inserting.
Even though the collections were indexed and optimized to combine small documents together.

The rate of insertion for a given collection is 172,800 documents per day.

I solved this by partitioning the DB into months.

  ASDF  -> ASDF_10_2018 , ASDF_11_2018 ...  ASDF_05_2020

by doing so the collection size under each db was decreased and i did not encounter any query or insertion issues.

The things is that there are now significantly more collections.
Orignally 3000 collections are now 3000 collections each month.

MongoDb now as an issues with the number of open files it has to hold in order to maintain my partitioned DB's and collections.

Questions :

1) Is there a better way then the portioning described above of solving the problems that occurred due to the large collections.

2) Is there a way to tell mongo to not open so many files.
  Most of the Db's are old and not commonly queried and can stay dormant on my part, is there a way to tell Mongo not to open the files related to them ?


texts: 20-40 ms performance difference during peak traffic on amazon RDS

texts:
I am noticing during peak traffic the average mysql response time is 20-40 ms longer consistently as reported by new relic. I have tried turning on query cache and making the innodb buffer pool larger with no luck. The total response time is still around 125ms; but am concerned that I am missing a setting that could help.

I don't recall having this issue with Rackspace with the same set of data and traffic. Could it just come down to the fact there is just a bit more load on the database and is acceptable?

I have an 8GB Amazon RDS instance with a 4gb buffer pool and I turned on the query cache with a 75% hit rate which didn't move the needle.

Could the issue be related to being in a shared environment at all? My CPU usage is under 20% (usually a lot less).

here are my settings:
https://gist.github.com/blasto333/9e82b6261681303f7369c3a2652e03f2

I am just looking for ideas to try. My two ideas were making the buffer pool larger was 3gb now 4gb; and turning the query cache on (As I had with rackspace). So far those didn't move the needle.


texts:
I'm working on upgrading some SSIS packages from SQL Server 2008 R2 to 2012 and am getting somewhat confused by the SSDT-BI tools.

I have downloaded "Microsoft SQL Server Data Tools - Business Intelligence for Visual Studio 2013" from here, which according to this supports "SQL Server 2014, SQL Server 2012, SQL Server 2008 and 2008 R2".

When upgrading a package I don't see an option to upgrade "up to 2012" only. In other words, how can I stop the packages being upgraded too far? Or are 2012/14 pretty much cross compatible?

Also, are there other similar implications for SSRS, SSAS solutions?

If I need a different version to ONLY upgrade to 2012, which would I need?


texts:
I know that typically you would have an autonumber field - but in this instance there is not, and it honestly does not matter which row get's updated as long as it is only 1.

I have sample DDL like such one table that contains requested qty, and one table that contains avaliable qty.  I want to update @Test1.amtonhand with the value from @Test2.amtonhand and join on @Test1.nrs=@Test2.nrs but only update the 1 record in @Test1

How is this achieved in SQL Server 2008

    Declare @Test1 Table
(
    nrs varchar(100)
    ,amtrqst varchar(100)
    ,amtonhand varchar(100)
)

Declare @Test2 Table
(
    nrs varchar(100)
    ,amtonhand varchar(100)
)

Insert Into @Test1 (nrs, amtrqst) Values
('abc', '10'), ('abc', '12'), ('abc', '13'), ('def', '5'), ('def', '12')

Insert Into @Test2 (nrs, amtonhand) Values
('abc', '10'), ('def', '5')

EDIT
My desired results from the update would be

@Test1 --
abc, 10, 10
abc, 12
abc, 13
def, 5, 5
def, 12


texts: pg_dump and ERROR: missing chunk number 0 for toast value

texts:
I'm using PostgreSQL 8.4.15. While running pg_dump to backup a database, I got the following error:

pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  missing chunk number 0 for toast value 123456789 in pg_toast_987654321
pg_dump: The command was: COPY public.my_table (id, .... all the columns ...)

When searching for this error message, I found a couple of references (here and here) that suggested to reindex the table. (In these discussions, there was a reference to querying the pg_class table to find the right pg_toast_XXXXXX value, but it seemed that it was because it wasn't displayed in their error messages. I skipped this part because I had a value displayed in the error message. I guess it might be a convenience due to a later version of PostgreSQL.)

I ran the following:

REINDEX table pg_toast.pg_toast_987654321;
VACUUM ANALYZE my_table;

I'm now able to use pg_dump without errors.

What's pg_toast and what did these commands actually do? Are these merely about simple cleanup or could they have got rid of some rows in that table? What could have caused the problem in the first place?

There are about 300000 rows in this table, but I would expect there to be only about 250 new rows since the previous successful backup (this table is only used for INSERT/SELECT, no UPDATEs).


texts: InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6

texts:
I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup

I found a possible solution here, but it does not seem official.
http://forums.mysql.com/read.php?22,578559,579891#msg-579891

2013-12-06 21:08:00 7f87b1d26700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.
2013-12-06 21:08:00 7f87b1d26700 InnoDB: Recalculation of persistent statistics requested for table "drupal"."sessions" but the required persistent statistics storage is not present or is corrupted. Using transient stats instead.
2013-12-06 21:08:07 7f903c09c700 InnoDB: Error: Table "mysql"."innodb_table_stats" not found.

Any official solutions or 100% fixes?


texts: How do you set the working directory to a mapped network drive for R language in SSMS?

texts:
So I'm messing around with the new "Integration with R" feature in SQL 2016.  However, having trouble setting the working directory to a mapped network drive (Z:) within SSMS.

Here's my code:

EXEC sp_execute_external_script
@language = N'R',
@script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
@input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

Getting the error message:

  "Error in setwd("Z:/"): cannot change working directory
  Calls: source -> withVisible -> eval -> eval -> setwd"

I've tried changing the drive letter to local, physical drives on the server and it works fine, just doesn't work for the mapped network drive.


texts: How can I delete a file from the primary filegroup?

texts:
In dealing with fragmented disk space, I added a second data file to one of my databases.  Most of the data is now in the the second file, and I'd like to drop the first to simplify things.  However, it's the primary data file:

Msg 5020, Level 16, State 1, Line 3
The primary data or log file cannot be removed from a database.

So how do I mark the second file as primary, so I can delete the first file?

In Googling this problem, I see lots of people trying to remove filegroups, but I'm just trying to drop one file from a group.

Same situation, but without the problem regarding "primary data file": https://stackoverflow.com/questions/2183742/remove-merge-sql-servers-database-secondary-data-file.

Similar, but for log files which have different issues: https://stackoverflow.com/questions/10839374/sql-server-database-has-2-log-files-and-i-want-to-remove-one-how.  I am interested in removing a data file.

A similar situation, but there the problem is in emptying the file: http://www.sqlservercentral.com/Forums/Topic464326-5-1.aspx.


texts: How to fetch related rows on multiple condition with ranking

texts:
I have following table for storing item information in PostgreSQL.

id  |  name      | category | seller | m_age | status | type_id | region |
--------------------------------------------------------------------------
10  | TBox       |  Gun     | 14     |  4    | Q4     | 12      |   EU   |
11  | Globe      |  Gun     | 36     |  12   | Q1     | 17      |   EU   |
12  | Nike       |  CFK     | 12     |  5    | Q2     | 12      |   AS   |
32  | Raven      |  Knife   | 21     |  14   | Q1     | 12      |   EU   |
46  | TBox       |  Gun     | 14     |  10   | Q4     | 12      |   AS   |
46  | Blaster    |  Gun     | 14     |  10   | Q4     | 12      |   EU   |
78  | PaintGun   |  Gun     | 12     |  10   | Q2     | 54      |   US   |
98  | TBox       |  OD      | 21     |  4    | Q3     | 54      |   AS   |

I want to find related items on basic of certain criteria.

Category > title > Tech Info Matched(m_age, status, type_id, region) > Seller

So for id 10 - TBox item rank should be following(name and category column value and Info matched and reason cololms are for visual reference only)

id  | name  | category | INFO MATCHED | RANK |             REASON              |
--------------------------------------------------------------------------------
10  | TBox  |  Gun     |      -       |   -  |                -                |
--------------------------------------------------------------------------------
11  | Diff  |  Same    |      1       |   3  | same cat diff name 1 info match |
12  | Diff  |  Diff    |      1       |   6  | diff cat diff name 1 info match |
32  | Diff  |  Diff    |      2       |   7  | diff cat diff name 2 info match |
46  | Same  |  Same    |      3       |   1  | same cat and name               |
46  | Diff  |  Same    |      4       |   2  | same cat diff name 4 info match |
78  | Diff  |  Same    |      0       |   4  | same cat diff name 0 info match |
98  | Same  |  Diff    |      1       |   5  | diff cat same name              |

I am still a novice to SQL. I have tried with normal CASE and function. but haven't got proper results. Rank will not work with this many condition. Is there any way to pull this results anyhow with PostgreSQL?


texts: Can we set LOCK_TIMEOUT instance- or database-wide?

texts:
It is possible to change the SQL Server's default (which is -1 or indefinite period of time) LOCK_TIMEOUT value session-wide.

Is it possible to set LOCK_TIMEOUT default value server-, instance- and/or database-wide? If not, why not?


texts: SQL select statements not matching on certain rows

texts:
I am running a Postgresql database with docker-compose, using the image postgres:10-alpine. For some reason, the database is unable to select certain rows based on string comparison. For example, this query works as expected:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='user1';
  id   | preferredUsername
-------+-------------------
 38793 | user1
(1 row)

However, the same query is not working for another user:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername"='user2';
 id | preferredUsername
----+-------------------
(0 rows)

The user definitely exists, and the query works if I use ILIKE instead of =:

peertube=# SELECT id, "preferredUsername" FROM actor WHERE "preferredUsername" ILIKE 'user2';
  id   | preferredUsername
-------+-------------------
 41576 | user2
(1 row)

What could be the reason for this wrong behaviour? I suspected it might have something to do with the encoding, but SHOW SERVER_ENCODING and SHOW CLIENT_ENCODING both show UTF8. I also tried to export the data and import it into a fresh database. That fixed the problem for a few days, but it came back after that.

I'm happy about any possible solutions or debugging ideas.

Edit: Some more queries, it works with trim() in the WHERE clause:

peertube=# SELECT "preferredUsername", "preferredUsername" FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername | btrim  |               md5
-------------------+--------+----------------------------------
 mailab            | mailab | 3d83ba6a9e5391c0c4d0253fbb2b01aa

However, this still seems weird because if there is whitespace, the hash of the field should be different. In fact, the md5 is the same:

peertube=# SELECT "preferredUsername", md5("preferredUsername"), md5('mailab') FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername |               md5                |               md5
-------------------+----------------------------------+----------------------------------
 mailab            | 3d83ba6a9e5391c0c4d0253fbb2b01aa | 3d83ba6a9e5391c0c4d0253fbb2b01aa

Edit 2:  Length is the same with or without trim, so that seems to rule out the whitespace theory:

peertube=# SELECT "preferredUsername", length("preferredUsername"), length(trim("preferredUsername")) FROM actor WHERE trim("preferredUsername")='mailab';
 preferredUsername | length | length
-------------------+--------+--------
 mailab            |      6 |      6

Edit 3: Details of the affected table: https://gitlab.com/snippets/1840320

Edit 4: Someone suggested the get_byte() function to check that there are no unexpected utf8 characters. And in fact, every single character corresponds to an ascii value in the range 97-122.


texts: I am working on creating an AG on SQL Server 2017 Standard and Windows Server 2016.  I have created the failover cluster using a file share witness and everything checks out OK with the cluster.  The only issue or warnings I get are related to only using 1 NIC for each server (These are VM's with multiple NIC's).  I can connect to the SQL instance using the windows failover cluster DNS entry.  When I down one server or manually fail over the cluster, the cluster DNS entry does not update to point to the new "failed over IP"  These 2 different IP's are on 2 separate subnets.  After some time (20 minutes) the DNS entry will update to the new (failed over) IP.  I have tried to adjust the TTL setting in the cluster name DNS entry however, it changes itself back to 20 minutes.  What am I missing here?


texts: Is this an error in SQL Server's documentation for ROLLBACK?

texts:
This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows:
ROLLBACK { TRAN | TRANSACTION }
     [ transaction_name | @tran_name_variable
     | savepoint_name | @savepoint_variable ]
[ ; ]

Apparently the stuff inside square brackets are optional and it seems you should choose one from TRAN or TRASACTION. But in fact you can completely omit both and ROLLBACK is a perfectly valid statement. Is this an error or intentionally?


texts: How to take backup of PostgreSQL 9.4 database to remote server using pg_basebackup?

texts: We want to take the logical and base backup on different server machine in the network. Currently both backups are running on same server machine causing space issues. We have the scripts in place what are the necessary changes we need to make in the scripts to implement the same. Do we need to run the script on DB server machine and direct the backup on other network machine or do we need to run the script of another network machine (Network machine have the same version of PostgreSQL installed on it). Please guide me through the standard procedure under best practices.


texts: Why does my execution plan show a Clustered Index Scan is occurring when using an index hint that specifically uses a Nonclustered Index?

texts:
The Backstory:
I have a table with the following structure:
CREATE TABLE WideTable1
(
    BoringColumn1,
    BoringColumn2,
    CellPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    PagerPhoneNumberColumn Phone(VARCHAR(20)), -- User-defined types, see below
    IsActive YN(CHAR(1)), -- User-defined types, see below
    BoringColumn3,
    ...
    BoringColumn350
)

The original query consuming this table was doing a few SQL functions on the CellPhoneNumberColumn and PagerPhoneNumber and then later using the output of those functions as predicates in another query. NULLIF(LTRIM(ISNULL(CellPhoneNumberColumn, PagerPhoneNumberColumn))) was one example of this.
The columns are also all user-defined types, in this case CellPhoneNumberColumn and PagerPhoneNumberColumn are defined as UDT Phone(VARCHAR(20)), so their underlying data type is VARCHAR(20). And IsActive is defined as YN(CHAR(1)) so it's actually a CHAR(1).
Additionally to complicate things further, the collation of this table / database is Latin1_General_BIN.
Long story short, the original consuming query was running into Cardinality Estimate issues.
In an attempt to alleviate the issue, I created an index view on the above columns and SQL functions being applied to them with the following definition (*note I didn't  create this original logical, just trying to fix the performance of it):
CREATE VIEW PhoneNumbersNormalized WITH SCHEMABINDING AS

SELECT
    NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20)))) AS Cell,
    SUM(CASE WHEN CAST(IsActive AS CHAR(1)) = 'Y'THEN 1 ELSE 0 END) AS IsActive
FROM dbo.WideTable1
GROUP BY NULLIF(LTRIM(ISNULL(CAST(CellPhoneNumberColumn AS VARCHAR(20)), CAST(PagerPhoneNumberColumn AS VARCHAR(20))))

I also created the following indexes on the indexed view PhoneNumbersNormalized:
CREATE UNIQUE CLUSTERED INDEX IXV_PhoneNumbersNormalized_Cell ON dbo.PhoneNumbersNormalized(Cell)
CREATE NONCLUSTERED INDEX IXV_NC_PhoneNumbersNormalized_Cell_IsActive ON  dbo.PhoneNumbersNormalized(Cell, IsActive)

The Problem:
When I select from the indexed view PhoneNumbersNormalized with the Actual Execution Plan included, I noticed the execution plan specifcally mentions the original underlying table WideTable1 as where the data is coming from.
Furthermore if I select from indexed view PhoneNumbersNormalized with an indexed hint on the nonclustered index I created above IXV_NC_PhoneNumbersNormalized_PhoneNumber_IsActive the execution plan shows no mention of this nonclustered index being used, instead it says a clustered index scan is what it's doing instead (note I obfuscated the original table name, it's not actually called WideTable1 on my server):

Pasted Execution Plan: https://www.brentozar.com/pastetheplan/?id=HJytxh2UP
Why is the execution plan always showing the original underlying table instead of the indexed view, and always using the clustered index on the underlying table even when my select query on the indexed view uses an index hint to force the use of the non-clustered index?


texts: How do I include zeros in a count(column)?

texts:
I am attempting to build a report for a client. I need a display of all the values in Tab1, Col1 (a validation table), a count of each time that occurred, and a percentage of the total. Here's what I'm looking for:

Number of records       Type        Percentage of total
2                       A           2
3                       B           3
0                       C           0

I have the basic query worked out, I just don't know how to include the zero count! Here's what I have so far:

select count(b.Col1) "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(a.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a,
Tab2 b
where Tab2.Col1 = Tab1.Col1(+)
group by a.Col1;

Thanks in advance.

EDIT:
Attempting this:

select count(b.Col1) "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab2 b
left join Tab1 a on tab2.col1 = tab1.col1 -- or (from tab2 left join tab1) if that is what's intended
group by a.Col1;

Gave me the same results as my query.

This:

select COALESCE(count(b.Col1), 0) AS "Number of Records",
a.col2 "Type",
to_char(round(ratio_to_report(count(b.Col1)) over()*100)) || '%' as "Percantage of Total"
from Tab1 a
left join Tab2 b on tab1.col1 = tab2.col1
group by a.Col1;

Also gives me the same results.

EDIT 2:

Table A columns: ID, Code, Description

Table B Columns: ID, RecordNumber, TableA_ID

Table B is a "master" and Table A is one of the many "details". Basically I need a count of TableA instances in TableB.. including the zeros.

Query that gave me my desired results:

select coalesce(count(b.TabA_id),0) as "num records",
a.desc "type",
to_char(round(ratio_to_report(count(b.TabA_id)) over()*100)) || '%' as "Percentage of Total"
from TabB b
right join TabA a on b.TabA_id = a.id
group by  b.TabA_id, a.id
order by a.desc;


texts:
I have a customer who currently runs Oracle on Windows (not my choice, I just have to deal with their current setup)
The basic layout of the server
C:\ - operating system and oracle application
D:\ - oracle datafiles
E:\ - oracle log files
The server recently died. The customer managed to recover the drives to new ones. But while D and E seem fine, C drive is constantly throwing "file system corrupt" errors, which is massively slowing down system. I believe they just took a ghost image of the drive, which means they just transferred the corrupted data along with it
I've suggested formatting C and reinstalling Windows, but their concern is about reinstalling Oracle. Mostly because they took over the company from the original owners and don't know the license details
So they've now asked, is it possible to "transfer" the currently installed Oracle application to another drive temporarily, format C and reinstall Windows and then transfer the Oracle app back to C again?


texts: QBE Query By Example. How does it handle OR conditions?

texts:
I've been looking at implementing QBE into an application but need more information on how more complex queries work.

How does QBE handle "OR" conditions?  For example when searching a telephone book how would QBE work for a query needing the telephone numbers of "John" or "James"?


texts: Database Size is Different after pg_restore (389 GB vs 229 GB)

texts:
I created a backup of my database in a PostgreSQL 11.6 (with TimescaleDB 1.60 extension) using pg_dump:

PGPASSWORD=mypassword pg_dump -h 127.22.0.4 -p 5432 -U postgres -Z0 -Fc database_development

and restored it to a new server running the same versions of PostgreSQL 11.6 (with TimescaleDB 1.60 extension) using pg_restore. For the restore, executed the following commands in psql as user postgres:

CREATE DATABASE database_development;
\c database_development
CREATE EXTENSION timescaledb;
SELECT timescaledb_pre_restore();

\! time pg_restore -Fc -d database_development /var/lib/postgresql/backups/database_development_2020-02-29

SELECT timescaledb_post_restore();

The database size of the original database was 389 GB but the restored database was 229 GB. These sizes were obtained by running

select pg_size_pretty(pg_database_size('database_development'))

Some differences:

The old database is stored on a ext4 partition, while the new database is stored on a ZFS filesystem with compression disabled. Both database instances are running inside a Docker container with an Ubuntu 18.04 host.

Question: How can we explain the differences in the database sizes? There were no errors encountered during both the pg_dump and pg_restore.


texts:
I am new here, so please be nice...

I have a question regarding TimesTen & Oracle. I have attempted an experiment with both pieces of software, and realize that the query speed on a table is slower on TimesTen than on Oracle.

The experiment setup was as follows:

500,000 rows of data
count(*) query on a column which is NOT a candidate key

Table definition:

CREATE TABLE menu(DISH_ID NUMBER NOT NULL PRIMARY KEY, price NUMBER(7))

Query:
SELECT COUNT(*) FROM menu WHERE price <= 50. The price value increases uniformly until the largest value in the DB. Time taken for the query to be executed 1000 times was recorded.

The query speed for different ranges is consistently slower on TimesTen, as compared to Oracle. No indexes were built on the search key. Is there a particular reason for this?

Other notes: I ran the same experiment, but built an index on the search key on both TimesTen & on 10g, and the difference is stark on TimesTen's favour.


texts:
SQL Server 2008 R2 Standard Edition (64-bit) on Windows 2008R2 Enterprise

Server has over 300 GB of memory but total memory use in control panel never goes over 86 GB
Have SQL configured to use the maximum amount of memory
Even under heavy use - CPU over 80% for minutes
Dedicated SQL Server
Several large databases
One heavily used table has an index size alone of over 10 GB

Set the service account to hold locks in memory

Is that normal?

What can I test?

Can I get SQL Server to use more memory?


texts: Can not start SQL Server, model db in RESTORING state

texts:
Quick run down:

First problem started when sysadmin received error that model is recovering.

Sometime later vendor tried to do some upgrades to the database. When they restarted SQL Server it failed to start.

Looking at error log. I found the following error

  Error The database 'model' is marked RESTORING and is in a state that
  does not allow recovery to be run. Error: 927, Severity: 14, State: 2.
  Database 'model' cannot be opened. It is in the middle of a restore.
  Could not create tempdb. You may not have enough disk space available.
  Free additional disk space by deleting other files on the tempdb drive
  and then restart SQL Server. Check for additional errors in the event
  log that may indicate why the tempdb files could not be initialized.

after searching around on the web found this article on MSDN

using steps from article started sql server using Sqlservr.exe -sInstanceName -T3608 -c -f -T3609 when i tried to run Use tempdb got error message. Following advice from article I copied tempdb.mdf and templog.ldf from another server.

Now I get the following error:

  Starting up database 'tempdb'. Error: 3456, Severity: 21, State: 1.
  Could not redo log record (19:45:366), for transaction ID (0:0), on
  page (1:16), database 'tempdb' (database ID 2). Page: LSN = (18:72:8),
  type = 1. Log: OpCode = 18, context 2, PrevPageLSN: (19:45:287).
  Restore from a backup of the database, or repair the database.


texts: Changes between Mysql version 5.1 TO 5.7

texts:
I have recently migrated Mysql 5.1 to Mysql 5.7. DB Migration was successful but when the application started firing few selected queries, Mysql started taking hours to execute them for which previously execution times wherein 4-5 seconds. That's  why wanted to know changes between this two versions.
 One of the query creating issue:

select count(*) COUNT_PRODUCTS
from (select d.ID,
             d.EAN,
             d.NAME,
             d.SAP_CATEGORY_ID,
             d.SAP_VENDOR_ID,
             d.RELEASE_DATE,
             d.REMOVED_DATE,
             d.IS_DRAFT,
             d.DATA_STATE,
             d.gc,
             ATTRIBUTE_CODE AS ATTRIB_NAME,
             ATTRIBUTE_VALUE AS APP_STATUS
      from (select b.ID,
                   b.EAN,
                   b.NAME,
                   b.SAP_CATEGORY_ID,
                   b.SAP_VENDOR_ID,
                   b.RELEASE_DATE,
                   b.REMOVED_DATE,
                   b.IS_DRAFT,
                   b.DATA_STATE,
                   group_concat(pl.LIFECYCLE_VALUE) gc
            from  (select a.ID,
                          a.EAN,
                          a.NAME,
                          a.SAP_CATEGORY_ID,
                          a.SAP_VENDOR_ID,
                          a.RELEASE_DATE,
                          a.REMOVED_DATE,
                          a.IS_DRAFT,
                          a.DATA_STATE
                   from (select p.ID,
                                p.EAN,
                                p.NAME,
                                p.SAP_CATEGORY_ID,
                                p.SAP_VENDOR_ID,
                                p.RELEASE_DATE,
                                p.REMOVED_DATE,
                                p.IS_DRAFT,
                                p.DATA_STATE
                          from PRODUCTS as p
                          order by p.EAN, p.IS_DRAFT) as a
                   group by a.EAN ) as b
            left join PRODUCT_LIFECYCLE_STATES pl
               on pl.PRODUCT_ID = b.ID  group by b.ID ) as d
       left join PRODUCT_ATTRIBUTE_VALUES AS PRODAV
          ON ID=PRODAV.PRODUCT_ID
         AND PRODAV.ATTRIBUTE_VALUE_ID IN (select ID
                                           from ATTRIBUTE_VALUES
                                           where ATTRIBUTE_CODE='APPROVED_ATTRIBUTES')
       left join ATTRIBUTE_VALUES AS ATVALS
          ON PRODAV.ATTRIBUTE_VALUE_ID = ATVALS.ID
         AND ATVALS.ATTRIBUTE_CODE ='APPROVED_ATTRIBUTES'
       ORDER BY d.SAP_CATEGORY_ID, d.NAME ) f

I did one change in my.cnf file to  suppress GROUP BY error by changing FULL_GROUP_BY.
This is my first quesion , so please let me know if any further information or rectification is required.

I am not able to reply to comments. Even after removing ORDER BY clause it is taking same time of hours. Actually, this query is also used without the COUNT in some places. If I keep FULL_GROUP_BY it is ending up with an error.

After running EXPLAIN on this query I am getting the different result in the old and new server.

New Server

Old Server


texts:
My web application is built using PHP Laravel framework, and I accidentally run php artisan migrate:fresh which is a command that drops all the tables and recreates them again.

I have done some research and it seems possible to recover the old table structure along with its data but I think my case is different than what I saw on other posts: I have the .frm and .ibd files, but I am not sure how to use them and if these are still useful.

I just want to know if I still can recover all the lost data.


texts:
So I just recently got to enable CDC on an always on AG. While many sources claimed enabling CDC on alwaysON AG is as easy as it is on any standalone server, turns out it really isn't. As MS says here:

  You should create the [CDC capture & cleanup] jobs at all of the possible failover targets
  before failover, and mark them as disabled until the availability
  replica at a host becomes the new primary replica. The CDC jobs
  running at the old primary database should be also disabled when the
  local database becomes a secondary database. To disable and enable
  jobs, use the @enabled option of sp_update_job (Transact-SQL). For
  more information about creating CDC jobs, see sys.sp_cdc_add_job
  (Transact-SQL).

Turns out after we enabled CDC on the primary replica, absolutely nothing happened on the secondary. So as per MS's advice, I did a manual failover, and created the jobs there, then did another failover back to the original primary.

And apparently as hinted at in the quote above, as fully compatible as CDC is supposed to be with alwaysOn AG's, it is entirely unable to determine whether it's currently running on the primary or secondary replica.

So, with the background out of the way, let's go to my question: How do you guarantee that the CDC jobs are always enabled on the current primary replica? And always disabled (so as not to produce errors) on the secondaries?

Since obviously a big point in alwaysOn AG's is that you don't need to manually be there the second the primary fails for whatever reason, because a secondary will pick up the slack and keep the system online, I assume it's then expected to just create a job on each of the replicas, constantly polling their status and changing the states of the CDC jobs as it does?

If this is correct, do you have any specific advice on how to go about this, specific scripts or guides etc? Because for what should obviously be a fairly common dilemma around the globe, it seems odd that I'd have to manually create a new TSQL job on each and every replica just to carry out what is essentially basic bare essentials functionality of CDC with alwaysOn AG.

This seems unlikely, which is why I'm asking. Or did I misunderstand something? Thanks!


texts:
I have a 'range' of integers (in this case destination ports) that occur:

For example: 33848-33254.

I want to select 'all' of these without having to do one at a time. is there a range operator? Or should I just use 'between'?


texts:
I have XML data which includes strings as byte array, which I'd like to convert to VARCHAR in SQL Server 2012. Example snippet with text="2Hallo" and the code I tried:

DECLARE @testxml XML = N'
<ProgramWzgSignStates>
    <WzgSignStates>
        <Element>
            <id>AQ_A07_1_280_F3~ZA</id>
            <text>
                <Element>50</Element>
                <Element>72</Element>
                <Element>97</Element>
                <Element>108</Element>
                <Element>108</Element>
                <Element>111</Element>
            </text>
        </Element>
    </WzgSignStates>
</ProgramWzgSignStates>'

SELECT
 Tab.Col.value('id[1]', 'varchar(50)') AS Id,
 Tab.Col.value('text[1]', 'nvarchar(MAX)') AS TextAsNVarChar
,Tab.Col.value('text[1]', 'varbinary(MAX)') AS TextAsVarBinary
,Convert(NVARCHAR(255), Tab.Col.value('text[1]', 'varbinary(MAX)')) AS TextUsingConvert
FROM @testxml.nodes('/ProgramWzgSignStates/WzgSignStates/Element') AS Tab(Col);

Which results in the following output:

Id                 TextAsNVarChar  TextAsVarBinary TextUsingConvert
AQ_A07_1_280_F3~ZA 507297108108111 NULL            NULL

Converting to NVARCHAR results in the ascii codes to be concatenated, while using VARBINARY returns NULL.

How can I retrieve the string 2Hallo as VARCHAR/NVARCHAR?