texts: Maximum number of TEXT/BLOB columns per table/performance of table with many of them?

texts:
I've created a system that generically imports CSVs into a database table with metadata columns for keeping track of when a CSV record has been changed, and it allows filtering on CSV columns instead of having to read the entire file each time. Right now, the structure of the table is essentially:

id, csv_id, hash, date_modified, column_0, column_1, ..., column_63, column_64

The columns are all VARCHAR(255) because I wanted the maximum number of columns with the maximum amount of size. My database is UTF-8, so each of the column sizes has to be multiplied by three and that value has to be below 65,535. This is the size of my current schema:

255 * 65 * 3 = 49,725

255 (*3) bytes is too small because URL columns might be stored in the database that can be up to 512 bytes or more. My question is if I made the current schema of:

column_0 (TEXT), column_1 (TEXT), ..., column_63 (TEXT), column_64 (TEXT)

Is there a maximum number of TEXT columns that can be on a database table? I'm using MySQL 5.6 right now but I'll be migrating the database to Amazon Aurora. My gut says the performance of this would be horrendous, and I do need to filter against the content of the columns.

If the performance hit isn't too large for a smaller subset of columns, I'm considering a hybrid solution:

id, csv_id, hash, date_modified, data LONGTEXT, column_0 VARCHAR(512), ..., column_19 VARCHAR(512)

Where data is a JSON array and the 20 columns are indices mapped elsewhere in the system, but if changing them all to TEXT (since the columns can be as longer, theoretically), how would a design of:

id, csv_id, hash, date_modified, data LONGTEXT, column_0 TEXT, ..., column_19 TEXT

Differ?


texts: Same Join Constraints on Multiple Columns

texts:
We have a situation that I have been able to recreate with the following simple example.  I have the following two sample tables:
CREATE TABLE contact_info
(
  id INT UNSIGNED AUTO_INCREMENT,
  priContactId INT,
  secContactId INT,
  blahBlah VARCHAR(32),

  PRIMARY KEY(id)
);

and
CREATE TABLE name_lookup
(
  id INT UNSIGNED AUTO_INCREMENT,
  contactID INT,
  contactName VARCHAR(32),

  PRIMARY KEY(id)
);

I populate them as follows:
INSERT INTO contact_info(priContactId, secContactId, blahBlah) VALUES(1, 3, "Team A"), (4, 2, "Team B");
INSERT INTO name_lookup(contactID, contactName) VALUES(1, "John Doe"), (2, "Mary Smith"), (3, "Jose Garcia"), (4, "Larry Brown");

Obviously, the contents of the tables are as follows:
+----+--------------+--------------+----------+
| id | priContactId | secContactId | blahBlah |
+----+--------------+--------------+----------+
|  1 |            1 |            3 | Team A   |
|  2 |            4 |            2 | Team B   |
+----+--------------+--------------+----------+

+----+-----------+-------------+
| id | contactID | contactName |
+----+-----------+-------------+
|  1 |         1 | John Doe    |
|  2 |         2 | Mary Smith  |
|  3 |         3 | Jose Garcia |
|  4 |         4 | Larry Brown |
+----+-----------+-------------+

We would like to perform a JOIN operation so that we get output like this:
+-------------+-------------+--------+
| John Doe    | Jose Garcia | Team A |
+-------------+-------------+--------+
| Larry Brown | Mary Smith  | Team B |
+-------------+-------------+--------+

The join constraints for both the priContactId and secContactId columns are the same and I am having tough time figuring out what the JOIN query should look like.
FYI, we are using MySQL version 5.6.49.


texts: Does the optimized column order for a PostgreSQL table always have variable length types at the end?

texts:
There's a popular and seemingly authoritative blog post called On Rocks and Sand on how to optimize PostgreSQL tables for size to eliminate internal padding by re-ordering their column length. They explain how variable-length types incur some extra padding if they're not at the end of the table:

This means we can chain variable length columns all day long without introducing padding except at the right boundary. Consequently, we can deduce that variable length columns introduce no bloat so long as they’re at the end of a column listing.

And at the end of the post, to summarize:

Sort the columns by their type length as defined in pg_type.

There's a library that integrates with Ruby's ActiveRecord to automatically re-order columns to reduce padding called pg_column_byte_packer. You can see the README in that repo cites the above blog post and in general does the same thing that the blog post describes.
However, the pg_column_byte_packer does not return results consistent with the blog post it cites. The blog post pulls from from PostgreSQL's internal pg_type.typelen which puts variable-length columns always at the end via an alignment of -1. pg_column_byte_packer gives them an alignment of 3.
pg_column_byte_packer has an explanatory comment:
    # These types generally have an alignment of 4 (as designated by pg_type
    # having a typalign value of 'i', but they're special in that small values
    # have an optimized storage layout. Beyond the optimized storage layout, though,
    # these small values also are not required to respect the alignment the type
    # would otherwise have. Specifically, values with a size of at most 127 bytes
    # aren't aligned. That 127 byte cap, however, includes an overhead byte to store
    # the length, and so in reality the max is 126 bytes. Interestingly TOASTable
    # values are also treated that way, but we don't have a good way of knowing which
    # values those will be.
    #
    # See: `fill_val()` in src/backend/access/common/heaptuple.c (in the conditional
    # `else if (att->attlen == -1)` branch.
    #
    # When no limit modifier has been applied we don't have a good heuristic for
    # determining which columns are likely to be long or short, so we currently
    # just slot them all after the columns we believe will always be long.

The comment appears to be not wrong as text columns do have a pg_type.typalign of 4 but they've also got a pg_type.typlen of -1 which the blog post argues gets the most optimal packing when at the end of the table.
So in the case of a table that has an integer column, a text column, and a smallint column, pg_column_byte_packer will put the text columns right in between the two. They've even got a unit test to assert that this always happens.
My question here is: what order of columns actually packs for minimal space? The comment from pg_column_byte_packer appears to be not wrong as text columns do have a pg_type.typalign of 4, but they've also got a pg_type.typlen of -1.


texts: How "relatively safe" is the data which was salvaged from corrupted MySQL InnoDB database?

texts:
I encountered the corruption like the following.

150208 14:27:52  InnoDB: Error: page 443375 log sequence number 8941 870477908
InnoDB: is in the future! Current system log sequence number 5968 4126168076.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: for more information.

However, fortunately, I was able to dump and restore all of the tables by using "innodb_force_recovery = 1" option. The official document says that I am "relatively safe" because the option value is less than 3. ...However, how safe is it?

  If you are able to dump your tables with an innodb_force_recovery
  value of 3 or less, then you are relatively safe that only some data
  on corrupt individual pages is lost.
  http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

What kind of "data loss" should I assume in this case?


texts: MySQL 5.7 Community Server End of Life/Support

texts:
Is there any official document that outlines the date till which MySQL Community Server 5.7 will receive security updates/patches? I see that page 20 of this document from Oracle outlines Premier and Extended Support end date for MySQL 5.7, but I am not sure if it is talking about Community Server Edition or MySQL Enterprise Edition. Is there a relation between extended support end date listed in the document and the date till which MySQL 5.7 will receive security patches?

Also, are security fixes released to Community Server first and then hardened and released to Enterprise edition of same MySQL version? Or are they released to Enterprise edition first and then to Community Server edition?

Short version of my question: Till what date will MySQL 5.7 Community Server get security patches? :)

Thanks


texts: SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT

texts:
I was researching the differences between SQL Server's READ COMMITTED SNAPSHOT and SNAPSHOT isolation levels and came across the following resource:

Choosing Row Versioning-based Isolation Levels

  For most applications, read committed isolation using row versioning
  is recommended over snapshot isolation for the following reasons:

  It consumes less tempdb space than snapshot isolation.
  Snapshot isolation is vulnerable to update conflicts that are not applicable to read committed isolation using row versioning. When a
  transaction running under snapshot isolation reads data that is then
  modified by another transaction, an update by the snapshot transaction
  to the same data causes an update conflict and the transaction
  terminates and rolls back. This is not an issue with read committed
  isolation using row versioning.

I'm somewhat new to these topics, but I can't seem to understand the two bullet points from the link above.

Why would the tempdb space be different for these modes? Does one store more granular versioning than the other?
Why is snapshot isolation more vulnerable to update conflicts?


texts: How to execute a trigger procedure with an expression as an argument?

texts:
How can I pass an expression as an argument when calling a trigger function?
create trigger my_trigger
after insert on my_table
for each row
execute function trigger_job(
    'job_name',
    (json_build_object(
       'foo', current_setting('foo', true),
       'bar', current_setting('bar', true)
    ))
);

I know how to call a "normal" function with an expression as an argument, but I can't get it to work calling a function from the trigger. I also know how to read the passed arguments from TG_ARGV[] in the trigger function, but that's beyond this question.
I also tried something simple like:
create trigger my_trigger
after insert on my_table
for each row
execute function trigger_job(
    'job_name',
    (true)
);

But I still get the error:

ERROR:  syntax error at or near "("
LINE 6:                 (true)
                        ^

Update: I am now using an intermediate function. The trigger calls the intermediate function with the single text parameter. That intermediate function builds the second argument and calls the final trigger_job function with both arguments. Simple.


texts: Allows to select on view, while hiding its definition

texts: I need to create a user in PostgreSQL with the least privileges possible. So, the user would only be allowed to do queries on specific tables and views. That user should not be allowed to get any additional info, especially not see the view definitions. How can this be done?


texts:
I disabled TDE on a database and once completed I dropped the DB certificate for it. Later I performed a manual failover to node2 and ran steps to enable TDE once again. However encryption state is stuck in state 2 with 0% of progress.

The error log shows:

  Database Encryption scan for database  was aborted. Reissue ALTER DB to resume the scan.

Every time I reissue the alter statement to turn encryption on it shows the same error. If I try setting encryption off I get an error that an encryption process is ongoing and can't get turned off.

I read this question but when I try to delete the certificate it gives me an error that the certificate is in use.

SQL Server 2016 TDE: Database encryption scan for 'DB_name' was aborted


texts: Setting the right Max Server Memory value on an Active/Active SQL Server Clustered Environment

texts:
I have looked around in the dba stack exchange and have not being able to find an answer to this one, so here it goes my concern. Hopefully somebody has done some research on it and can provide an authoritative answer.

We have an Active/Active SQL Server cluster running on 2 nodes. SQLA is the default node for one instance with the other (SQLB) as a fail-over and viceversa (SQLB default node for the other instance with SQLA as a fail-over). These servers are running on physical boxes with 64GB of RAM

We have to configure "Max Server Memory" and there are 2 theories around here

