texts: Arithmetic overflow error converting float to data type numeric

texts:
I am trying to move data from a csv file into a SQL server database. Some of my values are in the scientific notation. I figured out on how to get most of them converted but for one value I get the Arithmetic overflow error.

The value that is causing the error is 4.56621E-6. If I change the part before the E by removing the 1 so it reads 4.5662E-6 the import works fine. All the other values I need to import work fine.

I use a format file to import the data. Below the line for the column that is giving me grief:

88  SQLFLT8 0   0   "," 89  PPL_2_BL    ""

The format in the database is decimal(18,9). Any suggestions on how to avoid this error without manually changing values in the source file?

To put it into perspective. The CSV file contains more than 2.2 million rows with 154 columns each. Which results in a CSV file size of more than 2GB. Currently I am working with a test file. When the final go live comes. I need to switch over fast. Which means I can not analyze and edit the file for several days.

Update

I played around with the values a little bit.

4.56621E-6   -> fails
6.5789474E-6 -> works
4.5662E-6    -> works
4.56622E-6   -> fails
4.566210E-6  -> works (surprisingly)
4.66621E-6   -> fails


texts:
I feel like I read somewhere back about blockchain databases but I can't seem to find anything too solid on the topic. This paper called Blockchain Databases doesn't offer much in terms of description. It is just a few pages, mentioning "Hashchains" and that "Blockchain databases are structured as mutual distributed ledgers (MDLs)", which doesn't mean much.

This one, vChain: Enabling Verifiable Boolean Range Queries over Blockchain Databases, mentions "From the database perspective, a blockchain can be seen
as a database storing a large collection of timestamped data records". They also mentioned FlureeDB, working on a SQL blockchain database, and BigchainDB. Both of those projects have little in terms of describing implementation detail on how exactly the "blockchain technology" interfaces with "database". Further, they mention:

  The data stored in the blockchain can be modeled as a
  sequence of blocks of temporal objects {o1, o2, · · · , on }. Each
  object oi
  is represented by ⟨ti
  ,Vi
  ,Wi⟩, where ti
  is the timestamp of the object, Vi
  is a multi-dimensional vector that
  represents one or more numerical attributes, and Wi
  is a
  set-valued attribute. To enable verifiable query processing,
  an authenticated data structure (ADS) is constructed and embedded into each block by the miners (to be detailed in Sections 5-7). We consider two forms of Boolean range queries:
  (historical) time-window queries and subscription queries.

In the FlureeDB paper, it states:

  FlureeDB is a protocol token whose blockchain stores a series of
  point-in-time facts as atomic units we call Flakes. Every Flake is — and
  forever will be — unique. Flakes can be thought of as events asserting (or
  retracting) a fact at a particular point in time. A block is a collection of
  Flakes as a single atomic transaction, and represents each state change
  of the blockchain.

  ...

  Every database is a separate
  blockchain, and groups of databases are placed into shards

  ...

  When submitting a transaction, a user will spend their own
  tokens unless the database has established rules that allow it to spend
  tokens that it holds. FlureeDB allows a rich permission model that can
  control exactly what pieces of data can be modified by whom and also
  which transactions must be paid for by the transacting user. [NOT DESCRIBED]

Still after at least browsing through these papers I don't see any descriptions of how the data storage of database records works in a blockchain / Merkle tree.

My question is specifically, how blockchain applies at the level of storage. I would like to know how the concept of a table, record, column, and value, and relations/links between data, are generally accounted for in a blockchain. That's pretty much the only thing I don't see in any of these resources, how the data model is implemented in the blockchain.

Maybe this Filecoin paper will be helpful.


texts: mysqldump show Table doesn´t exist when doing LOCK TABLES

texts:
Our server had power outage and after start some of mysql tables was inaccessible. I tried many things but nothing helped and currently i am at freeze point.

I used command :

mysqldump database table > table.sql -u user -p password

but result is Table doesn´t exist when doing LOCK TABLES. This happend only when table.frm file was in database directory.

When i tried sql command Show tables i see my table. Then i droped it and see  error table doesn´t exists but *.frm file dissapeared.

Then mysqldump show Couldn´t find table: table

If i copy my *.frm file back, then mysqldump show first error.
When i tried used other commands select,drop,analyze,repair i always get error table doesn´t exists and cannot try other solution which i can find on net.

Windows server standart
MySQL 5.6.15 Win64


texts: Design and queries for analysis of event-sequences

texts:
I will have to store sequences of events with certain properties in a database (MariaDB preferably) and I have some issues regarding the design and queries. The crucial part is the analysis of the events, for there is a need to analyse events with respect to other events in the same sequence. Each event will have some properties, which will be of importance for the analysis.

We'd like to keep the database as performant as possible, while retaining the flexibility. I know, that this may be a problem, hence I am trying to evaluate options and not starting coding right away.

My first approach towards flexibility was the EAV-model. Regarding performance we could do better, but I made some preliminary tests and the performance was sufficient. However, these tests were conducted on rather simple queries, which took singular events into account, but not their interdependence. Events are - at least fore the moment - simply modeled in a single table, linked to other tables, for example the sequence they are assigned to.

To get back to my questions: I have a bad feeling that this design will get me into trouble, when I am trying to analyse sequences of events. I have made up some different strategies to retrieve the data I'll need for an analyzation, but I fear that they'll all be quite slow, when there is more than a handful of data.

Query all events that satisfy a certain condition (e.g. are assinged to a certain player) and in a second step retrieve all the sequences. This would result in more queries, but I might end up with less data
Query all sequences that satisfy as many conditions as possible and in a second step filter the data by conditions that are only applicable to events in my program. This would impede some filters at query time and thus yield more data, which might be of no use, but this strategy would result in less queries, which might be advantageous.
Parallel queries, which try to respect the interdependence between events at query time. This one seems appealing to me from a physicists point of view, for if it worked, it will be quite advanced and have a cunning logic. Anyway, there is a chance that this strategy is deemed to fail, for there are too many special cases to respect, which may be coded easily with sequential, but not with declaratice languages and I fear that this will yield the worst results concerning the performance.

Given these strategies I am afraid that the design I proposed may not be suited for my use cases, but I do not really have a clue how to redesign - or which other paradigms to use - to improve my situation.


texts:
I am trying to understand "how" Postgres creates the indexes using b-trees with an example.
To be specific, I am looking to understand how the b-tree will look like when the ("Name") & ("Username" and "SSN"- Composite index) respective indexes are created on the below table (doesn't matter but lets assume there are 1 million records):

If "Name" index is created, what will the B-tree look like? Is it something like:

In the above diagram for b tree 3,1,4,2 are the row ids. So does the b tree store the key, value and the row id in the node?
I read How does Postgres make its B-tree index? but it didn't answer my question.


texts: accessing postgis extension capabilities

texts:
The context is pertinent as it demonstrates behaviour different than other (more manual) successful attempts.

Deploying a rails application with postgres and postgis extension to AWS EC2 instance, using the rubber gem, database migration initiates but halts with  undefined method 'point' for #<ActiveRecord::ConnectionAdapters::PostgreSQLAdapter: is being generated when reaching

      t.point :lonlat, :srid => 3785

This is indicative of the extension not being accessed.

The database is created with user postgres (and I surmise this may be an initial problem, leading to permissions issues).  When logging in as postgres and access psql for the database,CREATE SCHEMA postgis; and CREATE EXTENSION PostGIS WITH SCHEMA postgis; return errors because they already exist.  So the extension is installed.

Yet SELECT PostGIS_full_version(); fails.  I have found by trial and error that SET search_path = public, postgis; will activate PostGIS_full_version.  However, even when activated, if a database migration is launched immediately after as ubuntu user, it fails just as well; thus the extension is not accessible (or the search_path is not granted to all).

I have also found that after a sudo service postgresql restart the SELECT PostGIS_full_version(); fails anew for the postgres user.

Behaviour is consistent between 9.3 and 9.4

How can the search_path be persistent and available across users?

[note: question posted to this site as the hunch is that it is a postgresql user permission and object access issue]


texts:
When I search the web for "ER diagram" and "database schema", these 3 diagram types are found, but all of them are referred to as "ER diagrams". I want to know:

What is a database schema diagram and what is an ER diagram?
Why all are named "ER diagram"?


texts: How do I go about creating this SQL query?

texts:
I'm attending the free DB course at Stanford Online, and frankly one of the  exercises gave me some trouble. I have a feeling that this should be horribly simple, so for a DBA, I'm obviously not very good with SQL.

We're working with a simplified scenario for rating movies.

  For all cases where the same reviewer rated the same movie twice and gave it a higher rating the second time, return the reviewer's name and the title of the movie.

Here's the schema:

Movie ( mID, title, year, director )
Reviewer ( rID, name )
Rating ( rID, mID, stars, ratingDate )

How should I go about this?


texts:
How long does changing PostgreSQL's xmloption setting to DOCUMENT from its default CONTENT last? It seems to be DB wide, not Cluster wide (any way to set it on a column by column basis?)

It seems to be connection specific, which is annoying cause I have a SQL script that needs to have it set to DOCUMENT to pass validation. The script is 24GB uncompressed, so I don't feel like modifying it manually.


texts: Building an Entity-Relationship Diagram for a Language Dictionary

texts:
I need advice on how to build entity-relationship diagram (ERD) for Language Dictionary.  Since I did not build ERD before, I will try to explain and show the ultimate goal of this project below.

Requirements

This will be an application on Android and Ios smartphones, which has the ability to search for the word (local databases are not planned; ie, the search for the word will drag data from the server) and therefore the request must be processed fairly quickly. The object returned from the server will have the following values:

{
  "word": {
    "core": {
      "id": 1,
      "word": "lorem",
      "description": "Blabla lorem blab blablab blab blablabblab blablab",
      "grammar": {
        "noun_class": "v-class",
        "morphology": {
          "root": "lor",
          "affixes" {
            "prefixes": [
              {"first": ""},
              {"second": ""},
              {"third": ""}
            ],
            "suffixes": [
              {"first": ""},
              {"second": ""},
              {"third": ""}
            ]
          },
          "endings": [
            {"first": ""},
            {"second": ""},
            {"third": ""}
          ]
        },
        "declension": {
          "singular": {
            "declension_1": "lorem",
            "declension_2": "loremo",
            "declension_3": "loremon",
            "declension_4": "loremi",
            "declension_5": "loremino",
          },
          "plural": {
            "declension_1": "lorem",
            "declension_2": "loremo",
            "declension_3": "loremon",
            "declension_4": "loremi",
            "declension_5": "loremino",
          }
        },
        "part_of_speech": "noun",
        "omonims": ["lorem", "lorem", "lorem", "lorem"],
        "synonyms": ["lorem", "lorem", "lorem", "lorem"],
        "antonyms": ["lorem", "lorem", "lorem", "lorem"],
        "hyperonyms": ["lorem", "lorem", "lorem", "lorem"],
        "hyponyms": ["lorem", "lorem", "lorem", "lorem"],
        "etymology": "Blabla lorem blab blablab blab blablabblab blablabBlabla lorem blabla lorem blab blablab blab blablabblab blablab",
        "transcription": "[lorem]",
        "pronunciation": {
          "id": 1,
          "audio": "https://s3.amazon.com/voice.waw"
        },
      }
    },
    "english": {
      "translate": "lorem",
      "in_text": "Blabla lorem blab blablab"
    },
    "german": {
      "translate": "lorem",
      "in_text": "Blabla lorem blab blablab"
    }
  }
}

Considerations

If you imagine that there can be more than 100 thousand words, then when searching for a word it is very important that the query on the database is happening pretty quickly.

Therefore I thought to construct tables on each letter of the alphabet separately i.e. one table on all words beginning on letter A, and so on. And it is important for me to know if this will improve the search?

And the grammatical meaning of the word, is it better to put them in a separate table and create relationships (M-M, 1-M, 1-1) to the word or should I still create them inside the same table as the word itself?

My current ERD

I created the ERD that follows based on my experience, which is very poor:

Perhaps I am missing something and the advice of experienced developers who have worked a lot with databases would help me a lot.

Note: I don’t know if it’s important, but I’m going to build a database using Python Django as Web-client and PostgreSQL.

23.06.2019 - I've preliminarily committed my "first" version of django models, I would be very grateful if anyone could confirm that I did everything right. https://github.com/khashashin/language-dictionary-model/blob/master/formatting.py


texts: Why MySQL is still "using temporary" when update by primary key?


texts: Using source columns in OUTPUT INTO clause of an INSERT statement (SQL Server)

texts:
I am writing a batch processing insert statement and would like to use a temp table to keep track of inserted ID's instead of looping through the items myself and calling SCOPE_IDENTITY() for each inserted row.

The data that needs to be inserted has (temporary) ID's linking it to other data that also should be inserted into another table, so I need a cross reference of the actual Id and the temporary Id.

This is an example of what I have so far:

-- The existing table
DECLARE @MyTable TABLE (ID INT IDENTITY(1,1), [Name] NVARCHAR(MAX));

-- My data I want to insert
DECLARE @MyInsertData TABLE (ID INT, [Name] NVARCHAR(MAX));
INSERT INTO @MyInsertData ( ID,Name)
VALUES ( -1 , 'bla'),(-2,'test'),(-3,'last');

DECLARE @MyCrossRef TABLE ([NewId] INT, OldId INT);

INSERT INTO @MyTable ( [Name] )
   OUTPUT Inserted.ID, INS.ID INTO @MyCrossRef
   SELECT [NAME] FROM @MyInsertData INS

-- Check the result
SELECT * FROM @MyCrossRef

The problem is that I cannot get the OUTPUT INTO clause to accept the ID, I've tried @MyInsertData.ID and other tricks joining the table to itself, but nothing seems to work.


texts: Survey database design: first version. Are there errors?

texts:
I have to represent surveys with questions like these ones:

Studying those groups of questions I found these rules:

A survey has questions' groups.
A questions' group has a name and questions (all question will have
the same number of options).
A question has a question text and a group of possible options to
answer it.
An option has a name and a type.
A type could be: Text, Yes/No or Checkbox.
An answer is a value that user gives to an question's option for a
given survey.

