Accent marks/symbols not displaying correctly in MySQL database

Anytime there’s an accent mark (é or ‘) or symbol (&) in my database it displays as HTML code. The table CHARACTER SET is UTF-8 Unicode and the COLLATION is utf8mb4_general_ci, which should display those characters correctly. Any ideas why this is happening?

(MySQL version 5.7)

Current data

`company`| `firstname`| `lastname` ---------------------------------- A&T  | Patrick    | O'Brien 

Expected

`company`| `firstname`| `lastname` ---------------------------------- A&T      | Patrick    | O'Brien 

Thank you in advance!

on-the-fly decompress a flat-file database

I’m facing the following problem. I have a flat-file database (e.g. CSV). Since it’s relatively large to store in memory, I’d like to compress it.

Given a key, I need to return the uncompressed text (record of values).

So one naive idea is to tokenize the text into words and to have the mapping $ \text{word} \mapsto \text{codeword}$

Of course, this naive idea lacks of understanding of statistical properties in the data, that other compression algorithms exploits.

So the next thing I thought about is Huffman code. The problem I’m facing is that I’d like to find the optimal tokenization for the text. Let’s say that one column in the CSV file contains only the text “the fox jumped over the lazy dog”, it reasonable to want that the algorithm would tokenize this string as one token.

but then again, going over all possibilities isn’t a feasible task. Are there any algorithms that deals with this problem?

So to summarize, I need to:

  1. Compress my data once and to decompress on demand
  2. Return the requested value (a record) for a given key
  3. Decompression should be “fast enough”

Which algorithms fit my problem?

In particular, I’d like to know if Huffman code is a good option, and if so, how to tokenize the text.

Thanks!

How to improve performance for read only innodb MySQL Database

I have a particular task which is to maximize the concurrent performance. There is only one particular type of query, which is

select * from table where col1 between ? and ? and col2 between ? and ? 

I have created a composite index for (col1, col2). The table is about 20G in size and 100 million rows

However, even in peak concurrent requests, the CPU utilization for MySQL is only 30%. I have tried various techniques like increase max_connections, innodb_buffer_pool_instances but none of them are working.

How to maximize the configuration so that it can perform such read-only query to extreme?

update matching column value in anywhere in database sqlserver

I have assetid column in multiple table in my Database and Asset(main) table

I want to update all occurences of tables in all occurences of inserts in database assetid column where value has 1020 to 1

update AdminInterests set assetid = 1 where 1020 

I just want to how to apply this update for entire database all tables with one query

Linked Server from On-Premise to Azure SQL Database

I am using: SSMS-18.4 SQL Server-SQL 2019 CU3 Windows 10

I was able to create a linked server successfully from on-premise SQL 2017 to the Azure SQL database without exposing my password.

--Read the password from text file  DECLARE @password VARCHAR(MAX) SELECT  @password = BulkColumn FROM    OPENROWSET(BULK 'C:\Azure SQL Database - Where is my  SQL Agent\password.txt', SINGLE_BLOB) AS x     --Drop and create linked server IF EXISTS(SELECT * FROM sys.servers WHERE name = N'AzureDB_adventureworks') EXEC master.dbo.sp_dropserver @server=N'AzureDB_adventureworks', @droplogins='droplogins'; EXEC master.dbo.sp_addlinkedserver  @server = N'AzureDB_adventureworks',   @srvproduct=N'',  @provider=N'SQLNCLI',  @datasrc=N'ugdemotargetserver.database.windows.net',  @catalog=N'adventureworks';  EXEC master.dbo.sp_addlinkedsrvlogin  @rmtsrvname=N'AzureDB_adventureworks',  @useself=N'False',  @rmtuser=N'taiob',@rmtpassword=@password; GO 

But the password is not getting the correct value. I am getting a login failure.

Some of the error message:

Login failed for user 'taiob'.  (.Net SqlClient Data Provider) Server Name: .\SQL2019  Error Number: 18456  Severity: 14  State: 1  Line Number: 1 

If I hardcode the password it works fine. If I print the variable I can see the value is correct. It is not a firewall issues as I can directly connect from the same SSMS that I am running the code from.

Food Delivery Service Database Design Questions

For a Food Delivery application I am developing for a school project, I’m facing some problems during the ER model design stage. I have to fulfil many requirements but my main issues I’m having are the following:

A)

Restaurant

  1. Each order’s food items must be from a single restaurant, and each order is assigned to a delivery rider who will collect and deliver the ordered items to the customer.

I’ve learnt about ternary relationships and my current design doesn’t seem to be enforcing the constraint in (A). I’m thinking if a ternary constraint connecting Restaurant to “Ordered In” will be a better design but as I’m not familiar with refining schemas, I don’t know if it’s a good design decision.

B)

Riders

  1. Each full-time rider maintains a monthly work schedule (MWS) while each part-time rider maintains a weekly work schedule (WWS).

  2. In the FDS application, a week is defined to be a duration consisting of seven consecutive days, and a month is defined to be a duration consisting of four consecutive weeks.

  3. Each WWS specifies the hour intervals that the rider is available for work each day.

  4. For example, a rider’s WWS might specify the following work hours for six work days: for Monday to Thursday, the rider works for the hours 10am to 1pm, 4pm to 6pm, and 7pm to 10pm; and for Saturday and Sunday, the rider works for the hours 10am to 1pm and 5pm to 8pm.

What would be a good way to differentiate between a part-time weekly work schedule and full-time monthly work schedule, which is a duplicate of 4 identical weekly work schedules, the exact wording of my requirements are all shown in (B).

I follow my notation rather similarly to those quoted in R. Ramakrishnan & J. Gehrke, Introduction to database design, Database Management Systems, chapter 2. McGraw Hill, third edition, 2003. Do feel free to ask for more clarification

DBCA and can’t init database

SQL> startup; ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/u01/app/oracle/product/12.2.0.1/db_1/dbs/initAWSDB.ora' 

I created a new Oracle 12c database using dbca

the only files in the dbs directory

$   ls -ltrh total 24K -rw-r--r--. 1 oracle oinstall 3.1K May 15  2015 init.ora -rw-r-----. 1 oracle oinstall   24 Mar 22 23:10 lkCFRDB -rw-r-----. 1 oracle oinstall 3.5K Mar 22 23:13 orapwCFRDB -rw-rw----. 1 oracle oinstall 1.6K Mar 22 23:27 hc_CFRDB.dat -rw-r-----. 1 oracle oinstall 3.5K Mar 22 23:27 spfileCFRDB.ora -rw-rw----. 1 oracle oinstall 1.6K Mar 22 23:42 hc_AWSDB.dat 

When I tried to start up with init.ora

SQL> startup pfile=/u01/app/oracle/product/12.2.0.1/db_1/dbs/init.ora ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory 

How to get data from database week wise between two given dates?

I have a SQL database where i saved data of all dates in table. What i want is, If given any two dates, i want all the data between those two dates in weekly format where week starts on Monday. if given date starts with Thursday for example, so data from Thursday to sunday should be displayed as a week data.

I give you one example. If i choose start date 08-01-2020 and end date 08-03-2020. Now this is two months gap and there are probably 30 days. I want data of those 30 days, but week wise from Monday to Sunday. But here given start date is on Wednesday, so the that weeks data would start from wednesday and end on sunday.

Hope you got my point. Thanks in advance.