First Theory: Follow Jonathan Kehayias pretty much universally accepted approach that, in a nutshell, goes like this "reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM." This will leave each box with a Max Server Memory setting of 53 GB
Second Theory: We need to cap Max Server Memory to a value that will allow for the secondary node to take care of the other instance if a failover occurs. This limits each instance to much smaller value (around 30 GB so you have 30 GB x 2 + 4GB for the OS = 64GB)

On a first look, the first theory would be desireable as it gives each instance a way larger memory space, but some people (me included) are not sure what happens if you fail over and then you have 2 instances on the same box, each with a Max Server Memory of 53 GB of RAM.

Do you know what is the way to go with this scenario? is any of these two approaches right? or do you have valid reasons that a third approach should be used instead? do you know of any serious blog-post or white-paper to back it up with evidence?

Thank you all!


texts: Comparing multiple tables on two databases using dynamic PL/SQL

texts:
The issue I'm having is I have a block of dynamic SQL that I can't get the results from.

Here's the SQL:

CREATE OR REPLACE PROCEDURE GGCHECKER

IS
    cursor c1 is
        SELECT TABLE_NAME, TABLE_OWNER, PERCENTAGE_CHECK, LAST_UPDATED, PRIMARY_KEY FROM system.TEMP_TABLE;

    CTABLE_NAME          VARCHAR2(50);

    CTABLE_OWNER         VARCHAR2(50);

    CPERCENTAGE_CHECK    VARCHAR2(50);

    LU_CTABLE            VARCHAR2(50);

    PK_CTABLE           VARCHAR2(50);

    var2                varchar2(4000);

    var3                varchar2(4000);

    var4                varchar2(4000);

    var5                varchar2(4000);

BEGIN

OPEN C1;

LOOP

    FETCH C1 INTO CTABLE_NAME, CTABLE_OWNER, CPERCENTAGE_CHECK, LU_CTABLE, PK_CTABLE;

var2:='CREATE TABLE system.COMPARISON_RANDOM AS (
    SELECT ' ||PK_CTABLE|| ' FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| ' SAMPLE(' ||CPERCENTAGE_CHECK|| ')
    WHERE ' ||LU_CTABLE|| ' < SYSDATE -1
    )';

    EXECUTE IMMEDIATE var2;

VAR5:='
SELECT '|| PK_CTABLE || '
    FROM (
            (
                SELECT * FROM ' || CTABLE_OWNER || '.' ||CTABLE_NAME|| '
                    WHERE ' || PK_CTABLE || ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@DBLINK
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
            UNION ALL
            (
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '@DBLINK
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
                MINUS
                SELECT * FROM ' ||CTABLE_OWNER|| '.' ||CTABLE_NAME|| '
                    WHERE ' ||PK_CTABLE|| ' IN (
                        SELECT ' ||PK_CTABLE|| ' FROM system.COMPARISON_RANDOM
                        )
            )
        )';

    var3:='DROP TABLE system.COMPARISON_RANDOM';
    execute IMMEDIATE var3;
    EXIT WHEN c1%NOTFOUND;

    END LOOP;
    CLOSE C1;
    var4:='DROP TABLE system.TEMP_TABLE';
    execute IMMEDIATE var4;

end;
/

So because it has to go through a number of tables on a number of schemas on each database all the SQL for the comparison of the data has to be dynamic. I'm looking to get the results from the query in variable var5. The issue being that I also need the schema and table name for ID's that are outputted so the data is useful to me.

Ideal output would be something like this:

SCHEMA_NAME | TABLE_NAME | COL_ID

ODD         |     MIKE   | 1234567890

Thanks in advance!


texts: Reading SQL Server 2008 transaction logs

texts: I have a requirement for tracking some usage on a table that will be getting retired in the next year and feel that I could get the pertinent data (stored procedures and in-line sql being used against it) from the transaction logs. I've see some expensive purchased options out there for reading the logs but I was wondering if anybody knew of any opensource solutions or some sample code of how to parse these logs?


texts: What is the difference between using varchar(100) and varchar(255)?

texts: I want to know difference between varchar(100) and varchar(255) in terms of memory usage and programming problems of using more memory.


texts: Is Using Execute Immediate Bad Practice in Oracle?

texts:
So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data.

As the sequence can't be guaranteed to exist when the script runs, the insert seems (I really don't know Oracle) to have to go in an EXECUTE IMMEDIATE statement.

While this works (abbreviated section of code below), it feels like a horrible cludge. Is this bad practice in Oracle? If so then how should it be done?

DECLARE
  SequenceCount NUMBER;

BEGIN

  BEGIN

    -- Check to see whether the sequence exists. If not create it, if so do nothing.

    SELECT COUNT(1) INTO SequenceCount FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'USER_DEVICE_MAP_BACKUP_SEQ';

    IF SequenceCount < 1 THEN
      IF DebugMode = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Creating Sequence.');
      END IF;

      EXECUTE IMMEDIATE 'CREATE SEQUENCE USER_DEVICE_MAP_BACKUP_SEQ START WITH 1 INCREMENT BY 1';
    END IF;

  END;

  BEGIN

    EXECUTE IMMEDIATE 'INSERT INTO USER_DEVICE_MAP_BACKUP SELECT USER_DEVICE_MAP_BACKUP_SEQ.NEXTVAL, USER_ID, DEVICE_ID, sysdate FROM USER_DEVICE_MAP';

    DELETE FROM USER_DEVICE_MAP;

  END;

END;


texts: pg_restore: [custom archiver] could not read from input file: end of file

texts:
Not going into details how that happened, all started with a corrupt DB. It was OK on the file-system level, but trying to:

select count(*) from mytable

resulted in the following:

  ERROR:  could not read block 257798 in file "pg_tblspc/16386/PG_9.6_201608131/16385/16506.1": read only 0 of 8192 bytes

The table is very large (~200GB) and contains some bytea columns with binary data. It is never updated, only inserted into and selected from.
I run pg_dump on it, it was working for a long time, produced a ~200GB file, but failed in the end with a similar error:

pg_dump -Z9 -Fc -d mydatabase -t mytable -v -f /datadir/mytable.backup

  pg_dump: Dumping the contents of table "mytable" failed: PQgetResult() failed.

  pg_dump: Error message from server: ERROR:  could not read block 257798 in file "pg_tblspc/16386/PG_9.6_201608131/16385/16506.1": read only 0 of 8192 bytes

  pg_dump: The command was: COPY public.mytable (id, account_id, fetched, col1, col2, col3, col4, col5) TO stdout;

Then I tried to restore it, again it was working a long time, in the end it took ~200GB space on that tablespace, however select count(*) from mytable returns 0.

 pg_restore -Fc -d mydatabase -v /datadir/mytable.backup

  pg_restore: processing data for table "public.mytable"

and after a long time

  pg_restore: could not read from input file: end of file

On server side this resulted in:

  ERROR:  canceling statement due to user request

  CONTEXT:  COPY mytable, line 14497030

  STATEMENT:  COPY mytable (id, account_id, fetched, col1, col2, col3, col4, col5) FROM stdin

  LOG:  could not send data to client: Connection reset by peer

  FATAL:  connection to client lost

It looks like the pg_restore requested to abort the whole operation.

I would like to recover as many rows as possible, I suspect that all the data should be still there since it's basically immutable, and that just the last record is corrupted.

Is there a way to force Postgres to keep the records which were restored up till the failed one?


texts: SQLite: Extract data from the `-wal` file

texts:
Yesterday I accidentally deleted an SMS banking notification thread on my Android device.

A classic case: I didn't backup the messages for very long time.

After flashing the recovery and rooting the device I pulled the mmssms.db SQLite3 database along with its -shm and -wal files. Inspecting the contents of the database didn't give a chance to recover the messages: neither the threads nor the sms tables provide a "soft deletion" column, so the messages are deleted from the database physically.

However, grepping for the banking notification text in the -wal file resulted in very many occurrences of the notification plain text so I'm sure: the data is locked there somehow. Opening the database using sqlite3 merely deletes the -wal file.

I'm wondering: is it possible to extract the data out of the -wal file or rollback the deletion (at least for both threads and sms tables) merging the deleted contents back to the mmssms.db database?


texts: SQL Server 2012 failover cluster node won't start cluster services

texts:
We have 2 node SQL server failover cluster setup in our environment.

Node2 SQL server machine got shut down.

I turned it back on and connect back to the failover cluster.

It successfuly connected however, node1 cluster service wont start even if I start it.

Under the Network connections on the failover cluster the SQL ethernet is unavailable. I try to do IPconfig on node1 and I can only see the machine's IP address, in node2 I can see the Windows cluster IP and SQL cluster IP. I ran cluster validation report and this is the one that stood out:

Node SQL-01.ourserver.com is not reachable from node SQL-02.ourserver.com. It is necessary that each cluster node can
communicate each other cluster node by a minimum of one network path (though multiple paths
are recommended to avoid a single point of failure). Please verify that existing networks
are configured properly or add additional networks.

node2 also got that same error saying node2 is not reachable from node1.

What can I do to start cluster service on node1 back on?

I'm not sure if this information would be helpful, but after this windows and SQL cluster got created, we changed the name of the machines


texts: Oracle Patching & Database Upgrade - Which Order

texts:
I have an Oracle instance running 11.2.0.2.0:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

I am going to upgrade to 11.2.0.3.11.

My question is which order should I perform the upgrade?

Option 1

Install 11.2.0.3
Upgrade Database
Patch to 11.2.0.3.11
Patch Database

Option 2

Install 11.2.0.3
Patch to 11.2.0.3.11
Upgrade Database


texts: How do you select the rolling, most recent value for each person for each month in a range where that most recent value is less than 6 months old?

texts:
I need help writing a SQL query for Redshift (Postgres will do) likely involving WINDOW functions, PARTITIONS, LAST_VALUE and other things above my head.

Users can submit survey responses at any time (usually every few months). After 6 months the score is no longer fresh/relevant.

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    user_id       INTEGER UNIQUE,
    user_name     VARCHAR(50)
);
INSERT INTO users VALUES
    (1, 'Steve Wozniak'),
    (2, 'Steve Jobs'),
    (3, 'Tony Ive');

DROP TABLE IF EXISTS responses;
CREATE TABLE responses (
    response_id     INTEGER UNIQUE,
    user_id         INTEGER,
    date            DATE,
    score           INTEGER
);
INSERT INTO responses VALUES
    (1, 1, '2016-08-21', 2),
    (2, 1, '2016-02-04', 8),
    (3, 1, '2016-04-11', 4),
    (4, 1, '2016-06-21', 10),
    (5, 2, '2015-11-04', 9),
    (6, 2, '2015-11-22', 8),
    (7, 2, '2016-07-11', 10),
    (8, 2, '2016-08-15', 2);

