MYSQL :: Stats Not Logging

Stats Query - If You Are Pro, Help Me :D
I've taken over developing a statistics page for a client.
The statistics are recorded in a table named 'visitors'.
This table is about 35,000+ records at the moment.
I need to be able to display total site hits and new visitor hits for periods such as today, this week, month and year.

It creates a new record on every page thats visited on the website.
The table has 7 fields:
-----------------------------
ID
REMOTE_ADDR - logs visitors ip address
HTTP_USER_AGENT - logs browser
HTTP_REFERER - logs referal if exists
SESSION_ID - logs visitors session id
PAGE - logs page visited
DATE_ADDED - date record created - timestamp
-----------------------------

Currently the ID fields are used to test if the current remote_addr has a previous record. This is very resource intensive to test each 35,000 rows against each other. Can anyone suggest a better system or a solution?

Thanks in advanced!

I've added an example of the code the previous developer was working on.
The sql below is meant to split up the day by hour showing total hits and new visitors for a specific day.
I've tested the SQL and it times out on the new visitor 'if function'.

SELECT
CONCAT(
DATE_FORMAT(date_added, '%k'),
':00 - ',
DATE_FORMAT(date_added, '%k'),
':59'
) as `day`,

DATE_FORMAT(date_added, '%W %e %M %Y') AS act_day,

COUNT( DISTINCT REMOTE_ADDR ) AS un_total,
COUNT( DISTINCT session_id) AS site_hits,

COUNT(IF(
v1.REMOTE_ADDR IN (
SELECT v2.REMOTE_ADDR
FROM visitors AS v2
WHERE v2.id < v1.id
GROUP BY REMOTE_ADDR
),
NULL,
1
) ) AS un_new,

FROM visitors AS v1
WHERE DATE(date_added) = DATE('".$_GET['day']."')
AND page != 'REFERAL'
GROUP BY HOUR(date_added)
ORDER BY date_added DESC;

Index Use Stats
Is there some way to obtain some sort of statistics of how often, or how much is any given index used for queries? What I want to know is if an index is not used so it can be erased or changed, because it is being of no help, and just wastes space and slows down insertions.

Running Update Stats
I am trying to run an update stats command but keep getting a syntax error:
Looking at the doco it looks like I can use:

update stats <table_name>;

but when I use:

mysql> update stats x_balance;
ERROR 1064: You have an error in your SQL syntax near 'x_balance' at line 1

What am I doing wrong.

Running Stats On Data
I have a table (artist_nodes) that refers to a number of keywords (around 100) (nodes table) associated with a number of artists (artists table) i.e.

artist_nodes table

col 1 - id_artist_node (primary key)

col 2 nodeID_artist_nodes

col 3 artistID_artist_nodes

the content of each column is a number code that relates to the primary keys of content in the artists and nodes tables.

Is there a method for counting the number of distinct values in the nodeID_artist_nodes column, summing these values, and calculating how often values occur with each other?

So for example, if 1 = installation, and 45=interior and 46= exterior

i am looking for the number of instances of 1, 45, and 46, and also how often each occurs with the others when associated with the same artistID (as a percentage)

so the output i am looking for in php would be something like

installation (1) = 35 instances (3% of sample)

interior (45) = 23 instances (1% of sample)

exterior (46) = 30 instances (2%)

installation with interior = 10 instances (x %)
installation with exterior = 20 instances (y %)
installation with exterior and interior = 4 instances (z %)

is there a way of doing the inverse of this, i.e seeing which keywords never occur together?

Trying To Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received.

Creating this for the total views is straight forward and I have that working. But I want to add two more features to this:

1. See the total views for just today
2. See a history for the listing over the duration of the post (ie. 30 days).

I am at a lost how I would create a table or several tables to accomplish this. I am looking for some guidance to get me thinking about the best way to do this.

Post Stats For Every File Looked Up?
I have 909,000 plus files in my database. Is it possible to post the amount of look ups , stats, for every file looked up?

Example:
People searching my database via my web site.

If 700 people looked at a file, while viewing that file, it also tells you 700 look ups as part of the results? Code:

Conceptualize Table Design For Stats
I have a stats page my users can go to so they can view how much traffic their listing has received.Creating this for the total view stat is straight forward and I have that working. But I want to also add two more features to this:

CODE1. See the total views for just today
2.See a history for the listing over the duration of the post (ie. 30 days).

How To Setup A MySQL Table To Keep Track Of Stats?
I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

Table Design For Storing Item Stats
I've created quite the database for my app, and I'm familiar with normalization and general db design. However, I'm ready to design my tables for recording all sorts of stats for my db items.