Do you think that those requisites are OK?

With, those requisites I have design the following conceptual model (here I call form instead of survey):

I need to make surveys dynamics. I'm going to reuse group of question, questions and/or options.


texts:
I have an application that runs every minute using the Windows Task Scheduler.  One of the first things the job does is connect to a SQL Server 2008 instance to record that the application started.

A few times a day the job is failing when making its first query with the following error message:

  Connection Timeout Expired.  The timeout period elapsed during the post-login phase.  The connection could have timed out while waiting for server to complete the login process and respond; Or it could have timed out while attempting to create multiple active connections.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=9; handshake=10342; [Login] initialization=0; authentication=1; [Post-Login] complete=4032;

We have hardware monitoring on the database server so I checked for CPU, RAM or Disk IO spikes at the times of the failures, but there weren't any spikes.  The connection string is setup to use integrated security (using one of our domain service accounts).  The pre-login handshake is always taking the longest.

This database server is used by a lot of applications and no other application is failing with this message.  We have web apps that use the database server without problems as well as other applications that run through the task scheduler (although they don't run as frequent as every minute).

Can I increase the SQL Server logging level to expose more information about what's happening during login?  Any ideas how I can address this issue?

Edit: I'm also seeing the following error message (although less frequent):

  A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.)


texts: SQL Server: Query Efficiency Having vs Subquery

texts:
I have the following query where I used a sub query since I can't use aggregates in the where clause.

SSMS suggested I use the HAVING clause but then I would need to group by all the other columns (right?) which I did not want to have to do so I ended up using the sub query.

Is using the sub query the most efficient way or should I use the Having clause so I don't have to read from the same table twice?

Is the difference negligible?

select *
from [DW_STAGE].[ETL].[Claims]
where pd_dt > (
        select dateadd(m, - 12, cast(max(pd_dt) as date))
        from [DW_STAGE].[ETL].[Claims]
        )


texts: How do I use the unload command to export a table to a IXF file

texts:
For a table T in schema S, how do I use the unload command to export T data into a IXF file? The IBM DB2 documentation isn't very helpful for this simple case.

I am trying to compare performance between the export command and the unload command. This is on DB2 9.7 for AIX


texts: Optimizing PostgreSQL for transient data

texts:
I have several tables with 100-300 columns of integer types each, that hold highly volatile data. The datasets are keyed by one or two primary keys, and when refresh occurs, the whole dataset is deleted and new data is inserted in one transaction. Dataset size is usually a few hundred rows, but can be up to several thousand rows in extreme cases. Refresh occurs once per second, and dataset updates for different keys are usually disjointed, so dropping and recreating the table is not feasible.

How do I tune Postgres to handle such load? I can use the latest and greatest version if that makes any difference.


texts:
There are two tables. P(pno,pname,color) and sp(sno,pno,qty) and s(sno,sname)
I want to get the most supplied pname and its total supplied quantity.
I wrote query using derived table like

select pno,totalqty
from (select p.pno, sum(quality) as totalqty
      from p,sp
      where p.pno=sp.pno
      group by p.pno
     )d
where totalqty = (select max(totalqty)
                  from (select p.pno, sum(quality) as totalqty
                        from p,sp
                        where p.pno=sp.pno
                        group by p.pno
                       )d
                 );

I have to repeat the expressions that find the total number of quantity of each part. I can not reuse derived name ( I used d). is there any better way?


texts:
I had my database working properly but I had an issue and when I tried to repair it I executed the following command

alter tablespace system add datafile '/var/oracle/oradata/data.dbf' size 10000m autoextend on maxsize unlimited;

Then I noticed that the command didn't resolve my problem then I deleted the file data.dbf... But the problem that I'm having right now is that when I'm trying to restart my database it doesn't run.

I looked and didn't find how to fix the problem. Can someone help me? Any help please... I'm down


texts: mysql need to show all months data for the selected year

texts:
I got stuck with my query. I need to show monthl (current + previous) records.

Let's take below example for year 2014:-

I have 7 records in the month 03(March)

I have 2 records in the month 04(April)

So total records in year 2014 is 9.

Now we come to year 2015:-

I need to show all records in Jan(No new records added in database), This should be 9.

My Query shows 0.

I need to show all records in Feb(5 new records added in database), This should be 14.

My query shows 5.

Query works fine when I am checking for month 03 and year 2014, showing all 7 records. but not for all conditions.

  I am calling same query for all months(01-12).

SELECT count(pkID) as TRecords
FROM   students
WHERE  (fkCategoryID ='56'
OR     fkSecondaryCategoryID ='56'
OR     fkOptionalCategoryID ='56')
AND    MONTH(`DateAdded`) <='1'
AND    YEAR(`DateAdded`) <='2015';


texts: mysql - best way to provide unique voucher

texts:
I am designing a website that will provide vouchers (serial codes). All voucher codes generated and saved in a table like this and not generated online:

 v_id             voucher                status (used?)
-------------------------------------------------------
  1              abcdefgabcdefg1             0
  2              abcdefgabcdefg2             0
  3              abcdefgabcdefg3             0
  4              abcdefgabcdefg4             0

I should display unused (status:0) voucher to end user after successful payment but I am not sure how can I do this because this site may have high traffic so more than one people may visit site in same milisecond of time, so for example I can not just use:

SELECT * FROM table WHERE status=0 LIMIT 0,1;

and then UPDATE it:

UPDATE table SET status=1 WHERE v_id=....

because two people may visit in same time so we provide same serial code in SELECT before we UPDATE.

what would be best way to ensure I display unique serial code?


texts:
I have the following set of MySQL queries:

SET @domain := (
    SELECT server_domain
    FROM engine.storage
    WHERE (size_bytes - bytes_inuse) >= 40000
    FOR UPDATE
);

UPDATE engine.storage
SET bytes_inuse = bytes_inuse + 40000
WHERE server_domain = (SELECT @domain);

The question is if the SET statement influences the functionality of FOR UPDATE? Will the SELECT statement be executed everytime I call SELECT @domain or once on assignment and before the UPDATE statement? Or is there even a better solution to manage this?

Thanks in advance!


texts:
I got this question in an exam, the teacher asked us to raise the employers' salaries using the following rules using a cursor:

An extra 4% for the first quarter with the highest salary.
An extra 6% for the second quarter with the highest salary.
For the rest an extra 8%