I would like to return a recordset grouped by month that contains the rolling, most recent score (LAST_VALUE) for each user up to that month if that response that is no older than 6 months old.

The result set would contain the following rows relating to User #1. NULL values shown for clarity but can be omitted:

+---------+------------+-------+
| User Id | Date       | Score |
+---------+------------+-------+
| 1       | 2015-11-01 | NULL  | <= No score submitted yet
| 1       | 2015-12-01 | NULL  | <= No score submitted yet
| 1       | 2016-01-01 | NULL  | <= No score submitted yet
| 1       | 2016-02-01 | NULL  | <= No score submitted yet
| 1       | 2016-03-01 | 8     |
| 1       | 2016-04-01 | 8     |
| 1       | 2016-05-01 | 4     |
| 1       | 2016-06-01 | 4     |
| 1       | 2016-07-01 | 10    |
| 1       | 2016-08-01 | 10    |
| 1       | 2016-09-01 | 2     |
| 1       | 2016-10-01 | 2     |
+---------+------------+-------+

And for #2:

+---------+------------+-------+
| User Id | Date       | Score |
+---------+------------+-------+
| 2       | 2015-11-01 | NULL  | <= No score submitted yet
| 2       | 2015-12-01 | 8     |
| 2       | 2016-01-01 | 8     |
| 2       | 2016-02-01 | 8     |
| 2       | 2016-03-01 | 8     |
| 2       | 2016-04-01 | 8     |
| 2       | 2016-05-01 | 8     |
| 2       | 2016-06-01 | NULL  | <= 2015-11-22 SCORE OLDER THAN 6 MONTHS
| 2       | 2016-07-01 | NULL  | <= 2015-11-22 SCORE OLDER THAN 6 MONTHS
| 2       | 2016-08-01 | 10    |
| 2       | 2016-09-01 | 2     |
| 2       | 2016-10-01 | 2     |
+---------+------------+-------+

All 12 months (or entire series) should be populated unless all NULL. Possibly use generate_series() in postgres or using a number table in Redshift (https://www.periscopedata.com/blog/generate-series-in-redshift-and-mysql.html)
The NULL values can be omitted (shown for clarity).

Ultimately I'm going to need to reproduce the same grouped by year and week of the year but I can probably extrapolate those queries if I can figure out this.


texts: Keyboard shortcut to execute statement at cursor in pgAdmin

texts:
In MySQL Workbench, one can use the keyboard shortcut Ctrl + ENTER to execute the statement at cursor (delineated with semi colons).

Is there a similar shortcut in pgAdmin?

For SQL queries that span on only one line, I use a voice command in Dragon NaturallySpeaking:

Sub Main
    SendKeys "{End}"
    Wait(0.3)
    SendKeys "+{Home}"
    Wait(0.3)
    SendKeys "{F5}"
End Sub

but I don't have any solution for SQL queries that span although more than one line.


texts: SQL Server 2008 R2 Database Diagram Error Code

texts:
I have sysadmin privileges on a SQL Server 2008 R2 server. I'm working with the development database and want to save a database diagram I created. When I try to save I get the following error message:

  ===================================

>String or binary data would be truncated.
>The statement has been terminated.
>The sp_creatediagram procedure attempted to return a status of `NULL`, which is not allowed. A status of 0 will be returned instead. (.Net SqlClient Data Provider)

Program Location: at Microsoft.SqlServer.Management.DataTools.Interop.IDTDocTool.Save(Object dsRef, String path, Boolean okToOverwrite)
   at Microsoft.SqlServer.Management.UI.VSIntegration.Editors.DatabaseDesignerNode.Save(VSSAVEFLAGS dwSave, String strSilentSaveAsName, IVsUIShell pIVsUIShell, IntPtr punkDocDataIntPtr, String& strMkDocumentNew, Int32& pfCanceled)

I don't want to ALTER the database at all.

My goal is the save the diagram to references key relationships and other information in the future.

Does anyone know how I can enable saving the database diagram this way?


texts: Hardening further mysql with sqlmap suggestion

texts:
I just ran this tool called sqlmap. It gave me this few list as below.

sqlmap identified the following injection points with a total of 0 HTTP(s) requests:
---
Parameter: ID (GET)
    Type: boolean-based blind
    Title: AND boolean-based blind - WHERE or HAVING clause
    Payload: ta=asaa&ID=74 AND 3387=3387&na=0&bc=C

    Type: UNION query
    Title: MySQL UNION query (NULL) - 6 columns
    Payload: ta=asaa&ID=74 UNION ALL SELECT NULL,NULL,NULL,NULL,NULL,CONCAT(0x71766a7171,0x78587565526f656b4e4b,0x716a707871)#&na=0&bc=C

    Type: AND/OR time-based blind
    Title: MySQL > 5.0.11 AND time-based blind
    Payload: ta=asaa&ID=74=74 AND SLEEP(5)&na=0&bc

I tested on the union query doest not give any result. But booleand-based blind do give the result and time based-based blind kind of stop my application? What can I do to improvise?


texts: what's practical example / use case SELECT SLEEP() for DBA

texts:
I don't know how to use SELECT SLEEP(). It looks like a dirty hack.

I found an interesting question How and when to use SLEEP() correctly in MySQL?. The answer is developer oriented:

  If you don't want to SELECT SLEEP(1);, you can also DO SLEEP(1); It's
  useful for those situations in procedures where you don't want to see
  output.

what are examples for DBA?


texts:
I have some developer colleagues in my office that are having a problem similar to this one:

db connection uses an anonymous logon instead of the domain user

the connection string (he is working on his local machine) is:

Data Source=localhost\Development;Integrated Security=SSPI;Database=COLA;Connection Reset=FALSE

and it is getting the error message below:

Login failed for user 'NT AUTHORITY\IUSR'.

where this login AUTHORITY\IUSR came from - we don't need it?

How can we make the Integrated Security=SSPI; get his local login instead of the AUTHORITY\IUSR?


texts: Trigger to log Users Assigned to website

texts:
Is it possible to create a trigger so that works with the following scenarios:

User types valid login info to the web application

A trigger is activated and login name is inserted to the audit table, along with timestamp

and

User types invalid login info

Trigger is activated and user name is also saved in the audit table along with timestamp


texts: Recreating Layers based on data already entered

texts:
Background:
Construction events take place on a certain block of road. The events are based on data like Lane#, Layer#, and SP and EP (Start Position and End Position).

I created a picture to make it easier to visualize

Now there are two things you can do to a specific layer in a lane:

Get rid of some material
Add some material

The entire layer for a lane is not made in one day, so on day 1 they could put together layer 4 from SP=0 to EP=75 and so on and so forth.

Data:

The data that I have looks like this:

Lane#|Activity|Date       |Layer#|SP |EP |Start_Thick|End_Thick
1    |Add     |10/17/2000 |1     |0  |150|0          |0
1    |Add     |10/17/2000 |2     |0  |150|12         |12
1    |Add     |10/17/2000 |3     |0  |150|10.5       |10.5
1    |Add     |10/17/2000 |4     |0  |150|2          |2
1    |Add     |10/17/2000 |5     |0  |100|2          |2
1    |Add     |10/17/2000 |5     |100|150|2          |3
1    |Sub     |10/20/2000 |5     |0  |125|-1         |-1
1    |Sub     |10/20/2000 |5     |125|150|-1         |-1.5
1    |Add     |10/20/2000 |6     |0  |50 |2          |2
1    |Add     |10/20/2000 |6     |50 |150|2          |2

Result:
The result I want to get when I say get me a snapshot for Lane 1 at SP>=0 and EP<=150 is this:

Lane#|Layer#|SP |EP |Start_Thick|End_Thick
1    |1     |0  |150|0          |0
1    |2     |0  |150|12         |12
1    |3     |0  |150|10.5       |10.5
1    |4     |0  |150|2          |2
1    |5     |0  |100|1          |1  <<
1    |5     |100|125|1          |1.5  <<
1    |5     |125|150|1.5        |1.5    <<
1    |6     |0  |50 |2          |2
1    |6     |50 |150|2          |2

Layer 5 can be explained like this:

The first two entries in the original table shows the addition of materials and the latter two shows removal from different positions. I have not tried to interpolate before with SQL so I am not sure if this is possible or not but I would appreciate any help.

I might have missed some information so feel free to ask for additional details if needed.

I am using SQL Server 2014


texts:
I have a large SQL Server 2008 test database (1.9TB) and I want to reduced its size on the disk. I have been deleting unused tables, and purging significant amounts of data, but its size on disk it no reducing. What should I do?

I've looked at the Shrink task in SSMS, but the options are bewildering. Do I shrink the database or the files? And with what options?


texts: docker and postgres: using password_file leads to no pg_hba.conf entry for host

texts:
I'm aware of the following solution for the no pg_hba.conf entry for host error: connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host

But I wonder why this error happens when using Postgres environment variable POSTGRES_PASSWORD_FILE in Docker, and not when setting the password directly with an environment variable such as POSTGRES_PASSWORD.

Here are the most useful bits of code. If you want to see a complete example, please take a look at this repository.

This version will trigger the error:

services:
  database:
    image: postgres:10
    volumes:
      - ./pass/db/db_pass:/run/secrets/db_pass:ro
      - data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD_FILE=/run/secrets/db_pass

While this version will work fine:

services:
  database:
    image: postgres:10
    volumes:
      - data:/var/lib/postgresql/data
    environment:
      - POSTGRES_PASSWORD=db_password

The ./pass/db/db_pass file contains exactly the same password: db_password.

Basically, the only thing that changes between the two configuration is that setting the password for the database container is 1) done with an environment variable POSTGRES_PASSWORD and 2) done reading the file pointed by POSTGRES_PASSWORD_FILE, file which is bind-mounted inside the container as read-only.

If we look at the Postgres' entrypoint, POSTGRES_PASSWORD_FILE is used to fill POSTGRES_PASSWORD: https://github.com/docker-library/postgres/blob/master/10/docker-entrypoint.sh#L8-L24.

# usage: file_env VAR [DEFAULT]
#    ie: file_env 'XYZ_DB_PASSWORD' 'example'
# (will allow for "$XYZ_DB_PASSWORD_FILE" to fill in the value of
#  "$XYZ_DB_PASSWORD" from a file, especially for Docker's secrets feature)
file_env() {
    local var="$1"
    local fileVar="${var}_FILE"
    local def="${2:-}"
    if [ "${!var:-}" ] && [ "${!fileVar:-}" ]; then
        echo >&2 "error: both $var and $fileVar are set (but are exclusive)"
        exit 1
    fi
    local val="$def"
    if [ "${!var:-}" ]; then
        val="${!var}"
    elif [ "${!fileVar:-}" ]; then
        val="$(< "${!fileVar}")"
    fi
    export "$var"="$val"
    unset "$fileVar"
}