I want to be able to view stats for each item, such as number of small views, number of large views, number of downloads, ratings and so on. I also want to be able to view these items by periods of time, such as by day, week, month, by multiple months, by year, etc. So, I'm trying to figure out what the most efficient way of recording this data, so that I can keep my tables as small as possible. I'm wondering if I will have to insert a row for every stat, or if I should enter a row for every day, or only a day if there is activity, etc.

Return Results Even If No Matching Row In Stats (was "Hmm, How To Get Around This?")
I have a MySQL query which works fine. This query references 3 tables, one of them (stats) may not have any entries for the particular user if they havent played any games. My issue is, if the user has no entry in the stats table the query doesnt work, and no results are returned. What would be the best way to get around this in MySQL so I get all the other information even if there is no entry for the stats table?

SELECT p.id
, p.player
, p.team
, p.mugshot
, sum(s.goals) as goals
, t.teamname
, t.logo
FROM league as p
JOIN (SELECT goals
, player_id
FROM stats) as s
on s.player_id = p.id
JOIN (SELECT id
,teamname
,logo
FROM teams) as t
on t.id = p.team
WHERE
p.player LIKE '$playerselect'
GROUP
BY p.id
, p.player;

Logging In
I'm having trouble logging into my sql server, I'm running version 4.0 just about anything I type in gives me this error message

"Error
SQL query:

mysql

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql' at line 1 "

Can anyone help me with this problem?

Logging
Is there a way to turn on some sort of logging? I am attempting to connect to a db through VB, just learned it and am not sure if I am getting in and not retrieving data or not even getting in yet. Can this be done through the console and then left on?

How Do I Set Query Logging
I have a situation where my database is deleting records.

I would like to enable logging but this produces a huge text file. Also I am not sure how to read the results.

I am using MySQL 3.23 (the IT department won't upgrade).

Can anyone tell me the most efficient way to set logging for a specific database? I just need to monitor whether a process is deleting these records.

Also how would I read this information?

Are there any known problems like this with MySQL 3.23?

IIS Logging To Mysql?
Can you setup mysql ODBC so that IIS can log into mysql? if so is there any good article/ instructions on the setup?

Query Logging?
I cannot get my query logging to work.

I put

log=hostname.log

in my.ini and at some point it WAS recording a log, but it since stopped. I started the service, stopped the service, did everything. I logged into my machine as admin, as the user, did it all again... cant get it to work.

I am running mysql 5 as part of the XAMPP stack. I installed it on this machine as admin and can run it as a service, but I cant get the log to work.

Any help would be appreciated. I really need to see what SQL is being executed.

MySQL 5 Logging
I've installed MySQL Server 5.0 on Win2K IIS as well as PHP 5.0. PHP works fine, and so does MySQL...the problem is that I can't get the two to talk to each other

Logging On For The First Time
I've downloaded the XP Windows standard version, installed it and logged on without a problem.

I've also downloaded the Linux standard version, installed it(Mandriva 2000 powerpack), and attempted to log on. However, I keep getting a message telling me that it can't find a socks in /var(whatever that is).

Logging In To Monitor Thru Dos
I am playing around with logging in and running commands through batch files on a win2000 machine running mysql, I have a password on my db, I am using mysql -h localhost -u root -p
to start up the monitor this works, but it asks for a password which kind of kills the automated part of what I am wanting to do, is there a way to include the password on this command? I thought about passing the password using %1, but I haven't done dos in so long that I dont even know if this feature still works, does anybody?

Logging Problem
I have a problem with mysql general log, regarding query logging. I'm
running Win2K, mysql 4.1.10 with JBoss 4.0.2. When I turn on logging,
the statements executed from JBoss and logged by mysql look like this:

SELECT uuid from tablex AS a WHERE a.id = ?;

MySQL never logs the parameters of the statements. Can somebody tell me
how to turn this on or what I am missing here?

How Do I Enable Logging?
I need to enable MySQL logging. I looked in the my.ini file and did not see any logging options. Where do I turn on logging? I am using Windows.

Logging Into Server
I have a linux box with MySQL.

The following fails. If I try to login locally using

mysql -u USERNAME -p

If I go to another computer on the network and try using the same user
name and password by:

mysql -h SERVER ADDRESS -u USERNAME -p

This login is successful.

Does anyone know why the first operation fails?

Logging With Trigger
I am designing a chatsystem with logging-functionality. And i want to handle the logging-stuff within a trigger. As a procedure the code works, so the syntax in ok. As a trigger i get no errors but nothing is logged.

create trigger messages_insert
before insert on messages
for each row
begin
declare a tinyint(1) unsigned;
select logging into a from users where id = 1;
if a = 1 then
insert into logfile set content = 'A';
end if;
end

Logging In / Cookies
I've been writing some code for site I'm creating, and I got stumped this far in. I have my login code, and it works, sorta - the page that displays right after logging in says that I'm logged in, but the I guess the session is destroyed after that.

I'm sort of fighting blindly here - I just started learning PHP a few months ago and don't know much about sessions and cookies yet. Here's the code that processes the login form; I originally borrowed it from an open-source browser-based RPG (which is why users are referred to as 'players'), but now only about lines 7-17 are the same. Also, it worked fine for that game, so I don't know what's wrong here. Code:

Logging Into Server
I am running Mac OS X 10.4 (PPC) I have installed MySQL server and have the use account mysql but I have no idea of the password. When the install ran it did not give me the opportunity to set a password and I can not find anywhere that states a default password.

Logging Doesn't Work
I have WinXP, and MySQL 5.0.18 running as a service. I followed the instructions for
setting up general query log file (mysqld --log), and the file was created (Nebo.log),
but it does not display any logs.
This is all I have after executing a few queries:

Nebo.log

mysqld, Version: 5.0.18-log. started with:
Tcp port: 3306 Unix socket: (null)
Time Id Command Argument

Can anyone offer any help?

Using A Database For Logging
I need to log the number of "brochure Requests" on a per Region, Per
date basis.

eg for say 20 Regions , have a 366 columns (one per day of the year).
Obviously I can write a script to Create all the tables, but it seems
kind of clumsy.
Do arrays exist in Mysql?
It might also be easier to organise it into 52 weeks of 7 days.

How do I design the tables?

Transaction Logging
I am reading up on transaction logging, and am wondering whether MySQL's binary log is its "UNDO/REDO log"? I'm a beginner to all this, and not sure whether that's the correct interpretation of the MySQL binary log.

How does MySQL's transaction logging, or any transaction logging mechanism, work? I was reading up on general transaction logging principles but it was getting far too specific to SQL Server and other DBMSs, so I figured that I could come here and someone here might point me to some MySQL-specific resources on this, or perhaps help to explain how this all works.

e.g. Does the transaction log get rid of all transactions once committed, what's the difference in how UNDO and REDO logs work, are these logs stored in a more complex manner than just logging the plain text statement (I'd imagine so, because otherwise how would an undo operation work)?