How to approach solving this.

My attempt was SELECT SALARY FROM (SELECT * FROM EMP ORDER BY SALARY) rownum > count(*)/4 to get the salary at first then add the %4  raise and update it to the new value.

I haven't tested it but I guess it might work, but I think it's a bit too far fetched and there may be a better or more efficient solution, your thought guys ?


texts: How to return 0 for record count when nothing returns for that hour using datepart?

texts:
In my sample query below, I am pulling the count by hour correctly, but I need it to return all hours of the day and display 0 for the hours that have no data. Is there an easy way for me to do this?

Here is my SQL:

IF OBJECT_ID(N'tempdb..#Temp') IS NOT NULL DROP TABLE #Temp;

CREATE TABLE #Temp (TempNumber NVARCHAR(50),TempDateTime DATETIME2)

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233600','2019-08-20 08:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233601','2019-08-20 08:32:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233602','2019-08-20 10:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233603','2019-08-20 12:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233604','2019-08-20 16:27:08.047')

INSERT INTO #Temp (TempNumber, TempDateTime)
VALUES ('11287233605','2019-08-20 23:27:08.047')

SELECT DATEPART(hh,TempDateTime) AS TempDatePart,
COUNT(DISTINCT TempNumber) AS RecordCount
FROM #Temp
GROUP BY DATEPART(hh, TempDateTime)

Here is what I'm looking for:


texts: Installing a SQL Server patch on a node within a 2-Node FCI

texts:
When installing a SQL patch on a 2-Node Automatic FCI with a shared disk (in the same datacenter / same subnet), with quorum achieved by Node and FileShare Majority, is it possible to install a SQL patch on one node without impacting the other node in that 2-Node Cluster? Obviously, this is for rollback purposes. What is the common approach in this scenario?

Thanks for the help!


texts:
How to get all the tables from database with all values in tables using single query..
I tried using