I don't get why then Postgres throws this no pg_hba.conf entry for host error if the database has been correctly created with the given password.

Looking at the entry point, the configuration should be the exact same, but I verified both with the command docker-compose -f no-file.yml run --rm database /bin/bash -c 'cat $PGDATA/pg_hba.conf' and the line host all all all md5 does not appear in the configuration using POSTGRES_PASSWORD_FILE.

Why does the default configuration changes when we make use of the POSTGRES_PASSWORD_FILE variable, and how to fix this elegantly? Do I have to provide a custom Postgres configuration file as described in https://hub.docker.com/_/postgres/, section "Database configuration"?


texts: Can't connect to a remote server using phpMyAdmin

texts:
I tried with two different servers, one had no issue but the other gave me this error:

MySQL said:
Cannot connect: invalid settings.
mysqli_real_connect(): The server requested authentication method unknown to the client [sha256_password]
mysqli_real_connect(): (HY000/2054): The server requested authentication method unknown to the client
phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in your configuration and make sure that they correspond to the information given by the administrator of the MySQL server.

Screenshot:


texts: Find a date range from rows with consecutive dates and identical column values

texts:
A similar question has been asked before but I've been having trouble adapting those answers to my question.

I want to find rows on consecutive dates, where another column has the same value, and return that as a single date range.

I have tried using OVER and PARTITION BY along with ROW_NUMBER() to get the results I need, but I'm not familiar enough with those concepts to figure out the correct output.

The system is Microsoft SQL Server 2014.

Given:

CREATE TABLE EXCEPTIONS
(
  ID    NUMERIC(18) NOT NULL,
  DATE  DATE        NOT NULL,
  TYPE  VARCHAR(20) NOT NULL,
  VALUE VARCHAR(20),
);

and the data:

INSERT INTO EXCEPTIONS VALUES
(17482, '2016-08-24', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-25', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-08-26', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-04', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-05', 'PRESSURE_EXCEPTION', 'Over'),
(17482, '2016-09-16', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-17', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-23', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-24', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-25', 'PRESSURE_EXCEPTION', 'Under'),
(17482, '2016-09-27', 'VOLUME_EXCEPTION', 'Low'),
(17482, '2016-09-28', 'VOLUME_EXCEPTION', 'Low')

The result of this query should be something like

StartDate  | EndDate    | Type                 | Value
2016-08-24 | 2016-08-26 | 'PRESSURE_EXCEPTION' | 'Over'
2016-09-04 | 2016-09-05 | 'PRESSURE_EXCEPTION' | 'Over'
2016-09-16 | 2016-09-17 | 'PRESSURE_EXCEPTION' | 'Under'
2016-09-23 | 2016-09-25 | 'PRESSURE_EXCEPTION' | 'Under'
2016-09-27 | 2016-09-28 | 'VOLUME_EXCEPTION'   | 'Low'

SQLFiddle available with more data


texts: How to backup SQL Server database once evaluation period has expired

texts:
I'm using Microsoft SQL Server Management Studio from last one month that is evaluation period software. I'm not able to login in management studio.
when I enter my user name and password it gives the error:

  TITLE: Connect to Database Engine

  Cannot connect to sony-PC

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or
  was not accessible. Verify that the instance name is correct and that
  SQL Server is configured to allow remote connections. (provider: Named
  Pipes Provider, error: 40 - Could not open a connection to SQL Server)
  (Microsoft SQL Server, Error: 2)

How can I take backup of my database?


texts: SQL query to find player with maximum runs

texts:
I have the following tables with me :

Table 1 Title : Runs

                    +------------+---------+
                    |    P_ID    |  Runs   |
                    +------------+---------+
                    |      1     | 100     |
                    |      2     | 45      |
                    |      3     | 35      |
                    |      1     | 90      |
                    |      2     | 85      |
                    +------------+---------+

Table 2 Title : Player

                    +------------+---------+
                    |    P_ID    |  Name   |
                    +------------+---------+
                    |    1       | Dhoni   |
                    |    2       | Sehawag |
                    |    3       | Sachin  |
                    |    4       | Dravid  |
                    |    5       | Kohli   |
                    +------------+---------+

I have to find the player name whose sum of runs is maximum.

I am new to MySQL and not able to figure out the correct/most efficient  query for that, please help.


texts:
I have three tables, in form of grandparent, parent, and child. By this I mean a foreign key exists in child linking to parent, and another foreign key exists in parent linking to grandparent.  Child and grandparent's only connection is via parent, and I currently have to join to ask questions like who are child's grandparents and who are grandparent's children.

I realised that a tiny bit of denormalisation is going to make my application code nicer to write, so, I want to add another foreign key in child, which links directly to grandparent (which never changes).
This of course is redundant information, but it means I don't have to join to ask questions like give me all of child's grandparents, or give me all of grandparents grandchildren.  This will make my app faster and my application code better, and I don't really need advice on the whys of doing this, but the how.

I've added the columns and I'm looking for advice on an UPDATE query to update the database with the correct values for the grandparent id in the child's new foreign key grandparent_id.

The actual tables are not grandparent, parent and child, but minicourse, topic and lesson, as below.

I tried the below query, from here, but, as I suspected, it is not quite what's needed here. The below updates all children with the same value (i.e. the wrong value, seems to be the first one it gets) of the grandparent id.  I want each child updated with the correct grandparent_id.
I'm rather new to operations like this in sql, so help would be much appreciated.

UPDATE lessons
SET minicourse_id=subquery.minicourse_id
FROM (
  SELECT topics.minicourse_id
  FROM topics
  JOIN lessons
    ON topics.id=lessons.topic_id
) AS subquery;

I could just do it manually (there are only about 10 minicourses) by running an update like this 10 times, having found what the values are by regular queries:

UPDATE lessons
SET
  minicourse_id =<I enter an actual minicourse_id here>