In fact, I find it funny that I'm asking all the questions above because I'm still not quite clear on what the purpose of transaction logs is in the first place!

While it would be great if somebody could help clear up the confusion I'm having, it would also be good if you could point me to some MySQL-specific resources on this topic. I browsed through the MySQL documentation and did a web search, but either did not find any resources period, or did not find resources that "dumbed it down" enough for me.

Logging Parameters
I can't see any useful-looking log files in my current localhost MySQL setup, and I can't see a way to specify logging in the Server Instance Configuration Wizard (1.0.8) either.

There are plenty of logging parameters listed in mysqld --help --verbose, but I'm not sure why I need to run that when I already have the service set up by the Wizard, which is working fine apart from logging. If I have to do it the mysqld way, how can I shut off the service, and is that a good idea anyway?

Replication Logging
Im wondering if there is some method to automatically "clean up" the binary logs left by replication even after they are used. For example, I have a number of mysql replication logs sitting on a server. There are about 25 of them, a GIG in size each.

My MYSQL replication is UP TO DATE. This means everything is properly sync'd. But I dont want to just 'DELETE' everything.

Logging On And Off MySQL
How does MySQL manage connections/sessions that are not formally and
properly closed?

What are the possible negative consequences if many of the
software/operators on the client side fail to properly close the sessions
they open? Will the MySQL server be able to handle this gracefully? Or will
they consume server resources until the system crashes?

Will the open sessions eventually time out close automatically?
Is there a timeout value that can be set on the server?

Can one query the server for information about open connections?

MySQL Logging Out
I'm not sure if this is related to MySQL or just happens to be occuring with MySQL on the Linux box, but MySQL will -- at random intervals -- log out without my explicit action and be followed by this message

[X]+ Stopped mysql -u <user> -p <database>
(where X is a number)

Now I don't have admin or root access to this server, but is there anything I can do or talk about with the administrator?

Error Logging
I'd like to be able to capture errors and write them to an error log table from the stored procedure. This is fairly simple in MS SQL ... I haven't found much information about how to do it in MySQL. Has anyone else run into this?