$sql = "SHOW TABLES FROM $dbname";
$result = mysql_query($sql);
$arrayCount = 0
while ($row = mysql_fetch_row($result)) {
$tableNames[$arrayCount] = $row[0];
$arrayCount++; //only do this to make sure it starts at index 0
}
foreach ($tableNames as &$name {
$query = "INSERT INTO metadata (table_name) VALUES ('".$name."')";
mysql_query($query);
}

but it gives me only tables name


texts: database design for song position under play-list

texts:
I have a music streaming application where I have playlists. Each playlist has maximum 100 songs. I have a mapping table called PlaylistSongMapping which has the following schema

+------+------------+--------+
|SongId| PlaylistId |Position|
+------+------------+--------+
|   1  |     10     |    2   |
|   2  |     10     |    1   |
|   3  |     10     |    3   |
|   5  |     10     |    4   |
|   6  |     11     |    1   |
+------+------------+--------+

The position of each song inside playlist is managed using the Position column. Inside the playlist I need the order change functionality. The current logic is to update Position column. The problem is if i need to move the song with SongId: 3 to first position I need to update rows of SongId 1,2,3. This number would be a big one when playlist has more number of songs. Is there a better logic so that number of update queries are very minimum.


texts:
I have this query running for long time

    (@P0 nvarchar(4000))

select emaildmo0_.Id as Id7_
    ,emaildmo0_.CreatedDate as CreatedD2_7_
    ,emaildmo0_.EmailAddress as EmailAdd3_7_
    ,emaildmo0_.EmailType_Id as EmailType4_7_
    ,emaildmo0_.isPreferedEmail as isPrefer5_7_
    ,emaildmo0_.ProfileId as ProfileId7_
    ,emaildmo0_.UpdatedDate as UpdatedD6_7_
from dbo.Email emaildmo0_
where emaildmo0_.EmailAddress = @P0

This is the execution plan

My first question why it is index scan not index seek

These 2 lines from the ExecutionPlan.xml

        <Warnings>
          <PlanAffectingConvert ConvertIssue="Cardinality Estimate" Expression="CONVERT_IMPLICIT(nvarchar(255),[emaildmo0_].[EmailAddress],0)" />
          <PlanAffectingConvert ConvertIssue="Seek Plan" Expression="CONVERT_IMPLICIT(nvarchar(255),[emaildmo0_].[EmailAddress],0)=[@P0]" />
        </Warnings>

May you explain for me what is this warning mean ? and if there is any suggestion to tune this query


texts:
I have setup a Master-Master configuration in MySQL with one additional slave, like this:

Master1 <-> Master2 <- Slave

Master1 is the write master used by my application, Master2 is used for reads and backup, and the slave is only used for reads. I have setup log-slave-updates on both masters, and everything seems to work correctly.

However I'm having trouble wrapping my head around what happens if Master2 fails and I have to switch the slave to follow Master1. Basically what I understand is you need to do a CHANGE MASTER TO MASTER_HOST = 'Master1' on the slave, but what I don't get is how you determine the parameters for MASTER_LOG_POS and MASTER_LOG_FILE. How do you make sure the point at which you start replication from Master1 does not make you lose data, or tries to apply events twice?


texts: Return Most Recent Entry But Sum One Field

texts:
What I am after here is to return the Max(date) but Sum(amt) so that way I only see the most recent information pertaining to an account, but I also see the Total Amt!  This is my DDL

Declare @Helper1 Table
(name varchar(100),inputid int, custid varchar(100), rdsdate date, amt decimal(10,2))

Insert Into @Helper1 (name, inputid, custid, rdsdate, amt) Values
('Bob', 123, 'rst11', '2017-01-01', '13.00'), ('Jack', 131, 'rst11', '2017-01-05', '-5.00')
,('Larry', 444, 'ems33', '2017-02-01', '10.00')

I tried this query - however it returns all info for custid rst11 not the Max() like I am after

Select
name
,inputid = MAX(inputid)
,custid
,rdsdate = MAX(rdsdate)
,amt = SUM(amt)
FROM @Helper1
GROUP BY name, custid
ORDER BY name asc

This is my desired returned result set (since rst11 has two entries return the max and SUM())

Jack 131 rst11 2017-01-05 8.00
Larry 444 ems33 2017-02-01 10.00

What is the proper way to achieve this in a query?


texts: Optimise and merge SELECT query results from 2 tables

texts:
I have a schema like this:

And a query like this
SELECT
  `users`.user_id,
  `users`.user_name,
  `users`.user_email,
  `users`.user_password_hash,
  meta_a.first_name,
  meta_b.last_name

FROM (SELECT m.meta_value AS first_name
   FROM `users` AS u
     LEFT JOIN `usermeta` m ON u.user_id = m.user_id
   WHERE u.user_name = 'demo' AND meta_key = 'first_name'
   LIMIT 1
  ) AS meta_a,
  (SELECT m.meta_value AS last_name
   FROM `users` AS u
     LEFT JOIN `usermeta` m ON u.user_id = m.user_id
   WHERE u.user_name = 'demo' AND meta_key = 'last_name'
   LIMIT 1
  ) AS meta_b,

  users

  LEFT JOIN `usermeta` USING (user_id)
WHERE `users`.user_name = 'demo'
LIMIT 1

Is it possible to optimize/shorten this query to get the same results instead of using a lot of these multiple SELECTs?
So at the end I get all the meta_key and meta_values that are associated to that user_id.


texts: MYSQL Error: 1026: Error writing file 'binlog' (errno: 2 - No such file or directory)

texts:
We are using MYSQL 8.0.20 Community Edition on Oracle Linux 7.8 (RHEL). On one server we are running master database and using row based replication to a slave on another server via bin-log. Replication is running OK (we use Percona toolkit to perform checksums). Had an issue suddenly when updating any records caused the following intermittent error:

1026: Error writing file 'binlog' (errno: 2 - No such file or directory)

This started very early in the morning one day and was intermittent throughout the day and suddenly the error stopped. No errors recorded in the MYSQL log files as far as I can see on either server (only in the log files from our application REST API that connects to the database to query and update records).
Also noticed the above error in MYSQL Workbench when trying to update records - again this was intermittent.
We don't have 'binlog' file per say..
-rw-r-----. 1 mysql mysql  392106655 Aug 24 11:17 binlog.000025
-rw-r-----. 1 mysql mysql 1073790505 Sep  1 09:02 binlog.000026
-rw-r-----. 1 mysql mysql  598606158 Sep 11 13:12 binlog.000027
-rw-r-----. 1 mysql mysql  290988040 Sep 17 17:30 binlog.000028

Not sure why 'binlog' was being updated to attempting to be updated - seems like it could not work out which binary log file to update. Show master status reveals:
    File            Position    Binlog_Do_DB    Binlog_Ignore_DB    Executed_Gtid_Set
    binlog.000028   291444378                                       8b2b0f26-0202-11ea-b1aa-00505687af0b:1-1858311

Bin log cache setup:

Bin log cache size: 32768
Max bin log cache size: 18446744073709547520
Binlog format: ROW

We do have tables where average row size is above bin log cache size (around 88K on average).
Not sure where to look, doesn't look like any memory or disk issues on the server. We have been running this setup for a while but with increased load of late.


texts: MyISAM table is not working as expected in MySQL


texts: Currently looking into reindexing our sql database, and cannot find any information on whether or not the procedure cache would be effected. Any help or information would be great. We are using SQL Server 2005 as our DBMS.


texts:
I search a way to export an access 2003 database to sql server express 2008.

the export is on la (other computer than the server).
the computer have office 2003.
the computer doesn't have admin right (cant install things who needs admin)

access sql migration said that he cant(because 2008 is too new)

whats the better(easyer and shortway) to export the db?


texts:
I'm dealing with a quite large SQL Server database and encountering a few problems.

The database is quite new and I've been importing data into the tables, and it's making the whole server go quite slow. The database is now 15GB and in 1 table for example it has just 4 columns, but 122 million rows and takes up 3.5GB. Another table has about 35 columns with 3 million rows and takes up about 1.5GB.

Whenever I open one of the big tables, or start using it with my MVC3 application, it nearly cripples the server; the the memory usage hits max and it starts running really slowly. (Server spec: Pentium dual core E6300 2.8GHz, 2GB RAM, Windows 2008, SQL Server 2005, one disk).

This is really the first dealings I've had with this amount of data so have been adding indexes to try and speed things up but once the memory hits the max level, I've had to restart the server in order for it to work at normal speed.

Anyway, I don't feel that 15GB is that big - surely 2GB RAM could handle it? I've not even finished adding the first round of data so it's going to get considerably bigger.

Does anyone have any advice for how it could be set up better and be more efficient. All PK's and FK's have been set up and I've added indexes to the large tables to see if that helps any but it's eventually started going slowly again. BTW, in the database properties, it says 'Space Available - 1596.96MB' How do you increase that figure?


texts: Does a DDL in Oracle affect other transactions?

texts:
I understand that DDL statements in Oracle have an implicit commit before and after the statement, which obviously affects the current transaction.

However, does a DDL statement in any way affect other transactions running against that table (assuming the change is compatible with the DML running against the table)?

For example, if I decide to modify a varchar2(10) columnn to varchar2(20), or add a new column altogether, or drop an obsolete, no longer used column -- are other transactions going to be happy about it, or is there going to be any sort of contention or interruption of running transactions?


texts: How do I fix an Orphaned User in Log Shipped version of a SQL Server 2008 database

texts:
Forgive my greenness.. but would appreciate some help on this...

We are doing development work on an instance of SQL Server 2008 and have multiple databases on that server that are core to our application along with a particular user that in some cases owns database, and in some cases can just run queries against them.

There is a particular database that resides on a production server that we don't want to touch, so we've set up log shipping to replicate its data to our development box. That database is properly restored on our development box and receiving ongoing updates.

On the production box, we added the same user that's need on our development box to the database that's doing the log shipping

When we try to query the log shipped version of database (read-only) from our application on the development box, our user can't access the database under the "current security context."

I ran a stored procedure that verified that my user is indeed orphaned...

I've run into this scenario before when restoring databases to a different machine, and have run a stored procedure to fix the "orphaned" user; however, because the development box's version of the database is read-only the stored procedure won't work.

Any suggestions on how to deal with this?


texts: migrate MySQL database with MySQL Workbench CE failed on table with blob data

texts:
I am using MySQL Workbench CE to migrate one schema from a server to another, all table is migrated successfully, except one table with blob data.

The error message is :

Found record bigger than max_allowed_packet.

I have set the max_allowed_packet to 1M on target server, and restarted MySQL, but still got the same error.

And I checked data in that table:

select max(OCTET_LENGTH(file_Data)) from attachment;
------
680960
select sum(OCTET_LENGTH(file_Data)) from attachment;
------
5165578

How can this be fixed?


texts: Oracle DB: Can one db file be shared between table space

texts:
Can one db file be shared between table space?

I am deleting temp table space as below,

Get all the files in  table space:

SELECT FILE_NAME
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = 'TEMPRM_TEMP';

Drop all the files in the table space:

ALTER TABLESPACE TEMPRM_TEMP
 DROP TEMPFILE '/tmp/TEMPRM/create/TEMPRM/datafile/o1_mf_temprm_t_bw3yo9lv_.tmp';

Drop the actual table space:

DROP TABLESPACE TEMPRM_TEMP INCLUDING  CONTENTS AND DATAFILES;

Are there any harm with this procedure ?


texts: Postgresql how to return nested type in a function

texts:
I'm trying to return nested type with a function, but always end up with some error.
With example below I get:
select mytest();
ERROR:  malformed record literal: "test3"
DETAIL:  Missing left parenthesis.

create type nestedtype as (test1 text);
create type maintype as (test2 text, other nestedtype);

CREATE OR REPLACE FUNCTION mytest() returns maintype as $$
  SELECT 'test' AS test2,
  ((select 'test3' as test1)::nestedtype) AS other
$$ LANGUAGE sql STABLE;

What am I doing wrong?
How can I return nested type used together with aliases?
In the end I need to return multiple fields from nested type, so it's not possible just switch to text, just in case such suggestion would come up.


texts: Boost STMM in Db2 by mimicking values of previous execution

texts:
Let’s say I have two kinds of workloads / processes that execute at different times (i.e. daily workload, nightly workload). And, the STMM is activated. The database is not alone in the instance, and it could have other instances as well with STMM.
When the first workload finishes, the bufferpools were adjusted to this first workload by the STMM. However, time after, when the second workload starts, the bufferpools. are readjusted for the second workload. And this scenario could happen continuously.
The problem is that the memory was well adjusted at the end of the workload, but this could take a lot of time to arrive to this value. Also, this adjustment is “destroyed” when the workload changes. And finally, none of the workloads has a good memory tuning at the beginning.
This I want to do, and the origin of my question:
If a take the memory values at the end of the workload / processes, from:
db2pd -db sample -bufferpools
db2 -x "select 'alter bufferpool ' || BP_NAME || ' size ' || BP_CUR_BUFFSZ || ' automatic;' from TABLE(MON_GET_BUFFERPOOL('',-2)) as bps where BP_NAME not lie 'IBMSYSTEMBP%'"

And then, when the workload will start again I apply the values, will be STMM still working fine? (From the output of the previous SQL).
For example:
db2 alter bufferpool ibmdefaultbp 1200 automatic
db2 alter bufferpool my_16k_bp 2000 automatic
db2 alter bufferpool my_32k_pb 5000 automatic

Let’s say, I include the alter bufferpool as part of the process (the ETL, the batch, etc.), at the beginning.
Will it boost the workload because the bufferpools and other memory heaps will be adjusted for that workload from the beginning? Is it good for the database? Will it help to adjust quickly the memory for previously executed workloads?


texts: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

texts: I am getting this error as a result of performing Insert and Select operation which involves calling of number of pl/sql procedures and functions. I am not getting exact reason. I tried optimizing queries I reduced fetch count of rows as well as reduces Inserting number of rows. What is the exact reason for this?


texts: How unsafe is to use ctrl+c or ctrl+d in mysql prompt?

texts:
How unsafe is to use ctrl + c when in mysql prompt whether to interrupt a hanging statement or ctrl + d to exit?

When I do ctr + d for exit mysql can see a ¿special character? typed besides Bye

mysql> ^DBye

Doing ctrl + c for exit mysql

mysql> Ctrl-C -- exit!
Aborted

Doing ctrl + c to cancel or interrupt a query with a huge row impression

mysql> SELECT * FROM db.table ORDER BY column DESC;
........
........
........
........
Ctrl-C -- sending "KILL QUERY 126537" to server ...
Ctrl-C -- query aborted.

But when trying to re-execute an improved query

mysql> SELECT * FROM db.table ORDER BY column DESC LIMIT 10;
ERROR 1317 (70100): Query execution was interrupted

Need to execute again and no error's returned.

I want to know if there's some security or safety stuff related to this.


texts: How do I turn off ALTER DATABASE script output in VS 2012?

texts:
I am using SQL Server 2005 in conjunction with Visual Studio 2012 and a SSDT database project.

When I publish my VS project and generate the script to run against my database, VS includes the following in the script.  I really don't want the script to go changing database properties, particularly the PAGE_VERIFY setting.  I notice that the script doesn't set the properties back the way it found them.  How do I turn this off?  I have spent time in the Tools->Options dialog and I just don't see the setting.

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL
        WITH ROLLBACK IMMEDIATE;

END
GO

IF EXISTS (SELECT 1
       FROM   [master].[dbo].[sysdatabases]
       WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET PAGE_VERIFY NONE
        WITH ROLLBACK IMMEDIATE;
END
GO


texts: What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

texts:
We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on.

We are using partitioning for this and the tables are innodb tables with 'innodb_files_per_table=ON'.

While doing this we are placing the partitions containing old data to a directory located in some other volume.

But a restriction in the form of 'every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is also a unique key.)' is a major road blocker in our activity.
(https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html )

Here for partitioning we have to use a particular column which in most of the  cases is not included in every unique key created on the table.
We can  not even change the key structure of the tables as it may impact the operation of applications.

So , we have to opt for other practices to archive the data. Any suggestions on this will be really helpful.

Sample scenario:
Table:

CREATE TABLE `tbl` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` int(11) NOT NULL AUTO_INCREMENT,
  `col3` int(11) NOT NULL DEFAULT '0',
  `col4` varchar(60) NOT NULL DEFAULT '',
  `col5` varchar(60) DEFAULT NULL,
  `changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
   CURRENT_TIMESTAMP,

   UNIQUE KEY `uniq1` (`col2`),
   UNIQUE KEY ` uniq2` (`col1`),
   UNIQUE KEY ` uniq3` (`col4`,`col3`),
   UNIQUE KEY `uniq4` (`col5`,`col3`),
   KEY `key1` (`changed`),
 );

We are partitioning  w.r.t  col1.

Query:

alter table tbl partition by range(col1)( partition p0
values less than (10000000) data directory = '/partitions',partition p1 values less than
(20000000) data directory = '/partitions',partition p2 values less than
(30000000),partition p3 values less than (maxvalue));

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning
function


texts:
According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38).  According to the same document, NUMBER(38) is supposed to be a NUMBER with a precision of 38 and a scale of 0. But in practice, I am seeing a NUMBER with data_precision set to NULL and a data_length set to 22.

SQL> create table ltheisen.silly ( id int );

Table created.

SQL> select substr(data_type,1,10), data_length, data_scale, data_precision from
 all_tab_columns where owner='LTHEISEN' and table_name='SILLY' and column_name='
ID';

SUBSTR(DATA_TYPE,1,10)                   DATA_LENGTH DATA_SCALE DATA_PRECISION
---------------------------------------- ----------- ---------- --------------
NUMBER                                            22          0

What gives?

Also, I am trying to do a generic conversion from Oracle to ANSI.  Is there any way to determine from the metadata that the NUMBER was created as, or fits into an INT?

Lastly, I tried creating with BINARY_INTEGER which appears in the documentation, but the create statement fails:

SQL> create table ltheisen.silly ( id binary_integer );
create table ltheisen.silly ( id binary_integer )
                                 *
ERROR at line 1:
ORA-00902: invalid datatype

What am I missing?

-------------------- UPDATE -----------------------------

Reading a little deeper gives this gem:

  Specify a floating-point number using the following form:

  NUMBER

The absence of precision and scale designators specifies the maximum range and precision
for an Oracle number.

So since the datatype is NUMBER and there is no precision specified, we can assume it is maximum precision?


texts: Does querying the sys entities and DMVs lock the underlying tables and what happens when SQL Server tries to write to them?

texts:
If I do a vanilla SELECT * FROM sys.tables or SELECT * FROM sys.dm_exec_query_stats are those tables getting locked since I'm reading data from them?

If so, what happens when SQL Server ties to write to them while I'm locking them e.g. when a new table is created?...does the table creation actually hang until the read lock is released?


texts: OUTER JOIN with join condition causes unexpected results

texts:
SQL Server 2008 R2, but likely behaviour is found on all other versions of SQL Server as well.

This may seem obvious, but to me it seems like a bug.

The following query gives unexpected results, here is the setup:

CREATE TABLE #Base (
Key1 int,
RefDate date
)

CREATE TABLE #JoinTable (
    Key1 int,
    RefDate date
    )

INSERT INTO #Base
SELECT 1, '2012-05-05'
UNION
SELECT 2, '2013-06-06'
UNION
SELECT 3, '2014-07-07'
UNION
SELECT 4, '2015-08-08'
UNION
SELECT 5, '2016-09-09'

INSERT INTO #JoinTable
SELECT 4, '2012-05-05'
UNION
SELECT 5, '2013-06-06'
UNION
SELECT 6, '2014-07-07'
UNION
SELECT 7, '2015-08-08'
UNION
SELECT 8, '2016-09-09'

The following query executes as I would expect, returning the 3 rows that only occur in the base table:

SELECT *
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1
WHERE j.Key1 IS NULL

Now I would like to restrict the rows to those before 1st of Jan 2014. As I understand it, I can take the two approaches below, both should be perfectly viable:

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1 AND b.RefDate < '2014-01-01'
WHERE j.Key1 IS NULL

SELECT b.*
FROM #Base b
LEFT OUTER JOIN #JoinTable j ON b.Key1 = j.Key1
WHERE j.Key1 IS NULL AND b.RefDate < '2014-01-01'

However, only the 2nd query returns the data that I want. The 1st appears to ignore the added join condition after the AND in the join line.

Why?

(sql fiddle link here)


texts: Why does user not have access to database?

texts:
I have a user ls_readonly which is supposed to have db_datareader privileges on several databases. I thought I had set it up right:

But when I connect to the server as ls_readonly and try to open the db in the Object Explorer, I get an error:

  The database wtest is not accessible. (ObjectExplorer)

I open a query window in master and try run:

use wtest

This responds with:

Msg 916, Level 14, State 1, Line 1
The server principal "ls_readonly" is not able to access the database "wtest" under the current security context.

What am I missing?

UPDATE: here's a clue. If I delete ls_readonly as a user from under the database's security context, then I go to the user under the server's security context and under "User Mapping" I grant access to the database, then it starts working.

It could be that the database was originally restored from a different server, which also has a ls_readonly user. I guess then that the user identification is not based on the username?


texts:
I try to connect from my little Java class to Oracle11g database and connection was refused with a SQL error.

From client (SQL Developer) connection works.

This is the ERROR when I try to run java class:

java.sql.SQLException:

ORA-00604: errore riscontrato in SQL ricorsivo livello 1
ORA-20001: Denied!  You are not allowed to logon the database
ORA-06512: a line 19

    at oracle.jdbc.driver.T4CTTIo`enter code here`er.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:389)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:382)
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:675)
    at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:448)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:383)
    at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:776)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:432)
    at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:553)
    at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:254)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:528)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at DBConnection.setDBConnection(DBConnection.java:48)
    at Main.main(Main.java:31)

This is the code of my connection class:

String host = "xxx";
String service = "xxx";
String username = "xxx";
String password = "xxx";

        if(host != null && username != null && password != null) {
            // Establishing DB Connection
            try {
                connection = DriverManager.getConnection(
                        "jdbc:oracle:thin:@" + host + ":1521/" + service , username, password);
                ...


texts: This is just for test purpose. I'm taking backup (INNODB DB) with --single-transaction and from another terminal I'm running alter table on one of the table of same DB on which dump command is running.Altered table is getting dumped with altered structure but DATA is missing.I repeated this test 3 times and same result is there. Just wanted to know this is the way it works? Please give some explanation. Thanks in advance.


texts: Potential issues migrating from SQL Server 2014 to 2012 by scripting tables and data?

texts:
I have a very simple question.

What would be the typical "gotchas" involved with scripting all the data to migrate a 2014 SQL server database to 2012?

Is this a pretty solid process? Are there potential issues of the ordering of scripts being messed up based on FK relations and other rules?

Have there been times of "Oh man, didn't see that coming" some time later when you run into some type of conflict because of the migration that wasn't caught previously?

With business critical data, is this unwise to do? It's a cost/benefit situation of upgrading and licensing for a newer SQL.


texts: As the title said, since we can convert any weak entity set to a strong one by adding the appropriate attributes, then why do we need weak entity sets?


texts: Joining three tables with null values in SQL

texts:
I have a BOOKS table, a USERS table, and a RATINGS table (with a book id, a user id, and a rating value).
For each book, I would like to be able to get an array with all the users and their rating for the chosen book BUT more importantly, if the user has not rated the book, I still want it in the array but with a rating value of null, as follows :

user
book
value

1
1
3

1
2
null

2
1
null

2
2
1

And so on..
The closest way I could get to it was by doing :
SELECT u.id as user, b.id as book, r.value
        FROM users u, books b
        LEFT OUTER JOIN ratings as r ON r.book_id = b.id
        ORDER BY b.id ASC

But if I try to add LEFT OUTER JOIN ratings as r ON r.book_id = b.id AND ON r.user_id = u.id as my final clause, it obviously doesn’t work. Wrong result: it gives me the first rating found for each book, not the one related to the user.
Any idea how I could achieve this?


texts: Can an SQL Job Deletes itself after its end date?

texts:
I have created a job which will sets up its start date and end date automatically with a dynamic value.

For example:

Job will run for eight days and at eighth day it needs to automatically deletes itself irrespective of its status.

Is there a way to code it while creating or scheduling ?


texts: Raise error if BCP command fails to dump data into a file

texts:
Using the BCP command I am generating files of SQL Server DB tables. The BCP command creates an empty file for each table when it's unable to dump data into the files. This can be caused by an error in query written or an empty variable is passed to the BCP command.

Is there any way we can capture these events as errors and make it return some error code?

I am performing this from a stored procedure. Is there any way I can handle this in SP?


texts: Why does my database using the simple recovery model have such a large transaction log?

texts:
Not a duplicate with:

Transaction Log is big! But simple recovery mode is chosen!
Why is my transaction file growing when db is in simple mode and there are no waiting transactions

Reason: My log is not big, but empty. It's big, but in use.

The Problem

I have a small database (100 MB) which gets hit with a lot of small transactional workloads throughout the day. I've watched the transaction log continue to grow despite seemingly understanding how to cap it. Clearly I'm missing something.

The database is in simple recovery model (I can't guarantee it wasn't in Full at one point when it was first created, but it has never been put under load in Full).
DBCC SQLPERF(Logspace) says:

| Log Size (MB) | Log Space Used (%) | Status
--------------------------------------------
| 927.2422      | 94.72562           | 0

DBCC LOGINFO says:

There are 39 rows (VLFs), all of which have a Status of 2.

DBCC opentran says:

  Oldest active transaction:
    SPID: 51
    Name: user_transaction
    Start time   : Mar 11 2019  7:27 PM

note: Given my server time, this transaction is about five minutes old.

I can't understand why every VLF is in use when my application only creates - at most - about a half dozen short-lived transactions, albeit very frequently.

I've tried killing the sole application process which connects to this database, but it didn't free up the transaction logs.

Workaround

I did a full backup just to scratch that itch and it freed up the entire log space... so... I don't know. I'll have to see if it grows excessively again.


texts:
I just wanted to configure "Resource Governor" on one of my production database server, where we have implemented AlwaysOn.

Many applications like SSRS, web App and SSIS connect to the database server.

I'm able to get application name for SSRS, web App and SSIS using APP_NAME() function, but not able to get application name for AlwaysOn processes.

Is there any way we can identify it, so that we can monitor/restrict how much resources being/will be used by AlwaysOn through resource governor.


texts: What is the best way or alternative for indexing an encrypted column in MySQL?

texts:
So in Oracle, one can use function indexes.  MySQL unfortunately, does not have function indexes (as far as I know).  Can someone share how they were successfully able to optimize a query that requires encryption on the very column that needs to be indexed in order to optimize the query?

For example, say I have the following query:

select some_thing from classified_table
where user_name = AES_ENCRYPT(user_name, v_EncryptKey)

If the user_name column is the only appropriate column for indexing, is there a way to create an index on the classified_table for user_name? Assume classified_table contains all mentioned columns.


texts: mysql - query to fill in missing datetime values

texts:
I have a Table similar to:

CREATE TABLE `stats` (
    `stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `stat_Start` timestamp NULL DEFAULT NULL,
    `stat_values` smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (`stat_ID`),
    );

It contains a list of events with the time it happenned, and the value.

I am now looking to generate a report from this similar to:

SELECT
    LEFT(stat_start,16) AS `time`,
    AVG(`stat_values`) AS `data`
FROM stats s
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16)
ORDER BY stat_start;

In other words, get the Average value for all entries per minute for the past hour.

However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute.
Is there a way of achieving this in MySQL?

so I might get something like:

'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --> row for which no events were recorded
'2017-04-07 09:03', 132


texts: Is SQL Server 2008 Change Data Capture (CDC) possible NOT to keep record when inserting?

texts:
SQL Server 2008 introduces a new feature for data auditing. The basic operation of this feature is as below:

When CDC is enabled on a table, SQL Server creates another table
(so-called cdc table here) that contains all columns of the original
table plus some extra meta-data column.
When insert happened, the cdc table kept a copy of record.
When update happened, the cdc table kept before changed and after
changed records.
When delete happened, the cdc table kept before changed record.
CDC use SQL Server Agent to monitor the log file and write the
changes to the cdc table
Typical size of the meta-data of each record in cdc table is about
51-bytes.

Eg. After 1 insert and 1 update actions
The original table kept 1 record
The cdc table kept 3 records (1 insert, 2 records per each update)

Assume the image size is 300KB, the total size is about 1,200 KB. It impacts the disk storage and disk IO.

Is it possible NOT to keep cdc record for a type of action (ie. insert)?

Is there any alternative for auditing Image that keeps less copy of records?


texts:
I know that the documentation states:

  Prepared statements only last for the duration of the current database session.

Is there any way way to either

save a prepared statement
create code which will regenerate a prepared statement?


texts: Does adding a postgres index concurrently take up disk space gradually or all at once?

texts:
I'm adding a unique index (concurrently, over the course of hours or maybe even days, on a column of 5 billion bigints, all of them NULL) on a Postgres 9.6 server that's pretty low on disk space. I can see that it's increased disk utilization from 94% to 95%, and I'm not sure if it's set aside all the space it wants, or if it's going to write more to disk later and take down the server over the weekend.
Does Postgres set aside disk space for indexes in advance, use more disk space gradually over the entire course of indexing, or write intermittently?


texts: What is the difference between a connection and a session?

texts: What is the difference between a connection and a session and how they are related?


texts:
I have an Images table where that table has these basic columns —

ImageId (int)
VotesUp (int)
VotesDown (int)

Each image can get upvotes (up , down , retract vote)

The upvotes table has :

ImageId (int)
UserId (int)
Score (int )   [-1,0,+1]

So if —

A user inserts +1 score then  ( if not exists) I add a new row to upvotes table & update the value in the Images table ( for quick fetching)
A user changes its score from +1 to 0 then decrease votes up
A user changes its score from +1 to -1 then decrease votes up and increase
votes down
A user changes its score from 0 to +1 then increase votes up
A user changes its score from 0 to -1 then increase votes down
A user changes its score from -1 to 0 then decrease votes down
A user changes its score from -1 to +1 then decrease votes down  and increase votes up

It's a pretty simple logic.

Here is the trigger on the upvotes table :

alter TRIGGER [dbo].[UpvotesChanged]
ON [dbo].[Upvotes]
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS( SELECT 1  FROM   DELETED ) --update
BEGIN

UPDATE imgs
SET    VotesUp = CASE

                    WHEN deleted.Score = 1 AND INSERTED.score =0      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesUp, 0) -1
                    WHEN deleted.Score = 0 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesUp, 0) +1
                    ELSE ISNULL(VotesUp, 0)
                END
                ,
    VotesDown = CASE

                    WHEN deleted.Score = 0 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = 1 AND INSERTED.score =-1      THEN ISNULL(VotesDown, 0) +1
                    WHEN deleted.Score = -1 AND INSERTED.score =1      THEN ISNULL(VotesDown, 0) -1
                    WHEN deleted.Score = -1 AND INSERTED.score =0      THEN ISNULL(VotesDown, 0) -1
                    ELSE ISNULL(VotesDown, 0)
                END
FROM   Images imgs
    JOIN DELETED
        ON  imgs.ImageId = deleted.ImageId
        JOIN INSERTED ON imgs.ImageId = INSERTED.ImageId

END
ELSE
--insert
BEGIN
UPDATE imgs
SET    VotesUp = CASE
                    WHEN INSERTED.Score = 1
                    THEN ISNULL(VotesUp, 0) + 1
                    ELSE VotesUp
                END,
    VotesDown = CASE
                    WHEN INSERTED.Score = -1
                        THEN ISNULL(VotesDown, 0) + 1
                    ELSE VotesDown
                END
FROM   Images imgs
    JOIN INSERTED
        ON  imgs.ImageId = INSERTED.ImageId
END
END

It's working as expected.

So where is the problem ?

Question :

I think this trigger can be simplified without all these if's/cases . I just can't see the simplification.

Is it possible to simplify this trigger?


texts: How to get the column names of a RECORD?

texts:
I have a function that declares:

DECLARE r RECORD;

The data on this record varies and at some point I need to know the column names.

How do I get the column names of a RECORD in another record ?


texts: Why does an SSIS package fail when executed from the IS Catalog?

texts:
When executed via the SQL Server Agent under the same user context, is there any reason a package would successfully run when using the file system (i.e. DTExec.exe), but fail to execute when launched via the SSIS Catalog (i.e. ISServerExec.exe)?

I have been helping to troubleshoot an SSIS package which fails with an "out of memory" error (generated by a 32-bit PostgreSQL ODBC driver) when launched from the catalog on an SQL Server 2014 server (on both CU4 and CU6).

This package succeeds under the following circumstances:

It is executed from Data Tools for BI (DOMAIN\MYUSER)
It is executed as an SQL Server Agent Job with a Package source of File system (DOMAIN\MYSERVICEACCOUNT)

It fails under the following circumstances:

It is executed from an Integration Services Catalog via right-click -> Execute (DOMAIN\MYUSER)
It is executed as an SQL Server Agent Job with a Package source of SSIS Catalog (DOMAIN\MYSERVICEACCOUNT)

There are no configuration overrides in place, the job is running in the same mode (32-bit) in all cases, and all tests are being performed directly on the server. Approximately 20GB of memory is free on the server, and the working set of the package is only around 1GB.

We are using MaxConcurrentExecutables as a workaround, which allows the execution to complete even when run via the IS Catalog, and are configuring 64-bit ODBC drivers. However, I don't understand why the behaviour is different in the first place.

As I understand it, the IS Catalog uses a CLR assembly to launch the package execution via ISServerExec.exe, as opposed to DtsDebugHost.exe (Data Tools), or DTExec.exe (SQL Agent). The app.config files are unmodified from their defaults and I can't see any differences between them.

Why would a package fail only when it is executed via the IS Catalog?


texts: Unpredictable performance of queries against snapshot

texts:
I've got a basic reporting solution working where we have a collection of views that get exported to CSV files on a daily basis. For numerous reasons, we don't run the reports against the transactional db, but against a snapshot that we temporarily create and then drop once the reports are complete.

These reports are pretty simple and run fine on dev machines, even with millions of rows.

On our test environment, the performance is all over the place, sometimes taking a couple of seconds (same as dev) but often taking over a minute. I've run a few scripts to try and identify the cause, and the problem is always a wait type of PAGEIOLATCH_SH.

The disk IO at these times is low (2-5mb/sec), the CPU is at about 5%, no other queries need to be happening for the problem to occur, and the database is small (2gb) compared to server memory (32gb). The reports are run on the same machine, but write to a different physical disk.

Is the fact that the database is in an Always On Availability Group likely to be causing an issue? Would it help for the snapshot to be on a different disk from the master?

Any pointers on what the problem might be or how to investigate would be much appreciated!


texts: Can spatial types be used to store the dimensions of 3-dimensional objects?

texts:
Money is made of dollars and cents, but stored as one field because dollars and cents are properties of the same value, not values themselves (you can use simple math to convert between the two). Weight is made up of pounds and ounces, but is also stored in a single column for the same reason.

Likewise, it seems that x,y and z dimensions are really just one value as well, with three properties since there is little need to know x without y and z in an application (but you may want to query on them discretely).

In OOP, we usually create objects to handle special primitive types like dates, money, weights, and dimensions. But unlike the other primitives, you can't simply add dimensions together and stuff them in a decimal column and hope to get them back out again.

You could store dimensions in a varchar with a delimiter, like 7;4,2;2, but this would make any queries for certain values difficult. ie. "get the record that has the longest (any dimension) side".

Of course, the conventional approach of storing x,y, and z in separate decimal columns would also have issues with a "get the longest side" query, since the database wouldn't treat them as one value collection. (You would have to get the longest of each, then the longest of the three results).

So I'm looking into the spatial data types which seem designed for map making, but it occurs to me that a data type designed to hold coordinates in 3d space would probably work just as well for defining 3d objects since it would still be a collection of x, y and z treated as a cohesive unit in one field with the ability to query coordinates separately.

Unfortunately, I'm having a hard time finding good information on using spatial types and I'm not sure if they can be used in this manner.

Can spatial types be used to store the dimensions of 3 dimensional objects?


texts: Sudden periods of performance degradation

texts:
For the past couple of days, Ive been observing periods of huge performance degradation. CPU usage on our master MySQL server (Percona Server (GPL), Release rel24.1, Revision 217, InnoDB version 1.1.8) will shoot up to over 70% and one group of queries will start becoming very slow. This will last for 4-5 minutes and then disappear, only to come back the next night with a new set of queries becoming slow. The following stats were captured during a recent occurrence of this happening:

Com_delete = 5.86666666666667 / sec
Com_create_table = 0 / sec
Threads_created = 1.61666666666667 / sec
Key_read_requests = 78.7666666666667 / sec
Bytes_received = 4766588.83333333 / sec
Qcache_not_cached = 0 / sec
Created_tmp_disk_tables = 224.45 / sec
Com_replace = 0 / sec
Innodb_buffer_pool_write_requests = 1537.2 / sec
Created_tmp_tables = 590.916666666667 / sec
Com_update = 82.0666666666667 / sec
Com_stmt_execute = 1053.75 / sec
Key_writes = 0 / sec
Innodb_row_lock_time = 0 / sec
Com_select = 38798.35 / sec
Connections = 3.55 / sec
Select_range = 1492.38333333333 / sec
Innodb_data_read = 24389768.5333333 / sec
Com_insert = 61.8666666666667 / sec
Table_locks_waited = 0 / sec
Bytes_sent = 66554017.7666667 / sec
Key_reads = 0 / sec
Sort_rows = 25265.05 / sec
Select_full_join = 0 / sec
Select_scan = 182.583333333333 / sec
Slow_queries = 1.06666666666667 / sec
Innodb_data_written = 3407197.86666667 / sec
Key_write_requests = 17.05 / sec
Innodb_buffer_pool_read_requests = 3687732.7 / sec
Questions = 41779.7166666667 / sec
Qcache_hits = 0 / sec
Threads_connected =
Threads_running =
Innodb_row_lock_time_avg =
Qcache_queries_in_cache =
Qcache_free_memory =

We can compare this to right now:

Com_delete = 3.88333333333333 / sec
Com_create_table = 0 / sec
Threads_created = 0 / sec
Key_read_requests = 1 / sec
Bytes_received = 3421724.13333333 / sec
Qcache_not_cached = 0 / sec
Created_tmp_disk_tables = 159.45 / sec
Com_replace = 0 / sec
Innodb_buffer_pool_write_requests = 989.65 / sec
Created_tmp_tables = 428.3 / sec
Com_update = 50.4166666666667 / sec
Com_stmt_execute = 728.4 / sec
Key_writes = 0 / sec
Innodb_row_lock_time = 0 / sec
Com_select = 28194.15 / sec
Connections = 0.866666666666667 / sec
Select_range = 1107.58333333333 / sec
Innodb_data_read = 19804706.1333333 / sec
Com_insert = 38.95 / sec
Table_locks_waited = 0 / sec
Bytes_sent = 48218433.8666667 / sec
Key_reads = 0 / sec
Sort_rows = 46135.2833333333 / sec
Select_full_join = 0 / sec
Select_scan = 125.633333333333 / sec
Slow_queries = 0.533333333333333 / sec
Innodb_data_written = 3794304 / sec
Key_write_requests = 0.6 / sec
Innodb_buffer_pool_read_requests = 2814013.88333333 / sec
Questions = 30203.2833333333 / sec
Qcache_hits = 0 / sec
Threads_connected =
Threads_running =
Innodb_row_lock_time_avg =
Qcache_queries_in_cache =
Qcache_free_memory =

Aside from the fact that the stats all generally seem higher, could there be some clue here as to what's going on? Besides running this stats script, are there are any other tools I can use to monitor what might be causing this instability?


texts:
When doing a mysqldump, some tables are being skipped because mysqldump treats them as View rather than Table although they're 100% tables.

So for some tables it looks like

-- Retrieving table structure for table services...
-- Sending SELECT query...
-- Retrieving rows...

and for some ones:

-- Retrieving view structure for table users...
-- It's base table, skipped

I saw this issue was already mentioned a few times however there's no solution.

  SHOW CREATE TABLE users;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
...
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8915 DEFAULT CHARSET=utf8

  SHOW TABLE STATUS LIKE 'users' \G;

*************************** 1. row ***************************
           Name: users
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 6638
 Avg_row_length: 713
    Data_length: 4734976
Max_data_length: 0
   Index_length: 2899968
      Data_free: 469762048
 Auto_increment: 8923
    Create_time: 2015-09-04 10:00:26
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

MySQL 5.5.41-0ubuntu0.14.04.1


texts: mysql replication: replicate all but one table to different database on different server

texts:
I got database1 on server1 and database2 on server2.

Now I need to replicate a single table from database1 to database2

Now I thought that this would work:

  replicate-ignore-table  = %
  replicate-do-table      = table
  replicate-rewrite-db    = "database1->database2"

but this does not seem to work as mysql does not accept % symbol with replicate-ignore-table directive. any ideas ?

EDIT:

I have tried :

  replicate-ignore-table  = database1.%
  replicate-do-table      = database1.table
  replicate-rewrite-db    = "database1->database2"

Now mysql is starting, but table is not being replicated.


texts: Generate a list from columns that do not contain a zero for each row


texts: External authentication using Oracle Standard

texts:
I want to authenticate my client connections externally, using a custom external application. Is this possible using Oracle Standard 11g? If so, how?

(our custom external application will not have access to the users password, it'll just relay the requisition elsewhere)

The only documentation I found speaks of OS authentication, or using a wallet.


texts: execute .sql script inside an anonymous pl/sql block which is invoked by a shell script

texts:
I got a shell script which connects to SQLPLUS and executes the .sql file like below:

 read a
 x=`echo $a | cut -d , -f 1`
 y=`echo $a | sed 's/.*,//'`
 c=`echo $USER | cut -c -2`"OPR"`echo $USER | cut -c 6-`
 sqlplus -s `echo $c`/`echo $c`@$OPERATIONAL_DB_NAME @dbl.sql $x $y

Below is the SQL (dbl.sql)

SET SERVEROUTPUT ON
DECLARE
x varchar2(100):='&&1';
y varchar2(100):='&&2';
BEGIN
execute immediate 'CREATE DATABASE LINK  SSS_DBL CONNECT TO ' || x || ' IDENTIFIED BY ' || x || ' USING ''' || y || '''';
@1_a.sql
@1_b.sql
@1_c.sql
END;
/
EXIT;

In the above sql, 1_a.sql,1_b.sql,1_c.sql are three sql files in which there are a set of insert statements, taking the data from a remote db, using the link created
above.

Now, I can not run these sql's inside the pl/sql block using @ symbol as written above. What is the other way to do this? If I use 3 procedures for each insert files, then, should
these procedures be run and compiled before using in this pl/sql, in each session or how should I use these?
I want to keep this script as independent of any session or db.


texts:
I am using Postgres 9.5. It is possible this is related to the changes in 9.6 pointed out by PostgreSQL not using index during count(*), but I cannot be sure yet.

I have a table that has a character varying column called text. I am attempting to optimize queries which have to perform a regex on this column.

This often looks like

select * from table where text ~ 'some|key|words|we|want';

or the inverse

select * from table where text !~ 'some|key|words|we|want';

sometimes in joins

select category, count(*)
from other_t join table on (other_t.table_id = table.id)
where table.text ~ 'some|key|words|we|want';

select category, count(*)
from other_t join
    (select id
     from table
     where table.text ~ 'some|key|words|we|want') as ti on (other_t = ti.id);

Notice that the regex is always the same.

I do not want to index the column directly because AFAIK indexing text columns results in a relatively huge index that would just be a waste of space. This rules out a partial index per the linked post above. So, I've been trying to use an expression index, which I should be able to do because the regex is always the same.

However, I can't get Postgres to use the index!

I've done a vacuum analyze, I've tried every type of query I can imagine, but it always just does a Seq Scan.

The column averages ~85 characters and typically only has one or two of the keywords we're looking for if it is a match.

Any ideas?

I am not interested in full keyword search. We have a finite, stable list of words we're looking for. I don't want to make an enormous trigram/ts_vector index on this text column when all I really need is an expression index that Postgres decides to use.

We cannot eliminate the text column because it has other uses. We have already thought of adding a column which is essentially a cache of this query, but that's just ugly. If that is the best option, so be it, but we really should be able to use an index here.

Index Creation

create index text_col_idx on table ((text ~ 'some|key|words|we|want'));

Explain Analyze

explain (analyze, verbose) select id from table where text ~ 'some|key|words|we|want';

Results

Seq Scan on public.table  (cost=0.00..405624.24 rows=5190575 width=4) (actual time=0.113..52582.259 rows=5113014 loops=1)
  Output: id
  Filter: ((table.text)::text ~ 'some|key|words|we|want'::text)
  Rows Removed by Filter: 4728206
Planning time: 10.938 ms
Execution time: 52766.607 ms


texts: Partial mysqlbinlog on latest binlog file

texts:
I'm trying to do a partial mysqlbinlog on the latest binary log file at a certain position therein (trying to extract just one query).

mysqlbinlog --start-position=1234 --stop-position=1234 /var/lib/mysql/mysql-bin.000021

The output contains:

  Warning: this binlog is either in use or was not closed properly.

How do I force MySQL to start logging in the next binlog file (i.e. mysql-bin.000022) and release the original file so I can use mysqlbinlog it?

Is there another solution to this problem?


texts: I created an index approx 5gb in size but the file system doesn't show any difference in free space

texts:
On a table with many millions of rows and two existing indexes (one clustered and one non-clustered primary key index) I created a third non-clustered non-unique index.   Before creating this index, in table properties the index space showed about 4.9gb.  After creation it showed approximately 9.9gb

I checked the disk free space in the OS of each disk before doing this.   I checked again after and there is virtually no significant change (certainly not to the tune of 5gb) on any of the disks.    I have checked again hours later and still no change.

I'm suspecting/assuming that some data file had a lot of free space inside it, and the new index simply occupied that already allocated space.  Is this likely to be what has happened?

Should I be concerned?


texts: It seems the SSMS of Sql server 2012 cannot print the SQL statements with syntax color anymore. Is it a bug? Does it have any workaround?


texts: phpMyAdmin how to run an output query? run a batch?

texts:
In phpMyAdmin the code below just outputs a list of sql statements for each table:

SET @DATABASE_NAME = 'my_db';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

How would I actually execute this to change all the tables? I've seen people say "batch" but I'm not sure if that is what I'm looking for.


texts: What permissions are required for complex cross-schema materialized view in Oracle?

texts:
I have a complex cross-schema query that I'd like to create as a materialized view in Oracle 19c (although the database has been ported forward from numerous historical versions of Oracle).  This includes joining a subquery to 5 additional tables across three total schemas, and then unioning to another copy of the query against 3 different schemas.  The account creating the view has DBA and CONNECT roles, and has been granted GLOBAL QUERY REWRITE, UNLIMITED TABLESPACE, CREATE PROCEDURE, CREATE MATERIALIZED VIEW, and CREATE TABLE system privileges.  It also has explicit SELECT permissions on all of the tables in the query.

I can run the query itself without issue.  However, when I add the setup for a materialized view:

CREATE MATERIALIZED VIEW <account_schema>.<materialized_view_name>
BUILD IMMEDIATE
REFRESH FORCE
ON COMMIT
AS
<SELECT...>

I get ORA-01031, or "insufficient privileges".  What am I missing?  Is there a way to query for a more detailed report of what's missing?  It points to a specific location in the query (the table in the last join), but I don't see any difference between it and the other tables.


texts: MySQL - Store date as a value with an ID?

texts:
I've got a database with the following structure:

Elements table
+------------+---------------+---------------+-----+
| element_id |    user_id    | element_data2 | ... |
+------------+---------------+---------------+-----+

Tag categories table
+-------------+---------------+
| category_id | category_text |
+-------------+---------------+

Tag values table
+----------+------------+
| value_id | value_text |
+----------+------------+

Tag-element relationship table
+----+------------+-----------------+--------------+
| id | element_id | tag_category_id | tag_value_id |
+----+------------+-----------------+--------------+

An element can have multiple tags associated with it, even multiple values on the same category.

What the app does is to let the user put new tag_values as unique elements (categories right now are not user-editable, but may be in a future). As they are not associated to the user, if two users write the same value_text, then the same ID is assigned to it.

So, to have a data sample, the tag-element table - the most important here - would look something like:

+----+------------+-----------------+--------------+
| id | element_id | tag_category_id | tag_value_id |
+----+------------+-----------------+--------------+
|  1 |          1 |               3 |            9 |
|  2 |          1 |               3 |            5 |
|  3 |          1 |               6 |           13 |
|  4 |          2 |               6 |            2 |
|  5 |          2 |               5 |           15 |
+----+------------+-----------------+--------------+

The question

As such, there is a category called game_date, which stands for the date where the element (that is an event) is celebrated.

Should the game_date values be stored as the other values, so two equal values for a date have a unique ID, or its uniqueness makes its performance poor?

I mean, it can be possible for multiple events to be on the same date and hour, but I don't know if it would be better another kind of structure where that date is stored as a Date field on any of the tables above. Just as the game_date can be also other categories involving date (such as game_recorded, etc.).

Thank you!


texts: Why is MySQL running as a single process?

texts:
I have two MariaDB(InnoDB) linux servers. Even if my.cnf is almost the same they behave very differently. On the bad machine MariaDB is ver 10.0.16-MariaDB-1, on the good machine is 10.0.27-MariaDB-0+deb8u1.

They act on different same-size databases (around 3GB) similar in structure and usage. The bad machine has a few more clients (7 against 4) reading the data. DBs don't perform any calculation: they just provide storage.

Checking the OS processes with top/htop I see that the one that misbehaves has only ONE process, while the good one has 31 distinct PIDs. I could imagine that this is the bottleneck.

MySQLTuner returns a big difference for table cache hit rate: bad machine 6% (400/6K opened), good machine 118% (400/338 opened).

This is the configuration of the bad one.
Where can I set the max number of processes? Or is there something else I didn't take in account?

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

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

[mysqld]
#
# * Basic Settings
#
user        = XXXXXX
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
innodb_file_per_table  = 1
innodb_file_format     = barracuda

skip-name-resolve

bind-address        = XX.XX.XX.XX

#
# * Fine Tuning
#
key_buffer      = 16M
max_allowed_packet  = 128M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10

#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size        = 16M

#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log

expire_logs_days    = 10
max_binlog_size         = 100M

#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name

#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M


texts: Associate multiple tables with a single table

texts:
I have a RDBMS database and I need to associate a column in a new table to multiple other existing tables whose schema can't be changed.

The new table looks something like this,

UUID(pk)   PartId(pk) Somecolumn1 SomeColumn2
asdf56das  0001       val1        val2
u44ighhuh  0002       val3        val4
u44ighhuh  0003       val5        val6

I want to associate UUID column with data from other tables.

One method is to add a XML column to this new table which would store the associations as,

<tablename><pkcolumn1>value</pkcolumn1>
<pkcolumn2>value2</pkcolumn2>...........<pkcolumnN>valueN</pkcolumnN>
</tablename>

With this approach there is some performance cost involved in creating and parsing XML. Is there a better way to associate these tables?


texts: How to speed up an insertion from a huge table with postgres?

texts:
I have 5 tables in my database with respectively a size of 70Gb, 500Mb, 400 Mb, 110Mb and 20 Mb.

I want to create a new table that contains all columns of all tables, so I tried 2 queries, the first one is :

select into new_table as select .. from t1 , t2 , t3 , t4 ,t5 where t1.id2 = t2.id and t1.id3 = t3.id and t1.id4 = t4.id and t1.id5 = t5.id

and the second one is :

insert into new_table select .. from t1 , t2 , t3 , t4 ,t5 where t1.id2 = t2.id and t1.id3 = t3.id and t1.id4 = t4.id and t1.id5 = t5.id

Before executing these two queries on my big data tables, I tried both on a total 1G database, the first on took only 7s and the second one approximately 10 mn.

Now, executing the first one on my huge database, made my disk full even though I had 250Gb free space before running the query, and without finishing the query so I got the follow error :

ERROR:  could not write to temporary file: No space left on device

The second one, is taking a lot of time and consuming my free disk space slowly and, as the first one, not returning the result.

What are the difference between these two queries ? Is there a way to make the insert into non transactional so as I can follow my insert steps. And I guess Postgres uses logs (journalization) so is there a way to deactivate that in order to speed up the insertion ? or I should follow another method in order to get a desired result without filling up all disk.

Ps : No triggers, only a primary key on each table.


texts: Are `--events` and `--routines` really needed to get a `mysqldump --all-databases` of an entire mysql instance?

texts:
Several fine answers on here have already exquisitely argued that

mysqldump --hex-blob --events --routines --triggers  [...]  --all-databases

should be all - modulo storage-engine considerations - one needs to get a

  mysqldump of an entire mysql instance ... everything written in one file:
  table structures, indexes, triggers, stored procedures, users,
  encrypted passwords....

And I wanted to believe - and use - that; however, reading up on the manpage of mysqldump(1) (for MySQL 5.7.27) I find that:

  --events, -E

  [...]

  If you require events to be created with their original timestamp attributes, do not use --events. Instead, dump and reload
  the contents of the mysql.event table directly, using a MySQL account
  that has appropriate
  privileges for the mysql database.

... and that:

  --routines, -R

  [...]

  If you require routines to be created with their original timestamp attributes, do not use --routines. Instead, dump and reload
  the contents of the mysql.proc table directly, using a MySQL account
  that has appropriate
  privileges for the mysql database.

This leads me to believe that since --all-databases will also cause the mysql database to be included in the dump, and by that, the mysql.event and mysql.proc tables, that I would not have to include the --events and --routines options at all to arrive at an as-complete-as-possible dump.

What's more, including those options, I expect, would get me a less-than-complete dump instead because:

  The output generated by using --events contains CREATE EVENT
  statements to create the events. However, these statements do not
  include attributes such as the event creation and modification
  timestamps, so when the events
  are reloaded, they are created with timestamps equal to the reload time.

... and likewise:

  The output generated by using --routines contains CREATE PROCEDURE and
  CREATE FUNCTION statements to create the routines. However, these
  statements do not include attributes such as the routine creation and
  modification timestamps, so when the routines are reloaded, they are
  created with timestamps equal to the reload time.

Although, the mysql database would still also be included, so I'm not sure what the net effect combining --events and --routines with --all-databases will be in the resulting dump. I suspect it will depend on whether mysql will be dumped last, first, or somewhere in-between.

Does this reasoning make any sense or am I missing something?


texts: Optimistic concurrency check failure when updating linked server table with timestamp column

texts:
I’m trying to update a table in a database on a linked server using code like this:

declare @FirstName nvarchar(30);
declare @LastName nvarchar(50);
declare @AccountNumber nvarchar(30);

set @FirstName = N'John';
set @LastName = N'Smith';
set @AccountNumber = N'ABC001';

update RemoteServer.MyDB.dbo.Customer
set    FirstName = @FirstName,
       LastName = @LastName
where  AccountNumber = @AccountNumber;

The remote server is also a SQL Server instance and is defined thus:

EXEC master.dbo.sp_addlinkedserver @server = N'RemoteServer'
        , @srvproduct=N'SQL Server'
 /* For security reasons the linked server remote logins password is
         changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'RemoteServer'
        ,@useself=N'False'
        ,@locallogin=NULL
        ,@rmtuser=N'sa'
        ,@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'RemoteServer'
        , @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

The Customer table is defined as

CREATE TABLE [dbo].[Customer](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [AccountNumber] [nvarchar](20) NOT NULL,
    [FirstName] [nvarchar](30) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [DBTimeStamp] [timestamp] NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY NONCLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
        , IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

and has an index on the timestamp column

CREATE UNIQUE CLUSTERED INDEX [IX_Customer_TimeStamp] ON [dbo].[Customer]
(
    [DBTimeStamp] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF
        , SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF
        , ONLINE = OFF, ALLOW_ROW_LOCKS = ON
        , ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

When I run the query I get this error message:

The OLE DB provider "SQLNCLI11" for linked server "RemoteServer" could not UPDATE
         table "[RemoteServer].[MyDB].[dbo].[Customer]".
The rowset was using optimistic concurrency and the value of a column has been
         changed after the containing row was last fetched or resynchronized.

If I remove the index on the timestamp column, the query works as I’d expect.

How can I force the linked server query to use pessimistic rather than optimistic concurrency? I’ve already tried adding the tablockx query hint, and also loading the row in a select before running the update and using tablockx and holdlock. Wrapping the whole thing in a regular or distributed transaction also makes no difference.


texts:
I am designing database for an exam, and I am stuck. I do not know how to do it.

Here is the relevant information:

Student answers 10 questions.
Each question has 3 options (answers) and student chooses one.
Only one answer is correct, other 2 are wrong.
 Students can take this exam only once. They can not try again.
There will be no other exams, this is the only one.

I need help with database design.

What I have tried so far on my own:

Table Students

ID bigint (primary key, identity)
Name nvarchar(MAX)

Table Questions

ID bigint (primary key, identity)
TextOfTheQuestion nvarchar(MAX)

Table Answers

ID bigint (primary key, identity)
TextOfTheAnswer nvarchar(MAX)
QuestionID bigint (foreign key to Questions.ID)
isCorrectAnswer bit

Table StudentChoices

StudentID bigint (primary key, foreign key to Students.ID)
AnswerID bigint (primary key, foreign key to Answers.ID)

This is my personal design for learning purposes. I am trying to learn Entity framework + C# on my own.


texts: Stored procedure not running on the same database when using different login

texts:
I am trying to run a stored procedure on the same database using two different login users. While using the Windows authentication , I'm able to run the stored and proc and see all the data available. But while running the same stored proc using a specific user authentication, I am not able to view the data. The stored proc is running but returning 0 rows.

The  permissions for both accounts are the same.

Any suggestions on what might be the problem or how to approach in solving this issue?


texts: Where can I download jvm files for oracle on ubuntu

texts:
I want to install Java packages for oracle, and I found following files in the internet.

@?/javavm/install/initjvm.sql;
@?/xdk/admin/initxml.sql;
@?/xdk/admin/xmlja.sql;
@?/rdbms/admin/catjava.sql;
@?/rdbms/admin/catexf.sql;

But I could find javavm folder anywhere on my computer. How can I get it?