WHERE
  lesson_id in(<I enter a manually-found list of lesson_id's here>);

but I thought it could be cool to learn how to do it with sql instead.


texts: Results not returning rows where second parameter should match null

texts:
I'm trying to left join three tables using columns from the first and third tables as the filters. The second and third tables could have null values where I am trying to join. I want to be able to return all rows if I don't specify the second parameter (:store_number), but it only returns rows where the store_number is not null.

SELECT t1.host_name, t1.vcenter_name, t1.connection_state, t1.power_state, t2.cluster_name, t3.store_number, t3.district_number, t3.time_zone, t3.active
FROM host t1
LEFT JOIN cluster t2 ON t1.cluster_id = t2.cluster_id AND t1.vcenter_name = t2.vcenter_name
LEFT JOIN store t3 on t2.cluster_name IS NOT NULL AND t2.cluster_name = t3.store_number
WHERE t1.host_name LIKE :host_name AND t3.store_number LIKE :store_number
ORDER BY t1.host_name;

To clarify, not all hosts have clusters, and not all clusters associate to store numbers. I want to be able to return all rows even if I don't specify a store number in the parameter, but currently it only returns rows that have a store number that is not null. Passing to the parameter as %value%.

Here is what I am getting currently:

+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host_name   | vcenter_name  | connection_state | power_state | cluster_name | store_number | district_number | time_zone | active |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host1       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host1       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+

Here is what I would like to get:

+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host_name   | vcenter_name  | connection_state | power_state | cluster_name | store_number | district_number | time_zone | active |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+
| host1       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster1     | store1       | 1               | nonprod   | false  |
| host1       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host2       | vcenter1      | Connected        | PoweredOn   | cluster2     | store2       | 2               | nonprod   | false  |
| host1       | vcenter2      | Connected        | PoweredOn   | NULL         | NULL         | NULL            | NULL      | NULL   |
| host1       | vcenter3      | Connected        | PoweredOn   | cluster3     | NULL         | NULL            | NULL      | NULL   |
| host2       | vcenter3      | Connected        | PoweredOn   | cluster3     | NULL         | NULL            | NULL      | NULL   |
+-------------+---------------+------------------+-------------+--------------+--------------+-----------------+-----------+--------+


texts: How to improve query performance sort operation overloads query

texts:
I have simple database which is consist from two tables.

I want to build simple query that will return

ForumId - Forum id (Topics).
Title - Title of last post (Posts).
DateTime - Datetime of last post (Posts).
AspNetUserId - Author of last post (Posts).

I tried different scenarios:

First query

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

SELECT
EnumerateLastPostInForum.ForumId
,EnumerateLastPostInForum.Title
,EnumerateLastPostInForum.DateTime
,EnumerateLastPostInForum.AspNetUserId
FROM
(
    SELECT
    dbo.Topics.ForumId,
    dbo.Topics.Title,
    dbo.Posts.DateTime,
    dbo.Posts.AspNetUserId,
    ROW_NUMBER() over(PARTITION BY dbo.Topics.ForumId ORDER BY dbo.Posts.DateTime DESC)AS Row#
    FROM dbo.Posts inner join dbo.Topics on dbo.Topics.Id = dbo.Posts.TopicId
) EnumerateLastPostInForum where EnumerateLastPostInForum.Row#=1

Amount of rows in Topics 10000, Amount of rows on Posts 1000000. Query statistics: execution time average 1600.

Second query

CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS;
GO

with LastPostInTopic(DateTime,TopicId,AspNetUserId)
as(
SELECT EnumerateLastPostInTopic.DateTime,EnumerateLastPostInTopic.TopicId,EnumerateLastPostInTopic.AspNetUserId FROM
(
    SELECT
        dbo.Posts.DateTime
        ,dbo.Posts.TopicId
        ,dbo.Posts.AspNetUserId
        ,ROW_NUMBER() over(PARTITION BY dbo.Posts.TopicId ORDER BY dbo.Posts.DateTime DESC)AS Row#
    FROM dbo.Posts
) EnumerateLastPostInTopic where EnumerateLastPostInTopic.Row#=1
),

LastPostInForum(ForumId,Title,DateTime,AspNetUserId)
as
(
SELECT EnumerateLastPostInForum.ForumId,EnumerateLastPostInForum.Title,EnumerateLastPostInForum.DateTime,EnumerateLastPostInForum.AspNetUserId FROM
(
    SELECT
        dbo.Topics.ForumId
        ,dbo.Topics.Title
        ,LastPostInTopic.DateTime
        ,LastPostInTopic.AspNetUserId
        ,ROW_NUMBER() over(PARTITION BY dbo.Topics.ForumId ORDER BY LastPostInTopic.DateTime DESC)AS Row#
    FROM dbo.Topics inner join LastPostInTopic on LastPostInTopic.TopicId = dbo.Topics.Id
) EnumerateLastPostInForum where EnumerateLastPostInForum.Row#=1
)

SELECT * FROM LastPostInForum

Amount of rows in Topics 10000, Amount of rows on Posts 1000000. Query statistics: execution time average 900.

In both cases i used non clustered index

CREATE NONCLUSTERED INDEX IX_Posts_TopicId_DateTime_AspNetUserId ON dbo.Posts (TopicId,DateTime,AspNetUserId);

Execution plan in both cases showed that the expensive operation is

Order by [MessageBoard].[dbo].[Topics].ForumId Ascending, [MessageBoard].[dbo].[Posts].DateTime Descending

Order is result of ROW_NUMBER() function,

The main question is how to improve performance? Maybe somehow to build query in another way to avoid sort? Change structure of database? I am new in area of SQL.


texts:
I have installed MySQL 5.1.73 community edition through RPM. It was working fine.
I uninstalled this version by using yum remove mysql-server command.
I wanted to upgrade MySQL from 5.1.73 to 5.6.19 community edition.
Then I installed it by sudo rpm -i /u000/MySQL-server-5.6.19-1.rhel5.x86_64.rpm command.
After installation, I was able to start mysql by issuing sudo /etc/init.d/mysql start.
After this, I tried to install MySQL-shared-compat-5.6.19-1.rhel5.x86_64.rpm using RPM command. But, it is giving conflict message with mysql 5.1.73 - install of MySQL-server-5.6.19-1.el6.x86_64 conflicts with file from package mysql-libs-5.1.73-4.el6.x86_64.
It is same for MySQL-client-5.6.19-1.rhel5.x86_64.rpm. Again, I got the similar conflict message.
After this, I tried to connect MySQL by issuing below command:

$ mysql -uroot -p3pkvFtaWM_PTCwJ8
-bash: mysql: command not found

How can I fix this issue and run the MySQL 5.6 as normally?
Do I need to clean MySQL from server?


texts:
I have a small doubt regarding making variables persistent in Percona Server 5.6 in Linux.

I need to make log-bin-trust-function-creators persistent so that I need not make it to true everytime on instance restart.

Doubt is that in my.cnf, should I give

  log-bin-trust-function-creators=1

or

  log_bin_trust_function_creators=1

Currently I have set log_bin_trust_function_creators=1, and it doesn't take in effect after restart.

InnoDB buffer pool cache should be in bytes only or it can be in G (currently I have set 12G and it is not working). Should I change to bytes?
In my.cnf, does the order in which the variables are given matters? or it takes randomly? Say, I can give buffer pool in the last in [mysqld] and InnoDB buffer size as first - does it matter?


texts: IS NULL condition in the predicate on Distributed Query does not run remote

texts:
I am executing the below distributed query with predicate condition but it is not executing the code completely remote.

This is happening only for IS NULL predicate on BIT column

Select * from [RemoteServer].[DBName].dbo.[TableName] where [column] is null

The distributed server in this case is SQL Server and the Column is BIT data type.

For this, the remote query is pulling all the data back to the main server and then applying the filter. The execution plan look like this..

This only happens if I check for Is null condition. If I do, [column]=1 or Column=0, then the query runs remotely.


texts: Connection to availability group listener on non-default port doesn't require port to be specified

texts: I inherited some 2016 SQL Servers with an availability group and listener.  The SQL instances are set up to use port 30001, and the AG listener is also set up to use port 30001.  When I connect using the listener, I DO NOT have to specify the port--it just works.  I know it shouldn't work, based on all the documentation.  Can anyone tell me how this might be possible?  In what scenario could you connect to a listener on a non-default port without having to specify the port?


texts: MySQL using swap during conversion to InnoDB

texts:
I'm trying to convert some MyISAM tables in my database to InnoDB (MySQL 5.1). The server has quite a bit of memory (96G), and the tables that are giving me problems can be on the order of 50 millions rows - 160 million rows (anywhere from 10G to 25G), but the same thing is happening regardless of table size.

During the "INSERT INTO ... SELECT * FROM..." MySQL starts using swap memory and I have to kill the conversion because it will take over 10 hours or so.  While researching the conversion process, I found multiple sources that say that innodb_buffer_pool_size should be about 70% of your server RAM, so I made it about that, but I think that may be what's causing the issue.

Here are some important my.cnf settings:

innodb_buffer_pool_size=64G
innodb_flush_method=O_DIRECT
innodb_log_file_size=224K
innodb_log_buffer_size=24M
innodb_flush_log_at_trx_commit=0
innodb_additiona_mem_pool_size=24M

key_buffer_size=8G
sort_buffer_size=4M
read_buffer_size=128K

Any ideas what I'm doing wrong/why it's going to swap?  Any ideas would be helpful.


texts:
I'm trying to create two users in a Postgres database—one with read-write access to all tables in two schemas, and one with read-write-create (i.e., able to make DDL changes) to the same schemas.

I currently have these statements.

CREATE SCHEMA
    schema_a;

CREATE SCHEMA
    schema_b;

CREATE ROLE read;
CREATE ROLE read_write;
CREATE ROLE read_write_create;

GRANT USAGE ON SCHEMA schema_a, schema_b TO read;
GRANT SELECT ON ALL TABLES IN SCHEMA schema_a, schema_b TO read;

GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA schema_a, schema_b TO read_write;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA schema_a, schema_b TO read_write;

GRANT ALL ON SCHEMA schema_a, schema_b TO read_write_create;

GRANT read to read_write;
GRANT read_write to read_write_create;

ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT ON TABLES TO read;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT ON SEQUENCES TO read;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT INSERT, UPDATE, DELETE ON TABLES TO read_write;
ALTER DEFAULT PRIVILEGES IN SCHEMA schema_a, schema_b GRANT SELECT, UPDATE ON SEQUENCES TO read_write;

CREATE USER
    read_write_user
WITH PASSWORD
    'a_password';

GRANT read_write TO read_write_user;

CREATE USER
    read_write_create_user
WITH PASSWORD
    'another_password';

GRANT read_write_create TO read_write_create_user;

After running these, read_write_create_user can create tables, read from, and write to all tables. But read_write_user can't read or write to any tables created by read_write_create_user.

What am I doing wrong?


texts: Connecting to SQL Server using Oracle SQL Developer

texts: I am using oracle sql developer to manage my oracle database. I recently added third party database jdbc drivers to be able to connect to my new MySQL and SQL Server installations. I need help with the right settings for the SQL Server connection i.e UserName, Host Name, Port including formats


texts: After fresh install getting `psql: command not found`

texts:
I downloaded and installed PostgreSQL and PostGIS from KyngChaos for my OS X 10.10 install. When I run psql I get,

bash: psql: command not found

According to the site How to run psql on Mac OS X? it gets installed into /Library/PostgreSQL. There is no file there.


texts: Stored Procedure: How remove Comments, spaces, and extra lines, or companies binaries?

texts:
We are trying to compare stored procedure codes. Example, these two stored procedure codes produce same result.

(1)
Is there method to convert stored procedures into binaries to validate they conduct the same thing?

(2)  How would i tell if stored procedures are the same (minus comments, line spaces, and extra text)?

Context: we just created an auto code generator for project (Slowly changing dimensions). Comparing new code generator with older archive code.

create procedure dbo.NumberSum
as
select 2 + 2 as SumDigits

create procedure dbo.NumberSum
as
-- Extra line comment
select 2 + 2 as SumDigits


texts:
Say I have a table with prices and different percents, and I need to calculate the sum of the percentage.

And I run this query,

SELECT
    i.user_id,
    i.payment_type,
    SUM(id.price),
    SUM(ROUND(id.price / (1 + (id.some_percent / 100)))) AS subtotal,
    SUM(ROUND(id.price - (id.crc_price / (1 + (id.some_percent / 100))))) AS some_percent_sum,
    SUM(ROUND((id.price / (1 + (id.some_percent / 100))) * (id.another_percent / 100))) AS another_percent_sum
FROM
    invoice i
        INNER JOIN
    invoice_details id ON i.invoice_id = id.invoice_id
GROUP BY i.user_id , payment_type

Will this query affect my SQL performance? Assuming all my index are optimized.

Or is it better to return the raw data and do the calculations in the server (whatever language c#, java, etc..)


texts:
We have a development Oracle 12c database and the SYSTEM user account is locked out for one of the SID's (works fine with another SID). When I try to do a conn SID / as sysdba it keeps connecting me to another SID that's idle. So I cannot even get to the SID that has the locked account.

Any ideas on how to connect to the SID that has the locked SYSTEM account so I can unlock and reset the password?

Thanks.


texts: MySQL timeout on INSERT lasts almost 60 seconds

texts:
I have this table:

CREATE TABLE `ClusterDescription` (
  `ClusterDescriptionId` int(11) NOT NULL AUTO_INCREMENT,
  `ClusterId` int(11) NOT NULL,
  `RightBarDateTime` datetime NOT NULL,
  `InstrumentId` int(11) NOT NULL,
  `RowCreated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`ClusterDescriptionId`),
  UNIQUE KEY `ClusterInstrumentId` (`ClusterId`,`InstrumentId`),
  KEY `InstrumentId` (`InstrumentId`) USING BTREE,
  KEY `OrderDesc` (`RightBarDateTime` DESC,`ClusterId` DESC)
) ENGINE=InnoDB AUTO_INCREMENT=3055989 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

The table has more than 3m rows.

I receive regularly this error:

  SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
  trying to get lock; try restarting transaction

for this command:

INSERT INTO ClusterDescription (ClusterId, RightBarDateTime, InstrumentId)
 SELECT IFNULL(MAX(ClusterId) + 1, 1), :rightBarDateTime, :instrumentId
 FROM ClusterDescription WHERE InstrumentId = :instrumentId

There can be up to 50 above INSERT at almost the same time.
:instrumentId is never the same in any of the parallel commands.
Checking the process list I see lot of these commands waiting to be executed with a Time value > 30 seconds.
I set innodb_lock_wait_timeout to 360 seconds which improved the situation. Before I received lot of the above mentioned error and this error:

  SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try
  restarting transaction

My questions:

What is wrong with this table definition?
Why do need the parallel INSERT commands more than 50 seconds to be executed?
Why do I receive a dead lock error when only 1 table is affected?

Running on MySQL 8.0.13

I need the InnoDB engine because of the descending index.


texts: Good Resources on Operating/Administering MongoDB

texts:
What are the best resources for learning how to operate and administer MongoDB?

There are plenty of resources on developing against it - which actually creates a problem when you've got plenty of development competence and really need to filter out that noise. For developers who can't afford to hire a DBA yet, we need to get reasonably good at keeping our Mongo cluster stable, performant, etc. Beyond the FAQs and quickstart guides, are there any other good repositories of info? Good blogs to follow, etc.?


texts:
Looking at the MySQL server variables the server and database collation are set to latin1_swedish_ci but the collaction_connection is utf8_general_ci. Additionally, the collation/charset is set at many levels: server, database, table and column. You also have the charset of the PHP MySQL connection to consider.

My question is fourfold:

Why are there different levels of MySQL collation/charsets? Is it so you can mix up your character sets to suit your needs? Correct me if I'm wrong but utf8 seems to be the best character set for general use so why is it that most of the LAMP setups I've used have latin1 as default?
Should you always ensure your PHP connection matches the charset of the database you're working on?
If you can have different tables that use different character sets do you just use SET NAMES or mysql(i)_set_charset to switch?
If you have a table that has multiple charsets how do you manage that since the connection can only use one charset at a time?

Many thanks.

EDIT:

Regarding the comment:

“You can use either, the former one only to set results charset and the latter one to set either PHP internal encoding for use with mysqli_real_escape_string and results encoding.”

I thought you aren't meant to use real_escape_string and SET NAMES together. See: http://www.php.net/manual/en/mysqlinfo.concepts.charset.php


texts: Connection Pooling for PHP based website using Oracle 11g database

texts:
I am using Oracle 11g database server and now I am getting to see that OCI's persistent connection isn't much of a use, and a new connection is created anyway when a new user accesses the page. I would like to have a pool of connections which are used, and no new connections are not created. I came across DRCP (database resident connection pooling), but I am unable to understand how to set the number of connections that can be created per server.

Questions

From the documentation available on the Oracle website, I can set the minimum and maximum number of pooled servers, but where would I be setting the number of connections??
Has anyone worked with Oracle 11g's database resident connection pooling?
If the database servers are unix boxes, can we do any ODBC connection pooling??


texts: How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file?

texts:
How do I import data into a new installation of oracle 11g r2 using a .dmp file and a .par file?

Here is what I have done so far, on a Windows 8.1 devbox:

1.) Open command line as administrator
2.) C:\WINDOWS\system32>sqlplus / as sysdba
This will reveal the SQL prompt as follows:
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 25 14:20:46 2016
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> CREATE DIRECTORY DMPDIR AS 'C:\path\to\DumpDir\';
    SQL> exit;
3.) Then back in windows command prompt:
C:\WINDOWS\system32>impdp parfile=C:\path\to\DumpDir\impdp.par;

Then I read this other posting which contains the following answer code:

impdp user/pass schemas=schema1 directory=dumpdir \
  remap_schema=schema1:schema2 \
  dumpfile=schema1.dmp \
  logfile=impdp_schema2.log

Since my question is slightly different, I guessed at the following, which I have not typed yet:

impdp schemas=schema1 directory=dumpdir \
  dumpfile=schema1.dmp \
  logfile=impdp_logfile.log

What is the correct syntax to use?  And what other steps should I take to do this properly?


texts: MySQL replication on slave server Broken/Stopped without error

texts:
(Hi; sorry for my English)
My question is: how can I restart MySQL replication on a slave server.
At first sight everything seems to be normal but when I check with

SHOW SLAVE STATUS\G

besides Relay_Log_Space, Read_Master_Log_Pos and Seconds_Behind_Master which
continue to increase. Other data remain fixed for several days.
I tried STOP/START SLAVE; but nothing has changed.

root@localhost [mysql]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: slave_replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000103
          Read_Master_Log_Pos: 197827259
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 149672940
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 305160334
              Relay_Log_Space: 65277287216
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 1724670
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3464562
                  Master_UUID: 727b7e74-325f-11e6-9b54-faa1cd739fa9
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0


texts: SQLITE: Select row ID based on only when the column all the values are same

texts:
I have a table where more than one rows with same ttid. I would like to get ttid rows based on status column is done.

tb  tbid    ttid    status
-----------------------------
216 6   5   done
216 6   5   running
216 7   5   preparing
217 11  10  done
217 11  10  done
218 12  11  done
218 12  11  running
212 15  14  done
212 15  14  running
220 17  15  done
220 17  15  done

I wanted to have the following rows only:

tb      tbid    ttid    status
--------------------------------
217     11      10      done
220     17      15      done

I have tried the following query but it gives wrong info for tb: 216, because two different tbid.

select tb,tbid,tbid,status FROM tt
status in ('done')

Could you please help how to get the expected results.


texts: Utilizing a Right and Left Outer Joins in same SELECT

texts:
I am looking to better build my SQL Development skill set.  Where I work currently most all the data fits into a hierarchy and we almost always start with the top most element to work our way "down".  This results in really only needing to use INNER and LEFT OUTER joins for the various queries.

I am hoping to get some guidance on a situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query.

Referencing a previous question on StackOverflow What is the difference between Left, Right, Outer and Inner Joins? I understand what a RIGHT OUTER and a LEFT OUTER JOIN is, however all problems I have been asked to solve, could be solved using only INNER and LEFT OUTER joins.

EDIT:

I did find that this is basically a duplicate of a previous StackOverflow question When or why would you use a right outer join instead of left?.  It would appear that in general there is a consensus that

Most all queries using a RIGHT OUTER JOIN can be converted into an
INNER and LEFT OUTER version.
There are some benefits for
    read-ability formatting or organizational processes which might
    require using a RIGHT OUTER instead of a LEFT OUTER.

There is also an answer on that question which details a particular situation where you would INNER JOIN a group of tables together and then RIGHT OUTER JOIN to find where the "right" table has records in the whole INNER JOIN'ed subset.  I think this can be accomplished with an INNER JOIN and LEFT OUTER JOIN version it would just require some work.


texts: SQL How to get multiple values into one column (MS SQL)

texts:
I have the following Tables:

    CREATE TABLE #table1
    (Batchnumber INT NOT NULL,
     BOXID       TINYINT NOT NULL,
     ErrorID     TINYINT NULL,
     Weight      INT);

    CREATE TABLE #table2
    (ErrorID     TINYINT,
     Description VARCHAR(100));

    INSERT INTO #table1(Batchnumber, BOXID, ErrorID, Weight)
    VALUES (12345, 1, NULL, 1000),
    (12345, 2, 1, 1000),
    (12345, 3, 2, 1000);

    INSERT INTO #table2
    (ErrorID, Description)
    VALUES (1, 'Network Error'),
    (2, 'Keyboard to chair interface problem'),
    (3, 'Unknown Error');

I need to have the following in the temp table

TempTable
Batchnumber | Weight | Remarks
12345         3000     Network Error, Keyboard to chair interface problem

So he needs only 1 row with the Batchnumber, the sum of the weight for all 3 records and 1 field with all the error codes explained

Can someone help me with this please?


texts: Subquery vs Join and where - which one is faster?

texts:
I have two sql's which gives me same results:

Query 1:

SELECT
u.*, COUNT(po.order_id) products_count
FROM (SELECT * FROM orders o WHERE o.date >= (CURRENT_DATE() - INTERVAL 1 MONTH)) o
LEFT JOIN products_orders po ON po.order_id=o.id
JOIN users u ON u.id=o.user_id
GROUP BY po.order_id

and Query 2:

SELECT
u.*, o.id order_id, COUNT(po.order_id) products_count
FROM users u
JOIN orders o ON o.user_id=u.id
LEFT JOIN products_orders po ON po.order_id=o.id
WHERE o.date >= (CURRENT_DATE() - INTERVAL 1 MONTH)
GROUP BY po.order_id

I wonder.. which one should be faster?
Query Explains shows them to be equals... What do you think guys?


texts:
I am using SSMS. I have about 5 logins. For a particular login I have all server roles other than sysadmin. It is a sql login (not windows authentication). In user roles it has all permission for almost all databases. For some database it has only datareader.
Now when I try to take backup of database using SSMS and when try to select backup destination by clicking button I get the following error:

"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup
Cannot access the specified path or file on the server. Verify that
you have the necessary security privileges and that the path or file
exists.
If you know that the service account can access a specific file, type
in the full path for the file in the File Name control in the Locate
dialog box."

Even though I have db_owner and all permissions I am getting this error. But if I select the path manually in the file name field then it allows to take backup without any error.
So why does it happen? I want this user to take backup without these error messages. (But I can't give sysadmin for the user). How can I solve it?
I already some article stating that use SQL query and sqlcmd to take backup. But I want it to work using SSMS.


texts: Connecting pgAdmin3 to Postgres on Heroku

texts:
I'm running a Heroku Postgres database (Database) in association with a Django app.  The docs that refer to the add-on are here.  I thought this would be a worthwhile question for the community because Heroku alerted its users today that it will be migrating everything off the shared database add-on that used to be default.

I'm having some trouble connecting pgAdmin3 to the Database.

As per the instructions in the link above, I grab the Database's credentials with the following command:

$ heroku pg:credentials HEROKU_POSTGRESQL_OLIVE

I'm using a homebrew installation of Postgresql on Mac OSX.

In the meantime, I have:

switched SSL on in my pg_postgresql.conf;
added the setting: listen_addresses = '*'
and added host    all         all         0.0.0.0/0         md5
and restarted my postgres server.

Then I go to pgAdmin3 and select File > Add Server.

I enter the credentials provided by Heroku as follows (first is Heroku credential name, second is pgAdmin field name):

In the "Properties" Tab:

dbname into Name;
host into Host;
port into Port;
I leave the pgAdmin field for "Maintenance Database" blank;
user into Username;
password into Password;

In the SSL Tab:

Im the field for SSL, I select "require" (as instructed by Heroku).

I hit enter, and pgAdmin thinks, then throws an error as follows:

An error has occurred:

Error connecting to the server: FATAL: permission denied for database "postgres"
DETAIL: User does not have CONNECT privilege.

Not sure what to do at this point.  Would appreciate any pointers.


texts: Handling lossy data before character- set migration

texts:
I am trying to migrate a database from US7ASCII to AL32UTF8 using CSSCAN. During the initial scanning using CSSCAN we have found the presence of lossy data both in data dictionary() and application data.Can any one please help as to how can we handle the lossy data before converting database from US7ASCII to AL32UTF8 .

**USER.TABLE|COLUMN      LOSSY**

SYS.SOURCE$|SOURCE         143


texts: Recovery with Postgresql logical replication

texts:
I'm using Postgresql 13 on Debian 10.6 and learning about logical replication.
I've set up logical replication with one publisher and one subscriber of one table. I'm wondering what my options are for recovering data (or rolling back) when, for example, someone accidentally does something on the publisher side like updating all the data in the table with the wrong value, or even deleting everything from a table. With logical replication these unintentional changes will of course be applied to the subscriber.
I've relentlessly searched online but have had no luck finding out what my options are.
I read about PITR but I'm thinking that's suited more for physical replication, whereas I want to test rolling back changes on a specific database on a server.


texts: How to cast an integer to a boolean in a MySQL SELECT clause?

texts:
I'm new here so be kind to me. I have the following scenario:

I have many tables which, for the sake of simplicity, are represented in a View in my MySQL database. My problem is that I need a value in this view representing if it is one kind of event or another (a simple boolean), which I tried to achieve with:

`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement` AS `IsStopingEvent`

The result is represented as int, and so is read by Entity Framework. The problem is that I really need a boolean return value, which I tried to achieve with:

CAST((`gu`.`StoppingUnitEventME` = `ese`.`MonitoringElement`) as boolean) AS `IsStopingEvent`

This resulted in an error, one that is not displayed to me in MySQL Workbench (I only receive that annoying "You have an error in ...").

Can you guys please help me out?

Tried to solve it in my application, but I really preffer this solved in the database, since it will be used by other software later.


texts: Adding Column value from Previous Row based on Where Condition.

texts:
I have a table with symbol, epoch and Volume. The epoch is stored based on Symbol and Epoch in ascending order.

I want to get cumulative volume which is sum of volume and prev timestamp and the volume at current timestamp for the same day and for same symbol.

I am new to SQL and this already seems to be quite complicated. Pls help.


texts: What exactly is written to transaction log?

texts:
Here's the scenario:

DB with Simple recovery model,
Roughly 14 GB of data (~80 million rows),
INSERT ... SELECT statement into empty table with 7 non-clustered, non-unique, single column indexes and one clustered single column primary key from table with no indexes,
Transaction logs set to have unlimited space with Autogrowth on by 2 GB

The query has been running for 3 hours now and transaction log files have grown to about 45 GB!

My question is simple - why? What is actually written to transaction log in this scenario? I read this article, this technet resource and this MSDN article but I still don't understand why the log is so big, especially in Simple recovery mode - this operation should be minimally logged...

The table is not replicated. I finally decided to cancel my query and instead create keys/indexes/constraints on new table, drop the old one and rename it. It should be faster, right?


texts:
Out of nowhere MySQL just stopped working. Last thing I changed was enabling CORS for my project.

I've tried most of the solution on the internet. and here is my error log

2015-11-09 15:56:04 d8c InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
151109 15:56:04 [Note] InnoDB: Using mutexes to ref count buffer pool pages
151109 15:56:04 [Note] InnoDB: The InnoDB memory heap is disabled
151109 15:56:04 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
151109 15:56:05 [Note] InnoDB: Memory barrier is not used
151109 15:56:05 [Note] InnoDB: Compressed tables use zlib 1.2.3
151109 15:56:05 [Note] InnoDB: Not using CPU crc32 instructions
151109 15:56:05 [Note] InnoDB: Initializing buffer pool, size = 16.0M
151109 15:56:05 [Note] InnoDB: Completed initialization of buffer pool
151109 15:56:05 [Note] InnoDB: Highest supported file format is Barracuda.
151109 15:56:06 [Note] InnoDB: 128 rollback segment(s) are active.
151109 15:56:06 [Note] InnoDB: Waiting for purge to start
151109 15:56:06 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.22-72.0 started; log sequence number 1665234
151109 15:56:06 [Note] Plugin 'FEEDBACK' is disabled.
151109 15:56:06 [Note] Server socket created on IP: '::'.

error log :

16:39:25 [mysql] Error: MySQL shutdown unexpectedly.
16:39:25 [mysql] This may be due to a blocked port, missing dependencies, 16:39:25 [mysql] improper privileges, a crash, or a shutdown by another method.
16:39:25 [mysql] Press the Logs button to view error logs and check
16:39:25 [mysql] the Windows Event Viewer for more clues
16:39:25 [mysql] If you need more help, copy and post this
16:39:25 [mysql] entire log window on the forums


texts: Effectively saving a graph (terabytes) in a database

texts:
I'm new here so I'm not entirely sure what tags to use so please notify/edit if this should be changed, thanks!
Background
I have a graph with ~4 billion nodes and ~1 trillion edges that I want to store in a database (like sqlite) as in this way data can be inserted on the hard disk rather than in RAM as many other graph data structures require.
Data format
This graph format looks like:
accession node position orientation
23101.1   1    1        plus
23101.1   100  2        plus
...
23101.1   100  1        min
...
~1trillion

The plan
I think of creating TABLES for accession, node, and orientation that I will refer to using foreign keys. So then the "main" table will be
accession node  position orientation
<FK1>     <FK2> 1        <FK3>

(P.s. not sure if I should do if for the position too, but this will simply be 1 till graph-path-length).
The question
I only have little knowledge of SQL but based on code like this I would have to execute something like the code below for each line in the graph file:
    INSERT INTO main (accession, node, position, orientation) VALUES
        ( SELECT id from accessions WHERE accession=23101.1,
          SELECT id from nodes WHERE node=1,
          1,
          1
);

and also catch an error when the node/accession does not exist and insert it. However, I wonder whether obtaining the foreign keys with the SELECT .. WHERE will not get really slow when they are billions of rows in the table? So overall, what would be a proper way to store this information in the database (if at all)?


texts: Get Stored Procedure Result Column Types

texts:
I'm trying to get whatever a stored procedure returns, with the column names and their types. I can do this with tables but I couldn't figure it out for stored procedures. I tried the sp_columns but only managed to make it work for tables.

I have also tried something like this but I'm not sure what I am supposed to match with what.

SELECT *
FROM SYS.PROCEDURES (NOLOCK) AS AA
INNER JOIN SYS.SCHEMAS (NOLOCK) AS BB ON (AA.schema_id = BB.schema_id)
INNER JOIN SYS.COLUMNS (NOLOCK) AS CC ON (AA.object_id = CC.object_id)

For example:

USER_ID VARCHAR(200)

I only need the names of the column names a stored procedure returns and their data types. SQL Server version is 2014, if it matters.

Any ideas? Thanks.


texts:
Is there a tool out there that will take sql and align it vertically?

what I have:

SELECT first_name AS foo
      ,last_name AS bar
      ,ssn AS bas
      ,address AS chu
FROM dbo.contacts;

desired results or something similar to the statement below:

SELECT first_name AS foo
      ,last_name  AS bar
      ,ssn        AS bas
      ,address    AS chu
FROM dbo.contacts;

My team has been using http://poorsql.com/ to line things up, but it would be nice to be able to make use of white space and have our AS statements line up without having to manually format everything.


texts: Is RI on a parent XtraDB table maintained after Optimize Table?

texts:
$ mysql dbnam -e' optimize table theTable'
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| dbnam.theTable | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| dbnam.theTable | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+

Just called optimize on a table in MariaDB, and got this disturbing message about how the db did a recreate for me.

If I have other tables that have Foreign Keys pointing at this one, are they still doing the right thing?  How can I verify this?


texts: How to JOIN a table with itself while using a sub-query?

texts:
I JOIN a table to itself as

SELECT t1.TagID AS TagID1
    ,t2.TagID AS TagID2
    ,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY t1.TagID, t2.TagID

How can I limit the rows retrieved for each table (t1 and t2) by

WHERE TagID IN(SELECT TagID FROM Tags WHERE Articles>50)

Note that the table TagMap is huge with over 200M rows, and I wish to limit its size before JOIN.

In fact, the reason I want to limit the number of rows of TagMap is that the table is too big and I cannot perform the JOIN.


texts: I need to Select 100 records (or less if there are not 100 records) per group from multiple tables

texts:
The following query gets me the entire universe of a.transactionId with b.dataItemValue:

SELECT DISTINCT a.transactionId, b.dataItemValue 'Round Type', c.transactionRoundTypeName 'Round Type Name'

FROM TransactionId_tbl a

INNER JOIN TransactionDataInteger_tbl b
ON a.transactionId = b.objectId
AND b.dataItemId = 601257

INNER JOIN TransactionRoundType_tbl c
ON b.dataItemValue = c.transactionRoundTypeId

WHERE b.dataItemValue is NOT NULL
AND b.dataItemValue != 7
ORDER BY b.dataItemValue

I need to limit this query to get only 100 records (or the number of existing records if there are less than 100) of a.transactionId for every record of b.dataItemValue

Thanks!


texts: MySQL creates lowercase database / table names

texts:
I'm currently building an application which requires a large MySQL database. Whenever I run the script, it executes perfectly but all of my table names (and the database name) end up in full lowercase.

I tried setting lower_case_table_names = 0 in the my.ini file (and I restarted the service).

However my script still keeps creating everything in lowercase. Column names are strangely not affected tough.

My script:

CREATE DATABASE IF NOT EXISTS Vacation;
CREATE TABLE IF NOT EXISTS Users (
    userId INT AUTO_INCREMENT,
    userFirstname NVARCHAR(32) NOT NULL,
    userLastname NVARCHAR(32) NOT NULL,
    userPrefix VARCHAR(16),
    userPhone VARCHAR(15),
    userEmail VARCHAR(32) NOT NULL,
    userPassword CHAR(40) NOT NULL,
    userPoints INT DEFAULT 0 NOT NULL,
    PRIMARY KEY(userId)
);

The result:

SHOW DATABASES -- vacation
SHOW TABLES FROM Vacation -- users

I'm currently using Ubuntu 14.04 (and I've tried reading this article for help).

Is there any way of fixing this problem? Thanks in advance.


texts: How would I exclude a group of tables based on a name prefix foo_* from a mysqldump?

texts: I'd like to exclude a group of tables from a mysqldump command (from a bash script). I might not know the exact table names ahead of time but they will be named with the prefix foo_ or might contain another known suffix like _BAK_[%Y%m%d]


texts: What would be the first things you do to find out about an MYSQL environment you are taking over?

texts:
I am new to DBA role, and wanted to know if there was any commands (or free software) that come with Mysql 5.5 (master/slave/slave) replication to quickly get familiar with
the database, and also making sure my.cnf is set up with the correct parameters.


texts: Does the Amazon RDS backup/snapshot service lock tables?

texts:
We have a database that is 100+GB.  Most tables use the InnoDB engine.

A regular dump takes 1-2 hours.

If we moved to Amazon RDS, would this backup cause table locks?

Does Amazon use a simple mysqldump?


texts: "ORA-00942: table or view does not exist" when deleting from a very real table in the user's schema

texts:
I have a table that definitely exists in the logged-in user's schema (therefore I own the table) and is not a view/synonym/anything wacky.

I can select from it, but when I try and either insert, update or delete from this table, I get

  ORA-00942: table or view does not exist

I'm completely baffled.


texts: Deciding between an IsDeleted/DateDeleted column or a separate History table?

texts:
We have a table that stores nothing but an AccountId, TagId, and DateCreated. Users can update the Tags on accounts, which adds or remove records from this table. No tag can exist more than once on an account, but the same tag can be added and removed multiple times. This table is primarily used in queries users build to filter accounts using either JOIN or EXISTS on the tag table.

I've been asked to start recording tag history, and I'm trying to decide if it would be better to use an IsDeleted and/or DateDeleted column on the existing table, or if I should create a separate history table that stores this data instead.

The primary purpose of tracking the history would be to run reports on a specific point in time. For example, getting data on accounts that had TagA on them during January 2013.

What factors should I consider when trying to decide between the two?


texts: One way Synched Dev Database from Production Database

texts:
I've got a scenario to implement in our environment of SQL Server Database. Now we have around 10+ different application which uses the same DB for their day to day activities. The strange thing is, along with the production tools, the dev apps also using the same DB which is very risky (Only recently they agreed that) after years of its working.

So what Im looking for is to have a solution with a new replica of the DB as DEV database which is always to be one way synch with the production DB. That means if any changes on the production DB (Schema or Data or anything) it should immediately reflected on the DEV DB. But if we have any changes on schema on DEV Db, it should not get auto synched to Production DB. It should synch only when we manually do that( But how, I have no idea with these synching)..

So how can we handle these situation wisely. Im very poor in these DBA activities. Sorry if this is a simple thing even for beginners.


texts: Query using a filtered index but wrong estimated number of rows

texts:
I have a table with a nonclustered index on a datetime2 field.

On that same table i have a field (char[1]) which is used to logically delete records and can have 2 distinct values : A(active) or D(deleted).

There are 451047 records with the datetime2 field set at NULL but only 7095 are marked as A.

Every query in the application looks for active records only, so, every query that's looking for NULLs in the datetime field was getting really bad estimates and, with that, bad execution plans.

Simple example:

I decided then to create a filtered non clustered index but estimates were still incorrect:

It looks like i still get the old estimates, even if the query is using properly the filtered index. Does anyone know the reason of this behaviour?

These are the new statistics of the filtered index:

Table definition:

CREATE TABLE [TYDATPRD].[HAND00F](
    [STDRECSTS] [char](1) NULL,
    [HDHAND] [numeric](14, 0) IDENTITY(1,1) NOT NULL,
    [HDCHKINDT] [datetime2](7) NULL,
    --lots of other columns which I don't think are needed

    CONSTRAINT [PK_TYDATPRD_HAND00F] PRIMARY KEY CLUSTERED (
        [HDHAND] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DATA]
) ON [DATA]

Nonclustered filtered index definition:

CREATE NONCLUSTERED INDEX [IX_HAND00F_HDCHKINDT] ON [TYDATPRD].[HAND00F] (
    [HDCHKINDT] ASC
)
WHERE [STDRECSTS]='A'
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
GO


texts: Does MariaDB 5.5.56 have INFORMATION_SCHEMA.INNODB_METRICS?

texts:
I'm trying to debug why I don't have INFORMATION_SCHEMA.INNODB_METRICS. I'm uncertain because I see it in the MySQL 5.5 reference glossary: https://dev.mysql.com/doc/search/?d=10&p=1&q=innodb_metrics.

mysql --version
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

When I try to configure it through my.cnf I get:

mysqld: unknown variable "innodb_monitor_enable"

When I try to configure it through the MySQL CLI I get:

MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_rw_commits;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'
MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_nl_ro_commits;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'
MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_ro_commits;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'
MariaDB [(none)]>  SET GLOBAL innodb_monitor_enable = trx_rollbacks;
ERROR 1193 (HY000): Unknown system variable 'innodb_monitor_enable'

My guess is that it was introduced in 5.6 because
https://dev.mysql.com/doc/refman/5.5/en/innodb-information-schema-metrics-table.html redirects to 8.0 and 5.6 works.

Additional info

Turns out that, as shown below

SELECT VERSION()
    -> ;
+----------------+
| VERSION()      |
+----------------+
| 5.5.56-MariaDB |
+----------------+

...I was looking in the wrong place. I actually am using MariaDB as the database management system, and @danblack pointed out it was released in 10.


texts: Does it make sense to separate records in a document DB (e.g. DynamoDB) for better query performance?

texts:
I've recently started working with DynamoDB, and most of my experience is in traditional data warehousing (e.g. sql server). One common pattern which I saw when working with SQL server was that we would sometimes separate "active" data from "historical" data, so we would have one table with last months or years data (the time interval which most of our queries were based on), and then another table with the rest of history, for occasional retrieval. We would do this improve query performance.
Now my question is does this design pattern make any sense when working with i.e. DynamoDB with a well partitioned key. So if I've partitioned my DynamoDB by customer id + time, and I'm occasionally interested in getting each customers consumption (whenever they check their dashboard in the app) during the past month or so, does it make sense to have separate tables for past month, and rest of history? As I've understood with partitioning it doesn't even consider the, or need to search for data from the partitions that contain data outside of my search range (since time was part of the partition key), but this is just my assumption. Query speed is of utmost importance, and the data I'm working with spans multiple years.


texts:
What do the contents of extproc file look like? I'm aware that my extproc.ora file is missing, and I'd like to create one in it's absence.

The instructions here tell me to:

  Configure the Oracle extproc.ora to point to the location of the
  ST_Geometry library file.

  Make a backup copy of the extproc.ora file on the Oracle server. Open
  the extproc.ora file and alter it to point to the location of the
  st_shapelib.dll (Windows) or libst_shapelib.so (Linux or UNIX)
  library.

  The following line is added
  to extrproc.ora to point to the ST_Geometry library in that directory:

  SET EXTPROC_DLLS=ONLY:C:\mylibraries\st_shapelib.dll

  Save and close the extproc.ora file.

I've tried creating an extproc.ora file from scratch, but I don't really know what I'm doing: SET EXTPROC_DLLS=ONLY:C:\mylibraries\st_shapelib.dll.

Not surprisingly, the file fails.

Rather than just dream up what I think the file should contain, I'd like to copy an existing example. I've searched the web, but have not come up with any complete examples of extproc.ora. Not even in the Oracle documentation: https://docs.oracle.com/database/121/NETAG/advcfg.htm#NETAG0133

What is an example of an extproc.ora file?


texts: SQL 2012 insert data into a table from another table and custom columns

texts:
I'm trying to insert data from one table into another table and I also have 4 columns that are not in the source table but need to be populated in the target table. Here is the Insert statement

SET IDENTITY_INSERT dbo.district ON

INSERT INTO Table1 (dim_district_key, workday_id,
district_code,district_abbr,district_name,state,region,dss_start_date,
dss_end_date,dss_current_flag,dss_version, dss_update_time, created_by,
created_on, last_updated_by, last_updated_on)

SELECT dim_district_key, workday_id, district_code, district_abbr,
district_name, state,region, dss_start_date, dss_end_date, dss_current_flag,
dss_version, dss_update_time, SYSTEM_USER, GETDATE(), SYSTEM_USER, GETDATE()
FROM Table2

These columns are not in table2: created_by, created_on, last_updated_by, last_updated_on

When I run the above insert statement I get
Msg 207, Level 16, State 1, Line 4
Invalid column name 'created_by'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'created_on'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'last_updated_by'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'last_updated_on'.


texts: Load data from file only for specific column based on other column value

texts:
I am familiar with basic usage of 'load data local infile' and importing specific columns only.
What I want is however to import a single column from file based/related on data from another column.
So if I have say a table with column names 'name' 'value' 'value1'... and a file with 'name' 'value1', can I load only 'value1' column which corresponds to 'name'?
Note that 'name' in file is not ordered.

Existing table:

name|value|value1

Peter, 5, ferrari

Alice, 2, fiat

John, 2, pontiac

File values:

name, value1

John, vw

Peter, chevy

Alice, fiat

Something like: LOAD DATA LOCAL INFILE "/home/file.csv" replace into table mytable fields terminated by "," optionally enclosed by '"' (@dummy,value1) where name=@col1;

Is there some way to do it?


texts:
Recently  on a database I enabled trace for event 12842 using the below statement in order to trace Oracle error ORA-12842 : Invalidated cursor.

alter system set events '12848 trace name ERRORSTACK level 3';

After sometime the error did occur but we could not find any trace file for that event.

I am suspecting the tracing got disabled after instance restart.

Is there any way to list out all the events for which tracing is enabled ?


texts: SQL Error: 1038: An object or column name is missing or empty

texts: Currently I'm working with windows credentials, when i trying to create a user with SQL Authentication encountered the attached error. I have seen it is due to empty login name. How to resolve this issue.


texts: I'm trying to print labels on a Zebra printer in SSRS 2014 and the orientation is coming out wrong. In report builder, I set the page size to 4.15 x 1.15 with correct margins. Also, in report builder, if I print it will typically come out the correct orientation (landscape), but occasionally I have to go into Page Setup, change the Orientation from Portrait to Landscape & back again to get it to print correctly (Not sure why it requires a Portrait orientation to print in Landscape in Report Builder. However, when I try to print from SSRS directly, it comes out in portrait and prints across several labels. I've done some research and saw recommendations to change the printer to print directly and bypass the print spooler, which I've attempted to no avail. Any assistance is greatly appreciated.


texts: Defrag a HEAP by creating clustered index and immediately dropping it

texts:
I'm writing a script which intends to defrag a HEAP based table by creating a dummy col with a clustered index and then immediately dropping it.  (It's someone else's app and I don't want to make any permanent changes - they did okay this, though.)

ALTER TABLE my_heap ADD dummy BIGINT IDENTITY;
GO
CREATE CLUSTERED INDEX ix_dummy ON my_heap (dummy);
GO
-- need to wait here until index completes
DROP INDEX ix_dummy ON my_heap;
ALTER TABLE my_heap DROP COLUMN dummy;
GO

When I run individual steps of the script, it works fine.  When I run it all at once, nothing seems to happen (frag levels don't go down).  I believe it's because the DROP happens before the CREATE CLUSTERED has finished - so the CREATE CLUSTERED doesn't have time to do its stuff.

How do I make the DROP wait for the CREATE CLUSTERED to finish? I'm doing this for about 30 tables, and so I'd rather not put in a simple WAITFOR DELAY 15 minutes for each one.

PS Using SQL Server 2005