Turning On Logging
I have a database that has various information submitted to it from the internet. I am having problems with people hacking/submitting info that shouldn't be submitted. I don't know how they are doing it but I though that if I turned on the Logging for that particular DB maybe it would give me some clues i.e. IP addresses, ISP, date/time info, SQL Statement executed etc.What form of logging would I need to turn on?

Logging On To MySQL - Problem
I'm trying to log on to MySQL for the first time. I only installed MySQL and PHP for the first time yesterday.

When I click on the mysql.exe (in C:Program FilesMySQLMySQL Server 5.0in) nothing happens.

I thought I was meant to get a command prompt to type in my username and pw. The following actually happens to me: I double click on it, the command box opens for about 2 seconds and then closes down.

Statistics Logging, Too Big Of A Table?
If you are logging hits/statistics in a table by each page view this logging/statistics table will grow very large.

Logging Record Deletions
I want to create a script to monitor when deletions are taking place from my database.

I thought something like using SQL count to check the number of records every minute so I can log when authorised deletions are occuring.

Server Log Not Logging All Queries
I started the general query log with

mysqld --log

On my machine this command works fine and every select, update and all
statements are logged. I tried the same command on another machine and
many many queries are missing, I am running apache, PHP on that machine
and all the queries come through the web on localhost.

I wonder why this is happening?

Mysql Logging Compiled In?
i'm trying to turn on mysql logging as follows, but it doesn't seem to
work: mysqld --log=/root/log

the logfile never shows up after a connection or query. so i want to
know if logging support is compiled into mysql. i did not install it
and i cannot ask the hoster. does anyone know how to check that? i
don't even know where to begin.

Mysql Logging Compiled In?
i'm trying to turn on mysql logging as follows, but it doesn't seem to work:

mysqld --log=/root/log

the logfile never shows up after a connection or query. so i want to
know if logging support is compiled into mysql. i did not install it
and i cannot ask the hoster. does anyone know how to check that? i
don't even know where to begin.

User Command Logging?
Is there a way to log what selects, inserts, updates, deletes various users do along with a timestamp of when? Something like a shell history of what the last 50 or so commands are that they ran.

Logging Queries On MYSQL
I am running Red Hat and MySQL, I am needing to make it that mysql logs all
queries done on the server, at the moment my /etc/my.cnf file looks like
this.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

What do I need to do to accompish this , I have looked on google and just
cant find the answer at all to this.

MySql Change Logging
Is it possible for me to have the daily updation and insert queries
performed on the MySQL DB saved? This will help me track when a
particular record was modified in the DB.

Is there any other way to track as to when the changes were made to the
DB? I also want to add who made the changes but this will come at a
later stage once the user login is implemented.

Logging On From Network Workstation
Having trouble logging on to the server from a workstaion on the network. Have added myself as a global user and the workstation as a host. MySql is listening on port 3306 but I can't make a connection. I get

[bob@Comp1 bob]$ mysql -h 192.168.0.2 -u bob -p
Enter password:

ERROR 1045: Access denied for user: 'bob@Comp1.home.wtaustralia.com' (Using password: YES)

I am using Mandrake10.0 and Mysql 4.0.18 I can connect using ssh but not as a remote user.

Temporarily Switch Off Logging
I am running MySQL v3.23.52-log on SuSI v8.0 platform.

log_bin is set ON.

How can I temporarily switch off logging?

How Do I Enable Binary Logging
I would like to enable binary logging on my database.Am using winXP The information i have talks of adding some lines to my.cnf file then restarting mysql.

Logging MySql Statements
My server load goes up to 100 and I don´t know why. What I know, if I
shot down the site, the server calms down and it works again.

I have figured out that i have a mysql problem but I can t figure it
out.

Is it possible to log all MySql Statements which are running on my
database ? If the server goes strange again I could check the log file
for the problem.

Logging Using The Mysql Command
Unfortunately, I was experimenting and I changed the field Host in table User from localhost to %. Now I can no longer logon via the mysql -u root command. It wants to log on as root@localhost and I want to log on as root@%. How do I fix my mistake?

Can't Limit /var/log/mysql Logging
Mysql is filling my /var partition because the log limit doesn't seem
to be respected.

I've tried many different settings in /etc/mysql/my.cnf but nothing
works.

Logrotate is not enabled/configured. I just want mysql to use a maximum
of, let's say, 1GB of my /var partition

The log is /var/log/mysql/
Debian GNU/Linux 4.0
kernel 2.4.27-2-386
mysql version 5.0.30-Debian_1-log

Following the /etc/mysql/my.cnf file Code: